How to number rows after data has been filtered?

M

Maria

I want to know continously give a serial number skipping in between some rows
that are filtered.

In excel if we try Fill series, it is not working on filtered rows.

Is there any way out? Please help.
 
D

Dave Peterson

I think I'd sort the data to group those visible cells. Then fill those empty
cells and resort the data to its original order.

I'd have a column that was essentially a row number column.
show all the rows first.
Insert a new column A.
put 1 in A2 and 2 in A3
select a2:a3 and drag down your data.

Filter your data to see the blanks you want to fill.
select that range of visible cells and type in XXXXXX, but hit ctrl-enter to
fill all those selected cells.

Now sort by that column.
filter to show just the XXXXXX's.
All the XXXXXX's will be grouped together and you can fill those cells with what
you want.

Then show all the rows and sort by that column A to put things in their original
order.
 
D

Debra Dalgleish

If you want to know the record number, in relation to the visible
records, you could use the following formula, and copy it down to the
end of the list:

=SUBTOTAL(3,$D$2:D2)

where the first row of data in the unfiltered table is row 2, and there
is an entry in every row in column D.

Note: Don't use this technique if you plan to use Excel's Subtotal
feature (Data>Subtotals) -- it may delete your table when you remove the
Subtotals.
 
D

Dave Peterson

Just to add to Debra's reply. If you filter by a different criteria (or show
all), your numbers will change since it is a formula.
 
Top