The one idea
A spreadsheet is two tools wearing one coat.
One is a grid for presentation — merged title cells, blank spacer rows, subtotals in the middle, colour that means something. The other is a table for analysis — one row per thing, one column per attribute, no gaps, headers once at the top.
Every formula, sort, filter, PivotTable and chart assumes the second. Give them the first and they do not fail loudly. They stop at the blank row and give you an answer for half your data.
Keep your data as a plain table with one row per record and no blank rows or merged cells. Make the pretty version somewhere else.
Keep the raw data on its own sheet, untouched. Do calculations on another sheet that references it. Present on a third.
Data should be tidy: each variable in a column, each observation in a row, each value in one cell. Formatting and layout are a presentation concern and should not be encoded in the data.
What ruins a sheet
| What people do | What breaks |
|---|---|
| Merged cells across a header | Sorting, filtering and PivotTables refuse or misalign |
| Blank row between sections | Every range selection stops at the gap |
| Subtotal rows mixed into the data | Your total counts the subtotals again |
| "Mar-24", "March 2024", "3/24" in one column | Grouping produces three separate months |
| A number stored as text, left-aligned | SUM silently ignores it |
| Two things in one cell: "Mumbai - North" | You cannot group by city without splitting it first |
The habit that fixes all six: one sheet holds raw data and nothing else. No title. No notes. No totals. Row 1 is headers, row 2 onwards is data, and nothing lives to the right of the last column.
Relative and absolute references
This is the one piece of syntax that confuses people for years, and it takes a minute to understand.
Prices are in column B and you want to add 18% GST to each. The rate is in cell
F1. In C2 you write:
=B2*(1+F1)
You drag it down. Row 3 becomes =B3*(1+F2) — and F2 is empty. Excel moved
both references because both were relative: they shift as you copy.
Lock the one that must not move with dollar signs:
=B2*(1+$F$1)
Now dragging down gives =B3*(1+$F$1), =B4*(1+$F$1). $F$1 is absolute.
The $ before the letter locks the column; the $ before the number locks the
row. F4 on Windows cycles through the four combinations while you edit.
Rule of thumb: any single settings cell — a rate, a target, a threshold — gets locked.
The formulas that do most of the work
"Total sales for the North region only"
SUM cannot do conditions. SUMIFS can:
=SUMIFS(D:D, B:B, "North")
Sum column D, where column B equals North. Add pairs for more conditions:
=SUMIFS(D:D, B:B, "North", C:C, "Retail", A:A, ">="&DATE(2026,1,1))
To count rows instead of summing, COUNTIFS takes the same conditions without
the first argument:
=COUNTIFS(B:B, "North", C:C, "Retail")
"Flag every order over 50,000 for review"
=IF(D2>50000, "Review", "OK")
Condition, what to show if true, what to show if false. Resist nesting more than two or three of these — at that point the logic belongs in a small lookup table instead, where you can read it.
"I have two files and need to join them"
The orders sheet has CustomerID in column A. The customers sheet has ID in
column A and city in column C. In the orders sheet:
=XLOOKUP(A2, Customers!$A:$A, Customers!$C:$C)
Read it as: take the value in A2, find it in the customers ID column, return
the matching value from the city column. The lookup columns are locked so
dragging down does not slide them sideways.
If you are on an older version without XLOOKUP, the same job is VLOOKUP:
=VLOOKUP(A2, Customers!$A:$C, 3, FALSE)
Look for A2 in the first column of A:C, return the 3rd column, and FALSE
means exact match only. Two traps that XLOOKUP removes: VLOOKUP can only look
rightwards from the key column, and that 3 is a position that breaks silently
the moment somebody inserts a column. Always write FALSE. Omitting it
gives approximate matching, which returns confidently wrong answers on unsorted
data.
"Some of them show #N/A and it looks terrible"
=IFERROR(XLOOKUP(A2, Customers!$A:$A, Customers!$C:$C), "Not found")
Use this after you have understood why the errors are there — never before. Wrapping errors you have not investigated is how a broken workbook gets presented as a clean one.
What the error messages actually mean
| Error | What it means | Usual cause |
|---|---|---|
#N/A | The lookup found nothing | Trailing space, different spelling, or a number stored as text on one side |
#REF! | The reference no longer exists | Someone deleted the row or column the formula pointed at |
#VALUE! | Wrong type of thing | Doing arithmetic on text, often a number that is secretly text |
#DIV/0! | Divided by zero or by an empty cell | The denominator has not been filled in |
#NAME? | Excel does not recognise a name | Misspelled function, or a function your version does not have |
#N/A is the one you will meet most. Nine times in ten the two values look
identical on screen and differ by an invisible trailing space, or one side is
the text "1024" and the other is the number 1024. Test it directly:
=A2=Customers!A5
If that returns FALSE for two things that look the same, you have found it.
=LEN(A2) compared with =LEN(Customers!A5) will show the extra character. The
next lesson deals with fixing this at scale.
A finance sheet totals =SUM(D2:D40). Someone adds three orders at row 41, just
under the last one. The total does not move, because the formula was written to a
fixed range and nobody re-read it.
Two defences. Format the data as a Table (Ctrl+T), which extends ranges as rows
are added. Or sum the whole column, =SUM(D:D), keeping the total off the data
sheet so it is not summing itself.
You track expenses with a "Category" column typed by hand. By month three you
have "Food", "food", "Foods" and "Food ". Your SUMIFS for "Food" is quietly
wrong.
The fix is not more careful typing. It is a dropdown: Data, then Data Validation, then List. Now the column holds only the five values you allow.
Revenue by city, from two files
1 of 6Put each file on its own sheet, unchanged. Name them
OrdersandCustomers. Do not edit, sort or tidy the raw sheets — if a number turns out wrong later, you need something to check against.
Try this
This formula is meant to sum January retail sales, and returns 0:
=SUMIFS(D:D, B:B, "Retail ", C:C, "Jan")
Name three things that could be wrong.
Your challenge
Level 3 · IndependentBuild a two-sheet workbook from scratch. Sheet one: 20 orders with an ID, a customer ID, a date and an amount. Sheet two: 8 customers with ID, city and segment. Deliberately make two order rows reference a customer that does not exist, and give one customer ID a trailing space.
Produce a summary sheet showing revenue by city and order count by city, plus a cell that reports how many orders failed to match.
Success criteria: the city totals plus the unmatched total equal the grand total, and you can say out loud which two orders failed and why.
What people usually get wrong
- Merged cells anywhere in the data. They break sorting and filtering. Use "Center Across Selection" if you need the look.
- Typing numbers into a formula.
=D2*1.18becomes wrong when the rate changes and nobody can find where 1.18 lives. Put it in a labelled cell. - Forgetting
FALSEon VLOOKUP. Approximate match returns nearby values as if they were correct. This is the single most expensive spreadsheet default. - Wrapping everything in IFERROR early. It converts a visible problem into an invisible one.
- Editing the raw data in place. Once you have overwritten it, you cannot check anything.
- Hard-coding a fixed range like
D2:D40in a sheet that grows. Use a Table or a whole column.
How someone experienced does it
Experienced users separate three layers onto three sheets: raw data exactly as it arrived, a working sheet of formulas, and an output sheet for people to read. When the source is re-exported next month, you paste it over the raw sheet and everything downstream recalculates. Workbooks that mix the layers get rebuilt from scratch every month by someone who resents it.
They also build the check before the answer. A cell reading
=SUM(Summary!B:B)-SUM(Orders!D:D) that should always show 0 is worth more than
any amount of care, because care does not survive a Friday evening.
And they name ranges. =SUMIFS(Revenue, Region, "North") is readable a year
later; =SUMIFS('Sheet2'!$D$2:$D$4000, 'Sheet2'!$B$2:$B$4000, "North") is not,
and you will be the one reading it.
When not to use this
A spreadsheet stops being the right tool roughly where several people need to edit at once, where rows run into hundreds of thousands, or where the same report is rebuilt by hand every month.
Those are signs the work belongs in a database — the SQL lesson in this module. Excel is excellent at exploring a dataset once. It is poor as the permanent home of something important, because there is no record of who changed what.
Why a number can be text, and why it matters so much
Every cell holds a value and a type. 1024 typed in is a number. 1024 that
arrived from a CSV export, from a system that pads with spaces, or into a cell
formatted as Text, is a string of four characters that looks like a number.
They display almost identically. Numbers align right by default and text aligns
left — that is your visual tell. SUM skips text silently and reports a smaller
total with no warning. Lookups fail with #N/A, because "1024" and 1024 are
genuinely different values.
To convert, multiply by one (=A2*1), use =VALUE(A2), or select the column and
use Data, then Text to Columns, then Finish, which re-parses each cell. The green
triangle in a cell corner often signals exactly this, and most people have spent
years clicking it away.
Prove it
Take two real files that share a key — a bank statement and a category list, a class list and a marks sheet, an order export and a product list — and join them.
Produce a summary that answers one specific question, and include a reconciliation cell showing that your totals match the source. Send it to someone and see whether they can follow it without you in the room.
Keep learning this
Paste this into any AI assistant. It turns the assistant into a tutor that tests you instead of just answering you.
Act as an experienced practitioner who is good at teaching. I have just learned Excel formulas for joining and summarising data across two files. Assume I am intelligent but relatively new to this — treat me as beginner level. Work through this in order, and wait for my reply at each step: 1. Ask me 5 questions that test whether I actually understood Excel formulas for joining and summarising data across two files. Do not reveal the answers yet. 2. After I answer, tell me which parts I got right, which I got wrong, and which I only half-understand. Explain only what I misunderstood — do not re-teach what I already know. 3. Give me one practical challenge based on something I could genuinely encounter at work or in daily life. Do not solve it for me. 4. Evaluate my solution the way an experienced person would judge it, including what a professional would have done differently. 5. Tell me what to learn next, and why that comes next. 6. Give me trustworthy sources for deeper study — prefer official documentation, primary research or standards bodies over blogs and videos. Rules for you: no buzzwords. No motivational filler. Say "I'm not certain" when you are not certain, and tell me which parts of your answer I should verify myself. Clearly separate facts from your recommendations and your opinions.
Become independent at this
Use this when you want a path from where you are to actually good, with checkpoints you can test yourself against.
I want to become independently capable at structuring spreadsheet data and writing lookup and conditional formulas — not permanently dependent on AI, tutorials or step-by-step guides. Design a progression for me with five stages: Beginner, Guided practice, Independent practice, Real-world application, Professional level. For each stage tell me: - what I must know - what I must be able to do without help - the mistakes people make at this stage - one practical challenge - one real project that would prove I reached this stage - one way I can test myself honestly Then tell me the signals that I am ready to move to the next stage, and the signals that I have skipped ahead too early. Keep the theory to the minimum I actually need. Focus on ability I can transfer to situations you and I have not discussed.