AutoFilter

Data OrganizationAnalytics7 min readMicrosoft 365 for Windows

The problem

A commercial analyst receives an order log of 200 rows: date, product, region, customer type, units, price and net sales. The question is simple: what did one product sell? Filtering to that product takes two clicks. The trap comes next. The total at the bottom of the sheet still shows the sales of every product, and the analyst, trusting the number on screen, reports it. A filter hides rows; it does not change a formula that sums them.

AutoFilter is the most used data tool in Excel and the one most often misread. This article covers the filter types, the way to total only what is visible, and the habits that keep a filtered list honest.

Short theory

An AutoFilter does not delete or move data. It sets some rows to hidden according to a rule and leaves the others visible. Each column has its own filter, and the filters combine with AND: a row stays visible only if it passes every active filter. Within one column, the choices combine with OR when you tick several values.

Two properties follow. A filter is a view, not a result: the data underneath is unchanged, and clearing the filter brings every row back. And since the rows are only hidden, any function that reads the whole range still reads them, unless the function is built to ignore hidden rows.

Steps in Excel

Turning the filter on

Click a cell inside the list and press Ctrl+Shift+L, or choose Data, then Filter. A drop-down arrow appears on each header. Excel finds the extent of the list from the surrounding data, so a blank row or column in the middle splits the list in two and only the part around the active cell is filtered. If your list contains empty rows, remove them first or select the full range before turning the filter on.

In an Excel table the filter arrows are already present, and the filter automatically extends when rows are added. For a plain range, new rows at the bottom are normally picked up when the filter is reapplied, but rows added after a gap are not.

Filtering by value and by text

Open the arrow and untick Select All, then tick the values to keep. For long lists, type in the search box: the list narrows as you type and ticking Select All Search Results keeps everything that matches. Text columns also offer Text Filters, with the conditions Equals, Begins With, Ends With and Contains. Wildcards work here: ? stands for one character and * for any number, so a product code pattern such as NEU* keeps every code that begins with those letters.

Filtering numbers and dates

A numeric column offers Number Filters: Greater Than, Between, Above Average and others. A date column offers Date Filters, which understand time as a calendar: Last Quarter, Next Month, Year to Date, or a period such as All Dates in the Period followed by a quarter or month. If a date column shows a hierarchy of years and months in the drop-down, it is because Excel groups dates automatically. This can be switched off in File, Options, Advanced, with the option Group dates in the AutoFilter menu, for lists where you prefer to see individual dates.

Filtering by color

When cells carry a fill color or font color, either manually or from conditional formatting, Filter by Color in the arrow menu offers the colors found in the column. Pick one and only those rows remain. A column can be filtered by one color at a time.

The top and bottom values

On a numeric column, Number Filters, then Top 10 opens a small dialog that is misleadingly named: it does not have to be 10. You choose Top or Bottom, a number, and whether the number is Items or Percent. Setting the net sales column to the top 10 items returns the ten largest rows. Setting 5 percent returns the rows that fall in the top 5 percent of the count of rows, in the sense of the number of items, not of the value. The companion file works this out on the Top N Check sheet, with N in B1 and the percentage in B2.

Two cautions. With Items, a tie at the cut-off can make the result show more rows than N, because all rows with the same value are kept. And the Top 10 filter works on the values of the column after the other filters have been applied, so the order in which you apply filters matters: top 10 of Velorin differs from the Velorin rows among the top 10 of everything.

Only Velorin is ticked; the funnel on the header and the blue row numbers say a filter is on.

Reading the status bar

Excel tells you how much of the list is visible. With a filter active, the left of the status bar reads something like 23 of 200 records found. The row numbers on the left turn blue. These are the two signals that a filter is on; a list passed along with a filter active and no note is a classic source of misunderstandings.

Totals that respect the filter

This is the part most people learn the hard way. Open the Orders sheet, which opens with Product = Velorin applied: 23 of the 200 rows are visible. The summary in I:J puts the two approaches side by side.

SUM over the net sales column returns 3,192,611.18, the total of all 200 rows, hidden ones included. The function SUBTOTAL with the function number 109 returns 299,838.52, the total of the 23 visible rows. The same is true for averages: the average of all rows is 15,963.06, while the average of the visible rows is 13,036.46.

The formula takes this shape:

=SUBTOTAL(109,G2:G201)

The first argument selects the calculation and whether hidden rows count. The numbers from 1 to 11 include rows hidden by hand; the numbers from 101 to 111 ignore them. In both families, rows hidden by a filter are always ignored. So 109 means sum, 101 means average, 102 count, 103 count non-blank, and so on. When in doubt, use the 100 series: it covers filter and manual hiding.

For counting visible rows, =SUBTOTAL(103,A2:A201) is the formula behind the "23 of 200" message. And =AGGREGATE(9,5,G2:G201) does the same with its own numbering (9 is sum) and an option, here 5, that ignores hidden rows; AGGREGATE can also skip errors, which SUBTOTAL cannot.

With Velorin filtered, SUM still adds all 200 rows; SUBTOTAL 109 adds only the 23 you can see.

Copying filtered data

A copy of a filtered range takes only the visible rows when pasted into a new place, which is useful, but with one hole: if some rows were hidden manually, they are included in the copy. For a reliable result, select the range, press Alt+; to select visible cells only, then copy. When pasting back into a filtered list, remember that Excel pastes into all rows, hidden or not, which can overwrite data you cannot see. The safe habit is to paste into a clean sheet.

Clearing and reapplying

Clear on the Data tab removes every filter condition at once and leaves the arrows. Pressing Ctrl+Shift+L removes the arrows too. Reapply runs the same conditions again, which matters after data changes: a filter does not update itself when a value is edited. If you change a product code in a visible row, the row stays visible until you reapply.

Common mistakes

Reporting a SUM over a filtered range. Use SUBTOTAL 109 for the visible rows, and label the number with the filter that produced it.
Filtering a list with a blank row in the middle. The part below the blank row is silently left out.
Leaving a filter active on a shared file with no note, so the next reader sees only a slice of the data.
Reading Top 10 as a percentile of values. It counts items (or the share of the count), not the share of the value.
Expecting a filter to update after editing data. Use Reapply.
Making structural changes on a filtered list. Clear the filter before inserting or deleting rows, so you see what you are touching.