Reader Matthew S. wins a copy of Slaying Excel Dragons for posting this tip: “When creating a row index it is better to use the formula =row(). Then when a row is inserted, one does not have to recopy the formula A1+1, for example, for the entire list. The formula has to only be added to the inserted row.”
In the example in Figure 1 (below), you want the item in row 6 to have a number of 1 and all the remaining items to be numbered sequentially from there. Entering =ROW() in A6 would return a number of 6. Adapting the trick to allow for the titles and headings, use a formula of =ROW()-5 and copy this formula down to number the list.
Fig. 1
If you insert an item before row 10, all of the subsequent items will be renumbered as shown in Figure 2.
Fig. 2
You would drag the formula in A8 to the newly blank cell A9, and the numbers will continue (see Figure 3). You can also safely delete an item without having all of the remaining formulas change into the dreaded =1+#REF! errors.
Alternate Way to Number Rows When You Have Blanks
In the following example, you have blank rows between each section. A formula counts the nonblank cells in column C to add a row number to each item. The trick in this formula is a single dollar sign before the top row number in the reference. Figure 4 shows a formula of =COUNTA(C$8:C17).
Fig. 4
When you entered the original formula in row 8, it would still have a single dollar sign, but it would contain the slightly harder-to-understand formula of =COUNTA(C$8:C8). In this case, the C$8 locks the start of the list to row 8, but the C8 allows the bottom of the list to move down to the current row.
Bill Jelen is the author of 33 books about Microsoft Excel. Win a book if your question or suggested tip is chosen as the topic of a future column. To send an e-mail, click on Bill’s byline at the top of this article. Bill next Webcast, “Macros for Budgeting and Corporate Accounting,” is scheduled for Tuesday, August 16, at 2:00 pm EST. For more information on the Webcast, click here.