Microsoft

Four useful Excel filtering behaviors

Excel's filtering feature is helpful, but add a bit of creativity and it becomes much more. Susan Harkins explains.

Excel

Excel's AutoFilter feature is easy to implement and use, but it can do more than first meets the eye. The trick is to be a bit creative. The tips in this article aren't related in any way; they all extend the feature or use it to complete a non-filtering task.

For your convenience, you can download the .xlsx or .xls demonstration file, but you can work with any simple dataset. The article includes instructions for Excel 2010, but it also provides steps for Excel 2003 when significantly different.

About AutoFilter

Excel's AutoFilter feature inserts an easy-to-use interface that makes filtering records quick and efficient. Once you apply the filter to the dataset, you simply choose the natural data to display records that match the specified value. It's limited, and often you'll need to bump up your filtering to the advanced features, but it makes quick work out of simple filtering tasks.

To engage the AutoFilter feature, click any value in the dataset. Click the Data set, and then click Filter in the Sort & Filter group. Excel will display drop-downs to the header cell for each column in the data set. Click these drop-downs to filter by existing values in each corresponding column. To clear a filter, click Clear in the same group. In Excel 2003, choose Filter from the Data menu, and then select AutoFilter (to clear a filter, uncheck AutoFilter).

Count filtered records

You can learn the number of records that satisfy a filter by glancing at the Status bar. As you can see in Figure A, the current filter displays three of seven records found. With a quick glance, you can learn two things: the data set has a total of seven records, and three of those records match the current filter. It's a handy feature, but not all of that useful because you can't reuse the information. In addition, older versions of Excel have a known issue with this display — it's inconsistent.

Figure A

Figure A

The Status bar displays the number of records in a filtered set.

If you'd like to return a count of qualifying records within the sheet itself, use the following SUBTOTAL() function, as shown in Figure B:

=SUBTOTAL(2,B4:B10)

Without a filter, the function returns the total number of records (seven) in the data set.

Figure B

Figure B

Use SUBTOTAL() to return the number of records that meet the current filter.

Now, specify a filter and watch that number update accordingly:

  1. Click the Year drop-down.
  2. Uncheck (Select All). In Excel 2014, select 2014, and you're done.
  3. Check 2014 (Figure C).
    Figure C
    Figure C
  4. Click OK. The filtered set contains three records (Figure D).
    Figure D
    Figure D

When the count is the result of a function, you can easily reuse that value. The Status bar count is good information, but you can't reuse it.

The SUBTOTAL() function offers many possibilities for summarizing filtered sets. For instance, if you wanted to sum Amount values, you'd use the function SUBTOTAL(9, C4:C10). Table A defines this function's number argument.

Table A

Table A

Enter same value in noncontiguous range

Occasionally, you'll end up with blank cells that need values, as shown in Figure E. When entering the same value, you can take advantage of filtering in a creative way, even if you're not using it to filter records!

Figure E

Figure E

You might end up with blank cells that need values.

By filtering the blanks, you can quickly enter the value in all the blanks at once, as follows:

  1. If AutoFilter isn't implemented, enable it for your dataset.
  2. The blanks are in the Year column, so click that drop-down arrow.
  3. Uncheck (Select All). In Excel 2003, select (Blanks), and skip to step 6.
  4. Check (Blanks).
  5. Click OK.
  6. In the resulting filtered set, select the blanks. In this case, that's B5:B7 (Figure F). Remember, one of the rows is hidden by the filter.
    Figure F
    Figure F
  7. Type 2014, but don't press Enter yet.
  8. Press [Ctrl]+[Enter] to enter the current value, 2014, in each selected cell (Figure G).
    Figure G
    Figure G
  9. Clear the filter by clicking Clear in the Sort & Filter group (Figure H).
    Figure H
    Figure H
  10. If you don't need the filter, click Filter in the Sort & Filter group to remove it.

You might have noticed that the SUBTOTAL() function updated a few times. This function excludes blanks when evaluating. That's why it returned five when B5 and B7 were empty and zero after filtering for blanks.

Ungroup dates

By default, Excel's list of filtering items will group dates, as you can see in Figure I. (To illustrate this behavior, I changed the year values to full dates.) By default, the items are collapsed. Click the plus sign [+] to expand the list; click the minus sign [-] to collapse a list. (This behavior doesn't apply to Excel 2003.)

Figure I

Figure I

Excel groups dates when filtering.

Most users probably think this a convenient shortcut. However, if you find yourself clicking through layers of date components, you can upgroup date filtering values as follows:

  1. Click the File tab, and choose Options.
  2. In the left pane, choose Advanced.
  3. In the Display options for this workbook section, uncheck the Group dates in the AutoFilter menu option (Figure J).
    Figure J
    Figure J
  4. Click OK.

Disabling this option forces Excel to display literal dates, as shown in Figure K.

Figure K

Figure K

Disabling this option forces Excel to display literal dates.

Filter by the selected value

Access lets you filter by the selected value; the feature is called Filter By Selection. Excel can do that too, but you'll have to add the option to the Quick Access Toolbar (QAT) to access it. Once you do, you can bypass the AutoFilter drop-downs and instead select a cell that contains the value you want to filter by and then click the Filter By Selection option on the QAT.

To add the Filter By Selection option to the QAT do the following:

  1. Click the QAT drop-down and choose More Commands.
  2. In the resulting window, select Commands Not in the Ribbon from the Choose commands from drop-down.
  3. In the resulting list, select AutoFilter (the one with the equals sign in the icon).
  4. Click Add (Figure L).
    Figure L
    Figure L
  5. Click OK.

In Excel 2003, choose Customize from the Tools menu. Click the Commands tab, and select Data from the Categories list. Choose the AutoFilter item and drag it to the toolbar.

To put the new option to use, select any value in the dataset and click the Filter By Selection icon in the QAT. Figure M shows the results of selecting 1/3/2015 in B5 and then clicking the option (circled in Figure M).

Figure M

Figure M

Use Filter By Selection for even quicker filtering.

Send me your question about Office

I answer readers' questions when I can, but there's no guarantee. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise, nor do I ask for a fee from readers. You can contact me at susansalesharkins@gmail.com.

Also see

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox