I think one of the most powerful single commands in Excel is the AutoFilter.  Even if you already are familiar with the AutoFilter you may find this helpful, because I am going to review the Custom and Top 10 properties of the AutoFilter too.

To use the AutoFilter, select the columns with the data you would like to filter and then go to the Main Menu and select Data, then Filter and finally AutoFilter.  Small drop down arrows should now be visible in the first cell of each column.  Now clicking on the drop down arrow will provide a unique display of all the contents in that row in ascending order.  You can use the AutoFilter on multiple columns, thereby making compound filters to quickly narrow down and find information.

One good example of using the AutoFilter on mutiple text columns would be a company phonebook.  Assume your company has worldwide offices and you want to find someone in the San Francisco office.  You would invoke the AutoFilter on the columns for State, and City.  First select the State of California; now you have narrowed the worldwide phone directory down to one State with a single click.  Assuming that you have offices in San Franciso and Los Angeles, select the AutoFilter drop down for the City column and select San Francisco.  With two clicks using the AutoFilter you have narrowed down the worldwide phonebook to the people in the San Francisco office.

To illustrate the AutoFilter with numeric data, let's assume you have annual revenue for each customer, and would like to quickly see the Top 10 largest customers.  Having already invoked the AutoFilter on the revenue column, click the drop down arrow and select Top 10.  The Top 10 AutoFilter menu opens and the defaults are 'TOP', '10' and 'Items'; just click OK.  Now your customers have been filtered to display the Top 10 largest revenue customers.  If you went back and did that again, but select 'Percent' instead of 'Items', you would have those customers that contribute 10% of your total revenue.

One last example using the Custom property.  Using the customer and revenue data again, let's assume you only want to see the customers with revenue greater than $5M.  Clicking on the drop down arrow and selecting Custom, opens the Custom AutoFilter menu.  Click the Number pull-down and select 'is greater than', click the pull-down to its right and your revenue values would appear, and you would select $5M, and click OK.  Your customers will have been filtered to present only those customers with revenue greater than $5M.

The AutoFilter is but one of the many tools we use in our workshops to teach sales and marketing teams how to use analytical forecasting techniques, calculate the impact of price erosion, prioritize customers by Pareto analysis, regress your competitors pricing, and more.