Ever been in a meeting or a conference call reviewing an Excel workbook? If the Row numbers are not shown on your printout, it can be hard to find exactly what the speaker is referring to.
That’s much easier when the first column is a sequential number. Having each row identifiable by a unique number makes it easier for everyone in a meeting or a conference call to know exactly which row of data is being discussed. Simply refer to the sequential number.
Sequential Numbering
Creating a number sequence is easy using the Fill Handle. Type 1, 2, 3, in the first 3 rows, select all three, then drag the Fill handle down for as many rows as you want. This image shows a table of CapEx projects sorted by the sequence column, starting with 1. That’s our column of sequential numbers.
But there is a potential problem. What if you want to sort the table by another column? That might reorder the sequential number column.
Yes, you could select all columns except the sequential column before sorting. But that’s just extra tedium. Especially if you select the table using a named range.
This image shows the table after sorting by the “Spend Date” column, the sequence in the first column isn’t sequential anymore.
Fix That With ROWS()
Here’s a simple fix. Instead of using entering the numbers manually, use the ROWS() function. Trust me, it’s easy. This same technique works with COLUMNS() function, too.
The ROWS() function returns an integer count of the number of rows in its argument.
The argument is a range which begins with the first row of data in the table. Don’t include the row of column headers. The first cell in the range needs to be anchored. That’s what the dollar sign before the “2” means. Anchored cells don’t increment when dragging the Fill Handle.
Here is the formula in the first row.
ROWS(B$2:B2)
This formula returns 1 because the range argument only includes a single row, row 2. Because the first cell in the range argument is anchored, we can use the Fill Handle to copy the formula to as many rows as we want.
The image shows the resulting ROWS() formulas.
Now when you sort this table, the whole table, the first column always shows a sequential count beginning with 1.
Get my Cheatsheet of Excel Telecom Tricks
I’ve written dozens of articles to simplify use of Excel and automate reports without programming. Click here to download a Cheatsheet summarizing these articles, with a link to read each one.
I do ask for your email address so that we can keep in touch. I dislike SPAM as much as you, and promise I won’t ever send any. You can unsubscribe at any time.
If you’d like to see more of my Telecoms Tools and Career-Building tips, follow me on LinkedIn
or follow me on Twitter