Faragiri

Learn Excel Office software · The basics, Formulas, Lookup and reference and 6 more

Why a formula changes meaning when it is dragged, the one-cell test for a lookup that returns nothing, and a pivot table in four steps that cannot be reordered.

9Branches
9Nodes below
3Depth

Nils prices stock for a garden centre chain in Malmo and has a cost in column B, a markup multiplier in F1, and a formula in C2 that works perfectly until he drags it down. =B2*F1 in row 2 becomes =B3*F2 in row 3, and F2 is empty, so every price below the first is zero. The fix is one character used twice.

=B2*$F$1. A dollar sign locks whatever comes immediately after it, so $F holds the column and $1 holds the row, and the reference stays on the markup cell however far the formula travels. =B2*$F$1 keeps the markup cell fixed when the formula is dragged down, while B2 is left alone deliberately, because that one is supposed to move.

The half-locked forms are worth knowing because they are what a grid needs. F$1 locks the row and lets the column move, which is what you want along the top of a table; $F1 does the reverse, down the side. Pressing F4 with the cursor on a reference cycles through all four, which is faster than typing them and is the reason this stops being a chore.

The lookup that returns nothing, and the one cell that explains it

Nils has product codes in two places: exported from the till system, where they arrive as 00412, and typed into the price list by hand as 412. They look identical in a cell and no lookup will ever match them. A lookup returns nothing when one side is text and the other is a number, and the failure is silent, which is what makes it cost an afternoon.

One lookup, four links

Four things that all have to hold, and the one that fails silently

None of these matters more than another, and three of them announce themselves when they break. The open link does not.

The open link is not the hardest to fix, and =ISNUMBER settles it in ten seconds. It is drawn open because it is the only one of the four whose failure looks like an empty cell rather than an error.

The test is one cell. =ISNUMBER(A2) says whether a lookup key is a number or text. TRUE on one side and FALSE on the other is the whole diagnosis, and it takes ten seconds against the hour usually spent retyping the formula.

Two fixes, and which one depends on which side you can change. =VALUE(A2) turns the text into a number, and Data then Text to Columns then Finish does the same thing to a whole column in place. Going the other way, =TEXT(A2,"00000") pads the typed codes back out to match the export. The one to prefer is whichever leaves the export untouched, because the export will be run again next week.

Make it a table before anything else

Almost every later problem is caused by the source being a range rather than a table. Ctrl+T turns a range into a table whose references grow with it, so a formula written against it keeps working when 400 rows become 700, and a chart or a pivot built on it picks up the new rows without anybody adjusting anything.

Range against table

Why Ctrl+T belongs before any formula is written

Coordinate ranges assume the sheet stays frozen. Tables assume more rows will arrive tomorrow.

Raw rangeBound to explicit coordinates like D2:D400
Excel TableBound to structured references like Stock[Value]
Row expansionFormula breaks or misses data when 400 rows become 700References automatically expand to include all 700 rows
Inserting columnsCoordinates quietly shift and calculate the wrong columnNamed column references remain intact
Downstream pivotsSource range must be updated by hand after new data arrivesRefresh picks up all new records without reconfiguring

The keystroke takes one second. Fixing a formula that silently dropped three hundred rows takes hours.

It also renames the references into words. A formula reading =SUM(Stock[Value]) survives somebody inserting a column, where =SUM(D2:D400) quietly starts summing the wrong thing. That is the same argument as naming a range and it costs one keystroke.

A pivot in four steps, in order

The steps cannot be reordered and three of the four failures in this feature are attempts to do them out of order.

One pivot table

Four steps, and three of the usual failures are steps taken early

A staircase rather than a list, because each tread depends on the state the one below it left the workbook in.

  1. 01Make the source cleanOne header row, no blank rows, no merged cells. A blank row truncates the range
  2. 02Insert, then PivotTableIt offers the range it can see, which is why the previous step decides what appears
  3. 03Drag fields into Rows and ValuesGroup by into Rows, count or sum into Values. Filters only if you will filter
  4. 04Refresh, after every source changeRight-click and Refresh, or Ctrl+Alt+F5. Nothing tells you it is stale

The figure has no step for formatting, which is where most of the time actually goes. Formatting survives a refresh and the numbers do not update without one, which is the wrong way round for how attention is usually spent.

