I have text in column B. But there are some columns that are empty. There is no rule the empty raws are at random. This is what I have now:


I want to have correlative numbers in column A. The problem is to detect the empty raws. That should be the result:


  • Do you want this as text is added? What if text is added in the middle, do you need the sequence of numbers to recalculate? If you could add a little more info about your process or how the data is added that would be helpful in finding a solution. In general, conditional formulas are your friend here. Cheers!
    – bjbk
    Aug 3, 2017 at 16:57
  • @bjbk: it is ok if the numbers are created after the text column. The other cases would be a plus. Can you put an example of the most basic case, please?
    – Nrc
    Aug 4, 2017 at 15:42
  • I would love to help, but if you could edit your question with more information. Such as, is the text simply just added to random rows? Is the empty row a result of inserting rows or deleting text? As asked, it really difficult to advise the best approach. It makes a difference in how the spreadsheet is managed. You may want to learn more about using functions and formulas. Cheers!
    – bjbk
    Aug 4, 2017 at 20:33
  • 1
    I'm just curious - do you need to keep the blank rows? For example, in your second image Column A has been populated based on the content in Column B, but all the rows that are blank in Column B (e.g. rows 3, 6, 7 etc) are still there. Is this by design (e.g. some of the other columns have data in these rows) or can you delete these rows altogether?
    – Monomeeth
    Aug 5, 2017 at 9:50
  • 1
    All good! I always get concerned when people have totally blank rows in a spreadsheet list, but obviously that's not the case here. :)
    – Monomeeth
    Aug 5, 2017 at 23:09

1 Answer 1

  1. Enter a 1 in the cell A1.
  2. Insert the following formula into cell A2.


    This formula checks the cell to the right to see if it is blank. If it is blank, the cell in the A column should be blank too. If not, the cell should be filled with the next integer, which equates to the maximum integer reached so far + 1.

  3. Fill the rest of the A column using this formula. Use the yellow fill handle so that the formula is adjusted correctly as it fills. The $ sign fixes the first cell of the range used by the MAX function.

  • It works very well. Clear and simple
    – Nrc
    Aug 5, 2017 at 15:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .