Clean up messy spreadsheet data

Use this guide when you have inherited or exported a list, such as customers, members or products, that will not sort properly, gives wrong totals or has obvious repeats. You need Excel or Google Sheets and, before anything else, a backup copy of the file.

Step by step

1. Back up and survey the damage

   Save a copy of the file with 'original' in the name and work only on the copy. Turn on filters with Ctrl+Shift+L in Excel, or Data then Create a filter in Google Sheets, and open the filter list for each column to spot odd entries, blanks and spelling variations. Make a short list of problems, for example 'dates in two formats, duplicate emails, postcodes in lower case'.