DEV Community: Michael Nocito

The Dialog That Quietly Deletes Your Zip Codes

Michael Nocito — Wed, 19 Aug 2026 13:00:48 +0000

This article gives you the import habit that stops Excel from rewriting your data before you have even seen it: bring CSV files in through Data > From Text/CSV, and set identifier columns to Text. Ten seconds, and it closes off three separate kinds of silent damage.

The damage is real and specific. 08053 becomes 8053, which is no longer a New Jersey zip code. A sixteen-digit card number becomes 9.78E+15, which is no longer a number at all. And a value like SEPT1 becomes the first of September. None of these shows an error. All of them are data loss.

The short version. Excel guesses what your data is during import, and it guesses wrong for anything that looks numeric but is really a label. Import through Data > From Text/CSV and declare identifier columns as Text.

What Excel does to a CSV behind your back

Predict this before the explanation: a CSV file contains the five characters 08053. You double-click the file and it opens in Excel. What is in the cell?

A CSV is plain text. It contains characters, not types. Something has to decide whether 08053 is a number or a piece of text, and when you open a CSV by double-clicking it, or through File > Open, Excel decides alone, column by column, without asking you.

Its rule is simple: if a value parses as a number, it becomes a number. 08053 parses as 8053, so the zero is gone the moment the file opens. Not hidden. Gone. The cell holds the number 8053, and saving the file writes 8053 back over the original.

That is the trap's real shape: the destructive step is the most natural action in the world, double-clicking the file.

The three casualties

What the file said	What Excel made of it	What was lost
08053, 02134, 00501	8053, 2134, 501	Leading zeros. These are no longer valid zip codes, and joins against a proper zip list fail
9784563217890123	9.78456E+15	Precision. Excel keeps 15 digits, so the last digit becomes 0. The original number is unrecoverable
SEPT1, MARCH1, DEC1	1-Sep, 1-Mar, 1-Dec	The value itself. These were gene names, product codes, or building-room labels, and now they are dates
3-2	2-Mar	A size, a score, a version number, read as a date

The third row is famous enough to have changed science. A 2016 survey of published genetics papers found roughly a fifth of them carried Excel-converted gene names in their supplementary data, SEPT2 turned into a date, identifiers turned into numbers (Ziemann, Eren & El-Osta, 2016, Genome Biology , 17, 177). The damage was so widespread that the gene naming committee later renamed the genes. SEPT1 is now SEPTIN1 partly because of this dialog.

Say out loud which column in your own data is the vulnerable one. Zip code, account number, employee ID, SKU, phone number. Nearly every real dataset has at least one.

Why it happens: number-shaped labels

The root of all three casualties is one distinction, and it is the same one that runs through this whole series: some columns are labels wearing a number's clothes.

A zip code is not a quantity. You will never add two zip codes, average them, or ask which is larger. It is a name written in digits. The same is true of account numbers, phone numbers, SKUs and IDs. The test, one line: would adding two of these together mean anything? If no, the column is text, whatever it looks like.

Excel cannot run that test, because meaning is not in the file. It sees digits and does the numeric thing. The fix is not to blame the tool. The fix is to be the one who answers the question, at the only moment the question is asked: import.

The fix at import

Do not double-click the CSV. Open a blank workbook instead.
Ribbon: Data > From Text/CSV. Pick the file. A preview appears, with Excel's guessed type at the top of each column.
Click Transform Data. This opens Power Query, which is just the room where the import decisions get made visibly.
Right-click the identifier column's header > Change Type > Text. If a Change Column Type prompt appears, choose Replace current. Do this for every number-shaped label: zip, ID, phone, SKU.
Close & Load. The data lands in the sheet as a Table, which is step 1 of the build order done for you. Name it and carry on.

While you are in the preview there is one more setting worth glancing at: File Origin. If the file came from another system and names or accents look wrong, set it to 65001: Unicode (UTF-8). That is a different failure with the same shape, and it gets its own article later in this series.

The whole detour costs under a minute, and it is the only moment the choice exists. After import there is no dialog to reopen. There is only cleanup.

When the damage is already done

You received an xlsx where the zips are already 8053. The zeros are not hiding behind the display. The stored value is the number. Two honest options:

Re-import from the source CSV if you still have it. This is the clean fix, and it is why you never overwrite the original file: keep the raw CSV, import into a copy. That habit belongs to the same family as keeping your checks in the file.

Rebuild the zeros if the width is known. US zips are five digits, so =TEXT([@Zip],"00000") produces a text value padded back to five characters. This works only because zip codes have a fixed width. A trimmed account number of unknown length cannot be rebuilt, and a card number that went through scientific notation is gone for good. Rebuilding is a patch. The import habit is the fix.

One warning for the other direction: typing an apostrophe before a value, like '08053, keeps a single manual entry as text. It does not scale to a column, and it does nothing at import time. Treat it as a spot tool, not a strategy.

Why this works

The general lesson under the specific trap: silent type conversion is a class of error that survives careful people, because it happens before attention starts. Ziemann and colleagues' finding was not about careless labs. About one in five published, peer-reviewed, checked-by-editors papers carried the damage (Ziemann, Eren & El-Osta, 2016, Genome Biology , 17, 177). The authors of those papers looked at their data plenty. The conversion just happened earlier than looking.

That is why the countermeasure is positional, not attentional. You cannot spot-check your way out, because a converted value looks clean. You put one habit at the single door every file walks through, and the class of error stops existing in your files.

Run it on your own file

Find your most recent CSV. Downloads folder, export from any system.
Import it twice. Once by double-click, once through Data > From Text/CSV with identifier columns set to Text.
Compare the vulnerable column. If the two disagree, you have been shipping converted data and today is a good day to have found out.
Keep the raw CSV read-only. Imports go into copies. The original is evidence.
Add the check. One cell: =MIN(LEN([@Zip])) on a zip column should say 5. The moment it says 4, a zero has gone missing somewhere upstream. That is article 2's habit pointed at this article's trap.

A cheat sheet

Situation	Do	Watch for
Opening any CSV	Data > From Text/CSV, never double-click	Double-click converts before you see anything
Zip, ID, phone, SKU columns	Transform Data, column type Text	The test: would adding two together mean anything?
Accents or odd characters in the preview	File Origin: 65001 Unicode (UTF-8)	Same trap, different casualty. Covered in article 15
Zeros already stripped, width known	`=TEXT([@Zip],"00000")`	Only fixed-width values can be rebuilt
Long numbers showing as 9.78E+15	Recover from the source file	Excel kept 15 digits. The rest are zeros now
Guarding the column forever	A check cell: minimum LEN equals the expected width	Predict the value before you look, per article 2

The one habit to keep. CSVs come in through Data > From Text/CSV, and every number-shaped label is declared Text at the door. The moment of import is the only moment the choice exists.

References

Ziemann, M., Eren, Y., & El-Osta, A. (2016). Gene name errors are widespread in the scientific literature. Genome Biology , 17, 177.

Your prediction from the top: did you say 8053? And which of your own columns are you going to re-import first?

The full version of this guide lives on my site: The Dialog That Quietly Deletes Your Zip Codes.

If it was useful: Buy Me a Coffee.

Check Your Work Before Anyone Else Does

Michael Nocito — Wed, 19 Aug 2026 13:00:23 +0000

This article gives you the checking habit: when to check, where the check lives, and the one rule that makes a check worth anything. It costs two cells per question and it catches the mistakes that nothing on the screen will ever flag.

The reason it matters is blunt. Wrong Excel does not crash. It returns a confident number, formatted like every right number around it. The only thing standing between that number and your name on it in a meeting is a check you wrote twenty minutes earlier.

The short version. After any step that changes what the numbers mean, work out what one number should be, write it down, then look. Predict, then check. Never the other way around.

Everything here comes from the same build as the rest of this series: an Excel dashboard over 82,956 Steam games, walked in the eight-step build order. Checking is step 4 of those eight, and this article is that step grown into a habit. The counts quoted were re-run against the real file before publishing.

Why wrong spreadsheets look right

Before anything else, answer this from your own experience: when a formula of yours was wrong, how did you find out? Hold the answer. Most people's honest answer is "someone else noticed," and this article exists to change that.

A program with a bug usually fails loudly. A spreadsheet with a bug usually succeeds quietly. Type a wrong range into SUM and you get a number. Point COUNTIF at the wrong column and you get a number. Let a pivot decide to sum your ID codes, which is what happened in this build, and you get a number. Every one of those numbers is formatted, plausible, and wrong.

In the build, the wrong figure was 0.45% and the right figure was 0.71%. Put those side by side. Neither is round enough to look fake. Neither is big enough to feel absurd. No amount of staring tells them apart. The only thing that told them apart was a second number, computed a different way.

When to check: after meaning changes, not at the end

The instinct is to build everything and check at the end. The instinct is wrong for one mechanical reason: at the end, a failed check tells you something broke, but not which of nine steps broke it. You get to re-audit the whole file.

Check after any step that changes what the numbers mean:

Step	What can silently go wrong
A new calculated column	The condition catches the wrong rows, and every row still gets a value
A new filter	Rows you meant to keep are gone, and the sheet looks tidier for it
A join or lookup	Unmatched rows drop or duplicate, and totals move without a message
A new pivot	Excel picks the summary function, and Sum of an ID column reads like data
A paste over old values	One misaligned row shifts every value after it by one

What these five share: each one silently returns numbers instead of an error. That is the trigger. A step that would fail loudly does not need its own check. A step that would fail politely does.

Say out loud which of the five you did most recently. That step is where your next check goes.

Where the check lives

Outside the data, in a cell of its own, visible without scrolling. Not in your head, and not in a scratch file you will delete.

The shape is always the same: a label, the expected value typed as text, and the computed value beside it.

CHECK: loved games      765      =COUNTIFS(Games[PctPositive],">=95",Games[TotalReviews],">=2000")
CHECK: stayed hidden    175      =COUNTIFS(Games[PctPositive],">=95",Games[TotalReviews],">=2000",Games[IsHiddenGem],1)

The typed number is the prediction. The formula is the measurement. When they agree, the row reads as one glance of reassurance. When they disagree, you have found a bug while it is still cheap.

A check in your head does not survive the afternoon. A check off-screen does not get read. Two labelled cells beside the data get read every time you look at the sheet, which is the entire point.

How to check: predict, then look

This is the rule that separates a real check from a comforting one, and it is the one habit this article asks you to keep.

Work out what the number should be before you look at what Excel says. Use a different route than the formula took: filter and read the status bar count, take a ten-row sample and count by hand, or derive it from a number you already trust.

The reason is not politeness to your future self. It is how judgment works. Once a number is on screen, you reason from it. The question quietly changes from "what should this be?" to "could this be right?", and almost anything could be right. A check you read after the fact confirms whatever is already showing.

Two routes to the same number is the whole trick. A number is not made correct by having been produced carefully. It is made correct by having been produced twice.

Picture the last number you sent to somebody. Name the second route you could have computed it by. If no second route exists, that number was never checked, only produced.

The two-cell check from the real build

Here is the habit doing its work on real data, at the moment it mattered.

Step 2 of the build added a label column sorting 82,956 games into four groups, built in article 1. The source analysis said there should be 765 loved games: 175 still hidden, 590 found. Those three numbers were written into check cells before the pivot was built.

Then the pivot said the loved-and-found group was 0.45% of all games. The check cell said 590, and 590 of 82,956 is 0.71%. Disagreement. The hunt was short because the check was fresh: the fault had to be in the pivot, not the label column, because the label column's own checks still passed. The pivot's corner read Sum of AppID. Ten seconds in Value Field Settings fixed it, and the same fault turned up in three more fields, each caught by the same comparison.

One more check closed the loop, and it is the cheapest one on the page. The four group counts have to add up to the row count of the file: 78,064 plus 4,127 plus 175 plus 590 is 82,956. One cell of addition catches missing rows, double-counted rows and misspelled labels all at once.

Ship the checks with the file

The habit people get wrong at the last step: they delete the checks before sending, because checks look like scaffolding. Leave them in.

Two labelled CHECK cells in a shipped file do two jobs. They tell the next person that somebody verified this, which is rarer than it should be and gets noticed. And they keep working after you are gone: when someone adds rows next quarter and a check goes red, the file catches its own regression.

This is the same move as writing down what your data can't tell you. Both put your verification where the reader can see it, instead of asking to be trusted.

Why this works

Two findings, from two different fields, both pointing at the same practice.

Spreadsheet errors are the norm, not the exception. Panko's review of spreadsheet audits found errors in a few percent of cells, across nearly every spreadsheet examined, built by professionals doing careful work (Panko, 1998, Journal of Organizational and End User Computing , 10(2), 15-21). A decade later a broader review reached the same conclusion from more studies (Powell, Baker & Lawson, 2008, Decision Support Systems , 46(1), 128-138). At those rates, a file of any size contains an error. The question a check answers is not "is there a mistake?" but "is there a mistake in the number I am about to present?"

And looking harder does not work, because of anchoring. Tversky and Kahneman showed that people judging a quantity start from whatever number they were shown and adjust too little, even knowing the starting number is arbitrary (Tversky & Kahneman, 1974, Science , 185(4157), 1124-1131). That is why the prediction is written down first. It is the one moment your judgment is uncontaminated by the answer.

Run it on your own file

Open the file you most recently sent to somebody. Not a practice file. The real one.
Pick its headline number. The one somebody might repeat in a meeting.
Compute it a second way. Filter and read the status bar, count a sample by hand, or derive it from a total you trust. Write your expected value in a cell before you compare.
Compare. If they agree, label the pair CHECK and leave them in the file. If they disagree, you just learned this habit pays for itself on the first afternoon.
Add the addition check. If the file has groups, their counts must sum to the row count. One cell.
From now on, check when meaning changes. New column, new filter, new join, new pivot. Two cells each time, at the moment the step finishes.

Retrofitting checks onto every old workbook is misery, and you will not do it. Check the one number you are about to present, and build the habit forward from there.

A cheat sheet

Question	Answer
When do I check?	After any step that changes what the numbers mean. Column, filter, join, pivot, paste
Where does the check go?	A labelled cell beside the data, visible without scrolling
What does a check contain?	The expected value, typed, and the measured value, computed a different way
Which comes first?	The prediction. A check read after the fact confirms whatever is on screen
What is the cheapest check?	Group counts must sum to the row count. One cell of addition
Do checks ship with the file?	Yes. They prove verification happened and they catch future regressions
What if I have no second route?	Then the number is unchecked. Find a sample, a filter count, or a trusted total

The one habit to keep. Write the number you expect before you look at the number Excel gives you. Everything else on this page is scaffolding around that one move.

References

Panko, R. R. (1998). What we know about spreadsheet errors. Journal of Organizational and End User Computing , 10(2), 15-21.
Powell, S. G., Baker, K. R., & Lawson, B. (2008). A critical review of the literature on spreadsheet errors. Decision Support Systems , 46(1), 128-138.
Tversky, A., & Kahneman, D. (1974). Judgment under uncertainty: Heuristics and biases. Science , 185(4157), 1124-1131.

Back to your answer from the top: who found your last wrong formula, you or someone else? What would the two-cell version of that check have looked like?

The full version of this guide lives on my site: Check Your Work Before Anyone Else Does.

If it was useful: Buy Me a Coffee.

Chart Design Basics: Take Things Away, Then Point

Michael Nocito — Wed, 19 Aug 2026 13:00:14 +0000

This article gives you chart design as two moves you can run on any chart in five minutes. Move one: take away everything not carrying meaning. Move two: point, with one color, at the thing you want the reader to see. Everything else in chart design is refinement of these two.

The reason both moves work is the same fact: a reader gives a chart a few seconds, and everything on it competes for those seconds. Whatever you leave on the chart, you are spending the reader's attention on. Spend it on the finding.

The short version. Delete the legend, gridlines, axis or labels, whichever half is redundant, and the field buttons always. Grey every bar that is context. Spend the one accent color on the subject, even when the subject is the small bar.

The worked example is the hero chart from the build behind this series: hidden games against found games, the chart the whole dashboard argues from. What follows is what came off it, what went on it, and why.

Move one: take things away

Excel turns everything on by default, and it has a good excuse: it does not know what your chart is about. You do. That asymmetry is the entire method. Every default element is a guess about what might help some reader somewhere. You know the finding, so you can name, for each element, whether it carries meaning for this chart. If it does not, it is taking attention from something that does.

Before the list: picture the last chart you made, and name one element on it that a reader never needed. Nearly everyone can, immediately, which tells you how the defaults err.

What came off the real chart, item by item

Removed	Why it carried nothing here
The legend	It named one series. There was only one. A one-entry legend is a label for nothing
The field buttons	Pivot-chart editing controls. They are interface, not information, and they ship in screenshots
The gridlines	Their job is helping the eye estimate values. The data labels do that job exactly
The vertical axis	Same job as the gridlines, already done by the labels

And one thing went on: data labels , the exact number on each bar. That is the trade at the heart of move one. Labels give precise values, so the two estimation aids, axis and gridlines, become redundant and come off. A chart with axis, gridlines AND labels answers the same question three ways.

Nothing here says gridlines are bad. On a chart with many bars and no labels, gridlines are the right tool and labels would be clutter. The rule is not a list of banned elements. It is one question asked per element: what job is this doing, and is anything else already doing it?

The never-twice rule

That question generalizes into the rule you can carry: never make the reader do the same job twice. An axis and data labels are two ways to read one number. A legend and direct labels on the bars are two ways to learn one name. Say which one earns its place, keep it, and delete the other. Twice is not thoroughness. Twice is noise wearing thoroughness's clothes.

Move two: point

With the clutter gone, every bar is still the same color, and the reader has to work out which one matters. Color is how you answer that before they ask. Color is not decoration. It is a pointer.

The method: mute what is context, save the color for the thing you want them to look at. Context bars go grey. The subject gets the one accent color. One, because two accents is two pointers, and two pointers point at nothing.

The counter-intuitive part, and the build hit it head on: on the hero chart, the story is not the tall bar. The found games' huge audience is the context. The subject is the tiny bar, the 175 hidden games with almost nobody playing them. So the tall bar went grey and the small bar got the color. Say the principle out loud, because it runs against instinct: emphasis follows the subject, not the size. If the finding is about the small thing, the small thing gets the ink.

The two-click trick

The mechanic that makes single-bar coloring possible, and the thing this article exists to teach your hands:

Click any bar once. Excel selects the whole series. Recoloring now recolors everything, which is how people end up believing single-bar color is impossible.
Click the same bar again. One click, not a double-click. Now just that bar is selected, and Format Data Point colors it alone.

First click, the series. Second click, the point. Grey the series with the first selection, then accent the subject with the second. The same two-click selection works in Tableau and Power BI, which is part of why this is a method and not an Excel tip.

Titles state the finding, with its comparator

The title is the most-read text on the chart, and the default, Chart Title, or a field name like Sum of EstOwnersMid, spends that position on nothing. Write the finding into it: the sentence the chart argues, with a number in it.

The build caught a subtler failure worth passing on. The draft title said "Found games reach 21x the audience." Read it cold: 21 times what? A reader arriving fresh has no comparison. The shipped title says "Found games reach 21x the audience of hidden ones." Same title, finished. A comparative claim carries its comparator , because the reader was not in the room where the comparison was set up.

Cover your own chart's title and read the bars alone. Then read the title. If the title tells the reader what to conclude, and the bars back it up, the chart is done arguing. That division of labor, title claims, bars prove, is the whole relationship.

Why this works

Both moves are applications of measured results, not taste.

Taking away: Cleveland and McGill's graphical perception experiments established that readers extract values most accurately from position and length judgments, and that accuracy degrades as more visual work is demanded (Cleveland & McGill, 1984, Journal of the American Statistical Association , 79(387), 531-554). Redundant elements demand visual work without adding information, which is the definition of the stuff move one deletes.

Pointing: color singletons are found fast. Treisman and Gelade's feature-integration experiments showed that a target differing from its surroundings in one feature, like color, pops out in essentially constant time regardless of how much else is on the display (Treisman & Gelade, 1980, Cognitive Psychology , 12(1), 97-136). One accented bar among grey ones is exactly that: the reader's eye lands on the subject before they have decided to look for anything.

Run it on your own chart

Open your most recent chart and inventory it. Legend, gridlines, axis, labels, title, field buttons. Name each one's job.
Delete every element whose job is already done by another element. Click it, press Delete. The never-twice rule decides.
Grey the series. One click, then a grey fill.
Accent the subject. Second click on the one bar the finding is about, then the one accent color. Ask first: is the story the big bar, or the small one?
Rewrite the title as the finding , number and comparator included.
Glance test. Look away, look back, and notice where your eye lands. It should land on the accented bar, then the title. If it lands anywhere else, something survived move one that should not have.

A cheat sheet

Element	Keep when	Delete when
Legend	Several series need naming	One series, or bars are labeled directly
Gridlines	Many values, no data labels	Data labels are on
Value axis	No labels, reader estimates	Data labels are on
Data labels	Few bars, exact values matter	Dozens of bars, labels become texture
Field buttons	Never on anything shipped	Always. Right-click, hide all
Color	One accent on the subject, grey context	Rainbow series with no subject
Title	States the finding, number and comparator	Restates a field name

The one habit to keep. Before shipping any chart, ask of every element: what job is this doing, and is anything else already doing it? Then ask of the color: where do I want the first glance to land? Two questions, five minutes, every chart.

References

Cleveland, W. S., & McGill, R. (1984). Graphical perception: Theory, experimentation, and application to the development of graphical methods. Journal of the American Statistical Association , 79(387), 531-554.
Treisman, A. M., & Gelade, G. (1980). A feature-integration theory of attention. Cognitive Psychology , 12(1), 97-136.

The element you named at the top, the one your reader never needed: is it still on the chart? Two clicks from now it does not have to be.

The full version of this guide lives on my site: Chart Design Basics: Take Things Away, Then Point.

If it was useful: Buy Me a Coffee.

The Names Came In as Gibberish and Excel Said Nothing

Michael Nocito — Wed, 19 Aug 2026 13:00:05 +0000

This article gives you the fix for a specific ugly sight: names importing as Ã©, Ã¼ and rows of symbols where letters should be. The fix at import is one dropdown: Data > From Text/CSV, set File Origin to 65001: Unicode (UTF-8). The rest of the page is what happened, how to spot it, and why it is worse than it looks.

It is worse than it looks because it is not a font problem. Those names are now wrong in the data. Search for the real title and you will not find it. Join on the name and the join fails. It is data loss that dresses up as a display glitch, which is why it ships.

The short version. A text file has to say which alphabet it is written in. The file was written in the modern universal one, UTF-8. Excel opened it assuming the old Western European one. Every character outside plain English got read as the wrong symbols.

This is the third door of the family from article 4 and article 6: Excel deciding what your data is and never mentioning it. In the build behind this series it was found late, in a finished dashboard: two of the top fifteen games displayed as garbage, and a count against the file put the damage at 4,685 of 82,956 names. Nothing had errored at any point.

What happened, in plain terms

A text file on disk is just numbered codes, one per character. An alphabet, an encoding in the trade's word, is the codebook that says which number means which letter. The file does not carry its codebook visibly. The program opening the file has to know, or guess.

The build's CSV was written in UTF-8, today's standard, which covers every language by spending two or more codes on any letter beyond plain English. Excel's legacy open path guessed the old Western European codebook instead, which reads one code per character, always. So every two-code letter was read as two separate wrong characters. An é, stored as two codes, came out as Ã©.

Predict what that does to a Romanian game title with four accented letters. Four two-code letters, eight wrong characters, and the title reads as gibberish. That is exactly the "Aventura Copilului Albastru" row that flagged the problem in the build's top fifteen.

How to spot it: the three-character signature

The corruption has a fingerprint, because the wrong codebook maps UTF-8's lead codes to a small set of characters. Three sequences do most of the appearing:

You see	It was probably
`Ã` followed by a symbol: `Ã©`, `Ã¼`, `Ã±`, `Ã€`	An accented Latin letter: é, ü, ñ, À
`Â` before punctuation or spaces	A non-breaking space or symbol
`â€` plus one more: `â€™`, `â€œ`, `â€"`	Curly quotes and dashes from any word processor
Strings of `È`, `Ä`, `Å` pairs	Eastern European, Turkish, Nordic letters

Every mapping in that table was re-run through the actual conversion before publishing: é read under the wrong codebook really does come out Ã©. Say the search you would run to sweep a file for this. Ctrl+F for Ã is the whole audit, and it takes ten seconds.

Why it is data loss, not a display glitch

A font problem would mean the stored value is right and the pixels are wrong. This is the reverse: the pixels faithfully show a stored value that is now wrong. Consequences, in the order they usually bite:

Search fails. The user searches the real title. The cell holds the mangled one. No match, and no hint why.

Joins fail. A lookup against a clean source list matches on the name. 4,685 rows no longer match anything, and per article 2's theme, a failed match does not error. The rows just drop or blank.

Grouping splits. The same publisher spelled cleanly in one file and mangled in another becomes two publishers, which is the entity-resolution problem manufactured out of nothing.

The one mercy: the corruption is mechanical, so it is reversible if you still have the original file, and often even from the mangled text, because the wrong reading was consistent. Which leads to the two fixes.

The fix at import

Data > From Text/CSV, the same door as article 4, and never a double-click.
In the preview, top-left: File Origin. Set it to 65001: Unicode (UTF-8).
Look at a row you know has accents before loading. The preview is the test: names read as names, done.

One import, two dropdowns, both traps from this family closed: File Origin for the alphabet, column type Text for the identifiers. That pairing is the whole defensive import, and it costs under a minute.

The fix after the fact

The build's dashboard was already assembled when the damage surfaced, so re-importing everything was the expensive path. What it did instead, and what works in general:

Go back to the raw CSV , which existed because raw files never get overwritten. Re-read just the damaged column under the right encoding.
Export that clean column with a byte order mark , a short invisible marker at the front of a file that tells Excel which alphabet follows, so the paste-source opens correctly on any machine.
Paste it over the mangled column, refresh the pivots. Names heal, joins come back, and the checks confirm the row count never moved.

If the raw file is gone, recovery is still often possible by reversing the wrong reading, but that is a rescue job, not a workflow. The workflow is the dropdown at import.

Why it hits names hardest

Notice which columns this family of failure lands on. Numbers survive encoding trouble, because digits sit in the plain-English range every codebook agrees on. The damage concentrates in names : people, places, products, titles. The columns you join on, search by, and put in front of readers.

6% of a column sounds survivable until it is 6% of your customer names in a join key. And the affected rows are not random: they are systematically the non-English ones, so any analysis touching international data quietly loses exactly that slice. A dashboard built on it would undercount a specific population and look completely fine, which by now you will recognize as this series' recurring villain: the error that returns confident numbers.

Run it on your own file

Ctrl+F forÃ in any imported dataset with names in it. Ten seconds.
Hits: find the raw file and re-import it properly. File Origin 65001, column types set, per this page and article 4.
No raw file: assess before rescuing. How many rows, which analyses touch the column, whether the join keys are affected.
Add the canary check. Keep one known-accented value in a check cell: =COUNTIF(Games[Name],"*é*") or simply eyeball a pinned row you know has accents. If the canary reads clean, the column probably does.
Fix the pipeline, not just the file. If an export lands on your desk weekly, the File Origin setting is part of the import routine now, or this article reruns weekly too.

A cheat sheet

Situation	Do	Watch for
Any CSV with names in it	Data > From Text/CSV, File Origin 65001	Check an accented row in the preview before loading
Sweeping a file for damage	Ctrl+F for `Ã`, then `â€`	Hits mean the stored values are wrong, not the font
Damage found, raw file exists	Re-import the column correctly, paste over, refresh	Export the clean column with a byte order mark
Damage found, no raw file	Reverse the misreading, as a rescue	Verify against any external source you can find
Joins mysteriously dropping rows	Check the join key for the signature	The dropped rows are systematically the non-English ones
Recurring exports	Put File Origin in the routine	A fixed file with a broken pipeline is a one-week fix

The one habit to keep. Every import of text data gets two settings at the door: File Origin 65001 for the alphabet, Text type for the identifiers. The family of silent converters has three doors, and this closes the last one.

Run the ten-second audit right now on the biggest imported file you have open this week. What did Ctrl+F Ã come back with?

The full version of this guide lives on my site: The Names Came In as Gibberish and Excel Said Nothing.

If it was useful: Buy Me a Coffee.

Correlation vs Causation: The Three Explanations for Any Pattern

Michael Nocito — Tue, 18 Aug 2026 13:00:04 +0000

By the end of this page you will have one habit: every time two things move together, you say the three candidate explanations out loud before acting. X causes Y. Y causes X. A third thing drives both. You will also know the quiet fourth, coincidence in a small sample, and one cheap test that separates the explanations without running an experiment. It is about twenty minutes.

Here is what to actually do with it today. Take the last chart you saw where two lines moved together, and say the three explanations for it, in order, in one breath each. If any of them survives out loud, the finding is not ready to ship as a cause.

The short version: a correlation is one fact with four possible stories behind it. Only one of those stories is "X causes Y", and the correlation itself cannot tell you which story is true.

One picture carries the whole idea, so it comes first. The same observed link between X and Y, and the four stories that could each have produced it.

The original carries a diagram here. In words: Four panels side by side, each showing the same two boxes labelled X and Y. In the first panel a solid arrow points from X to Y, meaning X causes Y. In the second panel the solid arrow points the other way, from Y to X, meaning Y causes X. In the third panel a third box labelled Z sits above the pair in a warning colour, with two solid arrows running down from Z to X and from Z to Y, while the direct line between X and Y is dashed and crossed out, meaning a third thing drives both and the link between X and Y is not a real causal link. In the fourth panel there is no arrow at all between X and Y, only a small scatter of dots between them, meaning the pattern is chance in a small sample. All four panels would produce the same observed correlation, which is why the correlation alone cannot pick between them.

Every number on this page is computed. The worked example is a 12-row table you can see in full, and every correlation on the page was produced by running the shown code on that table. If reading data with pandas is new, SQL and Python for analysts covers the setup and comes back here in ten minutes.

1. The three explanations, and the quiet fourth

Before the explanation: a dashboard shows that customers who open your emails spend more. Marketing wants to double the email volume. What are the other ways that pattern could have happened, without email causing anything?

A correlation means two things move together. When X is high, Y tends to be high, or tends to be low. That is the whole fact. The question that matters is why, and there are exactly four candidate stories.

X causes Y. Opening emails makes people spend more. This is the story everyone wants.
Y causes X. People who already spend a lot are more interested, so they open more emails. Same data, arrow reversed, completely different action.
A third thing drives both. Loyal customers both open emails and spend. Loyalty is the engine, and email opening is just along for the ride. The third thing has a name: a confounder , which in everyday words is the hidden common cause that makes two innocent things look connected.
Coincidence. In a small sample, unrelated things line up by luck alone. This one is quiet because nobody proposes it in a meeting, and it is the most common story of all when the sample is small. Section 7 puts a number on it.

The habit this page is selling costs about ten seconds. Before acting on any pattern, say all four stories out loud, in order, and notice which ones you cannot rule out. Most patterns die at story three, and the ones that survive all four are the ones worth an experiment.

Why out loud? Because in your head, the story you want wins by default. Spoken, "or maybe big spenders just open more email" is hard to unhear.

2. The worked example: a real correlation you can compute

Before the explanation: ice cream sales and drownings rise and fall together, month by month, in almost every summary of this classic. Nobody believes ice cream drowns people. So what is the third thing?

Here is a full year for one lake town. Twelve rows, three columns: average temperature, ice cream sales in thousands of dollars, and drownings.

Month	Avg temp (°F)	Ice cream sales ($k)	Drownings
Jan	36	8	1
Feb	38	9	0
Mar	46	14	0
Apr	55	19	1
May	65	27	4
Jun	74	35	3
Jul	81	42	5
Aug	79	40	4
Sep	70	30	5
Oct	58	21	0
Nov	47	14	1
Dec	39	10	0

Read the sales column and the drownings column together. July: highest sales, most drownings. January and December: lowest sales, almost none. The two columns clearly move together, and the correlation between them computes to 0.86. Here is the code that produces every correlation on this page, so you can rerun it yourself.

import pandas as pd

df = pd.DataFrame({
    "month": ["Jan","Feb","Mar","Apr","May","Jun",
              "Jul","Aug","Sep","Oct","Nov","Dec"],
    "avg_temp_f":  [36,38,46,55,65,74,81,79,70,58,47,39],
    "ice_cream_k": [8,9,14,19,27,35,42,40,30,21,14,10],
    "drownings":   [1,0,0,1,4,3,5,4,5,0,1,0],
})

print(df[["avg_temp_f","ice_cream_k","drownings"]].corr().round(2))


             avg_temp_f  ice_cream_k  drownings
avg_temp_f         1.00         0.99       0.86
ice_cream_k        0.99         1.00       0.86
drownings          0.86         0.86       1.00

Now read the whole grid, not just the one cell. Ice cream and drownings sit at 0.86. But temperature correlates with ice cream at 0.99 and with drownings at 0.86. The third thing did not need hunting. It is right there in the output, correlated with both suspects more strongly than they need to be correlated with each other. Heat sends people to the ice cream stand and heat sends people into the lake, and the two crowds never have to meet.

Picture running that three-column grid on your own data: your metric, the thing it moves with, and the one variable you quietly suspect is behind both. The confounder usually announces itself in exactly this way, as the row with the biggest numbers in it.

3. What a correlation coefficient actually measures

Before the explanation: the number 0.86 came out of the machine. What is it a measurement of, and what would 1.0, 0, and negative 0.86 each look like?

The correlation coefficient, written r , measures linear co-movement: how tightly two columns track a straight line together. It runs from −1 to 1. At 1, knowing one value tells you the other exactly, and both rise together. At −1, the same, but one falls as the other rises. At 0, knowing one tells you nothing linear about the other. Our 0.86 means the monthly pairs sit close to a rising line, with some scatter.

Three limits on what r says, each of which matters in real work.

It is direction-blind. r(sales, drownings) and r(drownings, sales) are the same number. The statistic has no idea which column you think is the cause. Any arrow you see in it, you drew yourself.
It only sees straight lines. A perfect U-shaped relationship, where Y is high at both extremes of X, can produce an r near zero. A near-zero r means "no linear pattern", not "no pattern". This is one reason to plot the points before trusting the number.
It says nothing about size. An r of 0.9 can describe an effect too small to matter, because r measures tightness of tracking, not steepness. A tight pattern of tiny changes still scores high.

The word for a variable's r with a business outcome is often spoken as if it were a strength-of-cause score. It is a tightness-of-tracking score, computed with no knowledge of cause at all. Keeping those two ideas separate is most of this page.

4. The cheap test: hold the third thing still

