Number the Visible Rows in Excel AutoFilter

Number the Visible Rows in Excel AutoFilter

When you create a list in Excel, do you start with a column that numbers the rows? I usually create an ID column and type the number, or use a function to automatically number them. If you filter the list, here is a way to number the visible rows in Excel AutoFilter, so they’re in consecutive order.

Count of Visible Records

When you use an Excel AutoFilter to filter a list, usually the count of visible records appears in the Status Bar, at the bottom left of the Excel window.

Note: If the Status Bar shows Filter Mode, instead of the record count, you can use one of the workarounds shown here – Status Bar shows Filter Mode.

AutoFilter Rows 02

Record Numbers

That’s helpful if you’re looking at the results on the screen, but not much help if you print the filtered list and give it to someone else. To make things easier for them, you can add record numbers that will print for each row.

For example, in the screenshot below, the visible rows are 2, 4, 5 and 6, and the numbers in column B are 1, 2, 3 and 4.

AutoFilter Rows 03

Add Record Numbers to a Filtered List

In column B of our list, if we just type the numbers, or use a simple formula, such as =B2+1 the numbers won’t change if the list is filtered. In the screenshot below, the second record is hidden by the filter, and the record count shows as 1, 3, 4, 5.

AutoFilter Rows 04

Use SUBTOTAL Function

Instead of using the simple formula shown above, if you want to show the record numbers for the visible records, you can use the SUBTOTAL function. It is designed to ignore rows that are hidden by a filter, so its result will change if rows in our list are hidden.

WARNING: Don’t use this technique if you plan to use Excel’s Subtotal feature (Data>Subtotals) — it may delete your table when you remove the Subtotals.

In our list, there will always be a date in column C, so we can use the SUBTOTAL function to count the visible dates.

=SUBTOTAL(2, C$1:C2)

We’re counting dates, so the first argument, 2, tells Excel to count the numbers in the range. If you want to count text entries instead, use 3 (COUNTA) as the first argument.

AutoFilter Rows 05

The second argument, C$1:C2 is the range of cells that we want to count.

Copy the Formula Down

After you create the formula to number the visible rows in Excel AutoFilter, copy it down to the last row with data.

  • The first cell, C$1, has an absolute row reference, so when we copy the formula down, that part of the formula won’t change. We always want the count to start in row 1.
  • The second cell, C2, has a relative row reference, so when we copy the formula down, that part of the formula WILL change. We want the count to end at the row the formula is in.

Now, when you apply an Excel AutoFilter, or show all the records, your record numbers in column B will change.

Problems Hiding the Last Filtered Row

The SUBTOTAL function works well, and renumbers our rows as expected, but there’s something wrong.

In the screenshot below, the list is filtered for the products Paper or Staplers, as you can see in the AutoFilter tooltip. Can you spot the problem?

AutoFilter Rows 06

Although the list is filtered for Paper or Staplers, the File Folders record in row 9 is also visible. Also, its row number isn’t blue, like the row numbers for rows 2, 3, 5 and 8.

Hidden Named Range

When you use an AutoFilter, Excel creates a hidden named range for the database. Using Jan Karel Pieterse’s Name Manager utility, I can see the definition for Orders!_FilterDatabase.

Even though the list ends in row 9, the named range stops at row 8:  =Orders!$B$1:$H$8

AutoFilter Rows 07

If there’s a SUBTOTAL function in the last row of that database, Excel decides that it’s a special row for the list’s totals, and it’s not included in the named range.

So, if you want your last row hidden when using the SUBTOTAL function in a filtered list, you can use one of the following workarounds.

Workaround 1: Add a Dummy Row

Usually, I add a dummy row at the end of the list, with just the SUBTOTAL function, or other dummy data. Then, that row is treated as the last row, and all the real data is shown or hidden, based on the AutoFilter criteria.

n the screenshot below, the SUBTOTAL function is copied down to row 10, and when the AutoFilter is applied, row 9 is hidden. There’s no date in row 10, so it doesn’t affect the record numbering.

AutoFilter Rows 08

