Twenty-Three Products Went Live at £0.00 and the Catalogue Total Moved by £8.39, Because a CSV Saves What the Screen Shows and the Price Column Was Formatted to Whole Pounds

The monthly job is four clicks. Open Trade Price List 2026.xlsm, make sure the Prices tab is the one in front, File ▸ Save As, pick CSV (Comma delimited), save it into the upload folder. The webshop picks the file up at six the next morning and republishes the catalogue.

On Monday 2 March the person who had run that job for three years had left, and their replacement did it exactly as the handover note described. Excel showed two warnings. The first said the selected file type does not support workbooks that contain multiple sheets and only the active sheet would be saved. The second said some features in the workbook might be lost if it were saved as CSV. Both offered an OK and both got one, which is the correct answer to both of them, because that is what an export is.

The prices in that workbook were right. All 1,840 of them, to the penny, checked against the supplier schedule the week before.

In January somebody had prepared a one-page price appendix for a board pack and formatted the price column to zero decimal places so the figures would sit in a narrow column without a forest of pennies. The format was never put back, because a number format is not data and nothing in the file behaves differently because of one. Every formula that read that column read 0.09, 0.14, 12.47 and 48.50. Every report totalled correctly. Every lookup matched.

A CSV is the one consumer that does not read the cell. It reads the screen. A column formatted to whole pounds exports as whole pounds, and the file that went to the webshop said 0 on the M6 washer, 0 on the cable tie, 0 on the end cap and 0 on the 5A fuse.

Twenty-three lines were priced under 50p. Every one of them left the building as zero, and the webshop, which has no opinion about whether a price is plausible, published them at £0.00 and took orders for nine days.

What this covers. The formats and their limits are the same in Excel 2016, 2019, 2021, 2024, Microsoft 365 and Excel for Mac; the CSV UTF-8 option in section 9 needs Excel 2016 or later, and Save a Copy in section 5 appears only on files stored in OneDrive or SharePoint. Excel for the web can open and download these formats but cannot Save As to all of them, which matters in section 12. CELL, TEXT, ROUND, SUM, SUMPRODUCT, COUNTIF, COUNTIFS, LEN, TRIM, VALUE, LEFT, IF and IFERROR work everywhere; SHEETS needs Excel 2013; TEXTJOIN needs 2019; LET, UNIQUE, FILTER and SORT need 2021 or 365. A price list is the example because the damage is easy to price, but the same job is a payroll upload, a stock file for a marketplace, a patient extract, a bank mandate, a general-ledger journal — anything that leaves Excel as a file somebody else's system reads without a human in between.

1) Eight Lines Out of 1,840, and the Only Thing That Changed Was the Number Format

Read column C against column D. Nothing in column C is wrong and nothing in column D is a mistake in the sense of somebody mistyping: 0 is a completely accurate transcription of what the cell displayed. 0.09 shown to zero decimal places is 0. 48.50 shown to zero decimal places is 49, because display rounding goes half away from zero, so the wall enclosure went up 50p and the crimp tool went down 40p in the same file.

Then read the last two columns. On the four zero-priced rows the trade value of what shipped is a five-figure number and the invoice is nothing. On the grommet and the cable, both published at £1.00, the company was over-paid on one and under-paid on the other, which is the shape of the other 1,817 rows: noise, in both directions, cancelling.

That cancelling is the whole reason this survived nine days. Somebody did reconcile the upload. They summed the price column in the CSV and compared it to the workbook, and got £22,952.00 against £22,943.61 — a difference of £8.39 on twenty-three thousand pounds, which reads as a rounding artefact because it is a rounding artefact. A total is the wrong instrument for finding a line that has gone to zero. A total is where a line that has gone to zero goes to hide.

🎯 Scenario: Take the last export you sent anybody and open it in Notepad, not in Excel. Excel will re-parse the file and show you something plausible; Notepad shows you what you actually sent. It is thirty seconds, and it is the only way to see a file the way the system that receives it sees it.

