Excel 2010 Just the Steps For Dummies
Book image
Explore Book Buy On Amazon

If you’re working with a large table of data in Excel 2010, you can apply number filters to columns that contain values to temporarily hide unwanted values. Excel provides several options for filtering numeric data, including filtering for top or bottom values (using the Top 10 option) or filtering for values that are above or below the average in a column that contains numeric data.

Excel 2010 tables automatically display filter arrows beside each of the column headings. To display the filter arrows so that you can filter data, format a range as a table using the Table button on the Insert tab. Or, you can click the Filter button in the Sort & Filter group on the Data tab.

Filtering for top or bottom values

Follow these steps to filter for the top or bottom numbers in a table:

  1. Click the filter arrow for the numeric column by which you want to filter data.

    The filter drop-down list appears.

  2. Point to Number Filters in the drop-down list.

  3. Choose Top 10 in the resulting menu.

    The Top 10 AutoFilter dialog box appears.

    Use the Top 10 AutoFilter dialog box to filter on top or bottom values in a table.
    Use the Top 10 AutoFilter dialog box to filter on top or bottom values in a table.
  4. From the first option, select whether you want the Top (highest) or Bottom (lowest) values.

  5. In the second option, select the number of items you want to see (from 1 to 500).

    This spin box displays a default value of 10.

  6. In the third option, select whether you want to filter the items by their names or by their percentiles.

    For example, choose to list the top 10 customers per their sales dollars, or list the top 10% of your customer base.

  7. Click OK.

    Excel displays the records that match your criteria.

Filtering for above- or below-average values

Use these steps to filter a table for above-average or below-average values in a column:

  1. Click the filter arrow for the numeric column by which you want to filter data.

    The filter drop-down list appears.

  2. Point to Number Filters in the drop-down list.

  3. Choose Above Average or Below Average in the resulting menu to filter by numbers that meet either condition.

    Only the records with values above or below the total average (for that column) appear in the filtered list.

    A Below Average filter applied to a column of numeric data in a table.
    A Below Average filter applied to a column of numeric data in a table.

To remove filters and redisplay all table data, click the Clear button on the Data tab. If multiple columns are filtered, you can click a filter arrow and select Clear Filter to remove a filter from that column only. To remove the filter arrows when you’re done filtering data, choose Filter in the Sort & Filter group of the Data tab (or press Ctrl+Shift+L).

About This Article

This article is from the book:

About the book author:

Diane Koers owns and operates All Business Service, a software training and consulting business formed in 1988, that services the central Indiana area. Her area of expertise has long been in the word-processing, spreadsheet, and graphics areas of computing. She also provides training and support for Peachtree Accounting Software. Diane's authoring experience includes over 40 books on topics, such as PC security, Microsoft Windows, Microsoft Office, Microsoft Works, WordPerfect, Paint Shop Pro, Lotus SmartSuite, Quicken, Microsoft Money, and Peachtree Accounting. Many of these titles have been translated into other languages, such as French, Dutch, Bulgarian, Spanish, and Greek. She has also developed and written numerous training manuals for her clients.
Diane and her husband enjoy spending their free time fishing, traveling, and playing with their four grandsons and their Yorkshire Terrier.

This article can be found in the category: