Skip to content
Tutorials Center
My learning

Topics

Lesson 44 of 55 ยท Working with Computers

Spreadsheets and Data Basics

Learn how spreadsheets organize data in rows and columns, how formulas, sorting and filtering work, what CSV files are, how databases differ, and how to clean messy data.

Almost every kind of information that people manage, from a household budget to a hospital's patient list, ends up as a table: rows of things and columns of facts about them. A spreadsheet is a program built around that table. You type values into cells, write formulas that calculate from other cells, and the results update on their own when anything changes.

This lesson explains how spreadsheet tables are organized, how formulas, sorting and filtering work, what a CSV file is, how a database goes beyond a spreadsheet, and how to clean data that arrives messy. Excel, Google Sheets, LibreOffice Calc and Numbers differ in details, but the ideas are shared. The examples use Python running in your browser, written to show what a spreadsheet does behind the scenes. They build on the text and number ideas from Text and Character Encoding and Bits and Bytes.

Rows, columns and cells

A spreadsheet is a grid. Columns are named with letters (A, B, C ...), rows with numbers (1, 2, 3 ...), and each box where they cross is a cell with an address such as B3 (column B, row 3). A group of cells is a range, written B2:B6 for "B2 down to B6".

A well-built table follows a few habits that make everything else easier:

  • The first row holds headers: short, unique names like Product, Units, Price.
  • One row is one record (one sale, one student, one measurement).
  • One column holds one kind of value. Do not mix numbers and notes like "about 5" in the same column.
  • No blank rows or columns inside the table, and no merged cells in the data area.

Tables built this way are called tidy, and they can be sorted, filtered, summarised and imported into other programs without trouble. Beautiful but irregular layouts, with merged titles, colored subtotals and notes in the margins, are fine for a printout but difficult for software.

A cell holds a data type: a number, text, a date, or a true/false value. The type matters, because the sum of 5 and 3 is 8, but the text "5" and "3" side by side is just 53. A common beginner problem is a number stored as text, which then refuses to add up. Typically such cells are left-aligned, or have a small green marker.

Formulas and functions

A formula starts with =. It can use numbers, cell addresses and functions:

TEXT
=B2*C2                 multiply the units in B2 by the price in C2
=SUM(D2:D6)            add up a range
=AVERAGE(C2:C6)        the mean of the range
=IF(D2>100, "big", "small")
=COUNTIF(A2:A6, "Tea") how many cells equal Tea

Formulas refer to cells, not to values, so changing B2 re-calculates everything that depends on it. A spreadsheet therefore keeps a dependency graph of which cells use which. When you copy a formula down a column, the references shift along: =B2*C2 becomes =B3*C3. This is a relative reference. To pin a reference, write $ in front of the part that must not move: $C$1 always means C1. Mixing them up is the single most common spreadsheet bug.

The function names above are those of Excel and Google Sheets; other programs use the same names almost everywhere. Spreadsheets cannot show every error politely, so you meet messages like #DIV/0! (division by zero), #REF! (a reference to a deleted cell), #NAME? (a misspelled function) and #VALUE! (the wrong type, such as text in arithmetic). Each tells you where to look.

CSV: the plain-text table

How do you move a table from one program to another? The most widely supported way is a CSV file, short for comma-separated values. It is plain text: one line per row, and the values on a line separated by commas.

TEXT
product,units,price
Tea,12,3.50
Coffee,8,4.25
Cocoa,5,5.00

The first line is the header. CSV has no colors, formulas, several sheets or column widths, just the values, which is why almost any program can read it. A few rules cause most of the trouble:

  • A value that contains a comma or a line break is wrapped in double quotes: "Smith, Jane".
  • A double quote inside a quoted value is written twice: "say ""hi""".
  • Some countries use a semicolon as the separator because the comma is their decimal mark.
  • The text encoding must match on both sides; UTF-8 is the safe choice, and otherwise accented letters come out garbled (the same problem described in Text and Character Encoding).
  • Everything in a CSV is text until a program decides otherwise, so leading zeros in a code such as 00742 may disappear when a spreadsheet "helpfully" turns it into the number 742.

