# Excel From Zero

> Learn Excel from scratch: what cells and formulas really are, how recalculation works, relative vs absolute references ($A$1), core functions, formatting vs values, sorting, filtering, Tables, and what every error value means.


---

# Excel From Zero

You have opened Excel, typed some numbers, maybe used SUM because someone told you to. Then a formula you copied down a column gave nonsense, a total ignored half your data, or a cell filled with `#NAME?` and you had no idea what you did wrong. Most of that confusion comes from not knowing what Excel is actually doing with what you type.

This guide builds the working model first: a cell holds either a value or a recipe, recipes recalculate by themselves, and a reference is an instruction that changes when you copy it. With that in your head, formulas stop being spells you memorize. You will follow one small shop's sales list from an empty sheet to a clean Excel Table.

Checked against Excel for Microsoft 365 on Windows. Where Mac or older versions (2019, 2021) behave differently, the text says so.

## How to read this

- **In a hurry?** Jump to [Phase 2](02-cell-references.md) for the `$` sign, or [Phase 5](05-tables-and-errors.md) for Tables and the error cheat-card.
- **Want it to finally make sense?** Read in order. Each phase reuses the sales list from the one before, so type it in once and keep the file.

## The phases

1. **[Workbooks, Cells, and Formulas](01-workbooks-cells-and-formulas.md)** - what a cell really holds, how a formula recalculates, and the sales list you will build on.
2. **[Cell References: Relative, Absolute, Mixed](02-cell-references.md)** - what happens when you copy a formula, and what `$` is for.
3. **[Ranges and Everyday Functions](03-ranges-and-everyday-functions.md)** - SUM, AVERAGE, COUNT, COUNTA, MIN, MAX, and ROUND, and what each one ignores.
4. **[Formatting, Sorting, and Filtering](04-formatting-sorting-filtering.md)** - why a formatted 0.5 is still 0.5, and how to reorder and narrow data without breaking it.
5. **[Tables and Error Values](05-tables-and-errors.md)** - why Ctrl + T beats a loose range, structured references, and a cheat-card for `#DIV/0!`, `#NAME?`, `#REF!`, `#VALUE!`, and `#N/A`.

## Where to go next

Once this feels solid, [Excel Formulas That Do Real Work](/guides/excel-formulas-that-do-real-work) covers lookups and conditional math, and [Excel Pivot Tables and Charts](/guides/excel-pivot-tables-and-charts) summarizes data in a few clicks. If your data is outgrowing a spreadsheet, read [Spreadsheets to SQL to Pipelines](/guides/spreadsheets-to-sql-to-pipelines), and for dashboards see [Power BI From Zero](/guides/power-bi-from-zero).


---

# Workbooks, Cells, and Formulas

Excel looks like a grid of boxes where you type things. That is true and hides the one idea that matters: some boxes hold what you typed, and some hold an instruction that Excel re-runs every time anything it depends on changes. Know which is which and you can read any spreadsheet someone hands you.

## The three containers

- A **workbook** is the file (`.xlsx`). It is the whole thing you save and email.
- A **worksheet** (or sheet) is one grid inside the workbook. The tabs along the bottom are sheets.
- A **cell** is one box in the grid. Its address is its column letter followed by its row number, so `C4` is column C, row 4.

A sheet has 1,048,576 rows and 16,384 columns (the last column is `XFD`). You will never fill that, but it explains why Excel talks about "the whole column" so casually.

Three screen parts to know:

- The **Name Box** (left of the formula bar) shows the address of the selected cell.
- The **formula bar** shows what is actually stored in the selected cell.
- The cell itself shows the **displayed** result.

Those last two can differ, and that gap is the heart of this phase.

## A cell holds a value or a formula

Click a cell and type. When you press Enter, Excel stores one of two things:

| You type | Excel stores | The cell shows |
|---|---|---|
| `42` | the number 42 | 42 |
| `Notebook` | the text "Notebook" | Notebook |
| `=2+3` | a formula | 5 |

A **formula** is anything that starts with `=`. The cell displays the result, and the formula bar keeps the recipe. Everything without a leading `=` is a **constant**: a number, text, a date, or TRUE/FALSE.

Excel decides the type from what you typed, and it shows you its decision through alignment. By default, numbers and dates sit on the right of the cell and text sits on the left. If a number you typed hugs the left edge, Excel stored it as text, and arithmetic functions will not treat it as a number. Phase 3 shows what that costs you.

Dates are numbers in disguise. Excel counts days from a starting point, so 1 October 2026 is stored as 46296 and merely displayed as a date. Pressing `Ctrl + ;` enters today's date.

Four keys you will use constantly:

- **Enter** confirms and moves down. **Tab** confirms and moves right.
- **Esc** cancels what you are typing.
- **F2** edits the selected cell without retyping it.
- **Ctrl + Z** undoes.

## Formulas do arithmetic and obey an order

In a formula, `+` adds, `-` subtracts, `*` multiplies, `/` divides, `^` raises to a power, and `%` turns a number into a percentage. Multiplication and division happen before addition and subtraction, like school math. Parentheses override the order.

| Type this | Result | Why |
|---|---|---|
| `=2+3*4` | 14 | 3*4 first, then add 2 |
| `=(2+3)*4` | 20 | parentheses first |
| `=10/4` | 2.5 | plain division |
| `=2^3` | 8 | 2 to the power of 3 |

⚠️ **Gotcha.** The `=` is not optional. Type `2+3` without it and Excel stores the text "2+3" and shows exactly that.

## The real power: formulas point at cells

A formula that only does `2+3` is a calculator. The power comes from using **cell addresses** in place of numbers. `=D2*E2` means "take whatever is in D2, multiply by whatever is in E2". Excel does not remember the answer. It remembers the instruction, and it re-runs the instruction when D2 or E2 changes.

