Sequential numbering in Excel

  Graham ® 12:56 20 Feb 2005
Locked

I've used Filter to get a column of data, I now want to count them. I figured the best way would be to add a column at the side and number them 1 to X. I formatted the new column to Numbers and inserted a 1 at the side of the first cell. But when I click on the corner to drag down the column it just repeats the 1 instead of 2,3 etc.

Ive tried holding the ctrl key, but I still get 1s.

  VoG II 13:02 20 Feb 2005

Put a 1 in the first row and a 2 in the second row. Select both cells and copy down.

  Graham ® 13:05 20 Feb 2005

Thanks, thats what I thought. But I just get 1s :-(

  VoG II 13:13 20 Feb 2005

You've discovered an interesting "feature" with filtered lists :o)

Select all of the rows where you want to add numbers.

Use ASAP Utilities, Fill/Quick numbering of cells.

  Graham ® 13:41 20 Feb 2005

Using ASAP I can number the cells, but not correctly. With three rows in the filtered column selected, the numbers are 1,2,5.

With only 3 rows in the column, but 5 rows selected I get 1,2,5 10,11.

If I select more rows, the numbering continues 10,11,12 etc. It seems ASAP can't number correctly when Auto filter is on.

Any other way to count the rows? I'm thinking on the lines of deleting all columns except the one I want to count.

  VoG II 13:43 20 Feb 2005

Use Subtotals click here

  Graham ® 13:49 20 Feb 2005

Thanks, I'll try that. Also, if I paste the filtered column into a blank sheet I can use the row counters.

  Graham ® 13:57 20 Feb 2005

I get 'Formula contains an error'.

  VoG II 14:02 20 Feb 2005

Just tested on a filtered sheet

=SUBTOTAL(3,D4:D15)

with no errors and correct result displayed.

Very odd.

  Graham ® 14:06 20 Feb 2005

In the example, would A11 be the cell to select?

  VoG II 14:09 20 Feb 2005

You should be able to enter the formula anywhere (except within the filtered list).

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

AMD Radeon Adrenalin release date, new features, compatible graphics cards

Print designer Kelly Anna on confident mark making & modern femininity

New iMac Pro release date, UK price & specs rumours

Comment créer, utiliser et supprimer son compte Facebook ?