How to Clear Filter in Excel? Shortcut!

Excel filters are quite useful when you’re trying to analyze a large amount of data.

Excel allows you to quickly apply filter your data based on one or more than one column, with an easy-to-use interface.

While filters are great, there are some issues that might require you to first clear all the filters from your data in Excel.

For example, if you get an Excel file from a colleague and it already has some filters applied to it, you will only see the data that is visible after the filters have been applied.

If you want to see the entire data set, you will have to clear the filters first.

Sometimes, people forget to clear the filters which can lead to incorrect analysis of their data.

In this short tutorial, I will show you a couple of really simple ways to clear filters in Excel (including keyboard shortcuts)

Table of Contents

Methods to Clear Filter in Excel

Let’s have a look at some easy ways you can use to remove filters from your data in Excel.

Keyboard Shortcut to Clear Filter in Excel

The fastest way to add or clear filters in any data set in excel would be by using a keyboard shortcut.

ALT + A + C

To use this keyboard shortcut, press these keys in succession (one after the other).

Note that the above keyboard shortcut would only clear the filters that have already been applied to a data set.

It would not remove the filter from the data set (i.e., you would still be able to see the filter icon in the column header, as well as the entire data set because any applied filters have been cleared)

Also, note that this keyboard shortcut would only work if you have some filter criteria applied to your data set.

If you have not applied any filter, then this keyboard shortcut would do nothing

Keyboard Shortcut to Remove Filter in Excel

The keyboard shortcut I covered in the earlier section would clear the filter (in case it has been applied), but it would not remove the filter – which means you would still be able to see the filter icons in the column headers.

Now let me give you the keyboard shortcuts that you can use to remove the filter from the data set.

CONTROL + SHIFT + L

To use the above keyboard shortcut, you have to hold the Control as well as the Shift key and then hit the L key.

If you use this keyboard shortcut on a data set where the filters have been applied (i.e., some of the rows are not visible because of the filter), it would first undo the filter so you would be able to see the entire data set, and then remove the filter from the data set.

Note that the above keyboard shortcuts work as a toggle – so if you use it on a database that does not have any filters applied to it, this would apply the filter which would be visible in the column header.

This would only remove the filter from the data set that has been selected (where you can select the entire data set or any cell in that data set that has the filters applied to it).

If you have multiple tables in your worksheet (separated by blank rows or columns), and you have applied filters in these tables, the above keyboard shortcut would only remove the filters from the data set that has been selected.

Using the Clear Filter Option in the Ribbon

If you’re not a fan of using keyboard shortcuts, Excel also has a ready-to-use option in the ribbon to quickly clear filters from the selected data set.

Below are the steps to clear the filter using the option in the ribbon:

Select any cell in the data set from which you want to remove the filter
Click the Data tab

In the Sort & Filter group, click on the ‘Clear’ icon

Note that the above steps would clear all the filters, but they would not remove the filter functionality from the data set.

So you would still be able to see the filter icon in the column headers.

However, if you had filtered the data by applying filters on some of the columns, that would be cleared.

In case you want to remove the filter as well, instead of the clear icon, you can click on the Filter icon.

Doing this would first clear the filter so you would see the entire data set, and then the filters would be removed.

Remove Filter from One Column Only

If you have a data set where you have applied filters to multiple columns, using any of the methods I’ve covered above to clear or remove the filter would clear the filters from all the columns at one go.

But what if you only want to clear the filter from one column, but still keep the filter in the other column?

In this case, you will have to manually remove the filter from the column.

Below I have a data set where I have two filters applied – one on the date column and one on the training column.

And here are the steps to remove the filter only from the training column:

Click on the Filter icon in the ‘Training Completed’ column

Click on the filter icon in the column header

Click on the ‘Clear filter from’ option

The above steps would only clear the filter from the specific column (Training Completed column in this example), and would not impact the rest of the data set.

In case you want to clear the filter from multiple columns, you can repeat the same process for each column.

Also read: How to Remove Formulas and Keep Value

How to Know that Filter Has Been Applied to your Data?

There are some easy ways to quickly identify if filters have been applied to a data set or not.

The best way to check would be to just glance at the status bar (the Gray strip at the bottom left part of the worksheet) and see if the filter has been applied or not.

If the filter has been applied, you would see one of the two things in the status bar

Filter Mode – this indicates that one or more filters have been applied to this worksheet

X of Y records found (where X and Y would be numeric values) – this would tell you that some records are hidden because a filter has been applied

Once you clear the filter or remove the filter altogether, you would notice that these prompts go away from the status bar.

Another visual cue you have to identify whether filters have been applied or not is by looking at the column headers.

If a filter has been applied to a column, you would see a filter icon at the right part of the column header cell.

In this tutorial, I showed you how to clear filters in Excel using a simple keyboard shortcut or using the option in the ribbon. I have also covered how you can quickly identify whether filters have been applied to your data set or not.

I hope this tutorial was worth your time and that you found it useful.

I also have some other Excel tutorials that he may also like: