Understanding Spreadsheet Structure

What is a clean table?

A clean (or "tidy") table follows three rules:

1. One header row – each column has a single, clear name
2. One record per row – each row is one order, customer, or event
3. One variable per column – each column holds one type of information

Avoid:

• Merged cells
• Blank separator rows or columns
• Totals or subtotals inserted in the middle of raw data
• Multiple header rows

These make sorting, filtering, and analysis harder or impossible.

Example

Each row represents one order line, not a mix of headings, subtotals, and records.

How it works

• Separate layers: keep raw data, calculations, and presentation on different sheets.
• Consistent formats: use one date format (e.g., YYYY-MM-DD) and consistent category names.
• Unique identifiers: give each record an ID where appropriate.
• Formal tables: convert ranges into tables (in Excel: Insert → Table or Ctrl + T). Tables expand automatically and allow structured references like =SUM(Sales[Revenue]).

Common mistakes

Key takeaways

• A consistent layout makes filtering, formulas, pivot tables, and later migration into a database much easier.
• Clean structure first, analysis second.

Try it: Create a table of ten imaginary orders with one row per order and clear column headings.