Before the explanation: if temperature really is the engine behind both columns, what should happen to the ice cream and drownings correlation among months that all have roughly the same temperature?

It should shrink toward zero. If heat is doing all the driving, then among equally hot months there is no driver left, and sales and drownings should wander independently. That prediction is testable on the data you already have, and this is the cheapest causal check an analyst owns: segment by the suspected confounder and see whether the pattern survives.

warm = df[df.avg_temp_f >= 65]   # May through Sep, 5 months
cool = df[df.avg_temp_f < 65]    # the other 7 months

print(round(warm.ice_cream_k.corr(warm.drownings), 2))
print(round(cool.ice_cream_k.corr(cool.drownings), 2))


0.1
0.02

The correlation was 0.86 across the whole year. Inside the warm months it is 0.10. Inside the cool months it is 0.02. Once temperature is held roughly still, the relationship between ice cream and drownings is gone. The pattern did not survive the split, which is exactly what "a third thing drives both" predicts and what "X causes Y" does not.

Say in your own words why the correlation vanishing inside each band clears ice cream, before reading on. If you can say it, you own the method.

Here is the version I would say. The yearly correlation was built out of one contrast: warm months have high sales and high drownings, cool months have low sales and low drownings. Warm months average 4.2 drownings against 0.43 in cool months. Remove that contrast by comparing only like with like, and there is nothing left, so the contrast, meaning temperature, was the whole relationship.

What survival would have meant. If the within-band correlations had stayed near 0.86, temperature would be cleared instead, and the causal stories would still be live. Segmenting cannot prove cause. It can only eliminate a named suspect, one suspect per split. That is still a bargain, because it runs in one line on data you already have.

5. Reverse causation, and why timing settles it

Before the explanation: story two was "Y causes X". What is the one thing a cause must always do that its effect cannot?

Come first. An effect cannot happen before its cause. So the standard check on reverse causation is timing: line the two series up in time and ask which one moves first.

Take the email example from section 1. If opening emails causes spending, the open should come before the purchase, and this month's opens should predict next month's spending better than the reverse. If the data shows spending rising before open rates rise, the arrow you wanted is dead on arrival. In practice this means computing the correlation at a lag: shift one column by a month and recompute. It is the same corr() call on a shifted column, so it costs nothing.

Two honest cautions. First, timing can eliminate an arrow but cannot confirm one, because a confounder can also move both series in sequence. Cold snaps end, then ice cream recovers, then swimming recovers. Second, some pairs feed each other in both directions, like practice and confidence, and a lagged correlation will show both arrows at once. When that happens, write down both, because that is the true shape.

6. The full before and after

Same data, same finding, written up twice. The difference is what a reader is licensed to do after reading it.

Before

Ice cream sales are strongly correlated with drownings
(r = 0.86). Reducing summer ice cream promotions should
be considered as a drowning-prevention measure.

Every word before the parenthesis is defensible, and the sentence after it is fiction. The number is real. The leap from "correlated" to "reducing X will change Y" is the entire mistake, and nothing in the data supports it.

After

Monthly ice cream sales are associated with drownings
(r = 0.86, n = 12 months).

Checked: temperature is a confounder. It correlates with
both series (0.99 with sales, 0.86 with drownings), and
within temperature bands the association disappears
(r = 0.10 warm months, r = 0.02 cool months).

Read: both rise in hot months because heat drives both.
No causal claim. If prevention budget depends on this,
the variable to act on is warm-month lake supervision,
not sales.

Three habits are visible in the after version. It says "associated with" , which claims co-movement and nothing more. It names the confounder it checked and shows the check, which is what makes the write-up trustworthy rather than merely cautious. And it states the sample size, so the reader can weigh the coincidence story for themselves. Naming what you checked and what you could not check is the same discipline as documenting data limitations, applied to one sentence.

7. Edge cases that make correlations lie

Before the explanation: with only 12 rows, how often would two completely unrelated columns show a correlation as strong as 0.5, just by luck?

More often than feels possible. I generated 1,000 pairs of random 12-value columns, pure noise with no relationship at all, and counted: 106 of the 1,000 pairs hit an r of 0.5 or stronger, and 10 of them hit 0.7 or stronger. Roughly one random pair in ten looks moderately correlated at this sample size. That is the coincidence story with a number on it, and it is why an exciting correlation in a 12-row summary deserves suspicion before celebration.

import random
random.seed(11)
hits = 0
for _ in range(1000):
    x = pd.Series(random.random() for _ in range(12))
    y = pd.Series(random.random() for _ in range(12))
    if abs(x.corr(y)) >= 0.5:
        hits += 1
print(hits)   # 106

Four more ways a real correlation misleads, each named.

Both series just trend. Anything that grows over time correlates with anything else that grows over time: revenue, headcount, and the number of guides on this site would all inter-correlate beautifully. A correlation between two trending series is mostly a correlation with the calendar. Compare month-to-month changes instead of levels before believing it.

The pattern flips when you split it. A relationship can point one way in every subgroup and the opposite way overall, because group sizes differ. This is Simpson's paradox, and it is the same segmenting move from section 4 delivering a nastier surprise: the aggregate answer and every subgroup answer can genuinely disagree. It is why segmenting by the variables you know matter is not optional.

You only kept the survivors. Correlations computed on a filtered group, like top customers or games with many reviews, can be created or destroyed by the filter itself. If both variables helped a row pass the filter, the survivors will show a link the full population does not have.

One outlier is holding the whole number up. With few rows, a single extreme pair can manufacture a strong r on its own. Plot the points once before quoting the number. If the story collapses when one dot is covered with your thumb, it was never a story. Plotting before computing is the core move of exploratory data analysis.

Why this works

The segmenting test in section 4 is the working analyst's version of a very old result. Simpson showed formally that the association between two variables can change size, vanish, or reverse when a third variable is brought into the table, and that the aggregate table alone cannot tell you which reading is right (Simpson, 1951, Journal of the Royal Statistical Society, Series B , 13(2), 238–241). The practical consequence is the habit this page teaches: no association is safe to interpret until the candidate third variables have been held still and the pattern re-checked.

The coincidence story is also not a vague worry. Small samples produce extreme statistics as a matter of arithmetic, because estimates computed on few values swing widely, and any process that surfaces the strongest patterns, like a dashboard sorted by correlation, will surface the small-sample flukes first (Gelman & Price, 1999, Statistics in Medicine , 18(23), 3221–3234). The 106-in-1,000 simulation above is that effect made visible at n = 12.

And the reason this page keeps asking you to answer before it explains: being prompted to generate an explanation yourself, before receiving one, reliably improves understanding and retention of that material, across a large body of controlled studies (Bisra, Liu, Nesbit, Salimi, & Winne, 2018, Educational Psychology Review , 30(3), 703–725). Guessing the third thing before the correlation grid showed it is why temperature will come to mind the next time two of your own columns move together.

Using this on your own project

Think of one claim in a report you have shipped that quietly leans on "X drives Y". Auditing every such claim at once is miserable and you will not finish it. Do this instead, in order.

Say the four stories out loud for that one claim: X causes Y, Y causes X, a third thing drives both, coincidence. Ten seconds. Note which ones you cannot rule out.
Run the three-column correlation grid : X, Y, and your best confounder candidate. If the candidate correlates strongly with both, it is promoted to prime suspect.
Segment by the suspect and recompute the X and Y correlation inside each band. Vanished: the suspect owns the pattern. Survived: the suspect is cleared, and the causal stories are still live.
Check timing if reverse causation is plausible: shift one series and see which direction predicts better.
Rewrite the sentence as "associated with", name the confounders you checked, state n. If the claim still needs a causal verb after all that, the honest next step is an experiment: split subjects randomly, change X for one group only, and compare. Randomizing is the only move that silences all confounders at once, known and unknown, and it is exactly what an A/B test is. The Stats Kit has a lesson on running one properly.

If you have paper nearby, one optional drawing is worth five minutes. Draw the four panels from the figure yourself, from memory: X to Y, Y to X, Z down to both, and the dots of chance. Then write your own X, Y, and Z from a live project on the third panel. Redrawing it is a retrieval attempt, and filling in your own variables is the transfer step.

More detail on this, and more like it. Every how-to sits in one place on the guides index: SQL, statistics, charts, data migration, and the working habits around them.

The whole thing on one screen

This is the retrieval sheet. Cover the right column, work down the left, and say each answer out loud before you check it.

Concept	What it means
Correlation	Two things move together. One fact, four possible stories.
The four stories	X causes Y. Y causes X. A third thing drives both. Coincidence.
Confounder	A hidden common cause that makes two innocent things look connected.
r	Linear co-movement, −1 to 1. Tightness of tracking, not strength of cause.
What r cannot see	Direction, curves, and the size of the effect.
Finding the confounder	Correlation grid with the candidate: it should correlate with both.
The cheap test	Segment by the suspect. Pattern vanishes: suspect owns it. Survives: suspect cleared.
What segmenting proves	Eliminates one named suspect per split. Never proves cause.
Reverse causation check	Timing. The cause has to move first. Recompute r at a lag.
Coincidence at n = 12	About 1 in 10 random pairs shows
Trending series	Everything that grows correlates with everything that grows. Compare changes, not levels.
Simpson's paradox	Subgroups and the total can genuinely disagree. Segment before interpreting.
What settles it	An experiment. Random assignment silences known and unknown confounders at once.
The reporting habit	"Associated with", name the confounders you checked, state n.

The one habit to keep. If you take nothing else from this page, say the four stories out loud before acting on any pattern: X causes Y, Y causes X, a third thing drives both, chance. It costs ten seconds, and the expensive mistakes in analysis are almost never in the arithmetic. They are in the arrow.

One last thought, and I would genuinely like other people's answers. My temperature column confessed the moment I printed the full correlation grid instead of the one cell I was asked about. What is a correlation your team currently treats as a cause, and which of the four stories has nobody said out loud yet?

References

Simpson, E. H. (1951). The interpretation of interaction in contingency tables. Journal of the Royal Statistical Society, Series B , 13(2), 238–241.
Gelman, A., & Price, P. N. (1999). All maps of parameter estimates are misleading. Statistics in Medicine , 18(23), 3221–3234.
Bisra, K., Liu, Q., Nesbit, J. C., Salimi, F., & Winne, P. H. (2018). Inducing self-explanation: A meta-analysis. Educational Psychology Review , 30(3), 703–725.

The full version of this guide lives on my site: Correlation vs Causation: The Three Explanations for Any Pattern.

If it was useful: Buy Me a Coffee.

How to Choose the Right Chart: One Question About Your Data

Michael Nocito — Mon, 17 Aug 2026 13:00:40 +0000

By the end of this page you can pick the right chart in about five seconds, by asking one question: what comparison must the reader make? The four possible answers each map to one chart, and you will also know the two miscasts that cause most bad charts, the axis rules that keep bars honest, and the escape hatch for when one chart holds too much. It is about twenty minutes.

Here is what to actually do with it today. Open the last chart you made. Say out loud what the reader is supposed to compare in it. If the chart type does not match that comparison in the table below, remake it. It is usually a two-minute fix.

The short version: comparison across categories takes a bar. Change over time takes a line. Relationship between two measures takes a scatter. Part of a whole takes a bar too, once you pass a few slices.

One picture carries the fork, so it comes first.

The original carries a diagram here. In words: A decision fork. On the left, a single rounded node contains the question: compare what? Four lines branch from it to four small chart pictures on the right, stacked vertically. The first branch, labelled categories, leads to a miniature bar chart with four vertical bars of different heights. The second branch, labelled time, leads to a miniature line chart with a single rising line over an axis. The third branch, labelled relationship, leads to a miniature scatter plot of dots drifting upward to the right. The fourth branch, labelled parts, leads to a miniature horizontal stacked bar divided into segments, drawn next to a small crossed-out pie, meaning that for part-of-whole comparisons a bar is preferred over a pie. The picture says that the single question of what the reader must compare selects one of four chart types.

Every number on this page is computed. The example tables are shown in full, and every total, percentage, and correlation was verified by running the arithmetic in Python before it went on the page.

1. The one question, and the decision table

Before the explanation: two analysts get the same table of revenue by region. One makes a bar chart, one makes a pie. What question would decide who is right?

Not "what does the data look like". The deciding question is about the reader: what comparison must the reader make? A chart is a machine for making one comparison easy, and the chart type is chosen by that comparison, not by the shape of the table.

Here is the fork laid out fully, because it is the decision the whole page hangs on. The question: what must the reader compare? The four possible answers, and what each one means for you: values across categories, which needs lengths the eye can rank, so a bar. Change over time, which needs slope the eye can follow, so a line. Whether two measures move together, which needs one dot per record, so a scatter. Or shares of a total, which needs parts against a whole, and past a few slices that job also goes to a bar. What decides between them is only which comparison the reader must make first. It matters because the same table drawn with the wrong machine makes the right comparison hard and a wrong one easy.

The reader must compare	Chart	What the eye uses	Example question
Values across categories	Bar	Length from a shared baseline	Which region sold most?
Change over time	Line	Slope	Are signups accelerating?
Two measures, together	Scatter	Position of dots	Do bigger stores sell more per head?
Parts of a whole	Bar (pie only up to 3 or so slices)	Length, not angle	Which lines carry most of revenue?

If the reader must make two different comparisons, that is two charts, not one clever one. Section 7 covers the honest way to do that.

2. Categories: the bar chart, and how to make it readable

Before the explanation: here is quarterly revenue for five sales channels, in thousands. Which two are hardest to tell apart, and what would make that comparison instant?

Channel	Revenue ($k)
North	412
South	388
East	341
West	296
Online	268

The comparison is across categories, so this is a bar chart. The reason bars work is mechanical: every bar starts from the same baseline, so comparing values reduces to comparing lengths, and ranking lengths from a shared start is one of the judgements human eyes make most accurately. North against South is a 24 thousand dollar gap on 388, a 6.2% difference, and side-by-side bars from the same baseline make even that small gap visible.

Three habits turn a default bar chart into a readable one.

Sort by value , not alphabetically, unless the categories have a real order. Sorted bars answer "which is biggest, which is next" before the reader asks.
Go horizontal when labels are long. Category names read flat, nobody tilts their head, and ten categories fit without abbreviation.
Put the number at the end of the bar when there are few bars. The chart carries the ranking, the label carries the value, and the axis can retire.

Total across the five channels is 1,705 thousand. Keep totals like that in the subtitle or a caption, because a bar chart of parts does not show its own sum.

3. Time: the line chart, and the first miscast

Before the explanation: monthly signups run 180, 176, 189, 197, 214, 222, 241, 238, 260, 271, 266, 290 from January to December. What is the one thing a reader should take from that series, and which visual feature carries it?

The takeaway is growth: 180 to 290 is a 61.1% rise across the year, climbing steadily with small monthly wobbles. The feature that carries it is slope. A line chart connects the months in order, and the eye reads the tilt of the line as speed of change directly. That is the whole reason time gets a line: the horizontal axis has a true order, so connecting the points draws the trend itself.

Which brings up the first of the two great miscasts. A line chart across categories implies an order that is not there. Draw North, South, East, West, Online as points and connect them with a line, and the line manufactures a journey: revenue "falls" from North to Online, as if the regions were stops on a route. The slope the eye reads so well is now reporting a relationship that does not exist, because nothing travels from North to South. Categories are not ordered, so nothing may connect them. The moment the horizontal axis stops being time or another true sequence, the line comes off and the bars go on.

The reverse swap is milder but real: bars across many time periods turn a trend into a picket fence, and the eye compares individual posts instead of following the path. Use bars for time only when the periods are few and the question is "this quarter against last quarter", a single comparison rather than a trajectory.

4. Relationship: the scatter plot

Before the explanation: ten stores, two measures each: staff on shift and weekly sales in thousands. Staff of 4, 5, 5, 6, 7, 8, 9, 10, 11, 12 against sales of 21, 24, 26, 27, 33, 34, 40, 42, 46, 49. What question does this table ask that neither a bar nor a line can answer?

The question is whether the two measures move together, and the scatter plot is the only chart in the set built for it. One dot per store, staff on one axis, sales on the other. No baseline, no time, just position. The eye reads the cloud's shape: drifting up and to the right means the measures rise together, and how tightly the dots hug a line is the strength of the relationship. For these ten stores the pattern is very tight: the correlation computes to 0.99, nearly a straight line.

The scatter is also the chart most likely to be over-read, because a clean rising cloud whispers "staff cause sales". A scatter shows association only. Busy stores may get more staff precisely because they sell more, or a third thing, like location, may drive both. The chart cannot pick between those stories, and correlation vs causation is the page for telling them apart before anyone acts on the cloud.

Picture your own data for a second: two numeric columns you have quietly wondered about, one dot per row. That mental image, and whether a shape appears in it, is the fastest analysis step you own.

5. Parts of a whole: why the bar beats the pie past a few slices

Before the explanation: nine product lines share revenue as 14, 13, 12, 11, 11, 10, 10, 10, and 9 percent, which sums to 100. On a pie, could you rank the top four slices by eye?

Almost nobody can, and the reason is worth owning because it explains half of chart design. A pie encodes each value as an angle , the width of the wedge at the centre. A bar encodes the same value as a length from a shared baseline. Human vision judges lengths against a common baseline far more accurately than it judges angles: in Cleveland and McGill's experiments, position and length judgements produced markedly smaller errors than angle judgements of the same values (Cleveland & McGill, 1984, Journal of the American Statistical Association , 79(387), 531–554). The pie is not ugly. It is asking the eye to do the measurement it is worst at.

