Clean data first

Most spreadsheet problems start before any formula is written. Data that is inconsistent, merged or scattered makes every calculation harder and every report less reliable. Five minutes spent shaping data properly saves hours later.

What clean data looks like

Clean data for analysis follows a few simple rules:

• One header row with a clear, unique name for each column.
• One record per row: one sale, one expense, one employee.
• One kind of value per column: dates in a date column, amounts in an amount column.
• No blank rows or columns inside the data.
• No merged cells, subtotals or notes mixed into the table.
• Consistent values: "London" every time, not "london", "LDN" and "London " with a trailing space.

Common problems in real files

Our sample workbook, a sales export from the company's online system, has typical issues:

• Dates stored as text, so they sort alphabetically instead of chronologically.
• Numbers with currency symbols typed in, which Excel treats as text.
• Region names in different spellings.
• A total row in the middle of the data.
• Two header rows, with the second one splitting "Customer" into "First" and "Last".

Each of these will break formulas and PivotTables in ways that are hard to spot.

Quick fixes

• Find and replace (Ctrl and H) to standardise spellings.
• TRIM to remove extra spaces: =TRIM(A2).
• Text to Columns on the Data tab to split values or convert text dates.
• Remove Duplicates on the Data tab, after checking which columns define a duplicate.
• Sort by each column briefly to spot odd values at the top or bottom.

Check whether numbers are real numbers: real numbers align to the right by default, text aligns to the left. A small green triangle in the corner of a cell often means "number stored as text", and the warning menu offers to convert it.

Keep raw data separate

A good habit is to keep raw data on its own sheet, untouched, and do calculations and reports on other sheets. If something goes wrong, you can always return to the original. Later in the course, Power Query makes this even easier by recording cleaning steps so they can be repeated on next month's export.

Your turn

Open the practice file sales-raw.xlsx. Fix the header, remove the total row, convert the text dates and amounts to real values and standardise the region names. Then check your work against sales-clean.xlsx.