Autofill numbers in columns that contain empty cells


New Member
Jan 29, 2009

I have data in column A, C, E, G, and in lines 1-20, 25-36, 38-52.
How can I insert consecutive numbers on the left side of each cell containing data, since I can not use autofill as I have empty cells in some rows as well as empty cells in some columns?
Question 2: Can you use autofill starting at A1 and continuing for example in B7 or C15?
Question 2: Can you use autofill in a column, skipping the numbering when there is an empty cell?
Question 3: Am I making ANY sense AT ALL???;)

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Andrew Poulsom

MrExcel MVP
Jul 21, 2002
Put 1 in row 1 of the column you want to use (let's say H). Then in row 2 put something like this:


and copy down.


New Member
Jan 29, 2009
tHANK YOU, just did that and it is not skipping the empty cells. its just autofills the numbers as if i dragged the +symbol. I'll try again in case i did not copy right

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...