Say in your own words why nine near-equal wedges are the worst case for angles before reading on. If you can say it, the pie rule stops being a rule and becomes a consequence.

Here is my version. Ranking wedges means comparing 14% of a circle with 13% of a circle, a difference of 3.6 degrees of arc, between wedges that start at different rotations. The same values as bars differ by a visible step in length from the same starting line. The comparison the reader must make, ranking parts, is a length job.

The honest scope of the pie: two or three slices, where the question is only "is this half, more than half, or less". "Support is about a third of tickets" survives as a pie. Nine slices never does.

6. The axis rules: when zero is mandatory and when zooming is fine

Before the explanation: two bars, 62 and 68. The chart's axis starts at 60 instead of 0. How big does the difference look, and how big is it?

Drawn from a baseline of 60, the first bar has length 2 and the second has length 8, so the second bar is four times as long. The real difference is 68 against 62: 9.7%. The chart shows 4x where the data says under 10%, and the reader's eye believes the chart, because in a bar chart length encodes value. Cut the bottom off the bars and you have cut the values themselves. So the rule is absolute for bars: the value axis starts at zero, every time, no exceptions for tight ranges. If the differences vanish at zero, that is the finding, or the wrong chart type is on duty.

A line chart lives under a different rule, and the reason is the encoding. A line encodes change as slope , not as distance from the floor. Zooming the axis of a line chart stretches the slopes so real movement becomes visible, and no length is being lied about because no length is being read. A line of daily temperatures plotted from zero kelvin would be perfectly honest and perfectly useless. Zoom the line, label the axis clearly, and keep zero for the bars.

Name the fork when you feel the urge to truncate: the question is which visual feature the reader is measuring. If it is length, zero is mandatory. If it is slope, zooming is legitimate. What decides is the chart type already on the page, and it matters because a truncated bar is the single most common way an honest analyst publishes a misleading chart.

7. The escape hatch: small multiples

Before the explanation: monthly trend lines for five regions on one chart is readable. For twelve regions it is a tangle. What could you change without dropping a single region?

Split one crowded chart into a grid of small identical ones: same chart type, same axes, same scale, one panel per region. The technique is called small multiples , and it is the standing answer whenever one chart is asked to hold too much. The reader learns to read one panel, then reads the other eleven for nothing, because every panel works identically. Comparison across panels stays honest because the scales match.

The trigger signs: more than about five lines on one line chart, a grouped bar chart three groups deep, or any legend you have to keep re-checking. Each of those is one chart trying to answer "compare across categories" and "compare over time" at once. Small multiples give each comparison its own chart and tile them.

The one discipline that makes them work: identical scales on every panel. The moment each panel scales itself, cross-panel comparison quietly breaks, and that comparison was the reason for the grid.

8. The full before and after

Same data both times: the nine product lines from section 5.

Before

Chart:  pie, nine slices, one colour per line
Order:  as the rows came out of the query
Title:  "Revenue Share by Product Line"
Legend: nine entries, matched to slices by colour

The reader's task: match nine colours between legend and wedges, then rank nine angles between 9% and 14% by eye. Nobody completes that task. They read the title, look at the circle, and leave with "revenue is split up", which was true before the chart existed.

After

Chart:  horizontal bar, sorted largest first, axis at zero
Labels: value at the end of each bar, no legend
Title:  "Top three lines hold 39% of revenue"

Licences      14
Support       13
Training      12
Consulting    11
Hosting       11
Hardware      10
Integrations  10
Templates     10
Other          9

Same nine numbers. The ranking is now instant because it is a length comparison from a shared baseline, the legend is gone because the labels sit on the bars, and the title states the finding: the top three lines, 14 plus 13 plus 12, hold 39% of revenue. Titling the claim instead of the column names is its own habit with its own page, label the claim, not the data. A chart titled with its finding gets that finding remembered. A chart titled with its column names gets skimmed.

9. Edge cases: the miscasts, named

Before the explanation: most bad charts are not exotic. They are one of about five repeat offenders. How many can you name before the list?

The line across categories. The first great miscast, from section 3. Connecting unordered categories manufactures a trend out of nothing. If the horizontal axis is not a true sequence, no line.

The nine-slice pie. The second great miscast, from section 5. Part-of-whole with more than about three parts is a length job, so it is a sorted bar.

The truncated bar axis. A 9.7% difference drawn as 4x. Bars start at zero because length encodes value.

The double axis. Two measures, two vertical scales, one chart. The crossing points and gaps between the lines are artifacts of two arbitrary scale choices, and the reader cannot help reading them as events. If the two measures are related, that is a scatter. If they share units, share one axis. Otherwise, two panels.

The spaghetti line chart. Eight or more lines, one chart, one legend doing all the work. This is the small-multiples trigger from section 7. Alternatively, grey out every line but the one or two the claim is about.

The decorated third dimension. Tilted 3D bars and pies change the drawn lengths and angles the reader is trying to measure, for zero information. Depth that does not encode data only distorts the encodings that do.

Why this works

The one-question method is a compression of a real result, not a style preference. Cleveland and McGill ranked the basic visual encodings by how accurately people extract numbers from them, in controlled experiments: position along a common scale came first, then length, with angle, area, and shading well behind (Cleveland & McGill, 1984, Journal of the American Statistical Association , 79(387), 531–554). Every recommendation on this page is that ranking applied: bars win categories because they use length from a shared baseline, pies lose past a few slices because they use angle, bar axes must include zero because truncation falsifies the length, and lines may zoom because slope, not length, carries their meaning.

The reason this page keeps asking you to answer before it explains is also a measured effect. Prompting a learner to generate an explanation before receiving one reliably improves understanding and retention, across a large body of controlled studies (Bisra, Liu, Nesbit, Salimi, & Winne, 2018, Educational Psychology Review , 30(3), 703–725). Committing to "which two channels are hardest to tell apart" before the answer is why the sorted-bar habit will surface the next time you chart real data.

Using this on your own project

Reworking every chart in an old dashboard in one sitting is miserable, and you will stop at the third one. Do this instead, in order.

Take one chart you have already shipped and say out loud what the reader must compare in it. If you cannot say it in one sentence, that is the finding: the chart has no job yet.
Check it against the decision table. Comparison and chart type match: leave it alone. Mismatch: remake it, which is usually one dropdown in Excel or Tableau.
Run the axis check. Bars: does the value axis start at zero? Lines: is the zoom showing real change, and is the axis labelled so nobody mistakes the zoom?
Retitle it with the claim , a sentence with a number in it, and sort bars by value while you are there.
Only then move to the next chart. One chart per day fixes a dashboard in two weeks without the misery.

If you have paper nearby, one optional drawing is worth five minutes. Draw the fork from the figure yourself, from memory: the question in the middle, four branches, a tiny chart sketched at the end of each. The point is not the drawing. Reproducing the fork without looking is the test of whether you own it, and the version in your handwriting is the one you will actually recall in a meeting. Reading charts fast is its own trainable skill, and Viz Drill is built for exactly those reps.

More detail on this, and more like it. Every how-to sits in one place on the guides index: SQL, charts, statistics, Tableau, and the working habits around them.

The whole thing on one screen

This is the retrieval sheet. Cover the right column, work down the left, and say each answer out loud before you check it.

Situation	The move
The one question	What comparison must the reader make?
Values across categories	Bar, sorted by value, from a zero baseline.
Change over time	Line. Slope carries the meaning.
Two measures, together	Scatter, one dot per record. Association only, never cause.
Parts of a whole, 2 or 3 parts	Pie is acceptable. "About a third" survives.
Parts of a whole, more parts	Sorted bar. Ranking parts is a length job, not an angle job.
Long category labels	Horizontal bars. Labels read flat.
Bar chart axis	Starts at zero, always. Length encodes value.
Line chart axis	May zoom. Slope encodes change, and no length is being read.
Line across categories	Miscast. It implies an order that is not there. Use bars.
Nine-slice pie	Miscast. Use a sorted bar.
Two vertical scales, one chart	Split into two panels, or use a scatter.
Too many lines or bars	Small multiples: identical panels, identical scales.
Chart title	The claim, with a number in it, not the column names.

The one habit to keep. If you take nothing else from this page, ask "what must the reader compare?" out loud before the chart menu opens, and let the answer pick the chart. The expensive charting mistakes are never in the software. They are in skipping the question.

One last thought, and I would genuinely like other people's answers. The chart that taught me this was a line chart of survey scores across nine departments, and I stared at its "trend" for a full minute before noticing the axis was alphabetical. What is the longest a miscast chart has survived in something you inherited, and who finally caught it?

References

Cleveland, W. S., & McGill, R. (1984). Graphical perception: Theory, experimentation, and application to the development of graphical methods. Journal of the American Statistical Association , 79(387), 531–554.
Bisra, K., Liu, Q., Nesbit, J. C., Salimi, F., & Winne, P. H. (2018). Inducing self-explanation: A meta-analysis. Educational Psychology Review , 30(3), 703–725.

The full version of this guide lives on my site: How to Choose the Right Chart: One Question About Your Data.

If it was useful: Buy Me a Coffee.

Budget vs Actual Variance Analysis: The Sign Trap and the Percent Trap

Michael Nocito — Mon, 17 Aug 2026 13:00:32 +0000

By the end of this page you can read a budget vs actual table without being fooled by it, and build one in Excel that does not fool anyone else. You will know the variance formula, why analysts write F and U instead of trusting plus and minus, the two ways percent variance lies, and how to say the whole table in one sentence. It is about twenty minutes.

Here is what to actually do today. Open the last variance table you were sent and find its biggest percentage. Then find its biggest dollar amount. If they are different rows, and they usually are, you now know which row deserved the attention, and it is probably not the one that got it.

The short version: variance is actual minus budget. On a revenue line, positive is good. On a cost line, positive is bad. So analysts label every line F for favorable or U for unfavorable, rank by dollars, and flag by percent.

The sign flip is the trap people fall into first, so it gets the picture.

The original carries a diagram here. In words: Two panels, each showing a pair of vertical bars rising from a shared baseline. In the left panel, labeled revenue, a shorter bar marked budget stands next to a taller bar marked actual. The extra height of the actual bar above the budget level is shaded in the accent color and marked with the letter F and a check mark, because collecting more revenue than budgeted is favorable. In the right panel, labeled cost, the bars have the same shapes: a shorter budget bar next to a taller actual bar. But here the extra height above budget is shaded in the warning color and marked with the letter U and a cross, because spending more than budgeted is unfavorable. A dashed horizontal line runs across each panel at the budget height. The two panels are geometrically identical, and only the meaning of the line decides whether the overshoot is good or bad. That is why the sign of a variance cannot be read without knowing the line type.

Every number on this page is verified. The worked example is a small department's month, eight lines, shown in full below. Every variance, percentage, subtotal, and walk step was computed in a script before it went on the page, so you can check any cell by hand and it will agree.

1. The variance formula, and the sign trap

Before the explanation: two lines both came in over budget, revenue by $12,400 and contractor spend by $11,800. One of those is good news. Say which, and say what told you.

Variance is actual minus budget. Budget is the number you planned, actual is the number that happened, and the variance is the gap between them, with a direction. Revenue budgeted at $250,000 that came in at $262,400 has a variance of positive $12,400. Contractor spend budgeted at $20,000 that came in at $31,800 has a variance of positive $11,800.

Same formula, same plus sign, opposite meanings. More revenue than planned is good. More spending than planned is bad. The sign only tells you the direction of the miss; the line type tells you whether that direction helped. This is why finance teams do not trust the sign. They add a column that states the verdict directly: F for favorable, meaning the miss helped profit, and U for unfavorable, meaning it hurt profit. The rule in one breath: on revenue lines, over budget is F. On cost lines, over budget is U.

Some shops flip the formula to budget minus actual so that positive always means favorable on cost lines. That works until someone merges two workbooks with opposite conventions. The F/U label survives both conventions, which is exactly why it exists. Write the formula you used in a cell comment or a header note, and label F/U regardless.

2. The worked table, labeled F and U

Here is the department. Two revenue lines, six cost lines. Variance is actual minus budget throughout, percent is variance divided by budget, and the F/U column applies the rule from section one.

Line	Type	Budget	Actual	Variance	Variance %	F/U
Product revenue	Revenue	250,000	262,400	+12,400	+5.0%	F
Services revenue	Revenue	60,000	54,200	-5,800	-9.7%	U
Salaries	Cost	140,000	143,500	+3,500	+2.5%	U
Contractors	Cost	20,000	31,800	+11,800	+59.0%	U
Software	Cost	12,000	11,400	-600	-5.0%	F
Travel	Cost	3,000	5,900	+2,900	+96.7%	U
Office supplies	Cost	800	1,150	+350	+43.8%	U
Marketing	Cost	25,000	24,100	-900	-3.6%	F

Check one row by hand to trust the rest. Contractors: 31,800 minus 20,000 is 11,800 over, and 11,800 divided by 20,000 is 0.59, which is 59.0%. It is a cost line, over budget, so U.

The totals: revenue came in at $316,600 against a $310,000 budget, favorable by $6,600. Costs came in at $217,850 against $200,800, unfavorable by $17,050. Net, the department planned to contribute $109,200 and actually contributed $98,750, which is $10,450 short of plan.

Say why the same plus sign produced an F on the first row and a U on the third row, in your own words, before moving on. If you can say it, the rest of this page is bookkeeping.

3. Percent variance, and its two traps

Before the explanation: in the table above, Travel is off by 96.7% and Salaries by 2.5%. Which one costs the company more money?

Percent variance is the variance divided by the budget. It answers "how far off the plan was this line, relative to its own size," and it makes lines of different sizes comparable. It also has two reliable failure modes.

Trap one: a zero or tiny budget makes the percent meaningless. Divide by a budget of zero and the formula errors, or worse, someone hardcodes it to show a number. Divide by a tiny budget and you get a giant percent from pocket change: Office supplies here is 43.8% over, and the entire miss is $350. A percent with a small denominator is a loud noise about a small thing.

Trap two: percent and dollars rank the lines in different orders, and the percent order is the wrong one to act on. Salaries is off by just 2.5%, the second-smallest percent among the misses, yet that 2.5% is $3,500, which is ten times the office supplies miss. Money is spent in dollars, not in percents. So the working habit is: rank by dollars, flag by percent. Dollar order tells you where the money went. The percent flag tells you which lines drifted furthest from their own plan, which is a control question rather than a money question. Both are worth knowing. Only one decides what you investigate first.

Ranked by absolute dollars, this table reads: Product revenue +12,400 F, Contractors +11,800 U, Services revenue -5,800 U, Salaries +3,500 U, Travel +2,900 U, then everything under a thousand. Contractors is the story. It is second in dollars and second in percent, the only line that is loud on both measures.

4. Set a materiality floor before you read

Before the explanation: how big does a variance have to be before you say its name in the review meeting? If you do not have a number, the table decides for you.

A materiality floor is a threshold you set before reading the table: below this many dollars, or below this percent, a variance is noise and gets no airtime. The habit that matters is the word "before." Decide the floor first, then read. If you read first, the interesting-looking rows will pull your floor down to wherever they happen to sit, and every month's floor will be different.

A common shape is a two-part test: flag a line only if it is off by more than a dollar floor AND more than a percent floor. For this department, $2,000 and 5% would flag Contractors, Services revenue, and Travel, while letting Salaries pass as within tolerance and silencing Office supplies entirely. Whether those are the right floors is a judgement about this department, not arithmetic, and the floor itself should come from the data rather than from taste. Choosing thresholds from the data is the full method: pick it, write down why, and check the read survives a nearby choice.

Write the floor into the workbook where readers can see it. "Lines within $2,000 and 5% of plan are not discussed" is one sentence, and it converts your silence about small rows from an omission into a policy.

5. Build it in Excel with SUMIFS

Before the explanation: your actuals arrive as hundreds of individual transactions, and your budget is eight tidy lines. What has to happen to the transactions before the two can sit in one table?

They have to be summed per line. That is the whole build: the budget table supplies the rows, and SUMIFS pulls each line's actual total out of the transaction pile. SUMIFS adds up one column wherever another column matches a condition, and the SUMIFS guide covers the function itself in depth.

Set up two Excel tables. One named Actuals with the raw transactions, columns Line, Month, Amount. One named Report with columns Line, Type, Budget, and the formulas. Then, in the Report table:

Actual      =SUMIFS(Actuals[Amount], Actuals[Line], [@Line])
Variance    =[@Actual]-[@Budget]
Variance %  =IF([@Budget]=0, "", [@Variance]/[@Budget])
F/U         =IF([@Type]="Revenue",
               IF([@Variance]>=0, "F", "U"),
               IF([@Variance]>0, "U", "F"))

The IF([@Budget]=0, "") wrapper is trap one from section three, handled at build time: a line with no budget shows a blank percent instead of an error or a fake number. The F/U formula is the sign rule made mechanical, so the verdict column can never disagree with the convention.

Verify the machinery once with a line you can add in your head. The Contractors actuals in this example are three monthly invoices: 9,800 plus 10,400 plus 11,600, which is 31,800, exactly what the SUMIFS returns and what the worked table shows. Salaries is 47,800 plus 47,800 plus 47,900, which is 143,500. One hand-check per build catches a mis-pointed range, and a mis-pointed range is the most common way a variance report is wrong.

Picture running this build on your own cost center's transactions. Which line would you hand-check, and do you already know a month where SUMIFS would surprise you? That suspicion is worth chasing; it is usually a naming mismatch between the transaction labels and the budget lines.

