Intermediate Excel Tricks: Do Smashing Work with Excel Filters!

To get fancy sort options in Excel with little to no work, try out Excel filters. Here’s little a spreadsheet tutorial to get you started:

To activate filters, click on any header label in your table, go to the Data tab or menu, and click the filter option (a gray funnel icon in Excel 2007+) or auto filter option (Excel 2003). Now dropdown arrows should appear to the right of all of your column labels.

Excel Filters appear as little dropdown arrows next to each column label.

Excel Filters appear as little dropdown arrows next to each column label.

Click the down arrow on any column header and check out the options that are available. The first two options will be sorting ascending (A-Z) and descending (Z-A).

Sort Ascending Example.

The following table is sorted by the Manager column. If you choose to sort by the Model column, you will be able to quickly see groupings by model as in the second screenshot.

Pre-sorting: Entire rows should stay in synch when sorting. Once we sort on Column D, all the values in this row should move up.

Before: Entire rows should stay in synch when sorting. If we click the dropdown arrow next to 'Model req.' and choose sort A-Z, this row with 'Blackberry' should move up.

Post-sorting: After sorting column D ascending, this row has moved up. Not only did ‘Blackberry’ move up but all values in the row moved up, keeping our data in synch.

After: After sorting column D alphabetically, the row with 'Blackberry' has moved up. Not only did ‘Blackberry’ move up but all values in the row moved up, keeping our data in synch.

Filters give you the ability to to quickly find a row or group of rows based on your filter criteria. The filter dropdown will usually show a list of values in the column that you can choose from . This is really cool and magical – some programs would require you to write SQL code in order to find rows that match your criteria. No SQL knowledge is required with Excel Filters! Check out this example….

Filter by Phone Example.

The filter for for values of 'x' has been selected.

The filter for for values of 'x' has been selected.

For the Phone column in this table, we can chose to display only the rows that have (Blanks) in the the Phone column; only the rows that have an ‘x’ in the Phone column; or we can (Select All).

So if we check the filter for only values that are ‘x’, we will see only employees who have phones and the rest of the rows would be hidden. And if we check the filter for blanks we will only see employees who do NOT have phones and the rest of the rows would be hidden. How cool is that?

To reset the filter, we can choose Select All and all rows would be shown.

Filter shows only employees who have phones. Notice rows 8-10 are hidden.

If we filter for 'x' values in column E, we will see only employees who have phones. Notice rows 8-10 are hidden.

Filter shows only employees who do not have phones. Notice rows 3-7 are hidden.

If we filter for only the blanks in column E, we will see only employees who do NOT have phones. Notice rows 3-7 are hidden.

I invite you to try this out and see what you think! Put this one in your bag of Excel tricks and you really can impress your boss or colleagues with your ability to quickly filter for certain rows in your spreadsheet. Leave a comment and let me know how it goes and any additional filtering tricks you find!

This entry was posted in Excel Tricks, Intermediate Excel, Spreadsheet Tutorial. Bookmark the permalink.

One Response to Intermediate Excel Tricks: Do Smashing Work with Excel Filters!

  1. Michelle says:

    P.S. Very Important! If dropdown arrows do not automatically appear on all of your header labels (this is more likely to happen in Excel 2003) your data is at risk of getting out of synch! If this is the case, then highlight all of your header labels and click the Filter button so that the filters will appear on *all* of the labels, thus keeping all the data in synch when you apply a filter.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>