One row per record

Most problems in spreadsheets come from layout, not formulas. A professional data sheet follows a few simple rules:

1. One header row with clear, unique column names: Date, Order ID, Product, Region, Quantity, Unit Price.
2. One row per record: each order line is one row.
3. One type of data per column: dates only in the Date column, numbers only in Quantity.
4. No blank rows or columns inside the data.
5. No merged cells in data areas. Merged cells break sorting, filtering and formulas.
6. Raw data, calculations and reports on separate sheets.

A sheet that follows these rules can be sorted, filtered, summarised and charted in seconds.

Excel Tables

Select any cell in your data and press Ctrl + T to turn it into an Excel Table. Tables are one of the most useful features for beginners:

• Formatting with banded rows makes data easy to read.
• Filters appear on every header automatically.
• New rows are included automatically in formulas, PivotTables and charts.
• Formulas use readable names: =[@Quantity]*[@[Unit Price]] instead of =E2*F2.

Give each table a name (Table Design > Table Name), such as Sales, so formulas on other sheets can refer to Sales[Revenue].

Navigation shortcuts

On a Mac, Ctrl is usually replaced by Command (⌘) for most of these.

Our practice dataset

The course uses a sales table with these columns: Date, Order ID, Customer, Region, Product, Category, Quantity, Unit Price, Salesperson. Create a similar table with 30–50 rows of your own invented data, or use your own work data if it contains nothing confidential. Keep it open: every lesson builds on it.

Practice task

Convert your data into an Excel Table named Sales. Remove any blank rows and merged cells. Add a calculated column Revenue with =[@Quantity]*[@[Unit Price]]. Notice how Excel fills the formula down the whole column automatically.