6. The full before and after

Same eight lines, same numbers, two tables.

Before

Budget, actual, variance. Sorted by percent, no F/U, no floor. Travel tops the table at 96.7%, Office supplies sits second at 43.8%, and the reader's eye spends its first ten seconds on $3,250 of combined misses. Salaries, off by more than both of those together, sits sixth of eight. And a director skimming the variance column sees +12,400 and +11,800 as the same kind of number, when one grew the business and the other blew a plan by half.

After

Line	Type	Budget	Actual	Variance	Variance %	F/U	Flag
Product revenue	Revenue	250,000	262,400	+12,400	+5.0%	F	flag
Contractors	Cost	20,000	31,800	+11,800	+59.0%	U	flag
Services revenue	Revenue	60,000	54,200	-5,800	-9.7%	U	flag
Salaries	Cost	140,000	143,500	+3,500	+2.5%	U
Travel	Cost	3,000	5,900	+2,900	+96.7%	U	flag
Marketing	Cost	25,000	24,100	-900	-3.6%	F
Software	Cost	12,000	11,400	-600	-5.0%	F
Office supplies	Cost	800	1,150	+350	+43.8%	U

Sorted by absolute dollars, verdict labeled on every line, and a flag column applying the stated floor: off by more than $2,000 and more than 5%. The same information now reads in the order the money moved. Contractors surfaces immediately as the problem, Product revenue as the offsetting good news, and Office supplies drops to the bottom where its $350 belongs, still visible, no longer shouting.

7. Present it: the variance walk and the one-sentence claim

Before the explanation: your director has thirty seconds. What is the one sentence this table earns?

The walk first, because it is how finance audiences expect to travel from plan to result. A variance walk starts at the budgeted total, adds or subtracts the largest variances in order of size, and lands exactly on the actual. In one paragraph, this department's walk: we planned to contribute $109,200; product revenue added 12,400; contractor overruns took back 11,800; the services shortfall took 5,800 more; salaries, travel, and small items netted out another 5,250 against us; and we landed at $98,750. Every step is a labeled cause, the steps sum exactly, and drawn as a chart this paragraph is a waterfall: floating bars stepping from the budget column down or up to the actual column.

Then the claim. A variance report is not eight rows; it is one sentence with a number, backed by eight rows: "We missed plan by $10,450, and contractor spend at 159% of budget is the driver." That is a claim someone can act on, question, or own. Sending the table alone makes the reader do the analysis you were asked to do, and the difference between shipping rows and shipping a claim is the whole subject of report vs analysis.

8. Edge cases that flip meanings in real reports

Before the explanation: a cost line comes in 40% under budget. The F/U formula stamps it favorable. Name a reason it might be the worst news in the table.

Underspend is not automatically good. The hiring you budgeted and did not do, the maintenance that got deferred, the campaign that never launched: each prints as F and each may be a plan failing to happen. F and U describe the effect on this period's profit, nothing more. Read big favorable cost variances with the same suspicion as big unfavorable ones.

Timing masquerades as performance. An invoice that slipped from June into July makes June favorable and July unfavorable, and both are illusions. Before escalating any single-month variance, check the year-to-date column; timing noise cancels there while real drift accumulates.

The convention flips between shops. Actual minus budget here; budget minus actual elsewhere, so that positive means favorable everywhere. Neither is wrong. A workbook that does not state which one it uses is wrong. One header note fixes it forever.

Mixed signs hide inside totals. The two revenue lines net to +6,600 F, which quietly contains a $5,800 services shortfall. Every subtotal you present should be openable back into its lines, because someone in the meeting will ask, and because the netted number genuinely conceals a U.

Zero-budget lines need words, not percents. Spending that had no budget line at all is a different conversation from an overrun, and it deserves a sentence ("$4,000 of unbudgeted legal fees") rather than a blank or an error in the percent column.

Why this works

Ranking by dollars and drawing the walk as a waterfall are both bets on how human perception actually performs, and the bet has evidence behind it. Cleveland and McGill measured which visual comparisons people judge accurately, and position along a common scale beat nearly everything else (Cleveland & McGill, 1984, Journal of the American Statistical Association , 79(387), 531–554). A dollar-sorted table puts the decision-relevant quantity in a common column where those accurate judgements can happen, instead of asking readers to mentally rescale eight percentages with eight different denominators.

The prequestions under each heading are not a quirk. Attempting an answer before receiving one improves learning of exactly that material, an effect that holds across dozens of studies of self-explanation prompts (Bisra, Liu, Nesbit, Salimi, & Winne, 2018, Educational Psychology Review , 30(3), 703–725). Guessing whether Travel or Salaries costs more before section three answered it is why the dollars-over-percents rule will still be with you next quarter. The same logic says the cheat sheet below works best covered, as retrieval practice rather than rereading (Roediger & Karpicke, 2006, Psychological Science , 17(3), 249–255).

Using this on your own project

Rebuilding every variance report you have inherited is miserable, and the older ones have defenders. Do this instead, in order.

Add the F/U column to the report you own , using the formula from section five. It is one column and it ends every sign argument in the room.
State the convention in a header note : "Variance = actual minus budget." One line, permanent.
Re-sort by absolute dollar variance. If the current sort is alphabetical or by percent, this single change re-aims the meeting.
Write the materiality floor above the table and add the flag column that applies it. Agree the floor with the report's owner before the next cycle, not during it.
Hand-check one SUMIFS line per month against its raw transactions. Rotate which line. This is five minutes and it is your audit trail.
End every cycle with the one-sentence claim at the top of the email, above the table, with the driver named.

If you have paper nearby, one optional drawing locks the core idea in. Draw the two-panel figure from this page from memory: two bar pairs, one labeled revenue, one labeled cost, the same overshoot shaded on each, and mark which overshoot is F and which is U. If your labels come out reversed, the page is still open.

More detail on this, and more like it. Every how-to sits in one place on the guides index: Excel, SQL, Tableau, and the working habits around them.

The whole thing on one screen

This is the retrieval sheet. Cover the right column, work down the left, and say each answer out loud before you check it.

Concept	What it is
Variance	Actual minus budget. Direction and size of the miss.
The sign trap	Positive is good on revenue lines and bad on cost lines.
F / U	Favorable or unfavorable to profit. The verdict, stated instead of inferred.
The F/U rule	Revenue over budget is F. Cost over budget is U. Under budget flips each.
Percent variance	Variance divided by budget. The miss relative to the line's own size.
Percent trap one	Zero or tiny budgets make giant, meaningless percents. Guard the division.
Percent trap two	Percent order and dollar order disagree. 2.5% of Salaries beat 43.8% of supplies.
The working habit	Rank by dollars, flag by percent.
Materiality floor	The dollar-and-percent threshold set BEFORE reading, and written on the report.
The Excel core	SUMIFS pulls each line's actual from transactions into the budget table.
The build check	Hand-add one line's transactions and match the SUMIFS. Every build, once.
Variance walk	Budget, then the biggest drivers in order, landing exactly on actual. A waterfall.
The deliverable	One claim sentence with a number and a named driver, above the table.
Favorable underspend	Sometimes a plan that failed to happen. Read big F costs with suspicion.
Timing vs performance	Slipped invoices fake one bad month and one good one. Check year to date.

The one habit to keep. If you take nothing else from this page, rank every variance table by dollars and label every line F or U before anyone else reads it. The most expensive miss in the table is routinely hiding behind the smallest percent. If a workbook fights back in a way this page does not cover, there is a general diagnosis loop for being stuck.

One last thought, and I would genuinely like other people's answers. The first variance table I ever presented was sorted by percent, and the meeting spent eight of its ten minutes on a line worth a few hundred dollars. What is the smallest line item that has ever eaten a whole meeting you were in, and did anyone ever say the dollar amount out loud?

References

Cleveland, W. S., & McGill, R. (1984). Graphical perception: Theory, experimentation, and application to the development of graphical methods. Journal of the American Statistical Association , 79(387), 531–554.
Bisra, K., Liu, Q., Nesbit, J. C., Salimi, F., & Winne, P. H. (2018). Inducing self-explanation: A meta-analysis. Educational Psychology Review , 30(3), 703–725.
Roediger, H. L., & Karpicke, J. D. (2006). Test-enhanced learning: Taking memory tests improves long-term retention. Psychological Science , 17(3), 249–255.

The full version of this guide lives on my site: Budget vs Actual Variance Analysis: The Sign Trap and the Percent Trap.

If it was useful: Buy Me a Coffee.

Operations Analytics, Start to Finish

Michael Nocito — Mon, 17 Aug 2026 13:00:19 +0000

By the end of this page you can say, out loud and in your own words, what every core operations number does. What the unit of work is. Throughput, and why a count on its own answers nothing. Cycle time, and the rule that ties it to how much work is sitting open. Backlog. Utilization, and why aiming for 100 percent makes everything slower. Error rate, rework and first pass yield. Service levels, and why the average hides the customers you are failing. That list is most of what an operations analyst job, a technical screen, and a first real dataset will ask of you.

Here is what to actually do with it. Go through once end to end without stopping, just for the shape. Then come back to the retrieval sheet near the bottom, cover the right-hand column, and try to say each answer before you read it. That second pass is where the learning happens, and there is measured evidence for it further down.

The short version: operations analytics is the study of how work moves through a process. Every number in it is either how much, how fast, how much is stuck, or how much was wrong.

One idea decides more of your operations work than any other, so it gets the picture. Work arrives, waits, gets done, and leaves. How much is in progress and how long each item takes are two different spans over that same picture, and they are locked to each other.

The original carries a diagram here. In words: A left-to-right process diagram. On the far left an arrow labelled "arriving" points into a row of three small stacked boxes labelled "waiting", representing a queue. An arrow leads from the queue into a single larger rounded box labelled "working", representing the person or machine doing the job. A final arrow leads out of that box to the right and is labelled "done". Above the queue and the working box, a bracket in a strong accent colour spans both and is labelled "in progress", showing that work in progress includes everything waiting as well as everything actively being worked on. Below, a second bracket in the same accent colour spans from the arrival arrow all the way to the exit arrow and is labelled "time in system", showing that the time an item experiences covers its whole journey, waiting included, not just the part where somebody is working on it. The two brackets deliberately cover different spans of the same picture.

What this page is, and what it is not. This is the concept layer: what each number is and what question it answers. It is not a course in a particular tool. The calculations here are all division and subtraction, and they run in a spreadsheet or in SQL equally well.

1. What this field is called, and what a process is

Before the explanation: a coffee shop, a hospital ward, a warehouse and a support inbox all get measured with the same handful of numbers. What do those four things have in common?

The job title is usually operations analyst , operations data analyst , or business operations analyst. The field is called operations analytics , and you will also see operational analytics and process analytics for the same work. All of them mean measuring how work gets done and finding where it gets stuck.

There is one older name that means something different, and it is worth knowing so a job posting does not confuse you. Operations research is a mathematical field about optimizing decisions, built on things like linear programming. It shares an ancestor with operations analytics and it is a much heavier maths discipline. If a posting says operations research and asks for a maths degree, that is a different job from the one this guide prepares you for.

Now the thing all four examples share. A process is any repeated sequence of steps that turns something arriving into something finished. Coffee orders arrive and become drinks. Patients arrive and become discharges. Pallets arrive and become shipments. Tickets arrive and become resolutions.

Every process has the same four parts, and every number in this guide measures one of them:

Arrivals. Work showing up.
A queue. Work that has arrived and is waiting.
Capacity. The people, machines or hours available to do it.
Completions. Work leaving, finished.

That is the whole vocabulary. Once you can name those four parts in a business you have never seen before, you can measure it.

2. The unit of work, and the one word that prevents most mistakes

Before the explanation: a warehouse says it shipped 4,000 things last month. Do you know how much work that was?

You do not, and that is the point. Four thousand what? Orders, boxes, individual items, or pallets? A pallet might hold 200 items. An order might be one item or forty. The number 4,000 is useless until somebody says what one of them is.

The unit of work is the thing you are counting. Pick it first, name it out loud, and keep it the same all the way through the analysis. In a support inbox it is usually the ticket. In a warehouse it might be the order line. In a clinic it is the visit.

The related idea in the data itself is grain , which means what one row of your table represents. Say it as a sentence starting with "one row per". One row per order. One row per order line. One row per shipment. Those three tables can all have 4,000 rows and describe completely different amounts of work.

This is the single most common way an operations number comes out wrong. Somebody counts rows in a table whose grain is order lines and reports it as orders. The calculation is correct and the answer is wrong. Grain is covered in more depth in the SQL foundations guide, because it is the same idea wearing different clothes.

Say this one out loud before you read on: in a hospital, what is the difference between counting patients and counting visits?

3. Throughput, and why a count on its own answers nothing

Before the explanation: night shift processed 900 orders and day shift processed 500. Which shift is better?

You cannot say yet, and the reason is the most important habit in this whole guide.

A count is how many things happened. 900 orders. It tells you the volume that got done and nothing else.

A rate is that count divided by the opportunity somebody had to produce it. Almost always hours. In operations the rate has a name: throughput , meaning completed work per unit of time.

Back to the two shifts. Night worked 160 hours, day worked 60.

Night: 900 ÷ 160 = 5.6 orders per hour
Day: 500 ÷ 60 = 8.3 orders per hour

Day shift is faster. The count said the opposite, because night shift simply had more hours to work with.

Both numbers are real and both have a job. The count tells you how much got delivered, which is what a customer experiences. The rate tells you how efficiently it was produced, which is what a manager can act on. Reporting only one of them is the mistake, in either direction.

The habit to keep from this section is short. Before you compare two operational numbers, ask what each one was divided by. If neither was divided by anything, you are comparing volumes and you should say so out loud.

4. Cycle time, and the three numbers that lock together

Before the explanation: your team wants tickets resolved twice as fast. Nobody is allowed to work faster and nobody is getting hired. Is there anything left to change?

There is, and this section is the answer. First, the time words, because they get mixed up constantly.

Cycle time. How long one item takes from starting work on it to finishing it.
Lead time. How long one item takes from arriving to finishing, waiting included. This is what the customer feels.
Wait time. The gap between the two. Usually the biggest part, and usually the one nobody measures.

People say cycle time loosely to mean either. In an interview, say which one you mean and you will sound like you have done the job.

Now the rule that connects everything. There is a formula in queueing theory called Little's Law , and it is one line:

work in progress = arrival rate × time in system

Work in progress is how many items are open right now, waiting or being worked on. Arrival rate is how many show up per day. Time in system is how long an average item takes end to end.

A worked example. Tickets arrive at 20 a day, and at any moment 60 tickets are sitting open. Rearrange the formula and time in system is 60 ÷ 20, which is 3 days. You just measured your own resolution time without timing a single ticket.

And here is the answer to the prequestion. To cut that 3 days to 1.5, you change one of the two other numbers. Halve the open tickets to 30, and 30 ÷ 20 gives 1.5 days. Or double throughput so items leave faster. Nobody had to type faster. Limiting how much work is open at once is a real lever, and it is why teams cap their in-progress column.

Picture your own work now. How many things do you personally have open and unfinished at this moment, and what would happen to how fast each one finishes if you cut that number in half?

5. Backlog, which is just arrivals minus completions

Before the explanation: a team closes 90 tickets every single week without fail. Is that team keeping up?

Backlog is the work that has arrived and is not finished. The number that matters is not the size of the backlog, it is which way it is moving.

The arithmetic is one subtraction. Arrivals minus completions, per period.

100 arrive a week, 90 close a week. Backlog grows by 10 a week.
Ten weeks of that and the backlog is 100 items deeper than it started.
Nothing about the team's performance changed. They closed 90 every week.

That is the answer to the prequestion. Whether 90 is keeping up is not a fact about the team, it is a fact about how much arrived. A closure number reported without an arrival number next to it cannot answer the question anyone is actually asking.

This is the finding that gets an operations analyst noticed, because it reframes a performance conversation as a capacity conversation. "Rep B is slower" is an accusation. "Rep B's queue receives 4 tickets an hour and one person can clear 2" is a staffing decision with a number attached.

One more term you will meet. Aging is how long the items in the backlog have been sitting. A backlog of 100 where the oldest is 2 days old is healthy. A backlog of 100 where the oldest is 8 months old contains work nobody is ever going to do, and saying so is usually more useful than another dashboard.

6. Utilization, and why 100 percent is the wrong target

Before the explanation: your team is busy 95 percent of the time. Your manager wants to know why everything is late. What would you tell him?

Utilization is the share of available time that is actually spent working. Four hours of work in an eight hour shift is 50 percent utilization. It feels like the number should be as high as possible. It should not, and the reason is the most counterintuitive idea in operations.

Waiting time does not rise steadily as a team gets busier. It rises gently, then explodes. The part of the maths that causes this is a single fraction: utilization divided by one minus utilization. Watch what it does.

At 80 percent busy: 0.8 ÷ 0.2 = 4
At 90 percent busy: 0.9 ÷ 0.1 = 9
At 95 percent busy: 0.95 ÷ 0.05 = 19

Going from 80 percent busy to 90 percent busy more than doubles the waiting. Going from 90 to 95 doubles it again. The team took on a little more work each time and the queue behind them grew far faster than the workload did.

That is the answer to the prequestion, and it is why the honest response to "everything is late" is often "because we are 95 percent busy, and the last 5 percent of capacity is what absorbs a bad day". A process with no slack has nowhere to put a surprise.

