Lesson 2 of 9 · about 18 minutes
Spreadsheets From Zero
You'll be able to
- Lay out data so each row is one record and each column is one kind of fact
- Sort and filter without scrambling your rows
- Write SUM, AVERAGE, MEDIAN, COUNTIF and SUMIF formulas
- Calculate percent change and share of total
- Use an absolute reference so a formula copies down correctly
A spreadsheet is a grid. Columns have letters, rows have numbers, and each box (a cell) has an address like D2. That is the entire setup. The skill is in keeping the grid tidy and knowing a dozen formulas cold.
You need no paid software. LibreOffice Calc is free for Windows, Mac and Linux. Google Sheets is free in a browser. Every formula in this lesson works the same in both, and in Excel.
One row, one record
The rule that saves you the most grief: each row is one thing, and each column is one kind of fact about it. One payment per row. Date in one column, vendor in another, amount in a third. Row 1 holds the column names and nothing else.
Here is the invented Willow Bend County checkbook we will use for the next three lessons. Twelve payments, July 2025 through January 2026.
| A: Date | B: Vendor | C: Department | D: Amount | |
|---|---|---|---|---|
| 2 | 2025-07-03 | Acme Paving | Roads | 48,200 |
| 3 | 2025-07-15 | Blue Ridge Fuel | Sheriff | 6,450 |
| 4 | 2025-08-02 | Acme Paving | Roads | 112,750 |
| 5 | 2025-08-19 | Northline Office Supply | Clerk | 1,180 |
| 6 | 2025-09-05 | Blue Ridge Fuel | Roads | 9,320 |
| 7 | 2025-09-22 | Harper Engineering | Roads | 27,500 |
| 8 | 2025-10-10 | Blue Ridge Fuel | Sheriff | 7,015 |
| 9 | 2025-10-28 | Valley Medical Supply | Sheriff | 3,870 |
| 10 | 2025-11-14 | Northline Office Supply | Clerk | 940 |
| 11 | 2025-12-01 | Harper Engineering | Parks | 15,000 |
| 12 | 2025-12-18 | Acme Paving | Parks | 21,400 |
| 13 | 2026-01-09 | Blue Ridge Fuel | Sheriff | 6,880 |
Type it in yourself. It takes ten minutes and you will learn more from the typing than from reading. Enter amounts as plain numbers (48200), then format the column to show commas. A number typed with a dollar sign and spaces can end up stored as text, and text does not add.
Sorting without wrecking the table
Click any cell inside the table, then use Data, Sort. Choose column D, descending. The biggest payment, $112,750 to Acme Paving, jumps to the top, with its date and department still beside it.
The disaster version: you select only column D and sort the selection. The amounts reorder, the vendors don't, and now Northline Office Supply received $112,750. Both LibreOffice and Excel warn you and offer to expand the selection. Say yes. Then keep an untouched copy of the original file anyway. Every data reporter has a story about the sort that ruined a week.
Filtering
A filter hides rows that don't match, without deleting them. In LibreOffice, Data, AutoFilter. In Google Sheets, Data, Create a filter. Small arrows appear on the header row. Click the one on Department, keep only Sheriff, and you see four payments. Clear the filter and all twelve come back.
Watch the status bar when filtered. Some programs sum only visible cells there while SUM formulas still count hidden rows. When in doubt, use a formula with a condition (below) instead of trusting what the screen shows.
The formulas you will use every week
Every formula starts with an equals sign. Put the summary in a separate area, say column G for labels and column H for formulas, so it never mixes with the data rows.
| Question | Formula | Result |
|---|---|---|
| Total spent | =SUM(D2:D13) | 260,505 |
| How many payments | =COUNT(D2:D13) | 12 |
| Average payment | =AVERAGE(D2:D13) | 21,708.75 |
| Middle payment | =MEDIAN(D2:D13) | 8,167.50 |
| Largest payment | =MAX(D2:D13) | 112,750 |
| Payments to Blue Ridge Fuel | =COUNTIF(B2:B13,"Blue Ridge Fuel") | 4 |
| Dollars to Blue Ridge Fuel | =SUMIF(B2:B13,"Blue Ridge Fuel",D2:D13) | 29,665 |
| Payments over $10,000 | =COUNTIF(D2:D13,">10000") | 5 |
D2:D13 means "D2 through D13." COUNTIF takes a range and a condition. SUMIF takes a range to test, a condition, and a range to add.
Why the average and median disagree
The average is $21,708.75. The median is $8,167.50. Sort the amounts and you see why: the two middle values of twelve are $9,320 and $7,015, and their average is $8,167.50. Meanwhile one $112,750 paving payment drags the average up. If someone says "the typical county payment is over $21,000," they are wrong about what typical means. Lesson 5 returns to this.
Share of total, and the dollar sign
Put the total in H2 with =SUM(D2:D13). Now, in E2, you want each payment's share of all spending. You type =D2/H2, format as percent, and get 18.5 percent. Good. You copy E2 down to E13.
Every cell below shows #DIV/0!. Look at E3. It now says =D3/H3. The spreadsheet shifted both references down one row, because that is what relative references do. H3 is empty, and dividing by an empty cell is dividing by zero.
The fix is an absolute reference: =D2/$H$2. The dollar signs lock the column and row. Copy it down and E3 becomes =D3/$H$2. In LibreOffice and Excel, put the cursor on the reference and press F4 to cycle through $H$2, H$2, $H2 and H2.
Example: share of total, checked
Acme Paving's $112,750 payment: 112,750 divided by 260,505 = 0.4328, or 43.3 percent of all twelve payments. The smallest, $940 to Northline, is 940 divided by 260,505 = 0.0036, or 0.4 percent.
Check: add column E. The shares should total 100 percent, give or take rounding in the displayed digits.
Percent change
Here are invented department budgets for two years. Columns: A department, B FY2025, C FY2026, D change, E percent change.
| Department | FY2025 | FY2026 | Change =C2-B2 | Percent change =(C2-B2)/B2 |
|---|---|---|---|---|
| Roads | 1,850,000 | 2,210,000 | 360,000 | 19.5% |
| Sheriff | 3,420,000 | 3,560,000 | 140,000 | 4.1% |
| Parks | 410,000 | 388,000 | minus 22,000 | minus 5.4% |
| Clerk | 295,000 | 301,000 | 6,000 | 2.0% |
| Total | 5,975,000 | 6,459,000 | 484,000 | 8.1% |
Always divide by the old number. Roads: 360,000 divided by 1,850,000 = 0.1946, or 19.5 percent. Divide by the new number by mistake and you get 16.3 percent, a wrong figure that looks perfectly reasonable. That is what makes it dangerous.
Notice the Sheriff's office had the second-biggest dollar increase and a small percent increase. Both are true. Which one leads depends on the story, and you should usually give readers both.
Common mix-ups
- Claim: The number on screen is the number in the cell. What the evidence shows: Formatting can hide decimals. A cell showing 19.5% may hold 0.194594. Use the stored value in later math, and round only at the end.
- Claim: A formula that runs without an error is correct. What the evidence shows: A range that stops one row short, D2:D12, gives a confident wrong total. Click the formula and check the outlined range.
Try it
- Type the twelve-row checkbook into LibreOffice Calc or Google Sheets.
- Build the summary block in columns G and H with all eight formulas. Match every result in the table above.
- Add the share-of-total column with
=D2/$H$2and copy it down. Confirm the shares add to 100 percent. - Sort by amount, largest first, and confirm the vendor names moved with their amounts.
- Type the budget table and calculate both change columns. Then deliberately divide by the new year instead and note how plausible the wrong answer looks.
Check yourself
1. In the county checkbook, which formula counts how many payments went to Blue Ridge Fuel?
COUNTIF counts the cells in a range that meet a condition. Here it returns 4.
2. A department went from $410,000 to $388,000. What is the percent change?
(388,000 minus 410,000) divided by 410,000 = minus 0.0537, or about minus 5.4 percent. Always divide by the old value.
3. You write =D2/H2 in E2 and copy it down to E13. Every cell below E2 shows an error. Why?
Relative references shift when copied. Lock the total with $H$2 so every row divides by the same cell.
4. Twelve payments total $260,505. The average is $21,708.75 and the median is $8,167.50. What explains the gap?
One $112,750 payment and a few others are far above the rest. The median, the middle value, is not pulled by extremes.
5. You select only the Amount column and sort it largest to smallest. What happens?
If only one column is selected and you tell the program to sort just the selection, amounts move while names stay put. Select the whole table, or let the program expand the selection.
Terms in this lesson
- Cell reference
- The address of a cell, such as D2: column letter, then row number.
- Relative reference
- A reference like D2 that shifts when the formula is copied to another cell.
- Absolute reference
- A reference like $H$2 with dollar signs that stays fixed when the formula is copied.
- Median
- The middle value when numbers are sorted. With an even count, the average of the two middle values.
- Percent change
- (New minus old) divided by old, times 100.