Excel: Automatically Number the Visible Rows

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.


Problem: What if you don't delete the past employees, but you hide the rows? The newer AGGREGATE function can ignore hidden rows.

In the figure below, the first argument of 3 tells Excel to use the COUNTA function. The second argument of 5 tells Excel to ignore hidden rows.

  1. AGGREGATE can ignore errors, other subtotals, or hidden rows.