2) A CSV Saves the Display, Not the Value

This is the rule the whole article hangs on, and it is worth stating in one line: when Excel writes a CSV, each cell is converted to text using the number format applied to that cell.

Not the stored value. The formatted result.

Four of those rows are the ones that cost people money. #,##0, — the format with a trailing comma that divides the display by a thousand, the one every "£k" summary uses — exports 1 where the cell holds 1250. £#,##0.00 exports a currency symbol into a numeric column. mmm-yy exports a month name where the receiving system was promised a date, and every line in the file becomes the first of that month. And 0% exports the symbol along with a number that is a hundred times the one in the cell, so an importer that strips the sign stores 8 where you meant 0.075.

General is the only number format that exports the value. That is the practical rule, and it is why the fix in section 12 is a separate sheet rather than a note in the handover document.

Two things this rule does not affect, both of which people expect it to:

• Column width. A narrow column showing ##### still exports the full formatted number. The hashes are a rendering fallback, not a format.
• Stored precision beyond 15 digits. General writes up to 15 significant digits because that is all Excel has; a 16-digit account number was already damaged before it reached the export, and that is a different article.

🎯 Scenario: Before any export, select the columns that carry numbers and press Ctrl+Shift+~ — General format, in one keystroke, on the selection. If the column suddenly shows 12.475 where it showed 12, you have just found what your last twelve exports contained. Press Ctrl+Z if you want the formatting back; the export is what matters, not the screen.

3) Only the Active Sheet, and the Five Tabs That Never Left

The first warning is literally true and almost never read: the selected file type does not support workbooks that contain multiple sheets. A CSV is one sheet. Not the first sheet, not the sheet you think of as the main one — the active sheet, the tab that happened to be in front when you pressed Save.

Trade Price List 2026.xlsm had six tabs: Prices, Discount matrix, Break quantities, Currency, Notes and Changelog. The upload got Prices, which was the right one. The webshop's importer also has a column for minimum order quantity, which it leaves at 1 when the column is absent, and the break quantities that said cable ties, box of 1,000 lived on a tab that has never once been part of the export.

Three consequences follow from "one sheet", and only the first is obvious:

1. The other tabs are not in the file. Whatever a downstream system infers when a column is missing, it will infer it silently.
2. Formulas that point at the other tabs are gone with them. The exported sheet contains results, not references, so nothing errors — but if the Prices tab held =Currency!B4*C2, the file carries the answer as it stood at the moment of the save and nothing about it will ever update again.
3. The active sheet is a property of the moment, not of the file. Save the workbook with the Changelog tab in front, and next month's export is a changelog. There is no error, no warning beyond the one you have already learned to click through, and the file is the right size and the right shape.

One cell tells you how many sheets you are about to leave behind: =SHEETS() returns the number of sheets in the workbook. Six, in a file that is about to become one.

🎯 Scenario: If a workbook exists to produce an export, make the export sheet the last thing you touch before saving and put its name in a cell — =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) returns the name of the sheet that cell is on. Put it in the header of the export sheet itself and it is a tautology on the right sheet and an alarm on the wrong one.

4) The Total That Cancelled, and the Check That Would Not Have

Rounding to the nearest pound moves each line by somewhere between −49p and +50p. Over 1,840 lines with no particular bias, those movements very nearly cancel. The catalogue total moved £8.39.

That is not a small error hiding a small problem. It is an average hiding a distribution, and the distribution is where the money was:

• 1,832 lines exported a different number from the one in the cell.
• 23 lines exported 0, because they were priced under 50p.
• 19 lines between 50p and £1.49 exported 1, so a 52p grommet was published at £1.00 and a £1.24 cable at the same £1.00.
• The remaining 1,790 were wrong by less than 50p each, which on a £48.50 enclosure is 1% and on a £4.20 gland is 12%.

The checks that would have found it are not totals. They are counts and extremes:

