Data Analyst · Step 2 of 8 · 10 min
Spreadsheets done right
You can set up a clean table, summarise it with a pivot table, and join two sheets with XLOOKUP.
One table, one row per thing
Good analysis starts with a tidy table: one header row, one row per record (one order, one customer, one day), and one kind of value per column. No merged cells, no blank rows used as spacing, no totals mixed into the data.
In Excel, select the data and press Ctrl+T (Cmd+T on Mac) to make it a real Table. In Google Sheets, use Format, then Convert to table. Tables keep formulas and ranges growing as you add rows.
Types matter
A number stored as text will not add up. A date stored as text will not sort by time. Left-aligned numbers and dates in odd formats are warning signs. Check with ISNUMBER, or try sorting the column and see whether it behaves.
Keep units in the header, not in the cell: “Revenue (USD)” with plain numbers below, not “$1,200” typed as text.
Pivot tables: the analyst’s first tool
A pivot table summarises a big table in seconds: total sales by region, count of orders by month, average basket size by channel. Drag a field to Rows, another to Values, and choose Sum, Count or Average.
Always check the pivot against something you know. If the grand total does not match the sum of the original column, a filter or a text-stored number is hiding somewhere.
XLOOKUP: joining two tables
Often you have orders in one sheet and customer details in another. XLOOKUP brings a value across: =XLOOKUP(A2, Customers[ID], Customers[Region], "Not found"). It works in current Excel and in Google Sheets. Older files may use VLOOKUP or INDEX/MATCH, which do the same job less safely.
Count the “Not found” results. They tell you about missing or mistyped IDs, which is a finding in itself.
Try it
Build your first pivot
- 1.Open any sample sales dataset (search “sample superstore dataset” or use your own anonymised data).
- 2.Convert it to a Table and check that date and number columns sort correctly.
- 3.Make a pivot table: total sales by month and by category.
- 4.Check the grand total against a simple SUM of the original column.