Standardise the entries e.g. Yes and No only- Once off method

Data Filter

The Data Filter is a good way to quickly clean up data.

To follow along go to 3.2

As shown below if we have data where cities are spelt in various ways we can use the Data Filter to quickly clean it. Steps to follow are:

  • Put on the data filter
  • In the search bar type a few letters of the item you are looking for. So for Washington we may type ‘Wash’ as this will hopefully be correct in all versions and unique enough that it doesn’t bring up other cities.
  • The filter will bring up all the items that match. Have a look at them and make sure they represent the same place. Untick any that are not the correct city.

Once off Cleanup

  • When you click OK you will be shown only the rows that match these items
  • Choose the one you want to use as the ‘approved’ name e.g. Washington DC, copy it and paste it to that column by highlighting the cells
  • NOTE: Only when the data filter is on will this work as it will only paste the items on the visible cells. The hidden cells will keep their old values. If you try this with hidden rows it will overwrite the data (but luckily you WEREN’T working on the original so you can recover!)

Once off Cleanup

  • When you search for WASH again you will see that they are now all the same, which will make it easier to create a dashboard that refers to Washington.

Once off Cleanup

  • You will now need to continue to do this until you have a unique list of the items.

Data Validation Error Check

Another option is to create a lookup list via Data Validation and then use the error check built into Excel.

To follow along go to 3.3

So once you have created the lookup list (column P in the image below), you can put Data Validation onto the column with the misspelt names.

Data Validation Error Check

Nothing will seemingly happen, but now go to Data, Data Validation and Circle Invalid Data as shown below.

Data Validation Error Check

You will see that Excel puts red circles around cells that don’t match an item in the data validation list.

Data Validation Error Check

NOTE: Excel doesn’t make a mistake. Although the ones above look correct there is an extra space which means they are not the same and this will affect your dashboards. In this case you will need to work through the cells and correct them. The data filter can be used for this again.

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