Data & NumbersUpdated 17 October 2026
Spreadsheets, data, units and dates
Why Excel mangles your CSV, cleaning a messy dataset, timestamps and the off-by-one-hour bug, and the formulas behind EMI, GST and unit conversion.
CSV is the format everything reads and nothing agrees on. Delimiters vary by locale, quoting rules vary by writer, encodings vary by whatever produced the export, and a spreadsheet will turn a column of order IDs into scientific notation on import without asking. The guides here cover why that happens and a repeatable order for cleaning a dataset: normalise, then deduplicate, then merge. The same habit of checking the assumption applies to numbers. A Unix timestamp has no time zone until you give it one, removing GST from a price is division rather than subtraction, and a temperature conversion needs an offset where a length conversion needs only a factor.
11 guides5 subjects
11 guides
Sending a spreadsheet to someone who does not have Excel
The right format depends on one question: does the other person need to read the numbers, reuse them, or change them? Each answer has a different file.
Merging CSV files whose columns do not line up
Twelve monthly exports, one combined file. Two of the months added a column, one renamed another, and a naive concatenation hides all of it.
Unit conversion is not one multiplication
Twice the temperature in Celsius is not twice the temperature. Half the conversions people treat as multiplication are not multiplication.
Why zipping your photos saves nothing, and what to do instead
Zipping a folder of holiday photos and watching it shrink by 0.4% is not a broken tool. It is the correct answer to the wrong question.
Unix timestamps, UTC and the off-by-one-hour bug
Your report is right eleven months of the year and wrong for one week in spring. That is daylight saving, and the fix is upstream of the report.
EMI, GST and percentages: the formulas the calculators are running
A 20% rise followed by a 20% fall does not get you back to where you started. That is not a rounding error, and the same confusion makes tax and loan figures wrong.
Why your CSV breaks when Excel opens it
Excel guesses the type of every cell it opens. Those guesses have corrupted postcodes, product codes and, famously, human gene names.
Converting CSV to JSON without losing your data
Every CSV value is a string. Turning them into JSON means deciding which strings become numbers, and getting that wrong destroys IDs and postcodes.
Cleaning messy spreadsheet data: a repeatable order of operations
Most "the data is wrong" problems are four or five specific defects. Fixing them in the right order matters, because some fixes mask others.
CSV or Excel? Choosing the right format for the job
A file that opens in Excel is not the same as an Excel file. The difference decides whether your formulas, formatting and column types survive.
CSV delimiters, quoting and encoding: the three things that break files
A CSV that opens in column A on one machine and correctly on another is not corrupt. The two machines simply disagree about what CSV means.