Excel - Numbering Visible Rows in Filtered List (with SUBTOTAL Function) - Episode 2503

Microsoft Excel Tutorial: How to apply consecutive numbers to visible rows in a filtered list?

Welcome to episode 2503 of Excel Tricks! In today's video, we will be answering a question from Amy in my St. Louis IIA webinar. She asked if there is a way to number a filtered list in Excel. Well, the answer is yes! And I'm here to show you how.

First, we have a list of data and we have applied a filter based on customer. Amy wants to number the visible rows in this filtered list, but she also wants the numbers to change if the filter is changed. So, let's clear the filter and insert a new column for the numbers. The key to this trick is the SUBTOTAL function, which has an option to count only the visible cells.

We will use the SUBTOTAL function to count column A, which includes text. Then, we will start at B$1 and have it go to B2. This will return the number 2 because it counts both the word "region" and "central". To fix this, we will subtract 1 from the formula. This will give us the correct number for the visible rows. As we copy this formula down, it will automatically adjust to the expanding range and give us the correct numbers for each visible row.

But what about the hidden rows? Well, we can use a simple trick to see what's really in those rows. We can create another sheet and use the formula =data!A1 to see the data from the first sheet. This will show us that the first Southwest row is numbered as 1, and the next Southwest row is numbered as 2. All the hidden rows are numbered as 1, but it doesn't matter because we are only trying to number the visible rows.

I want to give a big thank you to Amy for sending in this question and to the St. Louis IIA for hosting the webinar. And of course, thank you for watching! I hope this trick will come in handy for your future Excel projects. Don't forget to like and subscribe for more Excel tips and tricks. See you next time for another NetCast from MrExcel.

Table of Contents:
(0:00) Problem Statement: Number a filtered list in Excel
(0:28) Insert column for numbers using SUBTOTAL function and expanding range
(1:36) Apply a Filter and test numbers
(1:55) "See" what is in the hidden rows
(2:45) 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 #excelfilter #exceldatafiltering #excelformula #excelformulasandfunctions #microsoft365

Amy from the St. Louis IIA asks: Is there a way to number the visible rows in a filtered list? This video shows how a formula of =SUBTOTAL(3,B$1:B2)-1 in cell A2 solves the problem.

This video answers these common search terms:
how to insert numbers automatic in excel
how to insert consecutive numbers excel
how to insert ascending numbers in excel
how to insert numbers into excel
how to insert row numbers in excel
how to insert row numbers on excel
how to insert serial numbers in excel
how to insert series of numbers in excel
how to add sequence of numbers in excel
how to sequence numbers in excel+excel
how to sequence numbers in excel
how to do sequence numbers in excel
how do you sequence numbers in excel
how to auto sequence numbers in excel
how to create sequence number in excel
how to do number sequence in excel

[excelisfun]

Beautiful, Mr Excel!!!! But why not SUBTOTAL(3, B$2:B2) ?

[DimEarly]

Great tip, I use this all the time!

But you may have skipped an important health warning: keep the column with the SUBTOTAL function out of the filter range! (I saw you did that, so I’m guessing you know this quirk already…)

I think the auto filter is designed to always include the last row if there’s a SUBTOTAL in it (or anywhere in the table? I haven’t tested the exact logic), so if that column is in it will never hide the last row, even when it should.

[FidanMammadzada-gt]

Very Useful for auditors
! Thanks a lot!

[JaagrookBhartiye]

=IF(C2="", "", SUBTOTAL(3, $C$2:C2))
this formula doesn't work online on MS Teams, as "serial number", it goes to 0000 and then and then and so on...
is it possible to shed some light how to solve this issue with online excel spreadsheet, please?
All I need is a column with serial numbers on my online (MS Teams) excel spreadsheet, which works even after "filtering" the data.

[timvancaelenberge]

But how to number them AFTER the filter is applied? I only get errors.

[TDTSGCENG]

I have been searching for this solution for years. I've been cheating by just using Google Sheets to do it, as it can do a fill series automagically even on filtered data. But Excel is not so easy. Thanks

[tofankumarbehera]

Another issue
how to unmask masked data in a column

[TonyDiem]

What button did you click, I'm going through the 'all' list and none of them do the filter right off the cell for me? What am I missing?

[KARALISinc]

it does not work, says there's a problem with this formula.

[JoseAntonioMorato]

Dear Bill,
If the filtered range is a table column, it is easier to number the visible rows.
If it's not a table column, just select the range that will be the same.
=SCAN(0, Table1[Region], LAMBDA(x, y, x+SUBTOTAL(3, y))) 🤗

[ricos]

You know that some manager somewhere has asked for this! I'd probably always turn off the actual row numbers for this worksheet too, just to avoid confusion.

[skwg]

Why do I got the count of the last row same as the second-to-last row? e.g. in a 36 row table, both row 36 and row 35 would have the same count as 35

[IMA]

You can get rid of the extra (-1) by just making the first range B$2:B2

[Bradrackas]

Does this trick also work with filters in excel tables? If not, is there an option that does work?

[BradleyDunlap]

This is a genius solution. Great job Bill.