## Sequential number for visible rows only

How to display a column of sequential number to visible rows only?

Got this question from a friend.  Her task is simple as shown below:

I guess many people wanted to achieve the same thing but have no idea at all.  Are you one of them?

Actually, the solution is simple, simpler than you may have thought about.

In our example, input the following formula in A2 and then copy down:

`=SUBTOTAL(103,\$B\$2:B2) 'pls pay attention to where the \$ are`

As simple as that.  🙂

Normally it is easier to explain the logic of the formula from the beginning cell.  But for this case, I think the opposite may illustrate better.

Firstly let’s take a look at the syntax of SUBTOTAL

SUBTOTAL(function_num,ref1,[ref2],…])

The first argument function_num can be any number from 1 to 11 (includes hidden values) or 101-111 (exclude hidden values) that specifies what function to use in calculating subtotal to the range specified (i.e. ref1 as the second argument).

Then let’s look at our example

function_num 103 is used.  That simply asks Excel to do COUNTA (i.e. count the number of cell that is not empty) to the range \$B\$2:B11 but ignoring hidden rows (either by filter or just by hiding).

Now look at the formula in A11, which is

=SUBTOTAL(103,\$B\$2:B11)

Pay attention to the reference made to \$B\$2 (absolute reference) and B11 (relative reference).  That’s why you will see the range changes to \$B\$2:B11 and \$B\$2:B5 when you are looking into the formula in A11 and A5 respectively.  Literally it means a range “from the the row assigned a to the active row”.

The range \$B\$2:B2 changes dynamically according to the position of the formula.  It always refers to the top row (in our case Row 2) of the table to the active row of formula.

To put everything together, the formula does the simple job to count, from the top row to the active row, how many cell(s) is non-blank, ignoring hidden rows.  Therefore in A11, it gives you the answer of 5 correctly when you filter for “Wholesales”.

Now let’s look at the changes in formula row by row in the filtered range.

See? This is in effect to show a list of sequential number starting from 1 to the last visible row.

Tip: You may refer to any column with non-blank data, e.g. \$C\$2:C2 or \$D\$2:D2 both yield the same result, as long as there is no blank cell in the range.

Note: You may find that Filter refuses to include the last row with the formula start with SUBTOTAL.  For workaround, please read next post: Interesting bug – Filter with SUBTOTAL.

The chance of writing effective formula is higher, when you truly see the difference between relative and absolute references.

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips, Formula and tagged , . Bookmark the permalink.

### 23 Responses to Sequential number for visible rows only

1. Unfortunately this didn’t work for me – kept getting an ERROR returned. Not sure why. Seems like it should be something easy to fix, and I’ve looked at over 10 forum posts and none of them work for me.

Like

• MF says:

What error did you get? Mind posting your formula?

Like

2. Rusty says:

This works great and is much simpler than other, similar methods I’ve seen.

One addition I made, because I was using it to number visible rows, based on a priority (sorted/filtered criteria) or dragged/dropped (I can drag this row to the top to force it to be number one) was to replace the dynamic field (i.e. A2) to an indirect reference (INDIRECT(“A”&ROW()).
Doing this meant that if I moved my row up manually, it would reassess the row and then return the desired ranking.

Like

• MF says:

Glad to hear that you can modify it to fit your situation 🤗
👍🏻

Like

3. Prashant says:

Simply superb!

Like

• MF says:

Thank you Prashant 😉

Like

4. Sushant Kanojiya says:

Hi..
Thanks!!! It worked for me. However, would like to inform that my filter wasn’t working properly after applying the formula.
Is it for everyone? Or I am making some mistake?

Like

5. Rob Saunders says:

That’s really good thanks.

Like

• MF says:

Thanks. Glad to know you like it. 😃

Like

6. Aleksandar Spasovski MKD says:

Works like a charm! 🙂 Thanks

Like

• MF says:

Welcome. Glad you like it.

Like

7. nilesh kumar says:

formula works only when filter is on and then we inactive filter sequence formula not worked

Like

• aishahhabah says:

Does anyone have any method on how to make the sequence number maintain as is when filter is turned off?

Like

8. mukesh says:

thanks

Like

9. rosst ambrauw says:

didnt work dude

Like

• MF says:

In what way?

Like

10. Solaman says:

In above example, it sequence the number only one filtered data, but I need sequence number for several group of filtered data at the same time. Thanks in advance for help.

Like

• MF says:

Hi Solaman,
The formula works for visible rows. Therefore it works even you have more than one field filtered. Try it out.
Hope this helps.

Like

11. Frank says:

Unfortunately, this does not work for tables (list objects). Do you have a solution for tables?

Like

• MF says:

Hi Frank,

Thanks for your question.

You may do it in Tables. What you need to do is to make a minor revision to the formula:
=SUBTOTAL(103,\$B\$2:[@Division])

Note: You need to input \$B\$2: manually.

Cheers,

Like

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