Filter your data in Excel

One of the most useful tools you have in Excel is the Filter tool.

You know that the filter is ON when you see arrows on the header of your table.

Filter ON
The filter is ON when you see arrows in the header row of the table

Activate the Filter

1. Select one of the cells of your table

2. Activate the filter in the Data > Filter

Menu filter

3. Now you see arrows in the header of your table

The arrows on the headers mean you have filter

Display only a few rows

With the filter you can easily reduce the number of rows visible by selecting only one criterion.

Select one criterion (Filter on one column)

For instance, you might want to select only the rows in the beverages category.

  1. Click on the arrow containing your criterion (here the column Category)
  2. Uncheck all items (Select All)
  3. Select only the item you want (in this case, Beverages)

Only the row containing this criterion is visible but the other rows are not deleted.

Rows are not deleted

When you activate the filter, you can hide the rows not equal to your filter.

If the color of the row numbers is blue, you have a filter somewhere

As you can see, only the row numbers of the rows containing your filter are visible.

Moreover, the row numbers are blue. This indicates that a filter is active on your table.

Select multiple criteria (Filter on many columns)

You can select as many columns as you want.

For instance, if you want to select men from Germany (DE) and Austria (AT) who have bought from the Grocery category, you can do it like this:

Filter in action

How many rows are returned in your filter

When you activate a filter, you can see how many rows are returned by looking at the bottom left in the status bar

Number of rows filtered in the status bar

Remove the filter

There are many ways to remove a filter.

You can select the columns one by one and clear the filter for each one by clicking on the arrow and choosing Clear Filter From "Column Name", as illustrated here.

Remove one filter

You can also remove all the filters at once with the option Data > Clear

Remove all the filters with the option Clear

Permanent link to this article:

Leave a Reply

Your email address will not be published.