MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1476: number the visible rows.
Hey, welcome back to the MrExcel netcast.
I’m Bill Jelen.
This-- I love this one.
This was actually a challenge sent out by Bob Ulmas.
Bob is an Excel MVP, author of that Excel Outside the Box book.
Bob was looking for a way to number rows, but it had to ignore the hidden rows.
So, we're not just looking for 1, 2, and so on because when we hide a row, what we're
looking for is that to jump from 1 to 2 to 3.
I said, okay, here's what we're going to do.
We're going to use equal-- brand new in Excel 2010, AGGREGATE.
This is a beautiful thing.
We're going to count, count, that'll count numeric and check this: ignore hidden rows.
Isn't that a great setting?
Ignore hidden rows and the range that we're going to use is B dollar sign 1 to B1 and
we’ll say plus one because we want to add however many the previous ones there are.
Copy that down.
All right, so initially, we get the 1 2 3 4 5 6 7, but check this out.
When we hide rows-- format, row, hide, it will renumber.
Format, row, hide.
All right, so, it keeps updating and it counts only the visible rows above us.
Beautiful little trick there using Excel 2010.
Well, hey, I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.