Use ROWS() and COLUMNS() formulas to generate numbers in a sequence [quick tip]

Posted on August 17th, 2009 in Learn Excel - 16 comments

Here is a quick excel formula tip to start your week.

Use ROWS() and COLUMNS() formulas next time you need sequential numbers.

What does ROWS() excel formula do?

ROWS excel formula takes a range as an argument and tells you how many rows are there in that range. For. eg. ROWS(A1:A10) gives 10.

How can you use ROWS() formula to generate sequential numbers?

Simple. Assuming you want to have the sequential numbers in range B1:B10, in B1 write =ROWS($B$1:B1) and copy down the formula in the range in B1:B10. You will now have sequential numbers in that range.

But this is lame. I could just enter the numbers myself.

You are right. Using the ROWS () to just generate sequential numbers is lame.

But in most scenarios, we need sequential numbers to do something else (like passing them to an INDEX or OFFSET formula). Often we use helper column with the sequential numbers to do this. But by using ROWS() formula, you can remove the need for helper column and easily scale your formulas.

See this example:

Actual question on PHD forums: Fill down a formula with increment

 Hi, I need help on filling a formula down with a constant increment. I would like the first cell to be ‘=+B1′ the next to be ‘=+B4′ the next to be ‘=+B7′ etc… so that the increment is 3. How can this be accomplished?

Actual answer using ROWS()

in Column C, write: =+offset($b$1,rows($C$1:C1)*3,0)) and copy down

There are lots of interesting uses for ROWS() formula.

Similarly, you can use COLUMNS() formula when your data is across columns.

PS: I just crossed my personal record for hard disk crashes in a week. Now my work laptop is on the bed too.

PPS: There are more than 100 posts on the PHD Forums already. Lots of interesting questions and answers to day to day excel problems.

PPPS: Posting will be thin this week. I have composed the next installment of project management and spreadcheats series during the weekend. But the posts are in the work laptop. So wait (and pray)

PPPPS: Have a fun week ahead. :)

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

16 Responses to “Use ROWS() and COLUMNS() formulas to generate numbers in a sequence [quick tip]”

  1. Jon says:

    COLUMNS() can also be helpful within a VLOOKUP to dynamically define what you call the “return_what” term in your “plain english” explanation: =VLOOKUP(what, where, return_what, [my_list_is_sorted])

    Simply replace the hard-coded “return_what” number with COLUMN($C1:$F1), where $C1 is in the “what” column and “$F1″ is in the “return_what” column. That way, you can add and delete columns on your table without breaking the VLOOKUP formula!

  2. jeff weir says:

    Good thing about the ROWS arguement is that it’s a good substitute for just the simple ROW arguement, because as you’ve structured it above with absolute_reference:relative_reference format i.e. =ROWS($B$1:B1) then it isnt going to be stuffed up if you insert another row higher up in the spreadsheet.

  3. [...] can simplify the +1, +2..+11 part by using COLUMNS formula to generate the sequential numbers for [...]

  4. Paul Grenier says:

    Chandoo,
    Happy New Year to you and your beautiful family.
    It was only a few months ago I needed to use VLOOKUP in an amortization schedule. With the help from one of your emails on VLOOKUP I was able to get through it.
    I can’t find the lesson that was specific on understanding and using VLOOKUP.
    Is there something you could email me on VLOOKUP. I think your email on this subject was around March or 2010.
    Continued success in 2011.
    Paul

  5. J MACKEY III says:

    =(ROW(C4)*3)-2

    Does not require a helper column. The equation can be improved by adding a cell reference.

    =(ROW(C4)*$A$1)-($A$1-1)

  6. [...] The ROWS($A$1:A1) portion generates continuous numbers from 1 thru 100 and thus we get next 100 13ths. For more on this technique, read – Using ROWS() to generate a series of numbers [...]

  7. KAUSHAL says:

    Data validation in cell

    i have entry 5digit Alpha,then4 digit number, then1 digit Alpha

  8. [...] of typing all the 28/29 numbers, we can use ROW formula to generate these, [...]

  9. Keith Jones says:

    I am trying to add a column of number sequentially in excel 2010, my problem is that the rows are filtered, so instead of the serial number on the extreme left running numerically it will jump numbers.
    See below 383,384,406,426 if I now try to add a sequential number and drag it down by using the right handle bar the first number is just repeated and doesn’t increase.

    How do I get around this issue?

    Thanks

  10. Eileen Cook says:

    Hello. I have a database with company names in one field but the client would like each company to have the same sequence number.
    For example, if we have 10 rows with the same company name, they would like all those to have the same sequence number of 1, and so on.
    Need a formula so i’m not having to go through 50,000 records and group manually.

Leave a Reply