Excel
Error Handling With IFERROR and Safer Alternatives Today
Handle Excel formula errors without hiding bugs: IFERROR versus IFNA, testing inputs first, ISERROR functions, XLOOKUP not-found values and error codes.
6 min read
Tag
spreadsheets
45 posts
Excel
SUMPRODUCT as a Swiss Army Formula for Conditional Sums
Use SUMPRODUCT for weighted averages, OR conditions, multi-column counts and arithmetic before summing, with its speed limits and what to use instead.
6 min read
CSV
Excel CSV Versus RFC CSV Differences Worth Knowing About
Where Excel's CSV departs from RFC 4180: delimiter, encoding, BOM, the sep line, displayed values, formulas and the ID trap, plus a converter script.
7 min read
CSV
Opening a CSV With More Than a Million Rows in Excel
Excel stops at 1,048,576 rows and drops the rest if you save. Four ways to work with a bigger CSV: aggregate it, split it, use the Data Model or sample.
6 min read
CSV
Keeping Long Numbers Intact When Excel Opens a CSV
Excel keeps 15 significant digits, so a 19 digit ID loses its last four. Why it happens, how to import the column as text, and how to check a file for damage.
6 min read
CSV
Excel CSV UTF8 Versus CSV Comma Delimited Explained
Excel offers five CSV-like save formats. Here is what each writes, why plain CSV Comma Delimited loses characters, and which one to pick for each reader.
6 min read
CSV
Importing a CSV Into Google Sheets With the Right Types
Import a CSV into Google Sheets without losing zeros or flipping dates: the import dialog, the locale, IMPORTDATA and an Apps Script that keeps text.
6 min read
CSV
Saving an Excel Sheet as CSV Without the Extra Commas
Excel adds trailing commas and blank rows to a CSV when empty cells outside your data hold formatting. Find the used range, clear it and check the file.
7 min read
CSV
Why Excel Shows a CSV in One Column and What Causes It
Excel puts a whole CSV row in one cell for five different reasons. Learn how to tell which one you have by reading the first line of the file.
7 min read
CSV
How to Import a CSV Into Excel With Power Query Reliably
Import a CSV into Excel with Power Query so encoding, delimiter and column types are set once, and a refresh loads next week's file the same way.
7 min read
CSV
Opening a Semicolon Separated CSV in Excel Without Mess
A semicolon CSV lands in one column when Excel expects commas. Five ways to open it correctly, and how to convert the file when decimal commas are involved.
7 min read
CSV
Stopping Excel From Turning Gene Names and IDs Into Dates
Excel reads SEPT2 as 2-Sep and MARCH1 as 1-Mar, and saving makes the change permanent. Here is how to import, detect and recover gene names and similar IDs.
8 min read