One great way to find information within a large block of data is to use the filter in Excel. It allows you to see only certain entries in a column, to view the top or bottom entries, to look specifically for entries starting with a certain letter, and to sort data. This is a great way to find and correct typos.

Turn on the Filter

  1. Click anywhere inside your continuous data. Continuous data is data that has no blank columns or blank rows in it.
  2. On the Home tab of the ribbon, click the Sort and Filter button.
  3. From the menu that drops down, click Filter.

Using the Filter

When you turn on the filter, drop-down arrows will appear in each of your column headers (pictured above). When you click on a drop-down arrow, a menu pops down. The menu gives you options for sorting and several ways to filter your data. It also shows each unique entry in the column of data. This is a good way to spot if you have typos and other incorrect entries in your data.

Select Which Entries You Want To See

The filter can be used to see only specific entries (like if I wanted to correct Commmmuter in the first screenshot of this post).

  1. Click the drop-down arrow for the column you want to filter.
  2. Check the box next to only the entry/entries you would like to see. (To quickly un-check all the boxes, un-check the Select All box.)
  3. Click OK.

Once you’ve applied a filter, the image on the drop-down arrow in the column header changes to include an image of a funnel.

Other Ways to Filter

You can also filter based on a criterion. For example, if I want to filter on the retail price column of my spreadsheet, I can look for all bikes with a retail price above, below, or between set numbers. In addition, if I want to filter on a column that contains text, I can filter for cells that begin with, end with, or contain certain letters, numbers, or symbols. In this example, I’ll show you how to filter for bikes whose retail price is above $1000.

  1. Click the drop-down for the column you want to filter (in this case, Retail Price).
  2. Select Number Filters.
  3. Select Greater Than.
  4. In the top row of the window that opens, leave greater than selected (although you click the drop down arrow and change options).
  5. In the box next to greater than, type the number you want your results to be greater than.
  6. Click OK.

Clear a Filter

  1. Click the drop-down for the column that has the filter applied.
  2. Select Clear filter from “(column name)”.