Excel numeric: Custom AutoFilters for numeric columns

Excel numeric:YOUR OBJECTIVE

You recorded the details of your customers’ orders in an Excel table. Now you want to display the rows with orders that have a value of more than $ 5000. To filter your table in that fashion, you must use a custom AutoFilter for excel numeric columns.

In this learning module “Excel numeric”, you will be shown how to use the AutoFilter function to perform numeric filtering.

Excel numeric: HOW DOES IT WORK?

First activate the AutoFilter for your open Excel table by clicking the button on the Data tab, in the Sort & Filter group.

Now click the filter arrow on the “Order Value” column and select Number Filters. In the open submenu, you are given the following options to specify your filter criteria:

The Equals… option allows you to specify filters that display rows of data that contain a precise value in the column on which the filter is set. The Does Not Equal… option displays all table rows while excluding the rows that contain that value. For example: if you specify an Equals… value of 1000, then all rows of data with an order value of exactly $ 1000 will be displayed. Using Does Not Equal… with the 1000 argument, would cause all data rows to be displayed except for rows with an order value of $ 1000.

The options in this area, allow you to set a filter based on a comparison of the values in the relevant column to value(s) that you set here. For example: using the Less than… option, you can display rows with order values of less than $ 1000 (or any other value). Using the Between… option, you can specify a filter to select just those rows with order values between $ 1000 and $ 2000.

With the options in this part of the menu, you can filter rows of data relative to the average of the values in the column. Using one of the two average options, Excel will calculates the average value of all entries in the column and then display all rows of data containing values either above or below that average. Using the Top 10… option, you can display the rows that contain the highest or lowest values in the column; you can yourself specify how many rows to display.

The Custom Filter… enables you to use all options described above and customize your criteria further, mainly by combining them with an AND or OR operation.

In the current example, click the Greater Than… option. And then, in the Custom AutoFilter dialog box, enter 5000 in the input box next to is greater than:

To apply your settings, click  . Only the orders with an order value greater than $ 5000 are displayed:


Did you like it? Then share it or sign up!

You can share this article with your friends or sign up for our newsletter for further information.

Leave a comment

Connect with Facebook


Kein Banner zum Anzeigen