fbpx
Search
Close this search box.

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

Share

Facebook
Twitter
LinkedIn

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. 🙂

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

26 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

    • Vijay Kumar Bhardwaj says:

      =IF(LEN(B6)=10,IF(AND(SUMPRODUCT(--(CODE(MID(B6,{1,2,3,4,5,10},1))>=65),--(CODE(MID(B6,{1,2,3,4,5,10},1))=48),--(CODE(MID(B6,{6,7,8,9},1))<=57))=4),"OK","NOT OK"),"NOT OK")

      Use this formula to validate the authentication

      Vijay Kumar Bhardwaj
      Whatsapp :- +919463006767

    • Vijay Kumar Bhardwaj says:

      =AND(LEN(SUBSTITUTE(A1," ",""))=10,ISTEXT(LEFT(SUBSTITUTE(A1," ",""),5)),ISNUMBER(N(MID(SUBSTITUTE(A1," ",""),6,4))),ISTEXT(RIGHT(SUBSTITUTE(A1," ",""),1)))

      There is another Formula to validate

      Whatsapp : +919463006767

  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.

  11. john carr says:

    I've updated to Excel 2013. it automatically adds a row of "column #" after the column label and before the first case. I cannot delete this row. How can I remove it?
    Then I copied just cases data to a new sheet and dang Excel again added a row with cell formulas yielding an error since it refers to sheet 1 dang row of "column#". I can't delete those cell values permanently nor delete the row.

  12. Elijah says:

    I have an excel 2013 spreadsheet 1st row has 2 last names and first names, 2nd row has email. I want to split that information to have the first names in the first row and the last names in the second row. Please help.

  13. ron says:

    Hello, I just want to drag a formula down that will pull the values from a row. I've got the month/year in one row on a second sheet (column a, b ,c d, etc.), and on the first sheet I want to enter a formula that will pull each value to the cells in a row (a1, a2, a3, a4, ect.) any thoughts?

  14. Jim says:

    SUM(R[-7]C[-2]*R[-7]C)+SUM(R[-6]C[-2]*R[-6]C)+SUM(R[-4]C[-2]*R[-4]C)

    Please explain this formula to me.

    Thanks

  15. Craig says:

    hi pretty simple query here I am thinking ?

    If I have two columns the left one an increasing number of the rows containing data to the right of it and the right column some data entered.

    How do I get the numbered column to automatically increase its value when data is entered into the right column next to it when it was blank?

    Thanks for your time and any suggestions or tips would be most welcome.

    regards Craig

  16. Rich says:

    Hi All,

    I'm trying to figure out how to reference the second row of data in a table rather than the first, but it's also in a different row. We recently switched to clocking out for lunch and it's throwing off my reports which previously just had 1 clock in and 1 clock out time for each employee.

    I need to reference the first clock in, and second clock out. How can I do so?

  17. Abdullahi says:

    Using ms excel for seating timetable in an examination where you have five different courses in a session

Leave a Reply