Data tab — Excel guide
Getting data in and knocking it into shape: imports, sorting, filtering, cleanup, and what-if. Alt+A from the keyboard. Data Tools is the group auditors wear out first.
Get & Transform (Power Query)
Get Data, From Text/CSV, From Web, From Table/Range — imports that REMEMBER their cleanup steps. Shape the data once in the query editor (split, filter, retype, rename) and the recipe replays on every Refresh. When next month's file lands, you refresh instead of redoing; paste-and-clean can't compete.
Queries & Connections
Refresh All re-pulls every query and rebuilds every pivot cache in the workbook — pivots do NOT update themselves when source data changes. The Queries & Connections pane lists what's wired up and when it last ran.
Sort & Filter
The AZ/ZA buttons quick-sort by the current column; the Sort dialog handles multi-level sorts (by region, then by amount) plus sort-by-color. Filter (Ctrl+Shift+L, or Alt+A, T) toggles the header dropdowns. Advanced does criteria-range filtering — mostly displaced by FILTER() these days.
| Button | What it does |
|---|---|
| AZ / ZA | Quick sort by the active column |
| Sort | Multi-level sort dialog |
| Filter | Toggle header dropdown arrows (Ctrl+Shift+L) |
Data Tools
The cleanup bench. Text to Columns splits one column into several by delimiter or width. Flash Fill (Ctrl+E) learns a pattern from your examples and fills the rest. Remove Duplicates deletes repeat rows, keeping the first. Data Validation restricts entries — its List option is how every dropdown input sheet gets built. Consolidate merges ranges across sheets.
| Button | What it does |
|---|---|
| Text to Columns | Split by delimiter or fixed width |
| Flash Fill | Pattern-fill from examples (Ctrl+E) |
| Remove Duplicates | Delete repeat rows, first occurrence wins |
| Data Validation | Restrict entries — dropdown lists, limits, messages |
Forecast
What-If Analysis holds Goal Seek (solve backwards: set this cell to that value by changing the other cell), Scenario Manager (save named sets of inputs), and Data Table (grid of outcomes across one or two changing inputs). Forecast Sheet extrapolates a time series with confidence bands.
Outline
Group makes selected rows or columns collapsible behind +/− buttons in the margin — the honest alternative to hiding rows, because everyone can see something's tucked away. Subtotal inserts SUBTOTAL rows at each change in a column, with the outline built for you.
| Keys | Action |
|---|---|
| Alt+Shift+→ | Group selected rows/columns |
| Alt+Shift+← | Ungroup |
From the keyboard
Data tab sequences that come up daily.
| Keys | Action |
|---|---|
| Alt+A, T | Toggle filter arrows |
| Alt+A, S, S | Custom sort dialog |
| Alt+A, M | Remove duplicates |
| Alt+A, V, V | Data validation |
| Alt+A, E | Text to Columns |