First, the data has to be clean, and cleanliness here is specific: a pivot needs one header row, no blank rows and no merged cells. A blank row truncates the range and a merged cell in the header removes a field entirely. Second, Insert then PivotTable. Third, drag fields: what you want to group by into Rows, what you want counted or added into Values, and a field into Filters only if you actually intend to filter.

Fourth is the one nobody is told. A pivot does not update on its own, so refresh it after the source changes. Right-click and Refresh, or Ctrl+Alt+F5 for all of them in the workbook. A pivot showing last month's numbers beside this month's date is the commonest wrong figure in an office, and it is wrong in a way that looks completely normal.

Which build it works in, as of 2026

Everything above works in every version anybody still runs, which is deliberate. The version-sensitive functions are worth knowing by name so that an instruction can be recognised as not applicable: XLOOKUP arrived with Microsoft 365 and Office 2021, and TEXTSPLIT and the dynamic array functions that spill down a column are 365 only, so a tutorial using either was filmed for a build many offices do not have. Excel itself, in whichever build is in front of you, is the tool this page is about.

Excel build horizon

When functions arrived, and which ones fail on older machines

Formulas like XLOOKUP and TEXTSPLIT depend on the host build. The core mechanics on this page work on every release.

  1. 2016Power Query on Data tabReplaces recorded macros for repeatable weekly file imports
  2. 2019Perpetual standalone limitLacks dynamic array spill functions and returns name errors for XLOOKUP
  3. 2021Office 2021 standaloneIncludes XLOOKUP natively without requiring subscription licensing
  4. 2024AI formula assistantsGenerates formulas quickly but ignores local build limitations
  5. 2026Microsoft 365 channelSpill functions like TEXTSPLIT remain exclusive to 365 subscriptions

Before sharing a workbook, check whether colleagues run perpetual 2019 or modern 365.

Power Query sits on the Data tab in Windows builds from 2016 onward, and it is the answer once the same cleaning is being done every week rather than once; it replaced recording a macro to do the same tidy-up, and it re-runs on a new export with one click. Google Sheets does most of this page for nothing with a Google account, and its differences are in the function names rather than in the ideas.

Since 2024 a model will write a formula from a description of the columns and explain an error, free on every major assistant, and it is genuinely quick at both. What it does not know is which build is in front of you, so it will offer XLOOKUP to somebody running 2019 and the cell will answer with a name error.

Nine leaves, and three of them come first

Building one, and why it breaks is the dollar sign and the rest of the reference rules. VLOOKUP, XLOOKUP and INDEX MATCH is the matching problem in full. Summarising thousands of rows in seconds is the four steps and what to do when a field will not appear. After those, text, dates and the imported mess is where the 00412 problem is solved properly, and repeatable imports and transformations is the same job done once instead of weekly.

Spreadsheet architecture

Nine topics, and why physical verification sits above all of them

Formulas and pivots ensure internal consistency. Knowing whether the numbers match reality requires checking the shelf.

  • Foundations (First three)Core mechanics that prevent silent workbook failures
    • Dollar sign lockingAbsolute versus relative cell references
    • Lookup formulasMatching keys across VLOOKUP and XLOOKUP
    • Pivot summariesAggregating thousands of rows into clean tables
  • Data integrity and workflowsManaging external inputs and transformations
    • Text and code cleanupFixing leading zeros such as 00412
    • Automated pipelinesPower Query workflows repeated weekly
  • System boundariesWhere spreadsheet numbers meet the real world
    • Concurrent editingHandling multiple editors in one workbook
    • Shelf inventory auditTesting formula outputs against physical stock

A formula can be mathematically flawless while describing stock that is no longer on the shelf.

Sideways, the rest of the suite you will meet covers the point at which a spreadsheet should have been a database, working in the same document at the same time is what happens when four people open the price list, and reasoning better, on purpose is the habit of opening the source rather than the summary. All of it sits under the programs most work actually happens in.

The four steps produce a correct pivot and say nothing about whether the number in it should be believed. Nils's stock report has been right for a year and has never once been checked against a shelf, which is a different kind of error from a lookup returning nothing and is invisible to every feature on this page. What it takes to know whether a spreadsheet is describing the world is asked at choosing one, and formatting it honestly, where the same question arrives about a picture.

Inside this, 9

Above this