How to Clean Up a Messy Excel Spreadsheet

Some spreadsheets are untidy. Others look as though six people have edited them since 2008 and nobody has ever met.

The safest way to clean a messy workbook is not to attack everything at once. Work through it in a deliberate order and keep the original untouched.

1. Save a clean copy first

Before changing anything, duplicate the workbook. Give the working copy a clear name and leave the original alone. This gives you somewhere safe to retreat if a clean-up step removes something important.

2. Work out what each row represents

Is each row a customer, transaction, vehicle, order or appointment? Establish that before deleting duplicates or restructuring columns. Without that basic rule, it is easy to remove records that only look similar.

3. Fix the headings

Use one clear header row. Remove duplicate headings buried halfway down the data and give vague columns meaningful names. Avoid several columns that appear to contain the same thing unless they genuinely serve different purposes.

4. Remove empty clutter

Blank rows and columns can make filtering and importing harder. Remove genuinely empty structural clutter, but do not delete blanks that represent meaningful missing data without understanding the dataset first.

5. Standardise formats

Dates should follow one system. Numbers should be numbers, not a mixture of numbers and text. Names and categories should use consistent spelling. Decide whether values such as Yes, Y, TRUE and 1 all mean the same thing before standardising them.

6. Deal with duplicates carefully

Do not simply delete every repeated-looking row. Decide which columns define a duplicate and whether repeated records can legitimately occur.

See how to remove duplicates in Excel without losing your data for the safer approach.

7. Check formulas and references

Cleaning columns and moving data can break formulas. Check totals, lookups and references after structural changes, particularly if the workbook feeds reports or other sheets.

8. Turn the data into something usable

Once clean, consider using an Excel table with filters and consistent headings. The aim is not merely to make the spreadsheet prettier. It should become easier to sort, filter, update and understand.

Can’t be faffed?

If the spreadsheet is too large, too inconsistent or too mysterious to clean safely by hand, Faff It Off.

Send us the file, explain what the finished data needs to do, and we’ll quote for turning the spreadsheet swamp back into something useful.