Two things make this worse, and both are worth naming because you can measure them. Variability in arrivals , meaning the work shows up in bursts rather than evenly. And variability in service , meaning some items take far longer than others. A process with steady arrivals and predictable work can run much closer to full than a process with spiky arrivals. That is why a factory can run at 90 percent and an emergency room cannot.

7. Quality: error rate, rework, and first pass yield

Before the explanation: two teams both complete 1,000 items a week. One of them is doing noticeably more work than the other. How?

Because some of those completions were the same item twice. Work that comes back is rework , and it is invisible in a completion count.

Three numbers cover quality, and they are all shares of a total.

Number	What it asks
Error rate	Of everything we did, what share was wrong? 30 wrong out of 1,000 is 3 percent.
Rework rate	What share had to be done again? This is the one that eats capacity.
First pass yield	What share went through correctly the first time, with no rework? 1,000 started, 940 clean, so 94 percent.

First pass yield is the one worth learning by name, because it is the number that connects quality to speed. Every point of yield you lose comes back as extra volume through the same process. A team at 85 percent first pass yield is doing roughly 15 percent of its work twice, which means its real capacity is smaller than its headcount suggests.

That is the answer to the prequestion. Same 1,000 completions, different amounts of actual work delivered, because one team's number includes items it had already counted once.

One caution before you report any of these. An error rate is only as good as the definition of an error, and that definition is a decision somebody has to make on purpose. What counts as wrong, who decides, and when. Getting that written down before you measure is its own skill, and there is a walkthrough of it in turning fuzzy questions into measurable definitions.

8. Service levels, and why the average hides your worst customers

Before the explanation: your promise is to resolve every ticket within 8 hours. Your average resolution time is 5.8 hours. Are you keeping the promise?

An SLA , a service level agreement, is a promise about time. Resolve within 8 hours. Ship within 2 days. Answer within 30 seconds. SLA attainment is the share of items that met the promise.

Here is why the average cannot tell you. One hundred tickets:

90 of them resolved in 2 hours.
10 of them took 40 hours.

The mean is (90 × 2 + 10 × 40) ÷ 100, which is (180 + 400) ÷ 100, which is 5.8 hours. Comfortably inside an 8 hour promise.

And yet 10 customers waited 40 hours, five times the promise. Those are the ones who complain, escalate, and leave. The average did not lie, it answered a question nobody asked.

The fix is a percentile. The 95th percentile is the value that 95 percent of your items came in under. In the example above the 95th percentile is 40 hours, which tells the true story in one number. Operations reporting runs on percentiles for exactly this reason, and you will see them written as p50, p90 and p95. The p50 is the median.

The rule to carry into a job: an average describes the typical case, and a percentile describes the bad case. A promise is about the bad case, so measure it with a percentile. This connects directly to picking cutoffs you can defend, because somebody has to decide whether the promise is p90 or p95, and that decision is worth more than the dashboard.

9. Telling a real change from noise

Before the explanation: throughput dropped 6 percent this week. Should you tell anyone?

Not yet. Every process varies week to week without anything having changed. Reporting normal variation as a finding is the fastest way for an analyst to lose credibility, because the number goes back up next week and the alarm looks silly.

The distinction has names, and they are worth using. Common cause variation is the ordinary wobble a stable process always has. Special cause variation is a real change with a real reason behind it. The job is telling them apart before you say anything.

The cheapest honest test does not need statistics. Take the last 20 periods, look at how much the number normally bounces around, and ask whether this week's move is bigger than the moves you already ignored. If the number has swung 5 percent every week for five months, a 6 percent drop is Tuesday.

Three questions that catch most false alarms:

How many items is this based on? A 6 percent drop on 2,000 orders is a signal. On 30 orders it is two people taking a day off.
Is the denominator stable? A rate can move because the top changed or because the bottom did. A holiday week has fewer hours in it.
Does anything else move with it? A real change usually shows up in two places. If throughput fell and backlog rose, something happened. If throughput fell and nothing else moved, look at your data before you look for a cause.

Being able to say "that is inside our normal range" is a professional skill, not a dodge. It is also the section of this guide most likely to come up in an interview as a scenario question, because it separates people who report numbers from people who interpret them. The difference between those two jobs is covered in the difference between a report and an analysis.

10. The same report, counts and rates side by side

Same warehouse, same month, same underlying data. On the left, what most first drafts look like. On the right, the same facts with a denominator attached.

Counts only	The same month, as rates
Night shift shipped 900 orders. Day shift shipped 500.	Night ran 5.6 orders per hour across 160 hours. Day ran 8.3 across 60.
We closed 90 tickets a week, every week.	100 arrived a week and 90 closed, so the backlog grew by 10 a week.
Average resolution time was 5.8 hours, inside our 8 hour promise.	Ninety percent resolved in 2 hours. The slowest 10 percent took 40, so p95 attainment failed.
The team completed 1,000 items.	940 went through clean, so first pass yield was 94 percent and 60 items were done twice.
The team is busy 95 percent of the time.	At 95 percent utilization the queue term is 19, against 4 at 80 percent, so lateness is expected.

Nothing on the right required new data. Every one of those is the same figure with the question "compared to what" answered.

11. Where these ideas come from

Two of the rules in this guide are not conventions somebody agreed on. They are proved results, and knowing that is worth a sentence in an interview.

The formula in section 4 is Little's Law , proved by John Little in 1961 (Little, 1961, Operations Research , 9(3), 383–387). The striking part is how few assumptions it needs. It does not care what order the work is done in, how many people are working, or how the arrivals are distributed. If the process is stable over the period you measure, work in progress equals arrival rate times time in system. That is why you can apply it to a hospital and a helpdesk with the same confidence.

The explosion in section 6 comes from queueing theory, and the specific result is Kingman's formula (Kingman, 1961, Mathematical Proceedings of the Cambridge Philosophical Society , 57(4), 902–904). It shows that as a server approaches full utilization, waiting time grows in proportion to that utilization over one minus utilization fraction, scaled by how variable the arrivals and the service times are. The arithmetic in section 6 is that fraction on its own. It is also why reducing variability, rather than working faster, is often the cheapest way to make a process feel quicker.

The idea in section 9 is older still. Walter Shewhart separated ordinary process variation from genuine signals while working at Bell Labs in the 1920s, and published the method in 1931 (Shewhart, 1931, Economic Control of Quality of Manufactured Product , Van Nostrand). The control chart on a factory wall and the "is this inside our normal range" question in section 9 are the same idea, ninety years apart.

12. How to study this so it sticks

Reading this page again will feel like learning and mostly will not be. Three findings change what an hour of study is worth, and all three are cheap to act on.

The first is that retrieving something from memory stores it better than reviewing it does. Students who read a passage once and then took recall tests remembered far more a week later than students who read the same passage four times, even though the re-readers felt more confident (Roediger & Karpicke, 2006, Psychological Science , 17(3), 249–255). Applied here: cover the right-hand column of the retrieval sheet below and say each answer before you look. The struggle is the mechanism, not a sign you are doing it badly.

The second is spacing. The same total study time, spread across days rather than packed into one sitting, produces substantially better retention, and the effect holds across hundreds of experiments (Cepeda, Pashler, Vul, Wixted, & Rohrer, 2006, Psychological Bulletin , 132(3), 354–380). Three twenty-minute passes on three days beats one hour tonight.

The third is why this guide keeps asking you to say things out loud. Learners who explain a worked example to themselves as they go understand it substantially better, and transfer it to new problems better, than learners who read the same example without explaining it (Chi, Bassok, Lewis, Reimann, & Glaser, 1989, Cognitive Science , 13(2), 145–182). Operations is where that pays off most, because the mistake is almost never in the arithmetic. It is in what you believe the number was divided by.

If you have paper nearby and a spare five minutes, there is one drawing worth doing, and it is optional. Draw the picture at the top from memory: arriving, waiting, working, done, then the two brackets over it. Label which bracket is work in progress and which is time in system. Getting the second bracket to start at the arrival rather than at the working box is the whole idea, and drawing it from memory is both a retrieval attempt and a check on whether you actually have it.

13. The whole thing on one screen

This is the retrieval sheet. Cover the right column, work down the left, and say each answer out loud before you check it.

Concept	What it does
Process	A repeated sequence turning arrivals into completions.
The four parts	Arrivals, queue, capacity, completions. Every number measures one.
Unit of work	The thing you are counting. Name it before you count anything.
Grain	What one row of your table means. Say it as "one row per something".
Count	How many happened. Volume delivered, and nothing else.
Rate	A count divided by the opportunity to produce it. Usually hours.
Throughput	Completed work per unit of time. The rate version of a count.
Cycle time	Start of work to finish, for one item.
Lead time	Arrival to finish, waiting included. What the customer feels.
Wait time	The gap between the two. Usually the biggest part.
Little's Law	Work in progress = arrival rate × time in system.
Using Little's Law	Cut time in system by cutting work in progress, without anyone working faster.
Backlog	Arrived and not finished. Direction matters more than size.
Backlog growth	Arrivals minus completions, per period.
Aging	How long backlog items have been sitting. Old backlog is usually dead work.
Utilization	Share of available time actually spent working.
Why 100 percent fails	Waiting scales with utilization over one minus utilization. 80% gives 4, 95% gives 19.
Variability	Bursty arrivals or unpredictable job lengths. Makes every queue longer.
Error rate	Share of output that was wrong.
Rework rate	Share that had to be done again. Eats capacity invisibly.
First pass yield	Share that went through clean the first time. Links quality to speed.
SLA	A promise about time. Resolve in 8 hours, ship in 2 days.
SLA attainment	Share of items that met the promise.
Percentile	The value a given share of items came in under. p95 is the bad case.
Average against percentile	An average describes the typical case. A promise is about the bad case.
Common cause variation	The ordinary wobble a stable process always has.
Special cause variation	A real change with a real reason. Prove it before you report it.
Cost per unit	Total cost divided by units produced. Where headcount enters the reporting.

The one habit to keep. Before you report any operations number, say out loud what it was divided by. "Nine hundred orders, divided by nothing" is a volume. "Nine hundred orders across 160 hours" is a finding. Nearly every wrong operations conclusion is a correct count compared against a different denominator than the one somebody assumed, and that one sentence catches it before the number leaves your desk.

One genuine question, and I would like other people's answers. The idea that took me longest was utilization, because "busier is better" is so obviously true right up until you see what it does to a queue. Which operations idea did you understand backwards for the longest, and what finally fixed it?

References

Little, J. D. C. (1961). A proof for the queuing formula: L = λW. Operations Research , 9(3), 383–387.
Kingman, J. F. C. (1961). The single server queue in heavy traffic. Mathematical Proceedings of the Cambridge Philosophical Society , 57(4), 902–904.
Shewhart, W. A. (1931). Economic Control of Quality of Manufactured Product. New York: D. Van Nostrand Company.
Roediger, H. L., & Karpicke, J. D. (2006). Test-enhanced learning: Taking memory tests improves long-term retention. Psychological Science , 17(3), 249–255.
Cepeda, N. J., Pashler, H., Vul, E., Wixted, J. T., & Rohrer, D. (2006). Distributed practice in verbal recall tasks: A review and quantitative synthesis. Psychological Bulletin , 132(3), 354–380.
Chi, M. T. H., Bassok, M., Lewis, M. W., Reimann, P., & Glaser, R. (1989). Self-explanations: How students study and use examples in learning to solve problems. Cognitive Science , 13(2), 145–182.

The full version of this guide lives on my site: Operations Analytics, Start to Finish.

If it was useful: Buy Me a Coffee.

Build a Dashboard in This Order: Correct, Then Clear, Then Compelling

Michael Nocito — Mon, 17 Aug 2026 13:00:12 +0000

This is the order a dashboard gets built in, and it will save you work you've already finished. Eight steps, grouped into three passes. Nothing gets made to look good until the number underneath it has been checked, so nothing you polish gets thrown away and polished again.

Here are the eight, and you can start using them on your next file. Load the data and name it. Label the rows. Make the pivot or chart. Check the numbers. Fix what the check caught. Fix the words. Fix the number formats. Make it land. Steps 1 to 5 are the correct pass. Steps 6 and 7 are the clear pass. Step 8 is the compelling pass.

The short version. Checking is step four, not step nine. At step nine you can't tell which of eight steps broke it.

Eight steps, three passes, one direction of travel.

Everything below comes from one build: an Excel dashboard over 82,956 Steam games, built in an afternoon on 2026-08-04. You can open the finished dashboard and the data behind it and follow along in your own copy.

The eight steps, and what each one decides

Before you read the list, name the step you'd normally leave until last. Hold that answer.

Each step ends with something decided. That's what makes it a step rather than a stretch of work. If you can't say what a step decided, you haven't finished it.

Step	What you do	What it decides
1	Load the data and give it a name	What your formulas point at, and whether they survive new rows
2	Label the rows into groups	What you're comparing. Every later count reads this label
3	Make the pivot or the chart	Which numbers you're going to show
4	Check the numbers against a source you trust	Whether steps 2 and 3 did what you thought
5	Fix what the check caught	Nothing new. It puts steps 2 and 3 back where you thought they were
6	Fix the words: headers, labels, titles	What a reader thinks each number is
7	Fix the number formats	How fast a reader can read a number without misreading it
8	Make it land: layout, sorting, emphasis, the claim	The one thing the page is arguing

Step 1 is worth a note, because it's the step people skip fastest. Naming the data means turning the range into an Excel Table with a name, so a formula reads [@TotalReviews] instead of H2. That's article 3 of this series. Before any of it, look at what you loaded: how to explore a dataset before you trust it covers the first pass over an unfamiliar file.

Why checking sits at step four

Step 4 exists to settle one question, and the question is worth naming before the work.

The question. Did the pivot I just built count what I think it counted?

Answer one. Yes. The label from step 2 landed on the right rows, and the pivot counted rows. If that's true, every chart built on this pivot is safe, and the rest of the afternoon is design.

Answer two. No. Something in step 2 or step 3 quietly did a different job. If that's true, every chart, every headline number and every sentence you write after this point is wrong, and none of them will look wrong.

What decides it. A count you worked out separately, sitting in a cell of its own beside the data, written before you looked at the pivot.

Why it matters. Excel doesn't crash when it's wrong. It returns a confident number. Between you and presenting that number in a meeting, the only thing standing there is a check you wrote twenty minutes earlier.

Say out loud, before you read the next section, why a wrong number in a pivot is harder to spot than a wrong number in a formula. The answer is the whole reason step 4 is a separate step.

The number that was wrong, and how it looked

In the Steam build, step 3 made a pivot of the three row groups: games that were loved and found, games that were loved and stayed hidden, and everything else. The pivot was asked for each group as a share of the total.

It said the loved-and-found group was 0.45% of all games.

The check said 0.71%.

Look at those two numbers. Both are small. Both are plausible. Neither is round enough to look made up. Nothing on the screen was red, no cell showed an error, and the chart drawn from that pivot looked fine.

The cause was one word in the pivot's corner: Sum of AppID. Dropping a column into the Values area makes Excel pick a way to summarize it, and for anything numeric it picks Sum. AppID is a number the way a phone number is a number. Excel added up the ID codes of 590 games and reported the share of a total made of ID codes. That's article 6 of this series.

The correct figure is a count: 590 of 82,956 games, which is 0.71%. Fixing it took ten seconds in Value Field Settings. Then the same fault turned up in three more fields on the same page. One check at step 4 caught all four.

The check itself was small. Two cells beside the labelled column, holding a count of each group, worked out before the pivot existed. 765 loved games in total. 175 that stayed hidden, 590 that got found. If that middle cell had read 174, the label from step 2 was wrong, and everything after it was wrong too.

Now picture your own last spreadsheet, the one you sent to somebody. Which number on it did nobody ever work out a second way? That's where this lands.

The counts also have to add up. 175 plus 590 plus 82,191 outside the loved group is 82,956, which is the row count of the file. An addition check like that costs one cell and catches a whole family of mistakes at once.

The three passes, and why you can't skip one

The eight steps group into three passes, and the group names are the reason the order holds.

Correct is steps 1 to 5. The numbers are what you say they are.
Clear is steps 6 and 7. A reader knows what each number is and can read it without stopping.
Compelling is step 8. The page argues one thing, and the eye lands on it first.

You can't skip a pass. A beautiful dashboard built on a wrong number is worse than an ugly one carrying the same error, because the polish is the part that persuades people to trust it. Clean formatting, a sorted bar chart and a confident title are all signals of care, and a reader reads them as evidence that the arithmetic was cared for too.

The cost of running the passes out of order isn't wasted time. It's thrown-away work. Polish applied to a wrong number gets discarded twice: once when you fix the number, and again when you redo the polish that the fix undid. A chart you styled for ten minutes and then rebuilt cost twenty.

What makes this hard to hold to

Formatting is the part of this work that feels like progress. A chart getting cleaner is visible, immediate and pleasant. Checking a number feels like doubt, it produces nothing you can look at, and most of the time it tells you what you already believed.

That's why the order has to be written down instead of felt. A rule you follow when you feel like it is a rule that vanishes on the afternoon you're tired, which is the afternoon you need it.

There's a second reason, and it's mechanical. A number on screen is an anchor. Once you've seen 0.45%, you start reasoning from it, and the question quietly changes from "is this right?" to "could this be right?" Almost anything could be right. So the check goes the other way around: work out what the number should be, write it down, and only then look at what the pivot says. Predict, then check. A check you read after the fact confirms whatever is already on the screen.