=COUNTIF(C2:C1841,"<0.5")              → 23   lines a whole-pound format writes as 0
=COUNTIF(C2:C1841,"<1.5")              → 42   lines that publish at £1.00 or less
=SUMPRODUCT(--(C2:C1841<>ROUND(C2:C1841,0)))   → 1832  lines the file disagrees with
=MIN(C2:C1841)                         → 0.04 the cheapest thing you sell

=MIN is the cheapest check in this article and the one that would have caught it fastest. If the smallest price in your catalogue is four pence and your export format rounds to the pound, you do not need a reconciliation to know what is about to happen.

🎯 Scenario: Any export that a machine consumes deserves two numbers beside it, not one: a total, and a count of rows where the exported text and the stored value disagree. The total tells you about the file as a whole. The count tells you about the rows, and rows are what a downstream system acts on, one at a time.

5) Save As CSV Is Not a Copy — It Is a Conversion of the File You Have Open

This is the trap that costs people whole models rather than a month of prices, and almost nobody meets it until it has happened once.

File ▸ Save As ▸ CSV does not export a copy and leave you in your workbook. It moves you into the CSV. After the save, the title bar reads upload-march.csv. The open document — the one on your screen, with six tabs still visible for as long as you do not close it — is now backed by a one-sheet text file. And then:

• AutoSave turns itself off. It works only on .xlsx, .xlsm and .xlsb stored on OneDrive or SharePoint. The toggle greys out, and on a file people are used to never saving manually, that is the end of any saving at all.
• Every Ctrl+S from then on writes the CSV. One sheet. Values. No formats. No macros.
• Close the window and Excel asks nothing about the other five tabs, because as far as the open document is concerned they were discarded at the moment of the Save As, and it already told you so.
• Version history stops, because version history belongs to the .xlsx on OneDrive and you are no longer in it.

The sequence that eats a model is: Save As CSV on Monday, keep working in the window for another hour, Ctrl+S out of habit, close, and come back on Tuesday to open the .xlsm — which is exactly as it was on Monday morning, missing the hour. The hour is in a CSV, as values, on one sheet.

Three ways out, in order of preference:

1. File ▸ Save a Copy. On a workbook stored in OneDrive or SharePoint with AutoSave on, Excel offers Save a Copy in place of Save As, and it does what the name says: writes the copy, leaves you in the original.
2. Close the CSV immediately after saving it and reopen the workbook. Unglamorous, reliable, works everywhere, and takes four seconds.
3. Export from a scratch copy. Save a duplicate of the workbook, open that, Save As CSV from it, and let the scratch copy become the CSV. Nothing you care about is in the window.

One cell will always tell you where you actually are: =CELL("filename",A1) returns the full path, filename and sheet of the file that cell lives in. If it ends .csv, you are in the export.

🎯 Scenario: Put =CELL("filename",A1) in a corner of any workbook you export from regularly. It costs one cell, it updates on every recalculation, and it is the only thing on screen that distinguishes a workbook from a CSV pretending to be one.

6) .xlsm to .xlsx: The Macros Go and They Do Not Come Back

.xlsx cannot contain code. That is not a bug or an oversight — it is the point of the format, and the reason a mail server will let an .xlsx through and quarantine an .xlsm.

Save a macro-enabled workbook as .xlsx and Excel warns: the following features cannot be saved in macro-free workbooks: VB project. Answer Yes and the module is not archived, disabled or hidden. It is removed. Close the file and there is no undo, no version of the workbook in the session that still has it, and no way back except a backup or somebody's emailed copy.

This happens most often to people trying to be helpful. A recipient says "my Excel won't let me open this, it's blocking macros", and the obliging answer — I'll send it as a normal xlsx — is a one-way door for the sender if they use Save As on the master rather than on a copy.

What each modern format can hold:

🎯 Scenario: If a workbook contains code, give it a filename that says so beyond the extension — Commission model (macro).xlsm — because the extension is the one part of a filename Windows is configured by default not to show, and Commission model is what somebody will see in the Save As dialog just before they change the type.

7) .xls: 65,536 Rows, 256 Columns, 4,000 Formats and 56 Colours

Somebody's system still wants .xls. It happens with customs portals, older accounting packages, a customer's ten-year-old upload form. Excel will still write it, and the Compatibility Checker that appears first is the most useful dialog Microsoft ships, because it lists every loss by name before it commits any of them.

The limits that truncate silently if you click past it:

A 90,000-row transaction history saved as .xls becomes a 65,536-row transaction history. The warning is explicit and it is also the fifth line of a list nobody reads to the bottom of. Everything below row 65,536 is gone from the saved file, the workbook on screen still shows all 90,000 until you close it, and the file you emailed contains 73% of the data with no marker where it stops.

The 56-colour palette is the one that surprises designers: every fill and font colour is remapped to the nearest entry, so a carefully-built status column of three greens comes back as one green, and the conditional formatting that produced them was capped at three rules per cell on the way in.

And XLOOKUP, FILTER, UNIQUE, SORT, LET, TEXTSPLIT, IFS and SWITCH do not exist in the .xls world. They are written into the file with an _xlfn. prefix and return #NAME? for anyone who opens it in an older Excel — a formula that still works perfectly for you and is broken for the person you sent it to.

🎯 Scenario: When a portal insists on .xls, check the row count first with =COUNTA(A:A). If the answer is over 65,000, the honest response is not to save it — it is to split the file or ask what the portal really needs, because the version of the data that fits is not the data.

8) .xlsb: Smaller and Faster, and Nothing Else Can Read It

.xlsb is the same workbook written as binary rather than zipped XML. It holds everything .xlsm holds, macros included, at typically half to two-thirds the size, and it opens and saves noticeably faster on files with hundreds of thousands of rows. On a genuinely large model it is the right answer.

The costs are all about who else touches the file:

• Google Sheets will not import it. Neither will most web upload forms, most ETL tools out of the box, and pandas without pyxlsb installed.
• Many corporate mail and DLP policies block it, because binary is harder to scan than XML and the format carries macros.
• It is opaque to everything that reads a file as text: no unzipping to inspect a sheet, no diff in version control, no repair by hand when it corrupts.

The reasonable rule: .xlsb for a working model that lives on one team's drive and is opened by Excel and nothing else; .xlsx or .xlsm for anything that gets sent, uploaded, archived or read by another tool.

9) Which Comma? Separators, Quoting and CSV UTF-8

"Comma delimited" is a description of the format's name, not a promise about the file. Excel writes CSVs using the Windows list separator from Region settings, which on a machine configured for a country that uses the comma as a decimal point is a semicolon. The same workbook, saved by two colleagues, produces two files with two different delimiters and the same extension.

The quoting rules, which are the part people hand-write importers against and get wrong:

• A value containing the delimiter is wrapped in double quotes: "1,250".
• A value containing a double quote is wrapped, and each internal quote is doubled: Wall enclosure 400x300 "deep" becomes "Wall enclosure 400x300 ""deep""".
• A value containing a line break — an address typed with Alt+Enter — is wrapped, and the file now has a record that spans two physical lines. Any importer that reads line by line has just split one product into two.
• Leading and trailing spaces are preserved. North and North are two different regions to whatever reads the file, and =TRIM before exporting is cheaper than explaining it afterwards.

The encoding choice is the other half. The Save As list offers both CSV (Comma delimited) and CSV UTF-8 (Comma delimited):

• CSV (Comma delimited) writes in the machine's ANSI code page. Accented letters, £, €, ™ and dashes typed as en-dashes survive if the receiving system happens to use the same code page, and become é or ? if it does not.
• CSV UTF-8 writes UTF-8 with a byte order mark. Nearly everything modern reads it correctly, and a handful of older importers choke on the three BOM bytes and report a corrupt first column header.

There is no universally right answer, which is why the answer is to ask the receiving system once and write it in the handover note. What is always wrong is changing it because a file looked odd in Notepad.

