← All Kits · Excel Kit · Article 0: the build order
Show the Unit Without Breaking the Number
This article gives you the move that lets a cell read "21.2x" while it still holds 21.1919 underneath: a custom number format. Press Ctrl+1, pick Custom, type 0.0"x". The display changes. The number does not.
The principle is one line, and it is the whole article: format changes appearance, substance stays put. Anything you type into the cell changes substance. So any time you catch yourself typing a unit, a "k", a symbol, or a word next to a number, you are about to destroy a number.
The example is real, from the build behind this series. The KPI row's audience gap came out of AVERAGEIF as 21.1919: found games average 2,458,263 owners against 116,000 for the hidden ones. The dashboard shows 21.2x. The cell holds the full number. Both figures were re-run against the file before publishing.
- What typing the unit destroys
- Where the setting lives
- Reading a format code
- The five formats worth knowing
- The comma trick: thousands and millions
- Four sections: negatives, zeros, text
- When it is not a formatting problem
- Run it on your own file
- A cheat sheet
What typing the unit destroys
Predict the damage first. A KPI cell holds the formula result 21.1919. You retype it as "21.2x" so it reads better. Name one thing elsewhere in the workbook that just broke.
The moment the x lands, the cell holds text. Everything that treated it as a number fails, and most of it fails quietly:
| What pointed at the cell | What happens now |
|---|---|
| A formula dividing by it | #VALUE!, the one loud failure in the list |
| A SUM or AVERAGE including it | Silently skips it. The total changes with no error |
| A chart plotting it | The point drops out, or plots as zero |
| Sorting | Text sorts apart from numbers: 21.2x files after 100 |
| The next person's edit | They inherit a dashboard where one KPI is decoration |
SUM skipping text is the dangerous one, because a total that quietly excludes a member is exactly the shape of error article 2's checks exist for: confident, formatted, wrong.
Where the setting lives
- Select the cell, press Ctrl+1. The Format Cells dialog opens on the Number tab.
- Pick Custom, at the bottom of the category list.
- Type the code in the Type box. The sample line above the box shows the live result before you commit. OK.
The formula bar is the proof that nothing was harmed. Click the cell: the display says 21.2x, the bar says the formula, and the underlying value is still 21.1919 to full precision.
Reading a format code
A format code is a tiny stencil the number is pushed through. Three symbols cover analyst use:
| Symbol | Means | Example |
|---|---|---|
0 | Always show a digit here, pad with zero if needed | 0.0 shows 21.2, and 5 as 5.0 |
# | Show a digit here only if there is one | #,##0 shows 2,458,263, and 12 as 12 |
"text" | Print these characters literally | 0.0"x" shows 21.2x |
Say what 0.0"x" does out loud, in order: one decimal place, then a literal x. Every code in the next section reads the same way, left to right.
The five formats worth knowing
These five cover nearly everything a dashboard needs, and no more. All are from real use, and the first two are in the build's KPI row.
| Code | Shows | Job |
|---|---|---|
0.0"x" | 21.1919 → 21.2x | A multiple or ratio with its unit |
#,##0,"k" | 2,458,263 → 2,458k | Big counts, compressed by a thousand |
#,##0,,"M" | 2,458,263 → 2M | Very big counts, compressed by a million |
$#,##0.00 | 1234.5 → $1,234.50 | Money, without the Currency preset's extra baggage |
0.0% | 0.229 → 22.9% | Shares, one decimal, per article 7 |
Anything in quotes prints literally, so 0.0" hrs", #,##0" games" and 0" pts" are the same idea wearing different units. You now know how to write formats nobody taught you.
The comma trick: thousands and millions
The trailing comma is the one piece of this that looks like a typo and is not. A comma at the end of the code divides the displayed value by 1,000. Two commas divide by a million. The stored value never moves.
That is how the build's owner counts fit in a KPI tile: 2,458,263 displays as 2,458k, and the cell still holds every digit, so the 21.2x ratio computed from it stays exact. Round the display, never the data. The moment you round the data itself, every downstream calculation inherits the rounding, and your checks start disagreeing with your display.
Four sections: negatives, zeros, text
A format code can hold up to four sections separated by semicolons: positive; negative; zero; text.
#,##0;[Red]-#,##0;"–"Read it left to right: positives with thousands separators, negatives red with a minus, zeros as a dash. This is how finance sheets get red negatives without anyone touching font color, and how a dashboard shows a quiet dash instead of a noisy 0.
Use it when you need it and not before. One section is the normal case, and a wall of semicolons in every cell is its own readability problem.
When it is not a formatting problem
The boundary, so this tool does not get overused: a format changes how a true number is displayed. It cannot fix a number that is not true, and it cannot turn text back into numbers.
If 08053 lost its zero at import, that is article 4's problem, and a 00000 format only papers over it: the display shows 08053 while the cell holds 8053, and any join against real zips still fails. Display and substance disagreeing on purpose is worse than either being wrong alone. Fix the substance first, then format it.
Run it on your own file
- Find a cell where you typed a unit. Search for x, k, or hrs in a numeric column if you are not sure. Any hit is a broken number.
- Put the real number back. The formula or the raw value, no unit.
- Ctrl+1, Custom, write the stencil. Digits first, unit in quotes.
- Check the formula bar. Display shows the unit, bar shows the number. That disagreement is the feature working.
- Test one downstream formula. A SUM or division that touches the cell should now include it. If a total just changed, the typed unit had been silently excluding it, and you have found a real error on your page.
The Excel Kit drills AVERAGEIF, the KPI patterns and the rest of analyst Excel with worked examples and a mock exam. The stencil habit is yours from this page.
Open the Excel Kit →A cheat sheet
| You want | Code | Watch for |
|---|---|---|
| A ratio with its unit | 0.0"x" | Quotes around anything literal |
| Thousands compression | #,##0,"k" | The trailing comma is the divider, not a typo |
| Millions compression | #,##0,,"M" | Two commas, two divisions |
| Money | $#,##0.00 | Fix substance before display |
| Percent, one decimal | 0.0% | The % multiplies the display by 100; store 0.229, not 22.9 |
| Red negatives, dash zeros | #,##0;[Red]-#,##0;"–" | Sections are positive; negative; zero; text |
| To check nothing broke | Read the formula bar | Display and bar should disagree. That is the point |
Every article comes out of one step of the same real build, in build order. Links go live as each one publishes.
- Build a dashboard in this order: correct, then clear, then compelling
- Label your rows before you chart them
- Check your work before anyone else does
- Name your data so your formulas stop breaking
- The dialog that quietly deletes your zip codes
- A pivot table is a question, not a report
- Excel just summed your ID numbers and said nothing
- Percentages are the whole story and Excel hides them
- Show the unit without breaking the number (this article)
- Pick the chart your number already decided
- Chart design basics: take things away, then point
- One row at a time, or all rows at once
- Four numbers across the top do more than four charts
- Sort your bar chart or it means nothing
- The finding that was just your own definition
- The names came in as gibberish and Excel said nothing
- Make one control drive every chart on the page
- Write the sentence your dashboard is arguing
Which cell in your current workbook has a typed unit in it right now? Go press Ctrl+1 on it and watch a total somewhere quietly correct itself.