How to put series of number in excel when the filter is applied?

  • Thread starter Thread starter bhanu reakha
  • Start date Start date
B

bhanu reakha

1. I applied filters in my excel.
2. I tried to put series of number using fill series option
3. Fill series is not working.

Kindly provide me whether there is any option for the filling the series of
numbers after after applying the filters?
 
Suppose your filters are in row 1 with headers, and that you want the
numbers in column Z and that the first visible row after filtering is
row 20. Put this formula in Z20:

=COUNTIF(Z$1:Z19,"<>")

Then you can copy this formula down the rest of the visible cells in
column Z to give you sequential numbers. If you don't have a header in
Z1, you can do it with this:

=COUNTIF(Z$1:Z19,"<>")+1

then copy this down. Note the cell Z19 is the cell immediately above
the first visible cell, so adjust this to suit your data.

When you remove the filters the sequential numbers will still be
there, but only in the cells that were visible.

Hope this helps.

Pete
 
Thanks a ton for the solution.


Pete_UK said:
Suppose your filters are in row 1 with headers, and that you want the
numbers in column Z and that the first visible row after filtering is
row 20. Put this formula in Z20:

=COUNTIF(Z$1:Z19,"<>")

Then you can copy this formula down the rest of the visible cells in
column Z to give you sequential numbers. If you don't have a header in
Z1, you can do it with this:

=COUNTIF(Z$1:Z19,"<>")+1

then copy this down. Note the cell Z19 is the cell immediately above
the first visible cell, so adjust this to suit your data.

When you remove the filters the sequential numbers will still be
there, but only in the cells that were visible.

Hope this helps.

Pete
Click to expand...
 
Back
Top