Learn Excel 2010 - "Number The Visible Row": Podcast #1476

Sharing buttons:

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.