🎯 Scenario: Open your last export in Notepad and look at the first data line. Count the delimiters, look for quotes around anything you did not expect to be quoted, and check the first character of the file is not an invisible one. Those three glances cover most of what an importer will complain about.

10) Dates, Percentages and Currency Symbols Carry Their Formatting Out With Them

Section 2 gave the rule; this is where it bites in the columns nobody thinks of as numeric.

Dates. A date cell is a number wearing a format, and the CSV gets the format. dd/mm/yyyy writes 11/03/2026, which an American importer reads as 3 November. mmm-yy writes Mar-26, and every line in the file becomes the first of March to anything that parses it at all. dddd d mmmm writes Wednesday 11 March, which is not a date to any system anywhere. The safe export format for a date is yyyy-mm-dd, which is unambiguous in every locale, sorts correctly as text, and is what the importer's documentation asks for whether it says so or not.

Percentages. 0% on a cell holding 0.075 writes 8%: wrong by the rounding and carrying a symbol. Export the underlying number in General, or multiply it yourself and label the column Percent.

Currency. £#,##0.00 writes £1,250.00, which contains a currency symbol and a thousands separator, so it will be quoted, and it is text to every numeric importer that meets it. A money column exports as General or as 0.00, and the currency belongs in a column of its own with GBP in it.

Times and durations. [h]:mm writes 75:00. h:mm on the same cell writes 3:00, because h:mm wraps at 24 hours. Neither is a number. If the receiving system wants hours, export =E2*24 in General and send 75.

Text that looks numeric. A product code stored as text — 0047, 1-2, 3E5 — exports exactly as stored, which is the good news. The bad news is what happens when somebody opens the CSV in Excel to check it: 0047 becomes 47, 1-2 becomes 2 January, 3E5 becomes 300000, and if they then save, the damage is in the file. Check exports in a text editor. Checking them in Excel is how a correct file becomes a wrong one.

11) What Survives What

A round-trip table, for the question people actually ask: if I save it as this and open it again, what have I lost?

The row worth staring at is .txt (Text, tab delimited). It is the same one-sheet, values-only, as-displayed export as a CSV, and it removes the entire delimiter problem, because tabs almost never appear inside product names, addresses or descriptions while commas appear in all three. If the receiving system accepts tab-delimited, it is the better file.

12) Build an Export Sheet, Not an Export

The fix for everything above is one tab, built once, that produces exactly the text the file should contain — because if the export is a sheet, it can be checked, and the thing that was wrong here was invisible precisely because it was a formatting property of a working sheet.

An Upload tab, with one column per field the receiving system expects, in its order, and every cell producing a string on purpose:

A2  =TRIM(Prices!A2)                          product code, as text, spaces gone
B2  =TRIM(Prices!B2)                          description
C2  =TEXT(Prices!C2,"0.00")                   price — two decimals, always, no format dependence
D2  =TEXT(Prices!D2,"yyyy-mm-dd")             valid-from date, unambiguous in every locale
E2  ="GBP"                                    currency in its own column
F2  =TEXT(Prices!F2,"0")                      minimum order quantity, an integer on purpose

Every one of those is a text value. A text value has no number format to misread, so what the CSV contains is what the formula says, in every region, on every machine, whoever pressed Save.

Two rows above the data, put the checks where the person doing the export will see them:

=SHEETS()                                     6 — sheets that will not be in the file
=COUNTA(Prices!A:A)-1                         1840 — rows expected
=COUNTIF(Prices!C:C,"<0.5")                   23 — lines a whole-pound format would zero
=SUM(Prices!C:C)-SUMPRODUCT(ROUND(Prices!C:C,0))    -8.39 — the export error, in full
=CELL("filename",A1)                          which file you are actually in

Then the routine is: open the workbook, go to Upload, check the five cells, Save a Copy as CSV, and open the result in a text editor before it goes anywhere. Four of those five steps are seconds; the one that takes a minute is the text editor, and it is the one that would have caught this.

