Excel Row Numbering: Number The Visible Rows - Episode 2328

Microsoft Excel Tutorial: Number the visible rows.

Welcome to the MrExcel Netcast, where we bring you the best tips and tricks for mastering Excel. In today's episode, we will be discussing how to number only the visible rows in your data set. This is a common issue faced by many Excel users, and we have the perfect solution for you.

Our question for today comes from Jennifer in Daytona, who wants to filter her data to show only her best-selling product, tomatoes. She then wants to number just the visible rows and leave the rest with a dash. However, the usual method of using the fill handle does not work when there is a filter applied. So, what's the solution?

After some brainstorming, we came up with a simple yet effective solution. We will count the numbers above the visible rows and add one to each row. This will give us a sequential numbering for the visible rows, and we can easily fill the rest with dashes. To do this, we use the COUNTIF function and an expanding range. This trick will save you time and effort, and you can use it for any filtered data set.

But what if you want to number all the rows, including the hidden ones? We have a solution for that too. By using the COUNTIF function and an expanding range, we can get a mix of numbers for all the rows. And the best part is, when you filter the data, the numbers will adjust accordingly. This is a handy trick for organizing your data and making it easier to work with.

If you enjoyed this tip, make sure to hit the subscribe button and ring the bell icon to stay updated with our latest videos. Also, don't forget to check out my new book, MrExcel 2020: Seeing Excel Clearly, for more useful tips and tricks. And if you have any questions or comments, feel free to leave them down below in the comments section. Thank you for watching, and I'll see you in the next netcast from MrExcel.

Jennifer from Daytona wants to Filter a data set to only show Tomato and then number the visible rows. But you can't do this with the Fill Handle. In today's episode, a couple of formulas to solve the problem.

This video answers these common search terms:
how to count only visible cells in excel
how to count pnlyu visible cells in excell
how to count only visible rows in excel
how to count visible rows in excel
how to only count visible cells in excel
how to number only visible cells in excel
how to number pnlyu visible cells in excell
how to number only visible rows in excel
how to number visible rows in excel
how to only number visible cells in excel
how to count a filtered column in excel
how to count only filtered cells in excel
how to count in excel when filtering
how to count rows in excel when filtered
how to count in filtered excel table
how to count filter in excel
how to count with filter in excel
how to do count rows that are filtered in excel
can excell count rows with filters
how to put a count filter in excell

#excel
#microsoft
#exceltutorial
#microsoftexcel
#excelformula
#excelformulasandfunctions

Table of Contents
(0:00) Problem Statement: Number the Visible Rows
(0:32) Filter by Selection & Fill Handle Does not work
(0:45) COUNT all numbers above plus one
(1:21) Choose Go To Special Blanks
(1:40) Enter a dash with apostrophe minus minus
(1:58) Method 2: COUNTIF all rows above
(2:23) When you filter, the matches are numbered
(2:39) Clicking Like really helps the algorithm

[Rapture]

Not sure if it exactly what was wanted but what about:

=SUBTOTAL(103, $E$2:E2)

and copy down?

[omerkatzavian]

Thanks for the amazing video
It is possible to do this in another way :
=AGGREGATE(3, 5, $C$2:C2)

[vujohn]

the second part of the video saved me so much time thank you!

[calicops]

You know, about 20 years ago you generously sent me a Teacher Excel book you wrote. I just wanted to thank you. I contacted you then, and you were so helpful. I am very happy to hear you are still going, and I remain a loyal fan of yours. Thank you.

[mysterythecat]

would E2=If($C2="Tomato", countif($C$1:C2, "Tomato"), "--") work to solve question, and keep to the requested parameters? then fill down.

[johnborg]

Thanks Mr Excel. That was very Helpful. :)

[anthonyverdin]

Can you explain how/why using the COUNTIF function and an expanding range, you can get a mix of numbers for all the rows? I am so confused as to why this works.

[gkhudyan]

Hello. I am desperate and I need to be able to number about 700 visible rows and be able to remove a number from a cell, hide that specific cell and the numbers will auto-adjust to exclude the hidden cell. I thought this might help and tried your formula with no luck. Please help. I put in =COUNT(A$2:A4)+1 The first two rows are headers, the third row is hidden and the numbering needs to start at A4. When I use that formula all I get is 0. Please please please help.

[wjkruger]

Make this man president. Saved me a lot of time.

[AjayAnandXLnCAD]

Awesome Tricks. Especially the second one 👌

[just-becool]

I am using the data/sort to get sub totals. Excel puts the1, 2, 3 columns to the far left. Clicking on column 2, highlighting and “select visible cells” I color only the sub totals. I then have to sequentially number each subtotal row. How

[josepedromunizvargas]

Wonderful the second option for numbering visible rows. Thank you very much for your techings.

[wayneedmondson]

Hi Mr. Excel.. great tip.. thanks! If the objective is to have a dynamic sequential count of the filtered / visible records, you can do it with SUBTOTAL, as in: =SUBTOTAL(2, B$2:B2) and copy that down to the bottom of your list. The filtered list will always start with 1 and increment by 1 for whatever is visible on the face of the worksheet. This may not be exactly what Jennifer was after.. but.. it just got me thinking.. so sharing it here. Thanks again for the tips and tricks.. always something new to learn at MrExcel.com. Thumbs up!!

[amjidali]

Very Wel done!!!


Can you help


I have some data in Excel and the same data is Hyperlink with .doc files.
Sir here is my Question.
is it possible that when the mouse pointer over the data in specified cell or cells range (which is not hyperlinked) a comment or window pop up with a msg " NOT HYPERLINK ".
I shall be very thankful to you.

[just-becool]

This just isn’t working. I tried your other video too, where you use the aggregate instead of count, but your next-video-pop ups cover what you’re doing at the end so I can’t complete it. And I can’t tell what you’re saying Ctl what? V, G, D, B? I can’t hear, or see what you’re doing.On this video, I have to sort by color and it’s not working. I am using the data>subtotal feature, but then I have to number the lines that only contain the sub totals. It takes me hours to do it manually on my large data. This same manual way has been done for decades by many people. There HAS to be a way to do this. I would be the HERO! Help me be a HERO and save the business HOURS of payroll every month.Please make me the HERO

[stephanweaver]

Sum above and add subtotal(103, [any field in current row context]). Make dashes via number format.

[wmfexcel]

We can use the following formula for a sequential list for visible rows only.
=SUBTOTAL(103, $A$2:A2)
Details discussed here:


Hi MrExel, did you change your microphone? You sound a bit different in this video. :)

[rabdu]

Dear Sir, please always give us chance to download your excel tutorial. not all of us are expert in excel. Also please we need to keep number for any kind of products. thanks

[fernandodecastro]

Thank you so much for this video tutorial. it helps me a lot. i applied it in my work..

[mysterythecat]

I prefer the second solution. Since in theory, data is always updating, keeping calculated data flexible, is a priority.