Add autofilter to Excel and filter data by selection

Posted by Unknown

Takeaway: Cut filtering tasks by more than half by adding the AutoFilter option to the ribbon or toolbar.

Filtering data by selecting a value seems like a nice feature. You select a region, click a button, and Excel displays only those sales records for the selected region. Nice! Excel does support selection filtering, but you’ll have to go looking for it because it’s not available by default on the ribbon or toolbar. Fortunately, once you’ve added the option, selection filtering is just two clicks away.

First, let’s take a look at the available filtering process. Specifically, let’s suppose you want to limit sports apparel sales records. You might do so, as follows:
Click anywhere inside the data range.
Click the Data tab. In Excel 2003, choose Filter from the Data menu, select AutoFilter, and skip to step 4.
Click Filter in the Sort & Filter group. Doing so displays the filtering arrows in the header cells.
Click the Item column’s filtering arrow and uncheck Select All (the default).
Check T-shirt, and click OK. (Excel 2003’s filter is a bit easier to use - just select the value).

To clear the filter, click Clear in the Sort & Filtering group. In Excel 2003, choose Filter from the Data menu and then Show All. It’s a tedious process if you do it often.

Now, let’s reduce the previous process to just two clicks by enabling the filter by selection option (AutoFilter), as follows (in Excel 2010):
Click the File tab and then choose Options (under Help).
In the left pane, choose Customize ribbon.
Choose All Commands from the Choose Commands From dropdown.
Select AutoFilter in the resulting list.
To the right, click the Data tab and then click New Group.
Click Move Up to position the new group right after the Sort & Filter group.
With the new group selected, click Add.
Click OK and Excel adds a custom group with the AutoFilter option.

In Excel 2007, add this option to the Quick Access Toolbar (QAT), as follows:
From the QAT dropdown, choose More Commands.
Repeat steps 3 through 8 above.

If you’re still using Excel 2003, customize the toolbar as follows:
Choose Customize from the Tools menu.
From the Categories list, select Data.
In the Commands list, find AutoFilter and drag it to the toolbar.
Repeat step 3 for Show All.
Click Close.

Now, you’re ready to use the new filtering option, as follows:
First, select a cell that contains the value you want to sort by. For instance, select Medium in column C.
Next, click AutoFilter (the new option in the custom group you just added). In Excel 2007, you’ll find it on the QAT. In Excel 2003, you’ll be using the button you just added to the toolbar.

That’s it. In just two clicks, you filtered your data! You can use the AutoFilter option with any column or any value in your data set. You can further hone your records by moving to a second column, choosing a value, and clicking AutoFilter. As you can see below, the filtered recordset now shows Medium T-Shirt records, not just all Medium records.