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


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?
 
Ad

Advertisements

P

Pete_UK

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
 
B

bhanu reakha

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top