Filter Data with a Single Click – Excel Efficiency Tip

Can we select and filter data with a single click in excel.

As expected, the answer is YES. In this post let us see how to do it.

Filter is one of the well-known and most widely used function in excel. The basic way filter operates is identifying the value from the multiple values in the dropdown available in header and checking or unchecking the same.  

What if we can directly select the cell and are able to filter the data instead of doing the boring work of checking and unchecking the same from the dropdown list.

Why do we need to learn how to Select and Filter Data with a Single Click

• Reduce the effort of choosing one option from a long list of values.
• That makes our daily work much easy.
• Saves time. We will be more efficient.

Without any further delay, let us see how to select and filter data with a single click and different ways of doing that.

Ways to Select and Filter Data with a Single Click

There are 2 different ways to select and Filter Data with a Single Click.

1. Using the filter by Selection or Auto Filter
2. Using Right Click

Let us now understand each of them in detail.

Using the Filter by Selection or Auto Filter

Unlike other options, the Filter by Selection is not available in the ribbon. We need to enable the function and add it to Quick Access Tool bar before using it.

Know more about the Quick Access Tool Bar, its usage and how to add/ remove commands to QAT.

1. Go to More Commands from the QAT menu.
2. Choose Commands not in ribbon.
3. Select Auto Filter and click on Add.
4. Click on OK. Now a filter icon is visible on the QAT.
5. Select the cell on which the data need to be filtered and click on the auto filter icon. The data is filtered based on the cell value.
6. Since this is on QAT, you can access it with ALT shortcut. (Learn how to access options on QAT using keyboard shortcuts.)

Once you add the Autofilter option to Quick Access Toolbar, select the cell with the data point which you want to filter. Then click on auto filter option added to quick access toll bar. You can see the data filtered with selected cell value.

See the below image to understand how to use the Auto Filter function.

Limitations of using Auto Filter

The limitation of auto filter is, it a basic /simple filter and also cannot be applied directly on Excel Tables.

Read – How to create and use Excel Tables

Using Right Click

Surprising !!! Yes, you can filter the data using right click.

1. Right click on the selected cell.
2. From the menu, choose Filter and then Choose Filter by selected Cell’s Value or Color or Font Color or Icon.

The right click option can also be used on data formatted as a table in excel.

See the below image to understand how to use the Auto Filter function in a data using right click.

What are your thoughts on auto filter?

Do share your thoughts on using auto filter in the comments.

Do not forget to share this post with your friends.