Data tools — Excel guide
The features around the grid that keep data trustworthy: Tables, validation, cleanup tools, and what-if solvers.
Tables (Ctrl+T) — the single best habit
A Table auto-expands as rows arrive: formulas fill down, pivots and charts see new data on refresh, and =SUM(Sales[Amount]) reads by column NAME (structured references) instead of fragile ranges.
Data Validation
Data ▸ Data Validation restricts entry: dropdown lists (point the source at a spilled =UNIQUE(...)# for a self-updating list), number/date limits, or a custom formula — =COUNTIF($A:$A, A1)=1 blocks duplicate ids at the door.
Cleanup: Remove Duplicates, Text to Columns, Flash Fill
Remove Duplicates edits in place, once — the UNIQUE function is the live alternative. Text to Columns splits on a delimiter (TEXTSPLIT is its formula twin). Flash Fill (Ctrl+E) learns a pattern from one example — magic for one-off reshapes, but it doesn't update when data changes.
Goal Seek and Solver
Data ▸ What-If Analysis ▸ Goal Seek inverts any single-input model: 'set this cell to 0 by changing that cell' — a generalized IRR. Solver is the many-inputs-with-constraints version. Scenario Manager saves named input sets to flip between.
Power Query
Data ▸ Get & Transform records a cleanup ONCE (split, filter, merge files, unpivot) and replays it on refresh. When you find yourself re-cleaning the same monthly CSV, this is the exit ramp.