If you’re working with large sets of data in Excel, then it’s a good idea to add a serial number, row number or ID column to the data. Show
A serial number is a unique identifier for a row or record of data and they will usually start at 1 and increase incrementally with each row. This way you can refer to each record in your data set by the serial number. In this post, I’ll show you 15 interesting ways which you can add row numbers to your data. Use the Row HeadingsGood news! Excel comes with serial numbers out of the box. On the very left hand side of every sheet is the row heading with row numbers in an incrementally increasing sequence. You can use these as serial numbers for your data. Pros
Cons
Use the Fill HandleThis one is an amazing trick! Once you learn it, there is no going back to the old way of manually typing each number of a sequence. The fill handle will automatically create a sequence of serial numbers for you with just a click and drag. You first need to enter two sequential numbers. Notice the active cell has a square in the lower right? This is the Fill Handle and you can use it to automatically fill in the rest of the sequence which you started to add manually. Select the first two cells and then hover your mouse cursor over the lower right corner until it turns into a small black plus sign. Then click and drag down to the end of your data. If you’re working with a large data set, then click and drag might not be feasible. In this situation, you can double click on the fill handle to fill the sequence down to the end. When you release the fill handle this will fill in the sequence of numbers and a small fill handle options menu will appear at the bottom right of your filled sequence. There is one handy option in this menu that allows you to avoid filling down any cell formatting. Click on the menu options and choose Fill Without Formatting and any copied cell formatting will be removed. Pros
Cons
Use the Fill Series CommandThis option is very similar to the fill handle method, but doesn’t require and long click and drag actions down the length of your data. Add the first two serial numbers to your data set. Then select the entire range of cells which you would like to fill including the two serial numbers you have already entered. To select a longer range, it’s easier to start at the bottom and select up to the top. If you place the active cell cursor in the next column which contains data, you can use Ctrl + Down to get to the last cell. Move the cursor back over to the ID column, then Use Ctrl + Shift + Up to select all the blank ID cells. Then use Shift + Up to select any previously entered serial number cells. Go to the Home tab of the ribbon and click on the Fill command then choose Series. This will open the Series menu.
This will fill in the remaining series in your selected blank cells. Pros
Cons
Add One to the Previous NumberThis is the intuitive method to adding a series of numbers to your data. Excel’s is designed to calculate, so adding 1 to the previous serial number is no problem.
Enter the value 1 into the first row of your data. Then in the next row enter the above formula. In this example B3 is the cell directly above. Now you can copy and paste this formula down the remaining rows. To do this quickly, double click on the fill handle of the cell which contains the formula. Pros
Cons
Use a Relative Reference Named RangeThis method is going to use the same idea where you add 1 to the previous serial number, but it is going use a clever trick to make it more robust. What you need to do is create a named range that will always refer to the cell above it. Then you can reference this named range instead of a direct cell address like B3. Go to the Formulas tab and click on Define Name. This will open the New Name menu. Give your named range a name like Above, this is how you will reference it inside formulas later.
Add the above formula in the Refers to section of the New Name menu then press the OK button. This formula uses the INDIRECT function with row and column notation. R[-1] indicates the reference is one row above the current cell. Now when you use the Above name inside a formula it will reference the cell directly above the cell which you are entering the formula.
Enter the above formula into the first row and then copy and paste it to the end of your data. Using the SUM function is a small improvement to the previous method as there is no need to enter a value in the first row. The formula can be applied consistently for each row. The SUM function will count text values as zero, so you can safely reference the column headings when the formula is in the first row without producing a #VALUE! error. Pros
Cons
Use the ROW FunctionThere is an Excel function that can return the current row number and it’s perfect for creating serial numbers.
Add the above formula, where B2 refers to the column heading cell, into the first row and copy and paste it down. The ROW function returns the row number of the current row when no argument is passed to it. In order to start the serial numbers at 1, we then need to subtract off the row number of the column heading cell. This is accomplished using an absolute reference to the cell with the column heading. Pros
Cons
Use the COUNTA FunctionThis is another formula option that will rely on counting previous rows of data. In order to use this formula, you will need a column that will never contain blank values as the COUNTA function does not count empty cells.
Enter the above formula into the first row and then copy and paste it to the end of your data. In this formula C3 is a cell in the first row but another column which will not contain any blank values. Notice the range reference in the formula contains a partial absolute reference with the $ symbol. This causes the formula to include all records above the current record in its count as it gets copied down the data set. Pros
Cons
Use the SUBTOTAL FunctionThe SUBTOTAL function is interesting because it can return values based on what cells are visible. It can also be used to perform other aggregations like a count and not just a sum. This means you can use it to create serial numbers that change based on filtered data.
Add the above formula into the first row then copy and paste it down the data. This formula works exactly like the COUNTA method, because it is the same. The first argument in the SUBTOTAL function tells Excel to use a count type aggregation. The only difference is when you filter your data, the serial numbers will update based on what is still visible. Pros
Cons
Use the SEQUENCE FunctionThe SEQUENCE function is a new dynamic array function. This means one formula can produce an array of values. It can do a lot more, but this function can also generate a single column of increasing numbers that start at 1. Perfect for serial numbers!
Add the above formula into the first row of data. In this example C3:C8 is the entire range of a column. This will determine how many serial numbers the SEQUENCE function will return.
Another approach is to hard code the row count inside your SEQUENCE function like the above formula. Unfortunately, this method would mean you need to manually adjust the count each time you add or remove rows of data. Pros
Cons
Use Power PivotThis one is a bit weird since it will create serial numbers inside a pivot table. But it may be just what you’re lookin for. You can use power pivot with a calculated column to number your pivot table rows. Select your table of data and go to the Power Pivot tab and click on Add to Data Model.
This will open the power pivot add-in and you’ll be able to add the above formula into the table. In this example, the table has been named Data and we are ranking the Email column which contains unique text values. This results in column that can be used as a serial number. To use this calculated column inside a pivot table.
Pros
Cons
Use VBA CodeVBA has been in Excel and other Office applications for a long time. It was the go to method for automating any task in Excel. So you can certainly use it to automatically generate your serial numbers. These days, there are usually much better options available and I would only recommend using a VBA solution as a last resort.
Add the above code into the visual basic editor.
The above code will add an increasing sequence of numbers starting at 1 into any selected range. Now you can run the code.
This will fill your selected range with a sequence of numbers starting at 1. Pros
Cons
Use an Index Column in Power QueryPower Query is an amazing data transformation tool found in Excel and other Microsoft products. If you’re already using it to import your data from an external source into Excel, then it might be the perfect solution to adding serial number to your data. If your data is already in Excel, you can still use power query to add serial numbers but you will need to add your data into an Excel table first. I wrote a detailed article about Excel Tables, where you can learn all about them including how to convert your data into a table. Add your data into query by using a From Sheet query. Select a cell inside your table ➜ go to the Data tab ➜ choose From Sheet. This will open up the power query editor and you will be able to add a column with serial numbers from here. Go to the Add Column tab and click on the Index Column command. Click on the small arrow icon of the Index Column button to choose to start the index From 1. Now you can go to the Home tab ➜ Close & Load ➜ Close & Load To ➜ Choose to load the results into an Excel table and pick the location to load it to. Your data will be loaded into another table with an extra index column. If you add or remove data from the source, you will need to refresh the query output to see the updated results. You can refresh the query by right clicking on the table and choosing Refresh from the menu options. Pros
Cons
Use the Connection Properties for Connected TablesIf your data has been loaded into a table from an external source like power query or an exported SharePoint list, then you can enable row numbers from the connection properties menu. Select your connected table and go to the Data tab and click on the Properties command. This will open the External Data Properties menu and you can enable the option to Include row numbers and press the OK button. Now you need to refresh the connection in order to see the row numbers. Go to the Data tab and click on the Refresh button. After refreshing the connection, you will see a new column in the table called _RowNum which starts the count at 0. Pros
Cons
Use Office ScriptsOffice Scripts is a new TypeScript based scripting language which is currently only available in Excel online for Enterprise Microsoft 365 plans. This is the replacement for VBA and you can certainly use it to auto generate a set of row numbers. You will need a few things in order to use this method first.
Open your file in Excel online then go to the Automate tab and click on New Script.
Paste in the above code into the Code Editor. You can rename the script and press Save Script to save the script. Press the Run button in the Code Editor to run the script. This script relies on your data being inside an Excel Table named Data with a column named ID. Pros
Cons
Use Power AutomateNow that you have an Office Script to add serial numbers to Excel, you can use Power Automate to automate the running of this script. You can use power automate to run this script on a schedule, so you can automatically update your serial numbers every day or even every hour.
Now add a Run script step to the flow and select the file and script to run then Save the flow. This will now automatically run the script to add serial number at your desired frequency. Pros
Cons
ConclusionThere are a ton of options when it comes to adding serial numbers to your data in Excel. Each method will have various pros and cons that might make them a better option for your use case. It’s worth exploring them all to see which will work best for you. Whatever your skill set there is an option for you that will get you your desired results. Did I miss your favourite way to add row numbers in this post? Let me know in the comments if you have another method you use! About the AuthorJohn is a Microsoft MVP and qualified actuary with over 15 years of experience. He has worked in a variety of industries, including insurance, ad tech, and most recently Power Platform consulting. He is a keen problem solver and has a passion for using technology to make businesses more efficient. When you change your name Excel automatically updates the blank in any formulas that reference it?Excel includes a special group of functions to modify text. When you change a name, Excel automatically updates the name in any formula that references that name. To move or copy more than one non-consecutive worksheet, press _______ on the keyboard and click the worksheets you want to copy or move.
What formatting is automatically updated if you change data in the worksheet?You can use formatting to highlight important aspects of the data itself. Conditional formatting automatically applies different formats depending on conditions you specify. The formatting is updated if you change any data in the worksheet.
Are the names you created and names Excel creates automatically?Once you've created a named range, it will appear automatically in formulas when you type the first letter of the name. Press the tab key to enter the name when you have a match and want Excel to enter the name.
Does Excel automatically adjust cell references to reflect new formula locations?c. Excel automatically adjusts cell references to reflect new formula locations.
|