Tip, tricks, and updates for Windows & MS Office
Excel's Row Function
A reader named Melissa asks:
I have a question. I want to number rows. I highlight 1 and 2 and drag so I can get to 100 or so. [However] if a row gets deleted, the numbers are off. How can I prevent [this]?
Good question, Melissa. I am sure many of us have experienced this problem. You enter the numbers 1 and 2 into two cells of a column and drag it down, down, down to fill up all the numbers you need. You enter other data and formulas. Later you decide you no longer need the info in Row 42 so you select it, right click and delete. Only problem is, now your list jumps from 41 to 43. You could delete start from scratch, or you could use the Row function.
Here is how to fill a column with succeeding numbers using the Row function. If the row you are starting with is indeed Row 1, the process is super easy. Simply select cell the first cell and enter the formula =ROW(). Then all you need to do is drag the fill handle down as many rows as you like, and they will all be filled with the appropriate number. Once that’s done, try deleting one of rows. You will see that, no matter how many you delete, the numbers will still be in order.
If you are beginning your column on a row other than Row 1, you will need to do just a little more work. You will need to subtract one from the row number you are starting in, so that it comes back with the number “1” in the first row. Therefore, if you were beginning in Row 27, you would enter the formula =ROW()-26 into the cell in Row 27. Pull the fill handle down to copy your formula and you are good to go.
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.
Comment Notification
Subscribe to this post's comments using