In a range with filter, I want to have a column of row numbers which will change according to the filter, so that rows always count consecutively from 1. For example, the following is a range with filter:

Number   Name    Gender
1        Alice   F
2        Jason   M
3        Ka      F
4        Fiona   F
5        Albert  M

Now suppose we apply a filter to show rows where Gender is M:

Number   Name    Gender
2        Jason   M
5        Albert  M

The above is what Excel normally shows, but I want the Number to count from 1 consecutively like this:

Number   Name    Gender
1        Jason   M
2        Albert  M

So, the rows should be renumbered according to the filter. I have tried something like =MAX(...)+1 or using the SUBTOTAL() function, but I haven't succeeded yet. Is it possible to write a formula for the Number column to do this task? How?


Use this formula:


into cell A2.  (This assumes that you have a column heading in cell A1.)

Parameters of AGGREGATE():

  • Function_num = 3, Count all non-blank cells in a range
  • Options = 5, ignore hidden rows in the range
  • Array = $A$1:A1, range from first row to the row above selected cell

Sample output:

enter image description here

As mentioned by benshepherd, SUBTOTAL() can be used too.

  • 1
    I never heard of AGGREGATE before. Definitely going to use that now :) – benshepherd Oct 14 '15 at 10:39

@Máté Juhász just got there ahead of me. I was going for an alternative approach using SUBTOTAL. In A2, put =SUBTOTAL(103,B$2:B2) and fill down.

The SUBTOTAL function performs a numbered function on its arguments - these functions are described in the Help. 103 corresponds to COUNTA ignoring hidden rows. (You'd use a value of 3 to include hidden rows.) COUNTA counts the number of non-blank cells in a range. We use the B$2:B2 syntax to keep the top cell the same, and expand the range as we go down the list.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.