Build the example you will carry through this guide. A small stationery shop logs sales. Type this into a new sheet, headers in row 1, starting at `A1`:

| | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Date | Item | Category | Qty | Price |
| 2 | 2026-10-01 | Notebook | Paper | 3 | 4.50 |
| 3 | 2026-10-01 | Pen Pack | Writing | 5 | 2.20 |
| 4 | 2026-10-02 | Stapler | Office | 1 | 12.00 |
| 5 | 2026-10-02 | Notebook | Paper | 2 | 4.50 |
| 6 | 2026-10-03 | Marker Set | Writing | 4 | 6.25 |
| 7 | 2026-10-03 | Desk Lamp | Office | 1 | 18.90 |
| 8 | 2026-10-04 | Pen Pack | Writing | 10 | 2.20 |
| 9 | 2026-10-04 | Sticky Notes | Paper | 6 | 1.75 |

In most settings Excel reads `2026-10-01` as a date and right-aligns it. If yours sits on the left, it was stored as text, so try your region's style, such as `10/1/2026` in the US.

Now add a **Total** column. In `F1` type `Total`. In `F2` type:

```excel
=D2*E2
```

Press Enter. Row 2 is 3 notebooks at 4.50, so F2 shows 13.5. Click F2 and look at the formula bar: it still says `=D2*E2`. The cell shows the answer, the bar holds the recipe.

## Recalculation: change an input, the answer follows

Click `D2`, type `4`, press Enter. F2 now shows 18 without you touching it.

```mermaid
flowchart LR
  D2["D2: Qty = 4"] --> F2["F2: =D2*E2"]
  E2["E2: Price = 4.50"] --> F2
  F2 --> R["Shows 18"]
```

Excel tracks which cells depend on which. When an input changes, every formula downstream recalculates automatically. This is the mental model for the whole product: **constants are the facts you enter, formulas are the answers that follow from them.** Never type an answer you could compute, because a typed answer goes stale the moment an input changes.

*What just happened:* you edited a fact (Qty), and Excel re-ran the one recipe that depended on it. Nothing else changed because nothing else depended on D2.

Calculation is automatic by default. If a workbook ever stops updating, it may be set to manual (Formulas tab, Calculation Options). Pressing `F9` recalculates on demand.

Set D2 back to `3` before moving on. In Phase 2 you will copy `=D2*E2` down the column and see why it works.

Try these before the quiz:

```exercise
[
  {
    "type": "predict",
    "task": "A1 contains 6 and B1 contains 7. What number does the formula =A1*B1+1 display?",
    "accept": ["43"],
    "hint": "Multiplication happens before addition."
  },
  {
    "type": "predict",
    "task": "Same cells, but you now change A1 to 10. What does =A1*B1+1 display?",
    "accept": ["71"],
    "hint": "Excel re-runs the recipe with the new input."
  }
]
```

Check yourself before moving on:

```quiz
[
  {
    "q": "You click a cell and the formula bar shows =D2*E2 while the cell shows 13.5. What is actually stored in the cell?",
    "choices": ["The number 13.5", "The formula =D2*E2, which Excel evaluates to show 13.5", "The text =D2*E2"],
    "answer": 1,
    "explain": "A cell with a leading = stores the recipe. The displayed number is the current result, recomputed whenever D2 or E2 changes."
  },
  {
    "q": "You type 250 into a cell and it appears on the left edge of the cell. What does that most likely mean?",
    "choices": ["Excel stored it as text, not as a number", "Excel rounded it", "The column is too narrow"],
    "answer": 0,
    "explain": "By default numbers align right and text aligns left, so a left-aligned number was stored as text."
  },
  {
    "q": "What does =2+3*4 display?",
    "choices": ["20", "14", "24"],
    "answer": 1,
    "explain": "Multiplication runs before addition: 3*4 is 12, plus 2 is 14. Use =(2+3)*4 for 20."
  }
]
```

## Recap

1. A workbook is the file, a worksheet is one grid in it, a cell is one box addressed by column letter plus row number.
2. A cell holds either a constant you typed or a formula that starts with `=`.
3. The formula bar shows what is stored, the cell shows the displayed result.
4. Numbers align right and text aligns left by default, which tells you how Excel read your entry.
5. Formulas that use cell addresses recalculate automatically when their inputs change.
6. Enter facts as constants and let formulas compute everything else.

Next up, [Phase 2: Cell References](02-cell-references.md): what happens when you copy `=D2*E2` down a column, and how `$` changes it.


---

# Cell References: Relative, Absolute, Mixed

You write one formula, drag it down, and it works for every row. Then you try a formula with a tax rate in one cell, drag it down, and every row after the first shows zero. Both behaviors come from the same rule, and once you see the rule you can predict what any copied formula will do.

## A reference is directions, not a street address

By default a reference like `D2` does not mean "the cell at D2 forever". It means "the cell two columns to my left on this same row", measured from where the formula lives. This is called a **relative reference**.

When you copy the formula, Excel keeps the *directions* and recomputes the *destination*. Copy `=D2*E2` from F2 to F3 and the directions "two left, one left, same row" now land on row 3, so you get `=D3*E3`.

Try it on your sales list. Click `F2`, press `Ctrl + C`, select `F3:F9`, press `Ctrl + V`:

| Cell | Formula after paste | Shows |
|---|---|---|
| F2 | `=D2*E2` | 13.5 |
| F3 | `=D3*E3` | 11 |
| F4 | `=D4*E4` | 12 |
| F5 | `=D5*E5` | 9 |
| F6 | `=D6*E6` | 25 |
| F7 | `=D7*E7` | 18.9 |
| F8 | `=D8*E8` | 22 |
| F9 | `=D9*E9` | 10.5 |

Faster ways to do the same copy:

- **Fill handle.** Select F2. The small square at its bottom-right corner is the fill handle. Drag it down, or double-click it to fill as far down as the neighboring column has data.
- **Ctrl + D** fills the top cell of a selection down through the rest of it. **Ctrl + R** fills right.

Relative references are the default because most spreadsheet work is "do the same thing to every row".

## When relative breaks: the tax rate

Now add sales tax. In `I1` type `Tax rate` and in `J1` type `0.08` (8 percent). Put `Tax` in `G1`. In `G2` type a formula that multiplies the row's total by the rate:

```excel
=F2*J1
```

G2 shows 1.08. Now fill it down to G9. Every row below shows 0. Click G3 and read the bar:

| Cell | Formula | Why |
|---|---|---|
| G2 | `=F2*J1` | correct, 13.5 * 0.08 = 1.08 |
| G3 | `=F3*J2` | J2 is empty, counts as 0 |
| G4 | `=F4*J3` | J3 is empty, 0 again |

You wanted `F` to move down with each row, which it did. But `J1` also moved down, because "three columns to the right and one row up" is a relative direction, and from G3 that points at J2. The tax rate sits in one fixed cell, so that reference must not move.

## Absolute references: the dollar sign pins it

A `$` in front of a column letter or row number locks that part. `$J$1` means "column J, row 1, always", wherever you copy it. Fix G2:

```excel
=F2*$J$1
```

Fill down again:

| Cell | Formula | Shows |
|---|---|---|
| G2 | `=F2*$J$1` | 1.08 |
| G3 | `=F3*$J$1` | 0.88 |
| G4 | `=F4*$J$1` | 0.96 |
| G5 | `=F5*$J$1` | 0.72 |
| G6 | `=F6*$J$1` | 2 |
| G7 | `=F7*$J$1` | 1.512 |
| G8 | `=F8*$J$1` | 1.76 |
| G9 | `=F9*$J$1` | 0.84 |

*What just happened:* `F2` stayed relative and followed the row, `$J$1` stayed pinned, and changing J1 to `0.1` now updates the whole Tax column at once. That is the reason to put a rate in its own cell instead of typing 0.08 into every formula: one place to change, no hunting.

## Four forms of one reference

You can lock the column, the row, both, or neither. The `$` sits right before the part it locks.

| Written | Column | Row | Name |
|---|---|---|---|
| `A1` | moves | moves | relative |
| `$A$1` | locked | locked | absolute |
| `A$1` | moves | locked | mixed (row locked) |
| `$A1` | locked | moves | mixed (column locked) |

Instead of typing dollar signs, put the cursor in a reference while editing and press `F4`. Each press cycles `A1`, `$A$1`, `A$1`, `$A1`, then back to `A1`. On a Mac, Microsoft lists `Command + T` or `F4` (some keyboards need `Fn + F4`).

## Mixed references: one formula, a whole grid

Mixed references earn their keep when a formula must fill both down and across. On a spare sheet, build a multiplication grid: put `1`, `2`, `3` in `B1:D1` (across the top) and `10`, `20`, `30` in `A2:A4` (down the side). In `B2` type:

```excel
=$A2*B$1
```

Read it: `$A2` means "column A is locked, the row follows me", so each row picks its own side number. `B$1` means "row 1 is locked, the column follows me", so each column picks its own top number. Fill `B2` across to `D2`, then down to row 4:

| | A | B | C | D |
|---|---|---|---|---|
| 1 | | 1 | 2 | 3 |
| 2 | 10 | 10 | 20 | 30 |
| 3 | 20 | 20 | 40 | 60 |
| 4 | 30 | 30 | 60 | 90 |

`D4` holds `=$A4*D$1`, which is 30 * 3 = 90. A single formula built the whole table, which is impossible with only relative or only absolute references.

## Copy versus move

Copying a formula adjusts its relative references. **Cutting** and pasting (`Ctrl + X`, `Ctrl + V`) does not: a moved formula keeps pointing at exactly the same cells. If you move a cell that other formulas refer to, those formulas follow it to its new home. Use copy when you want the pattern repeated, cut when you want the same thing relocated.

## References to other sheets

To use a cell from another sheet, write the sheet name, an exclamation mark, then the cell: `=Sheet2!B4`. If the sheet name has a space, wrap it in single quotes: `='Price List'!B4`. The same `$` rules apply.

Test yourself:

```exercise
[
  {
    "type": "predict",
    "task": "G2 contains =F2*$J$1. You copy G2 into G5. Type the formula that G5 now contains, starting with the equals sign.",
    "accept": ["=F5*$J$1", "F5*$J$1"],
    "hint": "F is relative and moves down 3 rows. $J$1 never moves."
  },
  {
    "type": "predict",
    "task": "B2 contains =$A2*B$1. You copy B2 into D4. Type the formula D4 now contains, starting with the equals sign.",
    "accept": ["=$A4*D$1", "$A4*D$1"],
    "hint": "Columns shift by 2 and rows shift by 2, but only the parts without a dollar sign move."
  }
]
```

Check yourself before moving on:

```quiz
[
  {
    "q": "A formula in F2 reads =D2*E2. You copy it to F7. What does F7 contain?",
    "choices": ["=D2*E2", "=D7*E7", "=D7*E2"],
    "answer": 1,
    "explain": "Relative references keep their directions, not their destination, so both references move down five rows."
  },
  {
    "q": "Which reference keeps the column fixed but lets the row change when copied?",
    "choices": ["A$1", "$A1", "$A$1"],
    "answer": 1,
    "explain": "The dollar sign locks the part right after it. $A1 locks column A and leaves the row free. A$1 does the opposite."
  },
  {
    "q": "You copy =B$2 from C5 to D7. What does D7 contain?",
    "choices": ["=C$2", "=C4", "=B$4"],
    "answer": 0,
    "explain": "The column is relative and moves one to the right (B becomes C). The row is locked by the dollar sign and stays 2."
  }
]
```

