Select and Filter Data with a Single Click – Excel Efficiency Tip

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 data instead of doing the boring work of checking and unchecking the same from the dropdown list. That makes our daily work much easy right. Well, the question is can we do it?

As expected, the answer is YES 🙂. In today’s post let us see how to do it.

There are 2 ways of doing it.

  1. Using the Filter by Selection or Auto Filter
  2. Using right Click

Let’s see 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 can be seen in 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, it can be accessed with the ALT based shortcut. ( Learn how to access options on QAT using keyboard shortcuts.)

filter data Using Auto Filter in Excel

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

filter data Using Auto Filter in Excel

The limitations are auto filter is a basic /simple filter and also cannot be applied directly on Excel Tables.

However, there is an alternative way to use the auto filter with tables. Let’s see how to do it.

Using Right Click to filter data

If the data is an excel table (created using CTRL+T) then an auto filter can be used as follows.

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

See the below image to understand how to use the Auto Filter function in a data formatted as table.

filter data Using Auto Filter in Excel

The right click method also holds good for normal data as well.

What is your thought on advanced filter? Please do share your thoughts on advanced filter in the comments.

Please do share this post with your friends.


Leave a Reply