Standardise the entries- Data Cleanup Template

If you are going to be receiving the same data again and again with the same issues in them, it would be easier to create a template to handle the clean-ups.

In this case, we will need to create a ‘translation’ table that will convert the wrong name into the right name. This table will be used each time and you will add items to it as you go along.

To follow along go to 3.4

The steps to follow would be:

Step 1- Create a unique list of the ‘wrong’ names in the column.

Initially you can achieve this by

  • copying the problem column to a new sheet and
  • use the Remove Duplicate tool, to be left with only the unique version of each name.

As shown below you will then create a column next to it which represents the correct version. So every time Excel sees China or China0 it knows that you want it to appear as China. This process will be the most time consuming but you will only need to do it once.

Data Cleanup Template

Step 2- Lookup the translation table

Create a new column in the data and use a VLOOKUP to match the ‘wrong’ name with the correct name.

Data Cleanup Template

Step 3- Update the table as new ‘errors’ appear

Now every month you can put in the raw data and the VLOOKUP will pull through the correct names to use in a separate column.

If there are new misspellings the VLOOKUP will give you an error message and you can update the translation table. This way, each month you need to just cleanup new errors instead of doing it all again.

Back to: Excel Dashboards and Management Reporting > Getting the data right for a dashboard