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. 🙂
26 Responses to “Use ROWS() and COLUMNS() formulas to generate numbers in a sequence [quick tip]”
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!
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.
[...] can simplify the +1, +2..+11 part by using COLUMNS formula to generate the sequential numbers for [...]
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
=(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)
[...] 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 [...]
Data validation in cell
i have entry 5digit Alpha,then4 digit number, then1 digit Alpha
=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
=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
[...] of typing all the 28/29 numbers, we can use ROW formula to generate these, [...]
[...] Learn more about: using ROWS / COLUMNS formula to generate running numbers. [...]
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
@Keith
In the column you want to have a running 1, 2, 3...
I'll assume Column C
In C2: add a Function =SUBTOTAL(103,$B$2:B2)
Copy down to the end of your data
Hi Thanks, I still cant get the function to work
column is G
and the row starts at 66,67,69,then jumps to 324 jumps again to 328 etc.
I entered the formula in to G66 and it only shows the formula in the cell and in the cells I drag it into.
Thanks
@Keith
Can you post your file?
Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
or email to me
Click Hui... above and look at bottom of page
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.
@Eileen Cook
Sort your data by the Company Name (Column A)
Then add a new Column Sequence Number (Column B)
I'll assume the first company is in Row 2
In B2 put 1
In B3 =IF(A3A2,B2+1,B2)
Copy down
you rock!! I was missing the darn number ONE in B3 lol
Thank YOU so much.
xoxoox
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.
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.
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?
@Ron
On sheet1 in A1: =Offset(Sheet1!$A$1,,Columns($A$1:A1))
Drag down
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
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
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?
Using ms excel for seating timetable in an examination where you have five different courses in a session