Skip to Main Content

Data Cleaning with Excel

Through this guide you will learn tricks and tips to better clean and organize tabular data using Microsoft Excel.

Advanced Filters

From what we learned in the COUNTIF exercise, we know we must filter for the rows containing "C/O" in the Narrative column of our dataset in order to clean/update this shorthand to read "complaining of". For this task we can use advanced filters.

Screen clipping showing how to reach the custom filter option in Excel. The user first chooses Filter from the Data Tab, then from the filter icon in their chosen column they choose Text Filters, then Custom Filter.

 

Steps to create a custom text filter:

  • From the Data Tab, choose Filter
  • Choose your column on which to filter (Narrative) and click the drop-down arrow. Then choose Text Filters (just above the Search Bar)
  • Choose Custom Filter (the last choice in the menu). In Mac, Custom Filter choices are available in the "Choose one" menu once Filter is clicked.
  • The dialog box that pops up offers specific criteria from which you can choose to build your custom filter, such as whether the cells in the column contain, begin with, or end with a certain string of text. The Advanced Filter option assumes that you're searching for a string of text, so the "*text*" format is not necessary
  • For our example, choose "contains" from the criteria choices in each of the two criteria boxes on the left (Windows). In the blank text boxes enter C/O in one and c/o if the other. Join these two criteria with "and". (These joining options and criteria choices also appear in Mac as text is entered).

Screen Clipping showing the Custom AutoFilter dialog box, which appears when the Custom Filter option is chosen. Drop-down boxes towards the left of the dialog box allow the user to choose specific parameters for their custom filter.

The result of this advanced filtering is that we have filtered for all the rows containing "C/O". These rows are now ready to be cleaned.

Note: The above is a fairly simple example of applying Advanced Filter criteria. For instructions on applying more complicated criteria, see this example from Microsoft Support.