Amy from the St. Louis IIA asks: Is there a way to number the visible rows in a filtered list? This video shows how a formula of =SUBTOTAL(3,B$1:B2)-1 in cell A2 solves the problem.
Transcript of the video:
Today's question, is there a way in Excel to number a filtered list?
This question from Amy in my St. Louis IIA webinar.
How can I number the visible rows in a filtered list?
All right.
So we have a list here and we've applied a filter based on customer.
And Amy wants to number these one, two, three, four, five, but if you change it to something else, then have those numbers change as well.
All right.
So let's clear the filter for right now, insert a new column over here with the numbers.
And the key to this whole thing is going to be the SUBTOTAL function.
The subtotal and aggregate functions both have options to count only the visible cells.
And so I'm going to count A because that'll include text.
So at number three for that, I'm going to start in B$1, the single dollar sign there.
Make sure that we start at one and have that go to B2.
All right.
Now this is going to return the number two because it's going to count both the word region and the word central.
And so the number's going to be off by one. So I'll subtract one like this.
So there's our formula.
The subtotal of three, because we're counting A, in other words, counting text.
B$1:B2, only a single dollar sign there to lock it at the top, but it's an expanding range.
So as we copy this down, it'll go to B3, B4, B5 and so on, and then subtracting the one just that we don't count the heading as number one.
When I double click to copy this down, you'll see that it automatically changes to two, three, four, five, six but then let's come here to Southwest Airlines and click the auto filter.
And now row 12 is counted as two. Row 23 is counted as three.
Row 66 is counted as four and so on all the way down.
I'm just kind of curious to see what's really in row three, four, five, six and seven.
And a great way to do that is just to come here to another sheet and we'll say =data!A1 and we'll copy this down a little bit.
And so you can see the first Southwest gets a one and then all the way down here at the next Southwest, it changed this to two.
All of those other hidden rows are numbered with a one.
It doesn't matter because we're just trying to number the rows that we can see, but that kind of gives you an idea of what's happening behind the scenes.
So here it's counting all of the visible rows from region to central and the only one that would be visible is Southwest and region.
And we're subtracting one to get the number one. Cool little trick.
Thanks to Amy for sending that question in. Thanks to the St.
Louis IIA for hosting the webinar, and thanks to you for stopping by.
We'll see you next time for another NetCast from MrExcel.
This question from Amy in my St. Louis IIA webinar.
How can I number the visible rows in a filtered list?
All right.
So we have a list here and we've applied a filter based on customer.
And Amy wants to number these one, two, three, four, five, but if you change it to something else, then have those numbers change as well.
All right.
So let's clear the filter for right now, insert a new column over here with the numbers.
And the key to this whole thing is going to be the SUBTOTAL function.
The subtotal and aggregate functions both have options to count only the visible cells.
And so I'm going to count A because that'll include text.
So at number three for that, I'm going to start in B$1, the single dollar sign there.
Make sure that we start at one and have that go to B2.
All right.
Now this is going to return the number two because it's going to count both the word region and the word central.
And so the number's going to be off by one. So I'll subtract one like this.
So there's our formula.
The subtotal of three, because we're counting A, in other words, counting text.
B$1:B2, only a single dollar sign there to lock it at the top, but it's an expanding range.
So as we copy this down, it'll go to B3, B4, B5 and so on, and then subtracting the one just that we don't count the heading as number one.
When I double click to copy this down, you'll see that it automatically changes to two, three, four, five, six but then let's come here to Southwest Airlines and click the auto filter.
And now row 12 is counted as two. Row 23 is counted as three.
Row 66 is counted as four and so on all the way down.
I'm just kind of curious to see what's really in row three, four, five, six and seven.
And a great way to do that is just to come here to another sheet and we'll say =data!A1 and we'll copy this down a little bit.
And so you can see the first Southwest gets a one and then all the way down here at the next Southwest, it changed this to two.
All of those other hidden rows are numbered with a one.
It doesn't matter because we're just trying to number the rows that we can see, but that kind of gives you an idea of what's happening behind the scenes.
So here it's counting all of the visible rows from region to central and the only one that would be visible is Southwest and region.
And we're subtracting one to get the number one. Cool little trick.
Thanks to Amy for sending that question in. Thanks to the St.
Louis IIA for hosting the webinar, and thanks to you for stopping by.
We'll see you next time for another NetCast from MrExcel.
