Topic 1.4: Setting up a clean table

Most spreadsheet problems start with a badly set-up table. Follow these rules and everything later in this course becomes easier.

The golden rules

1. One header row at the top, with a short, clear name for each column (Name, Branch, Date, Amount)

2. One record per row — one customer, one sale or one staff member per row

3. One type of information per column — don’t mix dates and notes in the same column

4. No blank rows or columns in the middle of your data

5. No merged cells inside the data — they break sorting and filtering

6. Keep totals separate — put them below or on another sheet, not mixed in with the data

Example of a good table

Make it easier to use

• Freeze the header row so it stays visible when you scroll. Excel: View → Freeze Panes → Freeze Top Row. Google Sheets: View → Freeze → 1 row.

• Turn on filters so you can sort and filter each column. Excel: Data → Filter. Google Sheets: Data → Create a filter.

• In Excel, Home → Format as Table adds filters, banded rows and automatic formulas in one step.

Long numbers

Excel turns long numbers (like account numbers) into strange formats such as 9.80101E+12 and drops leading zeros from phone numbers. Format those columns as Text (Excel) or Plain text (Google Sheets) before you type.