Let’s say you have a filtered list and in each of the filtered cells you want to enter a sequential number, but in the hidden rows you don’t want to enter anything. There is a way, but it takes a few steps.

In the image below I have filtered the list and want to have sequential numbers in column D. This is a short example but it applies to large data sets

You can’t use the fill handle because it will enter data in the hidden rows as well as the visible rows. Also the fill handle won’t create a vertical sequential list in a filtered list even if you use the Ctrl key.

You can use a formula and then use Paste Special Values to capture the entries. This assumes there is a heading in the column you are adding the sequential number to.

Steps

1. In the first filtered cell enter the following formula

   =COUNTA($D$1:D4)

   The D4 in the formula needs to be the cell above the first filtered cell in the column and it needs to be a relative reference (no $ signs).

2. Copy that cell.
3. Select the remaining cells in the filtered list and press Ctrl + V.When you copy a single cell and paste in a filtered list you are only pasting in the visible cells.
4. Unfilter the list and copy the column and use Paste Special Values on the same column. This captures the values and removes the formulas.
5. Job done.

Formatted Table

If you have used a formatted table there is an extra step. A formatted table will copy the formula to all the cells (see image below) – not what you want.

After step 1 you will need to use Undo to reverse the formula being copied to all the cells in the column. Ctrl + z performs Undo.

Continue with Step 2 onwards.