Workaround 2: Change the SUBTOTAL formula

Today I found another workaround, that’s much better than the dummy row solution. Dick Kusleika was subtotalling filtered rows, and discovered that he could fix the problem by typing two minus signs in front of the SUBTOTAL function. (I left a space between the minus signs below, just for clarity. The formula will work with or without the space.)

=- -SUBTOTAL(2, C$1:C2)

AutoFilter Rows 09

With Dick’s solution, there’s no need for an extra row, so the worksheet looks better, and you’ll avoid other potential problems, such as filtering for blanks in one of the fields.

AutoFilter Rows 10
________________

28 thoughts on “Number the Visible Rows in Excel AutoFilter”

  1. You can also use =Subtotal(2,$C$1:C2)*1 or +0 or – 0 or /1 – Anything along with the Subtotal.
    Excel then gets confused and includes the last row inside the filter range

  2. I just want to see the number of the rows (how many of it…)EXCLUDING the hidden ones (with my AUTO FILTER)! it should have been the simplest thing in excel but currently just a TORTURE!it somehow always takes ” the hidden rows” into consideration! No simple solutions in any of the forums! =ROWS(xx:yy) is not working properly

  3. This is a great tip.

    Another way around the issue noted is to not include the column with the subtotals in the filter range.

  4. Great, kind of. I can use the subtotal() approach to find which row I’m on when using filters but now, how do I find the number of the previous or next “visible” row? What I’m looking for is a way to automatically update a difference between rows that are subject to filtering, e.g. a number of days between events.

  5. The argument “2” for Subtotal will ignore rows that are filtered out, but if you hide the rows (vs filtering them out) subtotal will include them. Use argument “102” to ignore hidden rows in a range.

  6. i keep a spread sheet of all the fire extinguishers my company has. I would like to do a count of how many i have inspected this year so far, for quarterly reports. I have filter on location sub location date, etc. What could i use to get the total of FE that i inspected. When i filter the ones i did this year, i want to set up an row that will continually update with the number of FE that i inspect.

  7. I need to number rows in an xls where rows are hidden. I have added a column to the far left where I want the numbering to occur. I tried =(first cell) + 1 and auto fill from there. It worked for the first few rows but then started back at 1 again and I noticed that the auto fill forula for each row is indeed adding 1 to each cell before it. Is there another way?

  8. Thanks for this explanation about sub totals. I had been tearing my hair out when I realised it was happening to my main spreadsheet. I am actually using =0+SUBTOTAL(9,$J$5:J46), i.e. adding 0 to the subtotal as I find it easier to see than the double negative.
    EDIT: Sadly it breaks again as soon as you automatically add a row by pressing tab in the final cell. The next row gets an ordinary sub total, and the filter problem starts all over again. It also mucks up the formatting of teh table so that the rows are not banded. The only way out of it is to remove the sub total from within the table to outside.

  9. I have a spreadsheet that contains 29,000 line. Excel 2010 will only allow me to view 10,000 – how do I increase this?

  10. using subtotal function
    =SUBTOTAL(3,$B2:B$2)
    1.CLICK ON THE CELL FOR SERIAL NUMBER
    2. PUT ‘+’ OR ‘=’ SIGN
    3. SELECT SUBTOTAL FUNCTION
    4.SELECT THIRD FUNCTION – COUNTA ( ORDER NO-3)
    5. PUT ‘,’
    6.SELECT RIGHT SIDE ONE CELL
    7. SHIFT + ;
    8. PUT $ SIGN AFTER 3, AND IN BETWEEN B&2

  11. Hello everybody,

    This worked for me:

    =SOUS.TOTAL(103;$A$6:A6)

    I wrote this formula in cell B6 because my filtered table visible data starts at row 6.

    Then, I dragged down cell B6 until the last cell of column B.

    Now, column B contains a list of sequential numbers (1 to 1136) besides column A of filtered data (6 to 2355).

    I used the fonction SOUS.TOTAL because my Excel 2016 is in French.

    I used the semicolon ; instead of the comma , because the comma didn’t work.

    I hope it helps someone.

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.