How to Clean Up Messy Spreadsheet Data Before Building a Dashboard

One of the most common things we hear from new clients is: 'We tried Power BI but it kept giving us wrong numbers.' Almost always, the problem is not Power BI. The problem is the source data.

A dashboard is only as reliable as the data that feeds it. If your source spreadsheets are inconsistent, the dashboard will reflect that inconsistency — quickly, visibly, and to everyone who views it.

Here is what to fix before you build anything.

1. One row per record, always

The most fundamental rule of structured data: each row represents exactly one thing — one sale, one customer, one invoice, one event. If you have merged cells, summary rows mid-table, or data that spans multiple rows for one record, fix this first.

Power BI and Tableau both expect tabular data. Summary rows belong in the dashboard, not the source file. Remove them.

2. Consistent field names across all files

If you have three monthly export files and one calls the column 'Customer Name', one calls it 'Client', and one calls it 'customer_name' — Power BI will treat these as three different fields when you combine the files. Every column name must be identical across all source files.

Create a field name standard and apply it consistently: use plain English, avoid spaces (use underscores or camelCase), and be specific ('order_date' not 'date').

3. Consistent date formats

Date formats are one of the most common causes of import errors. If some rows show '01/04/2025', some show '1 Apr 2025', and some show '2025-04-01', Power BI will struggle to recognise them all as dates.

Choose one format and enforce it: ISO format (YYYY-MM-DD) is the safest because it avoids the UK/US day-month ambiguity. If you're using Excel, format the date column as 'Date' rather than 'Text' or 'General'.

4. No blank rows or columns

Blank rows between data sections, blank columns used as visual spacers, and empty header rows all cause problems when importing. Every row should be data. Every column should have a header. No exceptions.

5. No merged cells

Merged cells are a formatting feature in Excel. They do not exist in structured data. When Power BI reads a file with merged cells, it sees the value in the first cell and blank values in all the others. Unmerge everything.

6. Consistent value formats in categorical fields

If a field contains categories — 'Region', 'Status', 'Product Type' — every value must be spelled and capitalised the same way. 'Scotland', 'scotland', 'SCOTLAND', and 'Scotand' (typo) are four different values to a data model.

Use Excel data validation to enforce a list of allowed values. Or, if you're using SQL, enforce this at the database level with a lookup table.

7. Remove calculated columns from source files

Source files should contain raw data. Calculations — margins, totals, running sums, percentages — belong in the data model, not the source. If your source spreadsheet has a 'Profit %' column calculated from two other columns, remove it. Power BI will calculate it more reliably in DAX.

The fastest way to check your data

1. 1.Open the file in Excel and press Ctrl+End — the cursor should land in the last cell of actual data, not three rows below it
2. 2.Check every column header — are they unique, consistent, and without special characters?
3. 3.Sort by date column and check the format is consistent throughout
4. 4.Filter each categorical column and look for near-duplicates or inconsistent capitalisation
5. 5.Look for any merged cells (Home → Find & Select → Go To Special → Merged Cells)

This process takes 30–60 minutes for a typical SME spreadsheet and prevents weeks of frustration downstream.