Excel 2016 Tips and Tricks

Lesson 2 – Working with Tables

Excel 2016

Click the Clear button in the Sort & Filter group on the Data tab to clear the filters from all the columns.

U SING N UMBER F ILTERS  D ISCUSSION

When a table column contains number entries, the Filter menu for the column offers several special Number Filters which provide powerful options for filtering the data. Instead of simply filtering the data by selecting one or more column entries, you can filter the data to display entries that are Greater Than , Greater Than or Equal To , Less Than , or Less Than or Equal To a specified number. You can also filter to display all entries that fall between two specified numbers or that are not equal to a specified number. When you select any of the first seven Number Filters from the Number Filters submenu, the Custom AutoFilter dialog box opens with the chosen filter type pre-selected. You simply need to enter the number by which you want to filter. You can specify a second filter criteria in the dialog box, if desired. When you specify a second filter criteria, you must also choose whether both criteria need to be met to display a row in the filtered data (choose And ) or whether a row that meets either criteria should be displayed (choose Or ). The Top 10 number filter goes a lot further than its name implies. Although it is pre-set to show the top ten number entries by value, you can change the options to show the bottom values instead and you can change the number of values to any number you require. You can also change an option to display values in the top, or bottom, ten percent (or the top three percent, bottom 15 percent, etc.)

When you select the Top 10 number filter, Excel opens the Top 10 AutoFilter dialog box. When you use the Above Average or Below Average number filters, no dialog box is displayed; Excel immediately calculates the average value in the column and displays only the values that are above, or below, the average value.

Page 50

OFFICEPRO, Inc.