## Recap

1. A plain reference like `D2` is a relative direction from the formula's own cell, so copying shifts it.
2. `$` locks the part that follows it: `$J$1` locks both, `J$1` locks the row, `$J1` locks the column.
3. Press `F4` while editing a reference to cycle through the four forms.
4. Put constants like a tax rate in their own cell and refer to them with an absolute reference.
5. A mixed reference lets one formula fill correctly both down and across.
6. Copy adjusts relative references; cut and paste does not.

Next up, [Phase 3: Ranges and Everyday Functions](03-ranges-and-everyday-functions.md): summarizing many cells at once.


---

# Ranges and Everyday Functions

Adding eight numbers by typing `=F2+F3+F4+F5+F6+F7+F8+F9` works until the list has eight hundred. Functions and ranges let one short formula summarize any number of cells, and knowing precisely what each function counts and ignores is what keeps a total from being quietly wrong.

## What a range is

A **range** is a rectangular block of cells, written as its top-left cell and bottom-right cell with a colon between them. Read the colon as "through".

| Written | Means |
|---|---|
| `F2:F9` | F2 through F9, a column of 8 cells |
| `A1:C1` | A1 through C1, a row of 3 cells |
| `A1:C3` | a 3 by 3 block, 9 cells |
| `F:F` | all of column F |

Click and drag over cells while typing a formula, or hold `Shift` and use the arrow keys, and Excel writes the range for you.

## What a function is

A **function** is a named, ready-made recipe. You give it inputs inside parentheses, called **arguments**, and it hands back a result. The shape is always:

```excel
=NAME(argument1, argument2, ...)
```

Arguments are separated by commas in US-style settings. Some regional settings use semicolons instead, so if a formula from a tutorial refuses to enter, try that. As you type a function name, Excel suggests matches; press `Tab` to accept one.

## The core seven

All examples use the Total column from your sales list: `F2:F9` holds 13.5, 11, 12, 9, 25, 18.9, 22, 10.5.

| Function | What it does | Formula | Result |
|---|---|---|---|
| SUM | adds the numbers | `=SUM(F2:F9)` | 121.9 |
| AVERAGE | adds, then divides by how many numbers | `=AVERAGE(F2:F9)` | 15.2375 |
| COUNT | counts cells holding numbers | `=COUNT(F2:F9)` | 8 |
| COUNTA | counts cells that are not empty | `=COUNTA(B2:B9)` | 8 |
| MIN | the smallest number | `=MIN(F2:F9)` | 9 |
| MAX | the largest number | `=MAX(F2:F9)` | 25 |
| ROUND | rounds to a number of decimals | `=ROUND(F7*1.08, 2)` | 20.41 |

Check one by hand: 13.5 + 11 + 12 + 9 + 25 + 18.9 + 22 + 10.5 = 121.9, and 121.9 divided by 8 numbers is 15.2375.

The fastest way to total a column is `Alt + =`, the AutoSum shortcut. Excel guesses the range above or beside the cell, so read the highlighted range before pressing Enter.

You can also select cells and glance at the status bar at the bottom of the window: it shows Sum, Average, and Count for the selection without writing any formula.

## ROUND: choosing the decimals

`ROUND(number, num_digits)` rounds `number` to `num_digits` decimal places. A positive number keeps decimals, `0` rounds to a whole number, and a negative number rounds to the left of the decimal point.

| Formula | Result |
|---|---|
| `=ROUND(2.15, 1)` | 2.2 |
| `=ROUND(15.2375, 2)` | 15.24 |
| `=ROUND(15.2375, 0)` | 15 |
| `=ROUND(21.5, -1)` | 20 |

Using it on a formula result nests one function inside another: `=ROUND(AVERAGE(F2:F9), 2)` gives 15.24. ROUND changes the stored value, which matters in the next phase.

## What each function ignores

This is where wrong totals come from. Suppose column A holds this, with `A6` typed as `'40` (a leading apostrophe forces text) and `A4` left empty:

| Cell | Holds |
|---|---|
| A1 | 10 |
| A2 | 20 |
| A3 | n/a (text) |
| A4 | (empty) |
| A5 | 30 |
| A6 | 40 stored as text |

| Formula | Result | Why |
|---|---|---|
| `=SUM(A1:A6)` | 60 | text and empty cells are skipped |
| `=COUNT(A1:A6)` | 3 | counts only real numbers: A1, A2, A5 |
| `=COUNTA(A1:A6)` | 5 | counts everything non-empty, including the text |
| `=AVERAGE(A1:A6)` | 20 | 60 divided by the 3 numbers |

Notice the text "40" in A6 never reached the total. SUM, AVERAGE, COUNT, MIN, and MAX read only true numbers from a range. That is why a column of numbers that "will not add up" is almost always a column where some cells are text. Fix that in Phase 4.

COUNT and COUNTA answer different questions. COUNT is "how many numbers", COUNTA is "how many filled cells". COUNTA also counts error values and cells holding an empty string `""` returned by a formula.

**Empty is not zero.** Take `10`, an empty cell, and `0`. `AVERAGE` of those three cells is 5, because it skips the empty cell and averages 10 and 0. If the empty cell held a 0 it would be 3.33. The empty cell is ignored, a zero is counted.

Averaging nothing is an error: `=AVERAGE(A1:A3)` over three empty cells returns `#DIV/0!`, because there is no number to divide by. Phase 5 covers errors.

## The loose-range trap

`=SUM(F2:F9)` is a loose range: Excel remembers rows 2 to 9 and nothing else. Add a new sale in row 10 and the sum does not change. Inserting a row *inside* the range works, because the range stretches. Adding below the last row does not. This is the main reason Phase 5 turns the list into a Table.

Try these, using the A1:A6 values above:

```exercise
[
  {
    "type": "predict",
    "task": "Column A holds 10, 20, the text n/a, an empty cell, 30, and the text 40 (typed with a leading apostrophe). What does =COUNTA(A1:A6) return?",
    "accept": ["5"],
    "hint": "COUNTA counts every cell that is not empty, whatever it contains."
  },
  {
    "type": "predict",
    "task": "Cells B1, B2, B3 hold 10, an empty cell, and 0. What does =AVERAGE(B1:B3) return?",
    "accept": ["5"],
    "hint": "AVERAGE skips the empty cell but counts the zero."
  },
  {
    "type": "predict",
    "task": "What does =ROUND(21.5, -1) return?",
    "accept": ["20"],
    "hint": "A negative number of digits rounds to the left of the decimal point, here to the nearest 10."
  }
]
```

Check yourself before moving on:

```quiz
[
  {
    "q": "A column has four numbers and one cell containing the text word pending. What does COUNT return, and what does COUNTA return?",
    "choices": ["COUNT 4, COUNTA 5", "COUNT 5, COUNTA 5", "COUNT 4, COUNTA 4"],
    "answer": 0,
    "explain": "COUNT counts only numbers. COUNTA counts every non-empty cell, including text."
  },
  {
    "q": "Your SUM over a column is lower than the total you expect, and a few values sit on the left edge of their cells. The most likely cause?",
    "choices": ["SUM only adds the first 255 cells", "Those values are stored as text and SUM skips them", "The range includes the header row"],
    "answer": 1,
    "explain": "Numbers stored as text are not numbers to SUM. Left alignment is the usual tell."
  },
  {
    "q": "In =SUM(F2:F9), what does the colon mean?",
    "choices": ["Divide F2 by F9", "A range: F2 through F9, every cell between them", "Only the two cells F2 and F9"],
    "answer": 1,
    "explain": "A colon builds a range from the first cell through the last. Two separate cells would be written with a comma."
  }
]
```

## Recap

1. A range like `F2:F9` is a rectangular block of cells; the colon means "through".
2. A function is a named recipe, `=NAME(arguments)`, and arguments can be ranges.
3. SUM, AVERAGE, MIN, and MAX use only real numbers in a range; text and empty cells are skipped.
4. COUNT counts numbers, COUNTA counts anything non-empty.
5. An empty cell is ignored by AVERAGE, but a 0 is counted.
6. `ROUND(number, digits)` rounds a value, and `Alt + =` inserts a SUM for you.
7. A typed range like `F2:F9` does not grow when you add rows below it.

Next up, [Phase 4: Formatting, Sorting, and Filtering](04-formatting-sorting-filtering.md): why a formatted 0.5 is still 0.5, and how to reorder data safely.


---

# Formatting, Sorting, and Filtering

Two things trip up almost everyone in their first month: a number that looks like one thing and behaves like another, and a sort that shuffles one column away from the rest of its row. Both come from the same idea from Phase 1. What you see in a cell and what the cell holds are different things, and Excel gives you tools that change one without touching the other.

## Formatting changes the look, not the value

A **number format** is a rule for how to display a stored value. The stored value stays exactly as it was, and you can always see it in the formula bar.

Select a cell, press `Ctrl + 1` to open the Format Cells dialog (or use the Number group on the Home tab), and pick a format.

| Stored value | Format | Displayed | Still stored |
|---|---|---|---|
| 0.5 | General | 0.5 | 0.5 |
| 0.5 | Percentage, 0 decimals | 50% | 0.5 |
| 4.5 | Currency, 2 decimals | $4.50 | 4.5 |
| 46296 | Date | 10/1/2026 | 46296 |
| 46296 | General | 46296 | 46296 |

A formatted 0.5 is still 0.5. Type `=A1*10` next to a cell showing 50% and you get 5, not 500. The percent sign is decoration.

Useful shortcuts: `Ctrl + Shift + ~` applies General, `Ctrl + Shift + %` applies Percentage, and `Ctrl + 1` opens the full dialog. The Home tab also has Increase Decimal and Decrease Decimal buttons.

One input shortcut works the other way: typing `15%` into a cell stores 0.15 and formats it as a percentage in one move.

### The display trap

Put `2.4` in `A1` and `2.4` in `A2`. Set both to Number with 0 decimals. They display as 2 and 2. Now type `=A1+A2` in `A3` and give it the same format:

| Cell | Stored | Displayed |
|---|---|---|
| A1 | 2.4 | 2 |
| A2 | 2.4 | 2 |
| A3 | 4.8 | 5 |

On screen, 2 + 2 = 5. Nothing is broken. Excel added the stored values, 2.4 and 2.4, and then displayed 4.8 with no decimals. When a total looks wrong by a hair, widen the decimals before you doubt the formula.

If you want the value itself changed, use a function, not a format. Put `2.456` in a cell. Formatting it to 2 decimals shows 2.46, but `=A1*100` still gives 245.6. `=ROUND(A1, 2)*100` gives 246, because ROUND changed the stored number. Format for presentation, ROUND when the rounded number is the number you want to calculate with.

### When a number is really text

If numbers sit on the left and SUM ignores them, they are stored as text. Often Excel marks such a cell with a small green triangle in the corner. Select the cell, click the warning icon that appears, and choose **Convert to Number**. You can do it for a whole selection at once.

Changing the format to Number alone does not convert text that is already in the cell. The conversion happens when the cell is re-entered or converted.

## Sorting

A sort reorders rows. Click any single cell in the column you want to sort by, then on the **Data** tab click **Sort A to Z** or **Sort Z to A**. For numbers that means smallest to largest or largest to smallest, and for dates oldest to newest or newest to oldest.

