Excel - How to Number the Visible Rows in Excel - Episode 1476
Microsoft Excel Tutorial: How to Number the Visible Rows in Excel
In this episode, number 1476, we will be discussing how to number visible rows in Excel. This was actually a challenge sent in by Bob Ulmas, an Excel MVP and author of the book "Excel Outside the Box". Bob was looking for a way to number rows while ignoring hidden rows, and I have to say, I love this solution.
So, how do we number only the visible rows? Well, we will be using a new function introduced in Excel 2010 - AGGREGATE. This function allows us to count numeric values while also ignoring hidden rows. We will use the COUNT function and set the option to ignore hidden rows. The range we will be counting is B1 to B1, and we will add one to account for any previous rows. Simply copy this formula down and you will have a perfectly numbered list that updates when you hide or unhide rows.
This trick is not only useful for numbering rows, but it can also be applied to other situations where you need to count only visible data. And the best part is, it works seamlessly with Excel 2010. So, thank you Bob for the challenge and thank you for tuning in to this episode of the MrExcel podcast. Don't forget to subscribe and hit the notification bell to stay updated on our latest episodes. See you next time for more Excel tips and tricks.
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...
Table of Contents:
(00:00) Numbering Visible Rows
(00:16) Bob's Request: Numbering Rows while Ignoring Hidden Rows
(00:26) Solution: Using AGGREGATE Function in Excel 2010
(00:36) Step-by-Step Demonstration
(01:09) Updating and Renumbering with Hidden Rows
(01:19) Clicking Like really helps the algorithm
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...
This video answers these common search terms:
number visible rows excel
excel count filtered rows
assign row numbers to visible cells
number only visible rows in excel
excel formula for visible row count
auto number filtered rows excel
count visible rows after filter excel
number rows based on filter in excel
excel visible row numbering
auto increment visible rows in excel
A Challenge from none other than Bob Umlas - author of "Excel Outside The Box: Unbelievable Excel Techniques". Bob wants to Number Rows, but ignore the 'Hidden' Rows. In Episode #1476 Bill looks to a New Function for Excel 2010: " =AGGREGATE" Learn about it with the 'Learn Excel from MrExcel' Video Podcast.
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!