excel numbering in afiltered data

Occasional Contributor

I have a set of data in excel sheet. I have filtered the data as I want. How can I number serialy these filtered data.

15 Replies
Hi Jamal

Are you able to sort the data before filtering. That would make the numbering easier?


Hi Jamal, 


This approach allows the sequential number to show dynamically.


Item Number in Filtered List.gif

Without filtering how can i do this
Sorry I don’t understand what you mean
ie , I want to put the number 1,2,3....after filtering.
As far as I’m aware it’s not possible to drag down to fill a filtered list or auto populate it in sequence while filtered.
It could probably be done with some VBA code.

does it can be done through MAX function. please download the attachment and view. I want to do like this

That only works with SUBTOTAL or AGGREGATE as Wyn suggested - apply formula before filtering and numbers will be recalculated upon each filtering

yes i got it..=MAX($j$1:j3)+1, where j is the column name. and 3 is the nmber just before the data begins

But that doesn't work with filtered rows

Filter.then select the column where the number has to be put ie visible cells. Then apply the formula. Then press cntrl+enterkey.then it works

And if you apply another filter?

No. It should not work in same column. Another filter should work in another column only
Does it works. Is it possible
I don't think so sorry