Excel looks at the cells around your selection, finds the connected block (stopping at the first fully empty row and column), detects the header row, and moves **whole rows** together. This is why Phase 2 left column H empty: A1:G9 is one block, and the tax rate in I1:J1 stays out of the sort.

Sort Total from largest to smallest and the list becomes:

| Item | Category | Qty | Price | Total |
|---|---|---|---|---|
| Marker Set | Writing | 4 | 6.25 | 25 |
| Pen Pack (Oct 4) | Writing | 10 | 2.20 | 22 |
| Desk Lamp | Office | 1 | 18.90 | 18.9 |
| Notebook (Oct 1) | Paper | 3 | 4.50 | 13.5 |
| Stapler | Office | 1 | 12.00 | 12 |
| Pen Pack (Oct 1) | Writing | 5 | 2.20 | 11 |
| Sticky Notes | Paper | 6 | 1.75 | 10.5 |
| Notebook (Oct 2) | Paper | 2 | 4.50 | 9 |

The date column travels with each row, and so do the formulas in F and G, because each formula refers to cells in its own row.

For two criteria, use **Data > Sort**. Set Sort by Category (A to Z), click **Add Level**, set Then by Total (Largest to Smallest), and make sure **My data has headers** is ticked:

| Category | Item | Total |
|---|---|---|
| Office | Desk Lamp | 18.9 |
| Office | Stapler | 12 |
| Paper | Notebook (Oct 1) | 13.5 |
| Paper | Sticky Notes | 10.5 |
| Paper | Notebook (Oct 2) | 9 |
| Writing | Marker Set | 25 |
| Writing | Pen Pack (Oct 4) | 22 |
| Writing | Pen Pack (Oct 1) | 11 |

⚠️ **Gotcha: never sort one column alone.** If you select a whole single column and sort it, Excel warns that it found data next to your selection. Choose **Expand the selection**. If you choose to continue with only the selected column, that column reorders and the rest stays put, so every price now belongs to the wrong item. There is no clue in the sheet that anything went wrong.

⚠️ **Gotcha: formulas that look at other rows.** Formulas that refer to cells in other rows can return different results after a sort, because the rows they pointed at have moved. Row-by-row formulas like `=D2*E2` are safe.

Sorting cannot be undone once the file is closed, and after sorting the original order is gone. If the original order matters, add a numbering column (1, 2, 3, ...) first, so you can sort back by it.

## Filtering

A **filter** hides the rows that do not match, without deleting them. Click any cell in the list and press `Ctrl + Shift + L` (or Data > Filter). Drop-down arrows appear on every header.

Open the Category arrow, untick Select All, tick **Writing**, and press OK. The row numbers turn blue, the other rows are hidden, and the status bar at the bottom left shows "3 of 8 records found" (if it is missing, right-click the status bar and tick Count). The three visible rows are the Marker Set and the two Pen Pack sales. To bring everything back, use **Data > Clear**; to remove the arrows, press `Ctrl + Shift + L` again.

The arrow menus also offer **Text Filters**, **Number Filters**, and **Date Filters**. Number Filters > Greater Than with 15 on Total shows Marker Set, Pen Pack (Oct 4), and Desk Lamp. Filters on several columns combine: each one narrows what the previous left visible.

Filter lists show every distinct entry, so inconsistent spelling shows up as separate entries: `Writing` and `Writing ` (with a trailing space) are two items. If a filter lists what looks like a duplicate, you have found dirty data.

### Totals and filters

Filter Category to Writing, then look at `=SUM(F2:F9)`. It still says 121.9. SUM adds every cell in the range, including rows hidden by the filter. To total only what you can see, use SUBTOTAL:

```excel
=SUBTOTAL(109, F2:F9)
```

The first argument picks the operation; 109 means SUM. SUBTOTAL ignores any row hidden by a filter, and with the 100-series codes it also ignores rows you hid by hand. Here it returns 58, which is 11 + 25 + 22. Plain SUM would still say 121.9.

Try these before the quiz:

```exercise
[
  {
    "type": "predict",
    "task": "A1 holds 2.4 and A2 holds 2.4. Both cells use Number format with 0 decimals. A3 contains =A1+A2 with the same format. What number appears in A3?",
    "accept": ["5"],
    "hint": "Excel adds the stored values (4.8) and then displays the result with no decimals."
  },
  {
    "type": "predict",
    "task": "A cell holds the number 0.5 and is displayed as 50%. What does =A1*10 return?",
    "accept": ["5"],
    "hint": "The stored value is 0.5. The percent sign is only the display."
  }
]
```

Check yourself before moving on:

```quiz
[
  {
    "q": "A cell displays $4.50 after you applied Currency format. What does the formula bar show?",
    "choices": ["$4.50 as text", "4.5, the stored number", "4.50 rounded and locked"],
    "answer": 1,
    "explain": "Formatting changes the display only. The stored value is the number 4.5."
  },
  {
    "q": "You filter a list down to 3 of 8 rows. What does =SUM over the whole column return?",
    "choices": ["The total of the 3 visible rows", "The total of all 8 rows, hidden ones included", "An error"],
    "answer": 1,
    "explain": "SUM adds every cell in the range. Use SUBTOTAL(109, range) to add only the visible rows."
  },
  {
    "q": "Why is sorting only one column of a table dangerous?",
    "choices": ["It deletes the other columns", "It reorders that column alone, so each row's values no longer belong together", "It converts numbers to text"],
    "answer": 1,
    "explain": "The column moves and its neighbors stay put, silently breaking the match between each value and its row."
  }
]
```

## Recap