The first stand-in reads a CSV with Python's built-in csv module, converts the types, works out a total column, then sorts, filters and groups. Each step matches a spreadsheet feature: a computed column (=B2*C2), sort, filter and a pivot table-style summary.

Python CSV to totals, sort, filter and group

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Note that a program must convert each value from text to a number before doing arithmetic. A spreadsheet does the same silently when you open the file, and it sometimes guesses wrong. When it does, you see "numbers" that will not add up.

From spreadsheet to database

Spreadsheets are excellent for a few thousand rows that one person edits. They struggle when many people edit at once, when the data has several linked tables, or when the data runs to millions of rows. A database is the next step. Instead of cells in a grid, it stores tables with defined columns and types, and you ask questions in a query language such as SQL.

Why use one?

  • Structure and rules: a column can be required to be a number, a date, or unique.
  • Relationships: a customers table and an orders table are linked by a customer id, so a customer's address is stored once instead of copied into every order.
  • Many users: the database keeps simultaneous edits from corrupting each other.
  • Scale: indexes let it find one row among millions quickly.

The idea of "filter, sort, group and total" stays the same, so spreadsheet skills carry over. Python includes SQLite, a small database engine in a library. The second stand-in loads the same kind of table into an in-memory database and asks the same questions in SQL. Tools for learning SQL go much deeper.

Python The same questions as SQL queries

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Cleaning messy data

Real data is rarely tidy. It arrives with extra spaces, "Tea", "tea" and "TEA" as three products, numbers like 1,200 or $5.00, empty cells, duplicate rows and typos. Data cleaning repairs these problems before analysis. If you skip it, a total can be wrong without any error message at all. That is the nasty part: bad data produces confident-looking answers.

A sensible cleaning routine:

  1. Look first. Scan the columns, count rows, find the smallest and largest values.
  2. Trim and normalize text: remove surrounding spaces and use a single capitalization.
  3. Parse numbers and dates into proper types. Decide what to do with values that cannot be parsed.
  4. Handle missing values deliberately: leave them out, fill them with a sensible default, or go back to the source. Never silently turn blank into zero if blank means "unknown".
  5. Remove duplicates, after normalizing, because duplicates hide behind spelling differences.
  6. Keep the original. Work on a copy and record what you changed.

The next stand-in cleans a small messy table following exactly those steps and reports what it did, so the changes can be checked rather than trusted.

Python Cleaning a messy table

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The code does not guess at "Cocoa" or "Bagel": rows that cannot be repaired are set aside and reported. Whether to fix, drop or ask the source is a human decision, and a clean-up that hides its decisions is risky.

What is real and what is the stand-in? Rows and columns, formulas, CSV rules, SQL and the cleaning steps are the real concepts. The Python above is a simple model of what a spreadsheet engine or database does; real spreadsheets also track dependencies between cells and recalculate in the right order, and real databases add indexes, transactions and an optimizer.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does the cell address C4 mean?

    Columns are lettered and rows are numbered; C4 is where column C and row 4 cross.
  2. 2. You copy =B2*C2 from row 2 to row 3. What does the copy contain if the references are relative?

    Relative references shift with the formula. Dollar signs would pin them.
  3. 3. Which statement about CSV files is true?

    CSV keeps only the values, which is why nearly any program can read and write it.
  4. 4. Why is a database often better than a spreadsheet for linked data with many users?

    Typed columns, keys between tables and controlled concurrent access are what databases add.
  5. 5. A column of prices contains $4.25, 5.00 and n/a. What is the best first step?

    Convert what can be converted and treat unparseable values deliberately; silent zeros can distort totals.

Summary

  • A spreadsheet is a grid of cells addressed by column letter and row number; tidy tables have a header row and one record per row.
  • Formulas start with =, use cell references and functions, and recalculate automatically; relative and $ absolute references behave differently when copied.
  • CSV is the universal plain-text table; watch quoting, separators, encoding and values that lose leading zeros.
  • Databases add typed columns, linked tables, safe multi-user access and speed; SQL asks the same filter, sort and group questions.
  • Cleaning means trimming, normalizing, parsing, handling missing values and duplicates, and keeping the original.

Time to put what you know together: start with Project: A Binary Calculator.