# 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

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

The normal way to change the color of selected text is through the use of the Font Color tool. If you have to use the ...

Discover More

Moving Through a Table in a Macro

Do you need to step through a table, cell by cell, in a macro? It's easy to do using the Move method, as described in ...

Discover More

Trimming Spaces from Strings

When processing text with a macro, you often need to remove extraneous spaces from the text. VBA provides three handy ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

##### More ExcelTips (ribbon)

Preparing Data for Import into Access

When importing Excel information into Access, you need to be concerned with the condition of the data. Here's how to make ...

Discover More

Displaying a Hidden First Row

If you hide the first rows of a worksheet, you may have a hard time getting those rows visible again. Here's a simple way ...

Discover More

Pasting Excel Data within Word's Page Margins

The programs in the Microsoft Office suite are designed to work with each other easily. Sometimes there can be hiccups ...

Discover More
##### Subscribe

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

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 five minus 5?

2023-04-10 10:10:29

J. Woolley

@Tom Williams

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.