1. A number format changes how a value is displayed; the stored value is in the formula bar and never changes.
2. A formatted 0.5 is still 0.5. Use ROUND when you want the stored value itself rounded.
3. Displayed totals can look off by a hair because Excel adds stored values, not rounded ones.
4. Numbers stored as text sit on the left; use the green triangle's Convert to Number.
5. Sort moves whole rows as a block; use Data > Sort for several levels and never sort a single column alone.
6. Filter hides rows without deleting them; `Ctrl + Shift + L` toggles it.
7. SUM ignores filters; `SUBTOTAL(109, range)` totals only the visible rows.

Next up, [Phase 5: Tables and Error Values](05-tables-and-errors.md): a list that grows, sorts, and filters itself, plus what each error code means.


---

# Tables and Error Values

Your sales list is a loose range: Excel only knows it as "some cells with data". Every formula you wrote points at fixed addresses like `F2:F9`, and a new sale in row 10 is invisible to all of them. An Excel Table fixes that by making the list a single object that Excel understands, with a name, a header row, and rows that can grow. This phase also gives you a cheat-card for the five error values, so a `#` in a cell stops being alarming.

## Make the list a Table

Click any cell inside `A1:G9` and press `Ctrl + T`. (Insert > Table and Home > Format as Table do the same, and work on Mac too.) A dialog confirms the range and asks whether **My table has headers**. Keep it ticked and press OK.

What changed:

- The block got a style with banded rows.
- Every header has filter arrows built in, so sort and filter from Phase 4 are always on.
- Excel treats the block as one object that can expand and shrink.

Click a cell in the table and a **Table Design** tab appears on the ribbon (older versions put it under Table Tools > Design). Its **Table Name** box on the left shows a default like `Table1`. Rename it to something meaningful: type `Sales` and press Enter. Names cannot contain spaces.

To undo the conversion later, use Table Design > **Convert to Range**.

## Structured references: formulas that read like English

Inside a Table, you refer to columns by name instead of by address. The pattern is `TableName[ColumnName]`.

| Formula | Meaning | Result on our data |
|---|---|---|
| `=SUM(Sales[Total])` | add the whole Total column | 121.9 |
| `=AVERAGE(Sales[Total])` | average of the Total column | 15.2375 |
| `=SUM(Sales[Qty])` | all units sold | 32 |
| `=MAX(Sales[Qty])` | the largest quantity | 10 |
| `=COUNTA(Sales[Item])` | number of items | 8 |

`=SUM(Sales[Total])` says what it does. `=SUM(F2:F9)` needs you to go look at column F. You can type these, or click a column header while building a formula and Excel writes the name for you.

### Calculated columns and `[@Column]`

Inside a Table row, `[@Column]` means "the value in this same row". Add a new column: click `H1`, type `Gross`, and press Enter. Because H1 sits directly beside the table, the table extends itself to include it. Now in `H2` type:

```excel
=[@Total]+[@Tax]
```

You can click F2 and G2 while typing and Excel fills in the names. Press Enter and the formula fills into every row of the column automatically. This is a **calculated column**. No dragging, no fill handle.

| Row | Total | Tax | Gross |
|---|---|---|---|
| Notebook (Oct 1) | 13.5 | 1.08 | 14.58 |
| Pen Pack (Oct 1) | 11 | 0.88 | 11.88 |
| Stapler | 12 | 0.96 | 12.96 |
| Notebook (Oct 2) | 9 | 0.72 | 9.72 |
| Marker Set | 25 | 2 | 27 |
| Desk Lamp | 18.9 | 1.512 | 20.412 |
| Pen Pack (Oct 4) | 22 | 1.76 | 23.76 |
| Sticky Notes | 10.5 | 0.84 | 11.34 |

`=SUM(Sales[Gross])` gives 131.652, which is 121.9 times 1.08.

If a header has a space or punctuation in it, such as `Unit Price`, the row form needs an extra pair of brackets, `[@[Unit Price]]`. Excel writes it for you when you click the cell, so you rarely type it.

## Why a Table beats a loose range

Now add a sale. In `A10` type `2026-10-05`, then `Eraser`, `Writing`, `8`, and `0.60` across row 10.

- The Table **expands** to include row 10, banding and all.
- `=SUM(Sales[Qty])` changes from 32 to 40. A loose `=SUM(D2:D9)` typed before the conversion would have stayed at 32.
- The `Gross` column is a calculated column, so it fills the new row by itself. If `F10` and `G10` stayed empty (those two were ordinary formulas before the conversion), select `F9:G10` and press `Ctrl + D` to copy them down.

Once F10 and G10 are filled, `=SUM(Sales[Total])` is 126.7 and `=SUM(Sales[Gross])` is 136.836.

*What just happened:* the Table is one object, so "the whole Total column" always means all of it, however long it gets. Anything that points at the Table by name, including charts and PivotTables (see [Excel Pivot Tables and Charts](/guides/excel-pivot-tables-and-charts)), follows the growth too.

| Loose range | Table |
|---|---|
| Formulas end at a fixed row | Column references grow with the data |
| You copy formulas down yourself | Calculated columns fill automatically |
| Formulas read `F2:F9` | Formulas read `Sales[Total]` |
| Add filter arrows by hand | Filters built in, on every header |
| Banding and totals by hand | Style and Total Row are checkboxes |

The Total Row: tick **Total Row** on the Table Design tab and a row appears under the data with a drop-down in every cell. Choosing Sum for the Total column writes `=SUBTOTAL(109,[Total])`, the filter-aware total from Phase 4. Filter the Table and the Total Row follows.

## Error values: a cheat-card

When a formula cannot produce an answer, the cell shows an error value that starts with `#`. Each one names a different failure.

| You see | It means | Typical cause | First move |
|---|---|---|---|
| `#DIV/0!` | divided by zero | the divisor is 0 or empty | check the cell you divide by |
| `#NAME?` | Excel does not recognize a word | misspelled function, text without quotes | check the spelling |
| `#REF!` | a reference is no longer valid | you deleted a cell, row, or column a formula used | undo with `Ctrl + Z` |
| `#VALUE!` | the wrong kind of value | arithmetic on text | find the text cell |
| `#N/A` | the value is not available | a lookup found no match | check the lookup value |

