Numbering Filtered Rows

Written by Allen Wyatt (last updated April 10, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


8

Greg knows he can use filtering to display just a subset of rows in a worksheet. He would like, in an unused column, to sequentially number the rows that are visible after filtering. He can use AutoFill to number unfiltered rows, but he wants to number just the visible, filtered rows.

The simple, traditional way to do this is to add a formula to the unused column before you apply your filter. For example, let's say that the values on which you are going to filter are in column A, beginning in cell A3, and that your unused column is F. Then, in cell F3 you should enter the following formula:

=SUBTOTAL(103,$A$3:A3)

Copy this formula down as many cells as necessary. The formula should show a count for each item visible in column A. When you apply your filter, the formulas are updated to display a count for each filtered row.

As simple as this is, it does have a drawback: If you remove the filter, then the formula is updated to number (again) the visible rows. If, however, you had wanted the numbers to remain on the cells corresponding to the rows that had been previously filtered, then the SUBTOTAL approach won't work properly.

In that case, a different approach is required. In this example, let's assume that your unused column is G. And, after you apply the filter, your first visible row is 4. So, you would enter the following into cell G4:

=MAX($G$1:G3) + 1

Note that the range provided for the MAX function starts with the first cell in the column (make sure you include the dollar signs) and extends through the row above the cell in which you are entering the formula. Copy this formula down through all the remaining visible cells in the column. You now have a sequential count of all of the visible rows. This count remains even if you later remove the filter.

The only time this approach will be messed up is if you remove the filter, change the values in column A, and reapply or change the filter. This is because the approach presupposes that column G is empty before you add the formulas.

Perhaps the most robust approach is to use a macro to do the count. The following will add the count to column G based on which rows are visible at the time the macro is run:

Sub NumberVisibleRows()
    Dim J As Long
    Dim r As Range

    J = 0
    For Each r In Selection.Rows
        Cells(r.Row, 7) = ""
        If Not r.Hidden Then
            J = J + 1
            Cells(r.Row, 7) = J
        End If
    Next r
End Sub

To use the macro, apply your filter, select the rows you want evaluated, and then run it. You can modify which column is numbered by changing the 7 in Cells(r.Row, 7)—there are two instances—to reflect the column number you want.

If you would rather not have to select cells before running the macro, then you could use the following variation:

Sub NumberVisibleRows2()
    Dim J As Long
    Dim r As Range
    Dim iStart As Long

    iStart = 2

    J = 0
    For Each r In ActiveSheet.UsedRange.Rows
        Cells(r.Row, 7) = ""
        If Not r.Hidden And r.Row >= iStart Then
            J = J + 1
            Cells(r.Row, 7) = J
        End If
    Next r
End Sub

To make this work properly, just set the iStart variable equal to the row number you want the macro to pay attention to. Thus, in the above example, the first row will be ignored because iStart is equal to 2. This allows the macro to ignore any header rows you may have in your data.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8460) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Determining the Current Page Number

While your macro is processing the text in your document, you may need a way to determine the current page number where ...

Discover More

Switching between Program Windows

It is not unusual for you to work with several (and sometimes many) programs at the same time. Each program is displayed ...

Discover More

Importing Huge Data Files

Sometimes, when importing data created by other programs, you may find that there is too much for Excel to handle. Here's ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Moving and Copying Cells

At the very heart of editing is the ability to move and copy cells in a worksheet. Understanding the differences between ...

Discover More

Deleting Duplicate Columns

Have a worksheet in which there may be entire columns that are duplicates of each other? If you want to delete those ...

Discover More

Defeating Date Parsing when Pasting Information

Paste information directly into a worksheet, and you may be surprised that Excel makes some of the data unusable. This ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is four less than 6?

2023-04-10 10:10:29

J. Woolley

@Tom Williams
Did you read any of the previous comments? Sometimes they are worth reading.


2023-04-09 17:03:33

Tom Williams

I don't understand "=MAX($G$1:G3)". The MAX of the cells above the first visible one is zero and nothing changes values in that column. You must have meant to use that formula in column G in combination with previous column F: " =MAX($f$1:f3)"? Why not just put 1 in the top row, 'top row + 1' in the second row and copy that down, apply the filter, and then paste special / values into whatever (previously blank) column you want to keep the static index numbers?


2023-04-09 13:50:40

J. Woolley

@Allen
Yes, that's it. Very clever.
Now in NumberVisibleRows2, I believe you have mixed columns 8 and 7. Apply one or the other, not both.


2023-04-09 10:30:31

Allen

Ahhh! I think I made an error in the formula. I think it may need to be this:

=MAX($G$1:G3)+1

Note the "+1". Copy the formula down.

Of course, I'm not near my Excel system, so I cannot test this -- just doing it off the top of my head.

-Allen


2023-04-09 10:15:44

J. Woolley

@Allen
I must be doing something wrong when I try to follow the Tip's instructions (see Figure 1 below)

Figure 1. 


2023-04-08 10:42:48

Allen

James, the approach using the MAX function is an alternative to using the SUBTOTAL function. And, yes, the formula (as I point out) goes into the column where you want the sequential count -- everything happens in column G in this example AFTER applying the filter.

-Allen


2023-04-08 10:21:51

J. Woolley

Here is something interesting about SUBTOTAL(Function_Num,...):
Function_Num 1-11 includes manually-hidden rows and 101-111 excludes them, but filtered-out cells are ALWAYS excluded.


2023-04-08 10:14:47

J. Woolley

@Allen
I don't understand. Did you mean that the MAX function in column G applies when the SUBTOTAL function is used in column F? If so, the MAX function in column G should be =MAX(F$1:F3), not =MAX($G$1:G3).
Also, in NumberVisibleRows2 you have mixed columns 8 and 7.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.