Tip, tricks, and updates for Windows & MS Office
Excel Row Numbering Help
Last week we talked about Excel’s Row function. As always, you all shared some tips that were just too good not to share! So here goes. Phil Williams writes:
To make the automatic row number a bit more robust, title the cell above the area that contains the numbered rows
Name this cell, say "START_LIST"
Now use formula =ROW()-ROW(START_LIST)
Now if cells are deleted above the start of the list it will automatically detect the change without having to alter a cell that contains the manipulation variable.
Very sharp, Phil! Mark Yount’s idea is similarly wise:
In reference to your Excel ROW() function newsletter, another way to ensure that the rows remain numbered beginning at one, use this formula in each cell, where cell A8 is the #1 row:
=ROW() - ROW($A$8) + 1
You must be sure to make the cell reference absolute using the $. I'm not sure why, but if you add or delete rows above the first row, this absolute reference is adjusted correctly to keep the reference to the first cell so the first row is always numbered 1.
It saves having to enter beginning row number into a different cell elsewhere on the worksheet.
Thanks Mark. Rod Laughridge made a great point, too. Rod writes:
One caveat: if you start at a row other than Row 1 and subsequently delete rows above the numbered ones, your numbering will be skewed. However, you can remedy this by entering the digit of the row number into a different cell elsewhere on the worksheet and use the cell number as a reference in your formula. For instance, if you are starting in Row 27, enter the number 26 into cell K130, your formula would be =ROW()-K130.
In this example, you have to make K130 absolute, otherwise when you copy it down, it won't refer to this cell.
Nice job, Rod. Finally, I just had to share Joanne Winfield’s brief yet terrific tip:
You can also number columns using the =column() function. Very cool!
Very cool, indeed! Thanks Joanne, and thanks to ALL of you who took the time to email me with your numbering tips. I’m sorry I don’t have room for them all here, but I sure do appreciate you!
See you online!
Tricia Goss, Editor
tricia@officeusers.org
Your Office Answers are Here!
Comment Notification
Subscribe to this post's comments using