### #DIV/0!

Division needs a non-zero divisor. `=F2/0` fails, and so does `=F2/D2` when D2 is empty or 0. `=AVERAGE` over a range with no numbers fails too, because it divides by a count of zero. Guard it with an IF: `=IF(D2=0, "", F2/D2)` shows nothing when the divisor is zero and divides otherwise.

### #NAME?

Excel found a word it cannot match to anything. `=SUMM(F2:F9)` is a misspelled function. Text must be in double quotes: `="Writing"` is text, `=Writing` is Excel hunting for a name called Writing. A function your Excel version does not have also gives `#NAME?`, which can happen when a file from newer Excel opens in an older one.

### #REF!

A formula pointed at a cell that no longer exists. If `=B2+C2` lives in D2 and you delete column C, the formula slides left into C2 and becomes `=B2+#REF!`. Undo right away if you can; the original address is gone from the formula, and Excel cannot guess it.

### #VALUE!

A formula got a kind of value it cannot use. If M1 contains the text `n/a`, then `=M1*2` returns `#VALUE!`, because text that is not a number cannot be multiplied. The `+` operator breaks on text where `SUM` would have skipped it (Phase 3), which is a reason to prefer SUM over chains of plus signs.

### #N/A

"Not available". You mostly meet it with lookups. `=MATCH("Calculator", B2:B10, 0)` asks for the position of "Calculator" in the Item column, using exact matching, and returns `#N/A` because no such item was sold. It is Excel saying "I looked and found nothing", often a data mismatch such as a trailing space. Lookups are covered in [Excel Formulas That Do Real Work](/guides/excel-formulas-that-do-real-work).

### Two things that look like errors

- `####` in a cell is not an error value. The column is too narrow to show the number; widen it.
- A green triangle in a cell's corner is Excel's own warning, for example for numbers stored as text. It is a suggestion, not a failure.

Excel has a few more error values (`#NUM!`, `#NULL!`, `#SPILL!`), which show up in more advanced formulas.

### Errors spread

Errors flow through dependent formulas. If one cell in `F2:F9` shows `#DIV/0!`, then `=SUM(F2:F9)` also shows `#DIV/0!`. Find the first error, not the last. Select the cell and use **Formulas > Error Checking > Trace Error**, or press Ctrl and the backtick key (left of 1 on a US keyboard) to show every formula as text and scan for the odd one. **Formulas > Evaluate Formula** steps through a formula one piece at a time.

`IFERROR(value, value_if_error)` replaces any error with a result you choose. Use it deliberately, in the one place you expect a failure. Wrapping a whole sheet in IFERROR hides real mistakes behind blank cells.

Test yourself:

```exercise
[
  {
    "type": "predict",
    "task": "Which error value does =SUMM(F2:F9) return? (SUM is misspelled.)",
    "accept": ["#NAME?", "NAME?"],
    "hint": "Excel does not recognize the word SUMM."
  },
  {
    "type": "predict",
    "task": "M1 contains the text n/a. Which error value does =M1*2 return?",
    "accept": ["#VALUE!", "VALUE!"],
    "hint": "The formula needs a number and got text."
  },
  {
    "type": "predict",
    "task": "A Table is named Sales and has a column called Qty. Type a formula that adds up that whole column, starting with the equals sign.",
    "accept": ["=SUM(Sales[Qty])", "SUM(Sales[Qty])"],
    "hint": "The pattern is TableName[ColumnName] inside SUM."
  }
]
```

Check yourself before moving on:

```quiz
[
  {
    "q": "You add a new row directly under a Table. What happens?",
    "choices": ["Nothing, the new row stays outside the Table", "The Table expands to include it, and formulas using Sales[Total] include it", "Excel asks you to retype every formula"],
    "answer": 1,
    "explain": "A Table grows when you type in the row directly below it, and column references like Sales[Total] cover the whole column."
  },
  {
    "q": "Inside a Table, what does [@Qty] mean?",
    "choices": ["The Qty value in the same row as the formula", "The sum of the Qty column", "The first value in the Qty column"],
    "answer": 0,
    "explain": "The @ symbol means this row. =[@Qty]*[@Price] multiplies the two values on the row where the formula lives."
  },
  {
    "q": "A formula that read =B2+C2 now reads =B2+#REF! after you edited the sheet. What most likely happened?",
    "choices": ["Column C was deleted", "C2 contains text", "Excel does not recognize a function name"],
    "answer": 0,
    "explain": "#REF! means a reference is no longer valid, usually because the cell, row, or column it pointed to was deleted."
  }
]
```

## Recap

1. `Ctrl + T` turns a range into a Table: one named object with built-in filters, banding, and the ability to grow.
2. Structured references like `Sales[Total]` name a whole column and always cover all of it.
3. `[@Column]` means "this row", and a formula typed in one cell fills the whole calculated column.
4. A Total Row writes `SUBTOTAL(109, ...)`, so it respects filters.
5. `#DIV/0!` is a zero divisor, `#NAME?` is an unknown word, `#REF!` is a deleted reference, `#VALUE!` is the wrong type, `#N/A` is a lookup that found nothing.
6. Errors spread to dependent formulas, so find the first one and fix it there.

For where this goes next, read [Excel Formulas That Do Real Work](/guides/excel-formulas-that-do-real-work) and [Excel Pivot Tables and Charts](/guides/excel-pivot-tables-and-charts). When one spreadsheet no longer holds the data, see [Spreadsheets to SQL to Pipelines](/guides/spreadsheets-to-sql-to-pipelines).