Keep the checks in the file when you send it. Two cells labelled CHECK beside the data cost nothing and tell the next person that somebody looked. That habit belongs with writing down what your data can't tell you, which is the same move applied to the things a check can't catch.

The same eight steps in Tableau and Power BI

The steps aren't Excel features. They're the order the work has to happen in, and they survive the tool change.

Step	Excel	Tableau	Power BI
1. Load and name	Excel Table named `Games`	Data source, with fields renamed	Power Query, table named
2. Label the rows	An added column of labels	A calculated field, or a set	A calculated column
3. Make it	PivotTable, PivotChart	Drag to rows, columns, marks	Visual, with fields dropped in
4. Check	COUNTIF in a spare cell	A text table of the same counts	A card visual of the same counts
6 and 7. Words and formats	Header text, Custom formats	Field aliases, number format	Field names, format pane
8. Make it land	Sort, mute, one accent color	Same	Same

That's what makes this a method rather than an Excel tip. Learn the order once and it comes with you.

Run it on your own file

Take a spreadsheet you already have, one with a few hundred rows and a question attached to it.

Write the check before you build anything. One cell: how many rows should be in the group you care about? Work it out with a filter and the status bar count, and type the number in as a label. Now it's a prediction, not a confirmation.
Build the pivot, then read the corner. Before you look at any figure in the body, read what the pivot calls its value. Sum of something that isn't money or quantity is the signal to stop.
Compare, out loud. Say both numbers. Yours and the pivot's. If they differ, you have a bug, and you know it's in the last two steps rather than somewhere in nine.
Only now touch a font. Headers, then number formats, then layout. In that order, and not before step 3 has passed step 4.
Write the claim last. One sentence with a number in it, describing what the page found. If you can't write it, the page is a pile of charts and step 8 isn't done.

Retrofitting a finished dashboard to this order is miserable and you'll abandon it halfway. Do it on the next build instead, and on the current one just add the checks.

If you have paper nearby, draw three boxes, one on top of the other, and write the step numbers into them. That picture is the whole method, and drawing it once is worth more than reading it twice.

Why this works

Two findings sit under this, and both are about the same thing: you can't spot an error by looking harder.

The first is the base rate of spreadsheet errors. Raymond Panko's review of the audit studies found that errors occur in a few percent of cells, which means that for a spreadsheet of any size the question isn't whether there's an error but how many there are (Panko, 1998, Journal of Organizational and End User Computing , 10(2), 15-21). A later review across the field reached the same place from more studies (Powell, Baker & Lawson, 2008, Decision Support Systems , 46(1), 128-138). A number is not made correct by having been produced carefully. It's made correct by having been produced twice.

The second is anchoring. Tversky and Kahneman showed that people asked to judge a quantity adjust from whatever number they were shown first, and adjust too little, even when they know the starting number is arbitrary (Tversky & Kahneman, 1974, Science , 185(4157), 1124-1131). That's why the check is written before the pivot is read. Once 0.45% is on your screen, your judgment of whether 0.45% is plausible has already been shaped by 0.45%.

This is also the honest reason for the note at the top. That number came out wrong four separate times in one afternoon, in a build being done slowly, on purpose, by someone writing down every step as he went. The order isn't a virtue. It's what catches that.

A cheat sheet

Pass	Steps	Done when	Skipped when
Correct	1 to 5	Every headline number has a second number beside it that agrees	You start choosing fonts before the pivot's corner has been read
Clear	6 and 7	A stranger can name each number without asking you	A header still says Sum of something, or a percent is showing eight decimals
Compelling	8	You can write the finding as one sentence with a number in it	The page has more than one thing shouting

The one habit to keep. Write the number you expect before you look at the number Excel gives you. Everything else in this article is scaffolding around that one move.

References

Panko, R. R. (1998). What we know about spreadsheet errors. Journal of Organizational and End User Computing , 10(2), 15-21.
Powell, S. G., Baker, K. R., & Lawson, B. (2008). A critical review of the literature on spreadsheet errors. Decision Support Systems , 46(1), 128-138.
Tversky, A., & Kahneman, D. (1974). Judgment under uncertainty: Heuristics and biases. Science , 185(4157), 1124-1131.

What's the step you named at the start, the one you'd normally leave until last? Was it step 4?

The full version of this guide lives on my site: Build a Dashboard in This Order: Correct, Then Clear, Then Compelling.

If it was useful: Buy Me a Coffee.

Build a Risk Index That Colors Itself

Michael Nocito — Mon, 17 Aug 2026 13:00:04 +0000

When this workbook is finished, you can change one number and watch the whole thing follow. Move a cut-off from 65 to 70 and every row re-bands, every fill recolors, every count updates, and the legend still matches the map. Nobody can color a cell by hand, because no cell has a color of its own. That is the whole trick, and it takes about twenty minutes to build.

The example here is a security risk index across twenty sites. The same shape works for vendor scoring, lead scoring, incident triage, or any list where a number has to turn into a label and a color.

The fault, and where it actually comes from

You have met this file. A scored list, colored by hand, that nobody quite trusts any more. Look closely and the same faults turn up every time:

Two rows score 61.4. One is amber, one is yellow.
The same band is drawn in two shades, because two people picked from the palette on two different days.
A row sits below the cut-off and is colored red anyway, because somebody knew that site was a problem.
A score lands exactly on 65, which appears in two bands, so the answer depends on who typed it.
One row has no band at all. It quietly drops out of every count.

These look like five separate mistakes. They are one mistake, five times. The rule lives in the formatting instead of in a column. A color is not a value you can test. You cannot write a formula that asks "is this row the right shade of amber," so nothing checks it, and it drifts.

The test: can you sort by band? If the band is only a color, you cannot sort it, count it, or filter it, and neither can anybody else. That is the tell.

The chain: score, then band, then color

Everything below is one idea applied three times. Each thing is derived from the thing before it, and only the first one is typed.

Layer	Where it lives	Who decides it
Sub-scores	Four columns, one per category	Your source data. Typed once.
Composite score	A formula, from the sub-scores and the weights	The weights row
Band	A formula, from the score	The cut-off table
Color	A conditional format, from the band	The band column

Read that bottom-up and you get the promise: change the cut-off table, and the colors move. There is no step where a human picks a fill.

Step 1: weights in one row

Put the four category weights in four cells on a Rules sheet, and nowhere else. Name the sheet Rules so it is obvious which tab holds the decisions.

Rules!B7:E7   0.30   0.30   0.20   0.20

Now the composite score on each row is one formula, pointing at that row:

=ROUND(SUMPRODUCT(E6:H6, Rules!$B$7:$E$7), 1)

SUMPRODUCT multiplies each sub-score by its weight and adds the results. The dollar signs lock the weights row, so you can fill this formula down twenty rows and every one still reads the same weights.

The temptation is to write =E6*0.3+F6*0.3+G6*0.2+H6*0.2 instead. Do not. That buries the weights in twenty formulas, so changing them means twenty edits, which means it never happens, which means the weights in the file stop matching the weights in the policy.

Step 2: cut-offs in one table

Five bands, five rows, with a lower bound and an upper bound written down. The bounds must climb in order, and they must touch with no gap and no overlap.

Band	Lower	Upper	What it requires
Low	0	24	Monitor on the normal cycle
Guarded	25	44	Fix inside the annual plan
Elevated	45	64	Named owner and a dated plan
High	65	79	Quarterly review until it drops
Severe	80	100	Escalate now

An overlap is the fault that produced the 65 problem earlier. If High starts at 65 and Elevated ends at 65, the band depends on which rule got evaluated first, which is not a policy anyone wrote down.

Where these five numbers come from is its own question, and a bigger one than the spreadsheet. If you picked them because they were round, read how to choose cut-offs from the data before you defend them to anyone.

Step 3: look the band up, never type it

The band column is a formula. It reads the score and finds the last cut-off the score clears.

=INDEX(Rules!$B$12:$B$16, MATCH(I6, Rules!$C$12:$C$16, 1))

Read it right to left. MATCH with a third argument of 1 means "find the largest value that is less than or equal to mine, and tell me its position." A score of 61.4 clears 0, 25 and 45, but not 65, so it stops at 45, the third row. INDEX then returns the third band name, Elevated.

The one rule this depends on: the lower bounds must be sorted ascending. MATCH with a 1 walks the list assuming order. Out of order, it returns a wrong answer confidently and without an error, which is the worst kind of wrong.

The gain is bigger than saving typing. Once the band is text in a column, you can sort by it, count it, filter it, and pivot on it. It became data.

Step 4: let the color follow the band

Select the whole data area, columns and all, then add one conditional formatting rule per band using a formula rule:

=$J6="Severe"

The single dollar sign is the entire mechanism. $J locks the column, so every cell across the row asks the same question. The bare 6 stays relative, so row 7 asks about $J7, row 8 about $J8, and so on down. One rule colors a whole row, and twenty rows need no extra work.

Repeat for the other four bands. Five rules, five fills, and that is every color in the workbook. There is no sixth place a color can come from, which is exactly why the two-ambers problem cannot happen again.

Anchor the rule to the row that starts your data. Write it while sitting on row 6 and the reference is $J6. Write it while sitting somewhere else and every row will be off by the difference, which looks like the colors slipped by one.

Step 5: two checks that catch the rest

Two formulas on the Rules sheet, in plain sight, doing the checking nobody remembers to do.

=IF(ROUND(SUM(B7:E7),4)=1, "Weights balance", "Fix the weights, they must total 100%")

Weights that total 96% still produce a score. It just quietly understates every row, and nothing looks broken.

=IF(F17=COUNTA('Risk Index'!$A$6:$A$25), "Every site lands in one band", "Banding gap, check the cut-offs")

Here F17 totals the per-band counts, each one a COUNTIF against the band column. If the two numbers disagree, a row fell through a gap in your cut-offs. That is the row that used to vanish from the report with nobody noticing.

Both checks read as a sentence, not a number. A reader who has never opened this file can tell whether it is healthy without knowing how it works.

Step 6: the change log

One tab, three columns: what I found, what I changed, why. Every correction gets a row.

This is the tab that turns a rebuild into something a client can approve. Without it, you hand back a file that looks different and they have to take your word for it. With it, they can disagree with any single decision without reopening all of them.

One entry type matters more than the others. When two source files disagree, the change log is where you say so rather than quietly picking a winner. "This site appears on two maps with two scores, flagged for your decision" is a better line than any silent fix, because which source wins is a policy call, not a spreadsheet one.

Choosing the five colors

The palette in the template is the five-class RdYlBu scheme from ColorBrewer, a tool built from map-design research by Cynthia Brewer and Mark Harrower (Harrower & Brewer, 2003). It is listed as colorblind safe, which matters more here than it looks: red and green are the two colors a large minority of readers cannot reliably separate, and risk maps reach for exactly that pair.

Band	Fill	Text
Low	#2C7BB6	white
Guarded	#ABD9E9	dark
Elevated	#FFFFBF	dark
High	#FDAE61	dark
Severe	#D7191C	white

Two rules go with it. Severity must run in one direction, so a reader learns the order once and never re-learns it. And the score stays visible in its own column next to the color, because color is a weak channel for judging how much bigger one value is than another. Position and length beat it decisively, a result Cleveland and McGill measured directly (Cleveland & McGill, 1984). The color is for scanning the map. The number is for answering the question.

Where else this shape fits

Nothing above is about security. Any time a number becomes a label, the same chain applies: vendor scoring, lead scoring, incident triage, credit tiers, health scores on accounts. Swap the four categories and the five band names and the mechanics are unchanged.

The part worth carrying to all of them is the sentence at the top. The rule belongs in a column. Once it is in a column, it can be sorted, counted, checked, and argued with, and a rule you can argue with is the only kind that survives contact with a second person.

References

Harrower, M., & Brewer, C. A. (2003). ColorBrewer.org: An online tool for selecting colour schemes for maps. The Cartographic Journal, 40(1), 27–37. doi:10.1179/000870403235002042
Cleveland, W. S., & McGill, R. (1984). Graphical perception: Theory, experimentation, and application to the development of graphical methods. Journal of the American Statistical Association, 79(387), 531–554. doi:10.1080/01621459.1984.10478080
Brewer, C. A. (1994). Color use guidelines for mapping and visualization. In A. M. MacEachren & D. R. F. Taylor (Eds.), Visualization in Modern Cartography (pp. 123–147). Pergamon. (Where the one-direction rule for ordered data comes from.)

The full version of this guide lives on my site: Build a Risk Index That Colors Itself.

If it was useful: Buy Me a Coffee.

One Migration, Start to Finish

Michael Nocito — Sun, 16 Aug 2026 13:00:38 +0000

New to this? Read the eight stages first. That page tells you which stage you are in and what to do about it. This page shows all eight happening on one real-shaped job, so it makes more sense second.

By the end of this page you will have seen a whole migration run, start to finish. You will know what was sent on the day each stage closed, who signed it, what it cost when the client asked for more, and what the paper trail looked like at the end.

The short version. A migration is eight stages, and each one ends at a gate. A gate is a checkpoint with an owner and a written answer: one named person agrees, in writing, that the stage is finished and the next one can start. Getting that agreement is the job. The rest is checking.

Two of the eight closing emails are reproduced here in full, at gate 1 and gate 6. All four email types, including the chase and the handoff, are in the emails guide.

The engagement

All of it is invented. The shape is not.

Client	Northwind Care Services, a home care agency
Moving from	CareTrack 4.2, in use since 2011
Moving to	Meridian Care Platform
What moves	1,482 client records, 58,740 visits, 6,310 documents, some of them paper
Kickoff	23 February 2026
Go-live	3 August 2026. Go-live is the day staff stop using the old system and start using the new one.
Where it is today	Mid cutover. Cutover is the actual move: the old system goes read only, and the data is loaded across. Six gates signed, go-live not yet accepted.

Note the last row. This is a live tracker, caught in the middle. A finished project would teach you less, because the interesting part of a migration is what it looks like while the answer is still open.

Stage 1: kickoff and scope, 24 February to 13 March

Before you read on, guess: of everything agreed in a kickoff, which single item saves the most time later?

It is not the scope. It is the name of the one person who answers data questions. Here that was action A003, raised on 27 February, answered by 2 March. The client named their Data Owner, J. Okafor. Every ambiguous field for the next five months went to one inbox instead of a committee.

The other thing that happened in week one was the exclusion list. Records before 2019 were confirmed as archive only and did not move. That one decision cut the volume by about a third.

Gate 1

Scope, including the exclusion list. Signed by A. Reyes, Client PM, on 13 March. Evidence held: the confirming email, saved to the 01-scope folder.

Subject: Northwind migration, scope confirmed and what happens nextCopy this email

Hi Alex,

Thanks for Tuesday. Scope is now agreed and attached as v1.0. In summary, we are moving client records, visit records and documents from 2019 onward. Records before 2019 stay in CareTrack as an archive and are not migrated.

Your next actions

Confirm read access to CareTrack for our team, by 11 March. Owner: your IT contact.
Confirm J. Okafor as the single point of contact for data questions, by 11 March.

Our next actions

Take the full initial export, starting 16 March.
Profiling report back to you by 1 April.

If access slips , profiling slips with it, and profiling is what tells us whether the August date holds. A week lost here is a week off the end.

The plan is here [link]. Please confirm you have received this and forward it to anyone who needs it.

The highlighted parts are the ones you swap for your own. Everything else is the shape, and the shape is what makes it work.

Four parts, every time: your actions with dates, our actions with dates, the impact of delay in their terms, and the link to the plan. The emails guide takes those apart.

Stage 2: profile the source, 16 March to 1 April

Profiling is where the estimate meets the data. Two findings changed the plan.

1,204 client records had no date of birth. That is a required field in the target, so it would have failed the load. The client accepted moving them blank and cleaning up afterward, logged as exception E008 and action A004.

The second was the picklist. Service type was free text in the old system, so home aide, HHA and H.H.A. were all the same thing typed three ways. That is normal, and it is the sort of thing you only find by looking. It is the reason the look before you trust habit exists.

Gate 2

The profiling report, and the decisions taken from it. Signed by A. Reyes on 1 April. Evidence held: report v1.2, countersigned.

The wording of that gate matters. Not "the report", the report and the decisions. A finding with no decision attached is just a fact you will rediscover in June. See the seven checks.

Stage 3: map the fields, and the first real pressure, 2 April to 1 May

On 2 April, the day mapping started, the client's Data Owner asked for scheduling history to be migrated as well. It was not in the signed scope.

Before reading the next paragraph, decide what you would say.

The answer is not yes and it is not no. It is the number. Scheduling history was three more weeks on the timeline and two weeks of effort. That was written into the change log as CR-02 the same day, with the impact filled in before any conversation about whether to do it.

Then two doors, both of them real:

Door one. We do it, and go-live moves from early August to late August.
Door two. We hold the date, and scheduling history becomes phase two.

The client took door two, on 13 April. That is the usual outcome, and the reason is worth understanding. The request was never unreasonable. It just had a price nobody had seen yet, and the person asking did not own the date. Once the cost was in front of somebody who did own the date, it answered itself. This is the whole of scope creep in one move.

CR-03 followed on 23 April: keep the legacy note formatting exactly as it looks today. Declined on 27 April, because the target cannot hold it. What made that decline land was explaining what the target can and cannot do, then agreeing plain text, rather than just saying no.

Gate 3

The field map, version frozen. Signed by J. Okafor, Client Data Owner, on 1 May. Evidence held: map v3.0, signed. The working copy is kept separately.

