How to Enable Filter in Excel – 3 Useful Methods to Add Filters

When you have worksheets with larger data, you definitely want to narrow them down. Some of the parts can be hidden from view using filters. In Excel, you can use filters to make the bigger data finely sorted that come up with just the required entries. Only the data will be visible to you that you want to view because filters let you grouping data conveniently.

If you can hide, the unwanted data from display then why make yourself tired of doing all the irrelevant stuff of sorting data manually? Well, you must keep one thing in mind using filters can help you hide some parts of data but it will not be permanent. Your data is invisible only because you have applied filters.

Mainly, filters are used to focus on the required dataset while eliminating the other data. This feature allows you to improve the overall performance because you can use filters with text, numbers, and dates. Not only this, but you can even use more than one filter at a time. Also, remember that filters can be applied only when your dataset has a header row that identifies each column.

How to Enable Filter in Excel?

As you know, filters are helpful in arranging the data as per your needs, however, you have to choose the entries that you don’t want to hide and uncheck all the other items.

We have three different methods that can enable filters in Excel:

1. Using Filter Under the Home Tab
2. Using Filter Under the Data Tab
3. Using Filter with the Shortcut Key

Let’s understand each method one by one with the help of examples. In the below-given table, you can see the invoices issued to the buyers of different cities. Using this data, we will further apply these three methods.

Method 1:

Using Filter Under the Home Tab

Follow the steps carefully to enable the filter in Excel:

1. Choose the data and click on the “Filter” option given under the “sort and filter” drop-down. This drop-down will be in the “editing” section.

2. The filters are added to the data range that you have selected. In the image, you can see drop-down arrows in the red boxes these are the filters.
3. By clicking on the drop-down arrow of the “City” column, you can see the different names of cities given in the dataset.
4. If you only want to see the invoice value of “Delhi”, choose “Delhi” and unselect the rest of the boxes.
5. Now, you can easily have a look at the filtered data for the city “Delhi”.

Method 2:

Using Filter Under the Data Tab

Here is the second method that lets you enable filter in Excel:

1. Click on the Data tab and choose Sort & Filter, group. Click on the Filter option.

2. Now, open the Home tab and then the Editing option, and select the Filter option given within the Sort & Filter section.
3. You can use the Excel Filter shortcut that lets you on or off the filters. The shortcut is: CTRL+SHIFT+L.

Method 3 

Using Filter with the Shortcut Key

For most, the above-mentioned methods are not so exciting, that’s why they often tend to find shortcuts. Luckily, you can enable the filters option in Excel using shortcut keys from your keyboard. It also speeds up your performance and improves productivity.

You can use any of the following shortcut keys: 

• Press “SHIFT+CTRL+L” altogether

This shortcut is already mentioned in the previous method that’s why it is highly recommended.

• Press “ALT+D+F+F” altogether

Using toggle keys can help in adding filters and pressing repetitively will turn them on or off the filters.

The Techniques of Filtering in Excel:

While filtering data, you must consider the following techniques:

1. You have to type the value that needed to be filtered in case the dataset is large. In that way, you can filter all the potential matches.
2. Using the “number filters” option is recommended when the numerical data needs to be filtered by specifying the greater than or the less than the number.
3. Using “Filter by color” is recommended when you need to filter the data by the color of specific rows.

To Sum Up

Now, you are familiar with three effective methods that can help you enable filters in Excel. No matter how large or small your dataset is, you can use filters easily as per your needs. Try all of the above methods at least once so that you can understand the intent of each trick. Keep practicing Excel tricks and tips!