How to compare two Excel sheets for differences and missing data
Compare two Excel sheets cell by cell or row by row. Formulas to find missing rows and changed values, conditional formatting, and a no-formula option.
Count how many people are in each team.
COUNTIF counts the cells in a range that match what you ask for.
Count how many people are in each team, in F2:F5. Write one formula in F2 and copy it down.
Typing formulas is easiest on a computer with a keyboard.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Team | Sales | Team | People | ||
| 2 | Tariq | West | 600 | North | |||
| 3 | Nadia | West | 800 | South | |||
| 4 | Daniel | North | 290 | East | |||
| 5 | Quinn | South | 900 | West | |||
| 6 | Jamal | North | 680 | ||||
| 7 | Isla | East | 420 | ||||
| 8 | Kofi | West | 340 | ||||
| 9 | Yusuf | North | 640 | ||||
| 10 | Sam | South | 870 | ||||
| 11 | Rosa | West | 840 | ||||
| 12 | |||||||
| 13 | |||||||
| 14 | |||||||
| 15 |
COUNTIF do?COUNTIF counts the cells that match something:
=COUNTIF(where to look,
=COUNTIF(B2:B11,"North") counts the cells in B2:B11 that say North.=COUNTIF(B2:B11,E2) counts the cells that match whatever is in E2. Change E2 and the count changes too.COUNTIF care about capitals?No. It ignores upper and lower case: "north" counts as North.
Put the comparison in quotes: =COUNTIF(C2:C11,">=500") counts sales of 500 or more.
Compare two Excel sheets cell by cell or row by row. Formulas to find missing rows and changed values, conditional formatting, and a no-formula option.
Know someone fighting with a spreadsheet? Send them this page.