Excel 2024: Number the Visible Rows in a Filtered Data Set

You've applied a filter to a data set and would like to number the visible rows. Two functions that can ignore rows hidden by a filter are SUBTOTAL and AGGREGATE. The key to this solution is to use the COUNTA version of SUBTOTAL for every cell from the Heading row to the current row. This number will always be one too high due to the heading, so subtract 1.

In this data set, notice how row 38 gets a row number of 1 because it is the first row visible from the filter.

This is a great improvement introduced near the end of 2020 for Microsoft 365 subscribers. In the past, you could hide multiple worksheets at once, but you had to unhide them one at a time. With the change, you right-click any sheet tab and choose Unhide.

Excel now lets you multi-select which worksheets to unhide. The first time you use the feature, a guide appears telling you how to multi-select.

The new Action Pen on the Draw tab of the Ribbon lets you hand-write data in a cell. After a moment, Excel converts the drawing to text or numbers as if you had typed the characters.

---

---