🎯 Scenario: If you cannot add a tab to somebody else's workbook, do the same thing in a new file with formulas pointing at theirs. The value of the export sheet is not that it is tidy — it is that the export becomes a thing with a definition, rather than a side effect of whatever the source sheet looks like today.

13) Five One-Cell Checks

1. Which file am I actually in? =CELL("filename",A1). Returns the full path, workbook name and sheet. If the extension reads .csv, a Save As has already moved you, and everything you do from here is one sheet of values.
2. How many sheets am I about to leave behind? =SHEETS(). Six in this workbook, one in the file it produces.
3. What will this column export as? =CELL("format",C2). Returns F0 for zero decimals, F2 for two, ,0 and ,2 for the thousands-separated versions, C2 for currency, P0 for percent, D1–D9 for dates and G for General. Anything but G means the file will not contain the cell's value.
4. How many lines does the format destroy? =COUNTIF(C2:C1841,"<0.5") for a whole-pound column. Twenty-three, here. Pair it with =MIN(C2:C1841), which was 0.04 and needed no interpretation at all.
5. What is the total export error? =SUM(C2:C1841)-SUMPRODUCT(ROUND(C2:C1841,0)). Returns −8.39. Small, which is the lesson: run it alongside check 4, never instead of it.

14) Twelve Traps

1. A CSV saves the display, not the value. Zero decimal places on a price column is a formatting decision on Monday and a pricing decision on the day you export.
2. General is the only number format that exports what the cell holds. Every other format is a transformation applied on the way out.
3. The trailing comma in #,##0, divides by a thousand on export as well as on screen, so a £k summary column exports as single digits.
4. Save As CSV moves you into the CSV. The title bar changes, AutoSave greys out, and every Ctrl+S after it saves one sheet of values over your work.
5. Only the active sheet is exported — the tab in front when you pressed Save, which is a property of the moment and not of the file.
6. .xlsm saved as .xlsx deletes the VB project. Excel asks once, there is no undo after the file closes, and the prompt appears at the moment you are least likely to read it.
7. .xls truncates at 65,536 rows and 256 columns, remaps every colour to a 56-entry palette, caps conditional formatting at three rules per cell, and turns modern functions into #NAME? for whoever opens it.
8. .xlsb cannot be read by Google Sheets, most upload forms or pandas without an extra engine, and is blocked outright by some mail policies.
9. "Comma delimited" means the Windows list separator, so the same file saved on a colleague's machine is semicolon-delimited with no change to its name.
10. Dates export in their display format. mmm-yy turns every row into the first of the month; dd/mm/yyyy is read as mm/dd/yyyy by half the world. Export yyyy-mm-dd.
11. Checking a CSV by opening it in Excel is how a correct file becomes a wrong one — leading zeros go, 1-2 becomes a date, long numbers go scientific, and a save afterwards writes all of it back.
12. A total will not find a line that went to zero. Rounding errors cancel; a count of rows where the file and the cell disagree does not.

Nobody in this story was careless. The prices were right. The person exporting followed the handover note exactly, including the part about clicking through the two warnings, which is correct advice — those warnings appear on every legitimate export anybody has ever done, and an instruction that says pay attention to this dialog is an instruction that will be ignored by the fifth month.

What went wrong is that a number format is the one property of a cell that has no consequences until it has all of them. It does not affect a sum, a lookup, a chart, a PivotTable or a comparison. It is, everywhere inside Excel, cosmetic. Then a CSV reads it as the definition of the value, and a decision somebody made in January about how a board appendix should look becomes the price of a fuse in March.

So the discipline is three habits, and none of them is about being careful. Export from a sheet built to be exported, where every cell is a deliberate string and the formatting of the source sheet cannot reach the file. Check the rows, not the total — a count of disagreements and a minimum, because the failures that cost money are the extremes and totals are built to hide extremes. And read the file you produced in something that is not Excel, because the only honest view of an export is the one the receiving system gets, and Excel will always show you something reasonable instead.