Wired ran a piece on competitive spreadsheet users, which is a genuinely entertaining corner of the internet. It also prompts a more useful question: of the several hundred functions in Excel, which ones actually change how fast you work?
It is a shorter list than you would expect, and most of it is not about functions at all.
Structure beats formulas
The single biggest determinant of whether a spreadsheet is workable is layout, and almost nobody is taught it.
Keep raw data in one flat table: one row per record, one column per field, no blank rows, no merged cells, no totals in the middle, headers on a single row. Do your calculations somewhere else, referencing that table.
Merged cells in particular break sorting, filtering, pivot tables and most formulas. They exist for printed layout and should never appear in data.
A clean table makes every tool below work. A messy one means fighting the software for the rest of the file’s life.
Format as Table
Ctrl + T, and it is the most underused feature in the application.
A real table gives you formulas that reference column names instead of cell ranges, so =SUM(Sales[Revenue]) rather than =SUM(D2:D5000). It expands automatically when you add rows, so charts and pivots include new data without being repointed. It keeps headers visible when scrolling.
The practical effect is that a whole class of “the formula stopped including the new rows” bugs simply disappears.
The lookup that replaced VLOOKUP
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])If you have XLOOKUP, use it and stop using VLOOKUP entirely.
VLOOKUP counts columns, so inserting a column silently breaks it while still returning a number. It also cannot look leftwards, and defaults to approximate matching, which produces wrong answers rather than errors. Every one of those is a silent failure, which is the worst kind in a spreadsheet.
XLOOKUP takes the ranges directly, looks in any direction, defaults to exact matching, and has a built-in not-found value so you are not wrapping everything in IFERROR.
On older versions, INDEX and MATCH together do the same job and survive column insertion.
Pivot tables
People avoid pivot tables because they look complicated, then spend hours writing SUMIFS formulas that do the same job worse.
A pivot answers “totals by category, by month” in about fifteen seconds, and rearranges instantly when the question changes. Learning it is an afternoon, and it pays that back within a week.
The prerequisite is the clean flat table from the top of this article. Pivots on messy data are what gives them their reputation.
Six shortcuts worth muscle memory
- Ctrl + arrow jumps to the edge of a data block. Add Shift to select on the way.
- Ctrl + Shift + L toggles filters.
- Alt + = inserts a SUM of the range above.
- F4 toggles absolute references while editing a formula.
- Ctrl + Shift + V or Paste Special for values only, which kills the formatting that arrives with pasted data.
- Ctrl + D fills down from the cell above.
The habit that prevents disasters
Never type a number inside a formula. Put it in its own labelled cell and reference it.
A tax rate hard-coded into forty formulas is forty places to update and thirty-nine chances to miss one. In its own cell it is one edit, and anyone reading the sheet can see what assumption is being made.
Most serious spreadsheet errors that have made the news were not exotic. They were a hard-coded value, a range that stopped short of the last row, or a copied formula whose reference shifted. Structure prevents all three.
Join the discussion