Two details in that gate line. The Data Owner signs, not the PM, because the Data Owner is the person who can actually be wrong about a field. And the signed copy lives apart from the working copy, so that in September nobody has to argue about which version was agreed. More in field mapping and file hygiene.

Stage 4: clean, de-dupe, and eight things a rule could not fix, 4 May to 1 June

Cleaning is rules, not edits. A rule can be previewed, counted, reversed and explained. An edit cannot.

Eight records were left that no rule could handle, and those became the exception sheet. One was a duplicate client, the same name and date of birth under two numbers. Only the client could say which record survived and whether the visit history merged. They chose to keep NW-4471 and merge.

Another, E007, was a date of birth reading 02/03/1948, which is February in one country and March in another. Somebody checked the paper chart. It was 3 February.

The client worked the whole list between 22 and 29 May. Every one carries a decision and a date.

Gate 4

Exception list closed or formally accepted. Signed by J. Okafor on 1 June. Evidence held: the exception sheet, eight items, all answered.

Read "or formally accepted" carefully. E008, the 1,204 blank dates of birth, was never fixed. It was accepted, in writing, with a named owner for the cleanup. An open exception is a decision nobody made yet, and it will surface after go-live wearing a different hat. See cleaning.

Stage 5: the dry run, 2 to 24 June

Everything goes into a sandbox. Everything, not a sample. A sample tells you nothing about the record that breaks the loader, because that record is always the strange one.

Six checks run against it, and they split into two halves. Counts, control totals, orphan links and spot checks on real records all ask the same question: did the data arrive. Then two more ask whether it works. Processability means putting the target system through a real job, generating an invoice, scheduling a visit, running the report the client runs every Monday. Integration means checking that everything downstream still works, the payroll export and the billing handoff included.

That distinction is not academic. Data can sit in the database looking perfect and still be unusable, because the loader wrote straight into the tables and skipped something the application expects to find.

The dry run earned its place here. On 12 June the loader was found to be silently truncating notes longer than 4,000 characters. Silently. No error, no warning, just shorter notes. In a care agency those notes are the clinical record.

That became action A008, fixed by splitting the overflow into a linked note record, and the field map was reopened and re-signed for that one row. A frozen map that changes after a dry run is fine, as long as the change is signed the same way the original was.

Gate 5

A clean reconciliation in the sandbox. Signed by the migration lead on 24 June. Evidence held: the reconciliation tab, every row Pass.

This is the one gate the client does not sign, and that is deliberate. It is an internal bar. You do not put a broken load in front of testers and ask them to be understanding about it. See the dry run.

Stage 6: user acceptance testing, 25 June to 14 July

Testers were people who do the work daily, not managers. That distinction is the whole stage. A manager will click through a system and say it looks fine. A scheduler will try to find one specific client on a Tuesday and notice that something is missing.

Which is what happened. Two testers reported service type showing as blank on older records. A free-text variant had been missed by the picklist map. Rule added, reloaded, re-checked, logged as A009 and closed on 13 July.

That is a good UAT finding, and it is worth saying why. It was found by somebody who knew what should have been there. No count, no reconciliation and no automated check would have caught it, because the number of records was right. Only the meaning was wrong.

Gate 6

UAT passed by the people who did the testing. Signed by A. Reyes on 14 July. Evidence held: the sign-off sheet, six testers.

Subject: UAT sign-off, and the cutover plan for the last two weeks of JulyCopy this email

Hi Alex,

UAT is complete. Six testers ran the scripts, twelve findings were raised, and all twelve are closed. The sheet is attached with each finding and how it was resolved.

The two that changed the load were the blank service type on older records, now fixed and reloaded, and the note truncation found in the dry run, now split into linked notes.

Your next actions

Reply to confirm UAT sign-off, by 14 July. This is the gate. Cutover does not start without it.
Tell your staff the freeze window: CareTrack is read only from 15 July.

Our next actions

Cutover rehearsal, 17 to 21 July. Full sequence, clock running, no production data touched.
Final delta export, 22 July.
Go/no-go call, 23 July, 9am. You decide, on the rehearsal timings and the reconciliation.
Cutover 24 to 27 July, then the bridge import, then reconciliation.
Go-live acceptance meeting, 3 August.

If sign-off slips past Monday , the freeze moves with it, and the freeze is the part your staff feel. Every day it moves is another day of double entry for the schedulers.

Plan is here [link]. Please confirm receipt.

Note what the delay costs, in their words. Not "this will delay the project". The schedulers doing double entry. See UAT.

The one that was declined

On 19 May the client PM asked to bring go-live forward by a week, to suit a board meeting. It was declined on 21 May, and the reason went in writing: the week had to come out of UAT, and skipping UAT sign-off would mean going live on a system nobody had tested.

Saying no to a client is a service, when the thing they asked for would hurt them. What makes it survivable is that the refusal is on the record with its reason, in the change log, next to the three requests that were not refused. A change log with only rejections in it looks like obstruction. This one shows one accepted, one deferred, two declined, and that pattern is what makes the declines credible.

CR	Asked for	Timeline	Outcome
CR-01	A custom referral source field promised during the sale	None if done now	Accepted
CR-02	Scheduling history as well as records	Plus 3 weeks	Deferred to phase two
CR-03	Legacy note formatting kept exactly	Plus 1 week	Declined, alternative agreed
CR-04	Go-live pulled forward one week	Minus 1 week	Declined, reason in writing

CR-01 is the quiet lesson in that table. Sales promised something that was not in the target system. It was small, so it was absorbed, and it was still written down as a change request. Things that cost half a day are exactly the things that never get logged, and a change log with gaps in it is not evidence of anything.

Stage 7: rehearse, freeze, cutover, bridge, 15 July onward

This is where the project is now.

The week before anything real happened, the whole sequence was rehearsed. Final export, load, reconcile, bridge, reconcile again, in order, with the clock running. Not to check the data, which the dry run had already settled, but to check the time. It came in at nineteen hours against a freeze window of a full weekend, which is the answer you want on the Tuesday before rather than at 4am on the Saturday.

Then the freeze went out, the delta export was taken on 22 July, and on the morning of 23 July there was a fifteen minute call with one question on it: go, or not.

That call had been in the plan since kickoff, with the name of the person who decides written next to it. A. Reyes, the client PM. The evidence list was agreed in advance too: rehearsal timings, dry run reconciliation clean, UAT signed, freeze confirmed by their own staff. Nobody was inventing a standard at nine in the morning.

It is worth being honest about what "no" would have meant, because it is smaller than it sounds. Once the new system is open and people are working in it, going back is close to impossible, since the old system is now missing everything created since. The real fallback is not a rollback. It is do not start, wait for the next window. That is why the decision sits before the load and not during it.

The load then ran in order: records, then documents, then the links between them. That order is not a preference. A document cannot be attached to a client record that is not there yet.

The bridge import closes the gap. Between the initial export in March and the freeze in July, staff kept working, and everything they created in those four months has to be brought across too. Naming that gap at kickoff is what stops it from being a discovery in week twenty.

Post-load reconciliation is running now.

Check	Source	Target	Result
Client records	1,482	1,482	Pass
Visit records	58,740	58,740	Pass
Documents	6,310	6,310	Pass
Active clients	913	913	Pass
Control total, balances	284,655	284,655	Pass
Documents with a client link	6,310	6,310	Pass

This table is a tab in the workbook. Open the worked example and you can see the formulas behind the Pass column, plus every other row on this page.

Five of those are counts. One is not. The control total sums the balances rather than counting the rows, and it is there because counts can match while values are wrong. If a decimal moved during a transform, the row count would not notice.

Every row above answers the same question: did the data arrive. Two more checks answer a different one, and they are not in this table because they do not produce a number. Can the target system actually use the data, and does everything downstream still work. Both were run again here, as they were in the dry run. See cutover.

Gate 7, not yet signed

Go-live accepted. Pending. Post-load reconciliation runs to 3 August. Until the client accepts it in writing, this gate is open, and the tracker says so.

An honest tracker has open rows in it. A tracker where everything is green on the day you are asked for it is a tracker somebody filled in backwards.

Stage 8: hypercare and close, from 4 August

Hypercare is the support period straight after go-live, when people hit the new system for real and find the things testing did not. It is agreed before it is needed, not during the first panic. Length, route, and who answers. Here it runs to 25 August.

One item is already open. A010: the client wants a count of documents loaded per year for their auditor. That is not a defect, it is the long tail, and it is what hypercare is actually for. Requests like it arrive for weeks and each one needs logging rather than answering off the side of a desk.

The stage ends with the final reconciliation report, an internal readout on what to change next time, and a handoff to Support that includes context rather than just a ticket. See hypercare.

When the client goes quiet

It has not happened on this engagement, which is itself the point. The single point of contact named on 2 March is why.

When it does happen, the ladder has three rungs and you climb them on a schedule you decided in advance. Restate the one action and the date it was agreed. Then attach the consequence to their goal, not yours. Then raise it to whoever owns the outcome, having told your contact you are about to. The wording for each rung is in when the client goes quiet.

Write the plan down at kickoff and follow it without deciding again each time. Deciding each time is how a chase becomes personal, and how three weeks pass with nothing on the record.

What the paper trail is actually for

Five months produced this: eight gates with a name and a date on each, ten actions with owners and due dates, four change requests with their cost written down before the decision, eight exceptions each with a client decision, a field map signed and version frozen, and a reconciliation that ties.

None of that is bureaucracy. It is the answer to one question, asked months later by somebody who was not in the room: why is it like this?

Every one of those rows answers it. The blank dates of birth are blank because the client accepted it on 27 March. Scheduling history is missing because it was deferred on 13 April to hold the date. Go-live was not pulled forward because UAT would have been skipped. You will not remember any of that in November, and you should not have to.

The tools on this page are free and open. The blank template and the worked example are the workbook this walkthrough describes. The Python that generates them is on GitHub, where every date is read off the plan rather than typed twice, so the gate sheet and the schedule cannot disagree.

The one habit

End every stage with an email that names what was agreed, who agreed it, and what happens next, then ask for confirmation. Eight of those emails are a migration you can defend.

Look back at the last piece of work you finished. Could you show, today, who agreed to it and when?

References

Gollwitzer, P. M., & Sheeran, P. (2006). Implementation intentions and goal achievement: A meta-analysis of effects and processes. Advances in Experimental Social Psychology, 38 , 69–119.
Ariely, D., & Wertenbroch, K. (2002). Procrastination, deadlines, and performance: Self-control by precommitment. Psychological Science, 13(3), 219–224. doi:10.1111/1467-9280.00441
Roediger, H. L., & Karpicke, J. D. (2006). Test-enhanced learning: Taking memory tests improves long-term retention. Psychological Science, 17(3), 249–255. doi:10.1111/j.1467-9280.2006.01693.x

The full version of this guide lives on my site: One Migration, Start to Finish.

If it was useful: Buy Me a Coffee.

What Data Migration Actually Is

Michael Nocito — Sun, 16 Aug 2026 13:00:31 +0000

Data migration is moving a client's records from the system they have to the system they bought. If you are about to run your first one, this tells you what you are signing up for. By the end you will know the eight stages, the words you will hear in every meeting, and the two failures that account for most of the pain.

The work itself is simple to describe. A client is leaving an old system. Their history has to arrive in the new one, complete, connected, and correct. You get the data out, you line up every field with a home in the new system, you fix what is broken, you load it, and you prove the numbers match.

The short version. A migration is a move, not a copy. You are responsible for the history arriving intact, and for being able to prove that it did.

Where migrations come from

Before the list: which of these four do you think produces the messiest data? Have a guess and hold it.

A company buys new software and leaves the old vendor. This is the common one. An agency moves off a fifteen year old scheduling system onto a modern platform.

Two companies merge. Now two customer lists have to become one, and the same person exists twice with different spellings.

A system moves from a server in the building to the cloud. The data model usually stays similar, so this is the gentlest kind.

A business finally replaces spreadsheets with a real system. This is the messy one. Spreadsheets have no rules, so anything a person could type is somewhere in the file.

The words you will hear

Term	What it means
Source and target	The old system and the new system. You will say these words hundreds of times.
Extract	Getting the data out of the source, usually as a set of files.
Field mapping	A line by line list saying that this field in the old system becomes that field in the new one. It is the blueprint for the whole job.
Transformation	Changing the shape of a value so the target will accept it. A date written as 03/04/24 becoming 2024-04-03 is a transformation.
Profiling	Looking at the real data to find out what is actually in it, rather than what the client says is in it.
Reconciliation	Proving the move worked by comparing counts and totals, source against target.
Orphan	A record that lost its parent. A document with no client attached. In a regulated industry an orphan is not untidy, it is a compliance problem.
Cutover	The moment the old system stops being used and the new one starts.
Hypercare	The close support period straight after cutover, when the team watches for what surfaces.

Say the difference between profiling and reconciliation out loud before reading on. If you can, the rest of this series will be easy.

The eight stages, and what a gate is

What do you think stops a migration team from starting work before it is ready? Answer that before the paragraph does.

Every serious framework describes roughly the same shape. Kickoff and scope. Profile the source. Map the fields. Clean and de-dupe. Dry run in a sandbox. User testing. Freeze and cutover. Hypercare and close.

Each one ends at a gate. A gate is a checkpoint with an owner and an answer, and you do not start the next stage until it closes. Mapping does not begin until scope is agreed in writing. Nothing loads until the map is signed off. You do not go live until a client has looked at real records and said yes.

Gates feel like paperwork on a small job. They are the only reason a large job finishes. The stages article walks all eight, with the gate that closes each one.

The tools

Most migrations run on four kinds of tool, and you will use all four in a week.

Spreadsheets hold the map, the exception list, and the working files. Excel is not glamorous and it is where most of this actually happens.

SQL does the profiling and the reconciliation. Counting rows, finding duplicates, and checking that nothing lost its parent are each a few lines. If you can write a COUNT with GROUP BY, you can do most of the checking work.

A bulk loader. Every target system has one, and it is the tool you will fight. Loaders are strict about formats, quiet about failures, and slow to tell you which row broke.

A scripting language once the volume passes what a spreadsheet handles comfortably. Python with pandas is the usual choice, and handling large files is its own skill.

Picture the last messy spreadsheet you saw. Now picture a loader rejecting it one row at a time with no message about why. That is the middle of a migration.

The two failures that cost the most

Duplicates that survive. One client becomes two in the new system. Their history splits. Staff pick the wrong record. This is why de-duplication happens before the move and not after, and why matching records that describe the same thing is worth learning properly.

A move nobody can prove. The data went across and the numbers were never checked. Weeks later someone notices a year of visits is missing. By then the old system may be gone.

The fix for both is the same and it is unglamorous. Count things, write the counts down, and compare them.

Where this comes from

Haller, Matthes and Schulz built a detailed process model for large migration projects from the existing literature plus twenty five industry interviews. Their earlier paper with the same group makes the stronger claim, and it is the one worth carrying: the testing and reconciliation work, not the loading, is what decides whether a migration succeeds.

The vendor frameworks agree on the shape. They assess, prove the approach at small scale, migrate at full scale, then run a defined hypercare window after cutover.

My own experience adds one stage most write-ups skip, and it is the one clients feel. A migration takes weeks, and the client keeps working during those weeks. What happens to the records they create in that gap is a real question with a real answer, and it gets its own article.

How to start on a real job

Ask what success looks like, and write the answer where the client can see it.
Get a full export before you promise a date.
Count everything in that export. Rows per table, at minimum.
Build the map before you touch the data.
Load nothing into the real system until it has worked in a sandbox.

If you have paper nearby, sketch the source, the target, and the checks in between. It is the whole job on one page.

The one habit

Count before, count after, keep the counts. Everything else in this series is detail on top of that.

Migrations are also a people job, and the hardest part is rarely the data. What is the part of a system change you have found hardest to get people through?

References

Haller, K., Matthes, F., & Schulz, C. (2012). A detailed process model for large scale data migration projects. Business Information Systems (BIS 2012), Lecture Notes in Business Information Processing, 117. Springer.
Matthes, F., Schulz, C., & Haller, K. (2011). Testing & quality assurance in data migration projects. 27th IEEE International Conference on Software Maintenance (ICSM) , 438–447.
Wang, R. Y., & Strong, D. M. (1996). Beyond accuracy: What data quality means to data consumers. Journal of Management Information Systems, 12(4), 5–33.

The full version of this guide lives on my site: What Data Migration Actually Is.

If it was useful: Buy Me a Coffee.

Month	Avg temp (°F)	Ice cream sales ($k)	Drownings
Jan	36	8	1
Feb	38	9	0
Mar	46	14	0
Apr	55	19	1
May	65	27	4
Jun	74	35	3
Jul	81	42	5
Aug	79	40	4
Sep	70	30	5
Oct	58	21	0
Nov	47	14	1
Dec	39	10	0

Month	Avg temp (°F)	Ice cream sales ($k)	Drownings
Jan	36	8	1
Feb	38	9	0
Mar	46	14	0
Apr	55	19	1
May	65	27	4
Jun	74	35	3
Jul	81	42	5
Aug	79	40	4
Sep	70	30	5
Oct	58	21	0
Nov	47	14	1
Dec	39	10	0

Month	Avg temp (°F)	Ice cream sales ($k)	Drownings
Jan	36	8	1
Feb	38	9	0
Mar	46	14	0
Apr	55	19	1
May	65	27	4
Jun	74	35	3
Jul	81	42	5
Aug	79	40	4
Sep	70	30	5
Oct	58	21	0
Nov	47	14	1
Dec	39	10	0