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.

Simple Filtering in Excel

While a sort rearranges all the data in your spreadsheet based on the order imposed from your chosen column, a filter uses the criteria you provide to filter out all rows from the spreadsheet that don't meet the criteria. The rows remain hidden, and will re-appear once the filter is removed. Filters can be applied temporarily on one or various columns in order to hone in on specific slices of your data. If you create a slice using filters that you'd like to examine separately from the rest of the data, you can copy the filtered slice to another worksheet or to a new spreadsheet.

Simple filters are helpful for finding and fixing data entry errors and typos, making quick changes to subsets of your data, and spot-checking data.

Simple Filter Instructions:

  • In the Ribbon, go to the Data Tab and select "Filter"
  • Notice that a drop-down arrow has appeared at the top of each column

Screen clipping showing the funnel-shaped filter icon in the middle of the Data Tab on the Excel ribbon.

  • Click the drop-down arrow at the top of the column you'd like to filter by
  • In the dialog box that pops up, notice that each distinct value (string of characters) that has been entered in this column is a filter option
  • You can click the individual choices on and off, and you can also search for a string of characters
  • If all choices are selected by default, you can choose 'Unselect All'
  • After you have chosen filter terms and clicked OK, Excel will display only the rows with the terms you chose

Screen clipping showing the simple filter options on an Excel column. Each unique value in the column is available as an option for filtering, and either single or multiple values can be used for filtering.