Automatically Update Colum Everytime a New Article Is Uploaded

15 Clever Ways to Add together Serial Numbers to Your Excel Data

If y'all're working with large sets of information in Excel, then it's a adept idea to add a serial number, row number or ID column to the data.

A serial number is a unique identifier for a row or tape of data and they will unremarkably start at 1 and increment incrementally with each row.

This way y'all 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 Headings

Good news! Excel comes with serial numbers out of the box.

On the very left hand side of every canvas is the row heading with row numbers in an incrementally increasing sequence.

You can apply these every bit serial numbers for your data.

Pros

• You don't need to do anything to create them as they are there by default.
• These numbers will adjust when you delete or insert a row.
• Works within Excel Tables.

Cons

• You tin't change them.
• You need to position your information starting in jail cell A1.
• If your data contains column headings, so the row number will start at row 2 for the commencement record.

Use the Make full Handle

This one is an amazing trick!

Once you learn information technology, at that place is no going back to the old fashion of manually typing each number of a sequence.

The fill up handle will automatically create a sequence of series numbers for you with just a click and drag.

Y'all first need to enter two sequential numbers.

Notice the active cell has a square in the lower correct?

This is the Fill Handle and you can use information technology to automatically fill in the residuum of the sequence which you started to add together manually.

Select the first two cells so hover your mouse cursor over the lower right corner until information technology turns into a modest black plus sign. Then click and drag downward to the end of your data.

If you're working with a large data gear up, then click and drag might not be feasible. In this state of affairs, y'all tin double click on the fill handle to fill the sequence downward to the end.

When you release the fill handle this will fill in the sequence of numbers and a small-scale fill up handle options carte du jour will appear at the bottom correct of your filled sequence.

There is 1 handy selection in this menu that allows you to avert filling down any prison cell formatting.

Click on the menu options and choose Fill Without Formatting and any copied cell formatting will be removed.

Pros

• Piece of cake to use and only requires manually entering two values.
• The serial number entered in a row will not change once information technology's entered.
• Deleting or inserting a row won't affect other tape ID's.
• Works inside Excel Tables.

Cons

• Each fourth dimension y'all add new rows to the bottom of your data table yous volition demand to manually add together a serial number or repeat the fill handle process.
• For big data sets, clicking and dragging the fill handle to the cease can be deadening.

Use the Make full Series Command

This option is very similar to the make full handle method, merely doesn't require and long click and drag actions down the length of your data.

Add the first two series numbers to your data prepare. And then select the entire range of cells which yous would like to fill up including the ii serial numbers you lot have already entered.

To select a longer range, information technology'due south easier to kickoff at the bottom and select upwardly to the meridian.

If you place the active cell cursor in the next cavalcade which contains data, you lot can apply Ctrl + Down to get to the last cell. Move the cursor dorsum over to the ID column, then Use Ctrl + Shift + Upward to select all the blank ID cells. So use Shift + Upwards to select any previously entered serial number cells.

Get to the Domicile tab of the ribbon and click on the Fill command and then choose Series.

This volition open up the Serial menu.

1. Choose Columns for the Series in option.
2. Choose AutoFill for the Type option.
3. Press the OK button.

This will make full in the remaining series in your selected blank cells.

Pros

• Easy to employ and only requires entering the first two numbers manually.
• No long click and drag actions required.
• You can delete or insert rows without affecting other existing serial numbers.
• Works within Excel Tables.

Cons

• This is not a dynamic solution, then when you lot add new rows to your data table yous will demand to manually add a serial number or echo the fill process.

Add One to the Previous Number

This is the intuitive method to adding a serial of numbers to your data. Excel'southward is designed to calculate, and so adding i to the previous serial number is no problem.

          = B3 + 1        

Enter the value i into the first row of your data. And then in the adjacent row enter the higher up formula. In this instance B3 is the jail cell straight above.

Now y'all can copy and paste this formula downwardly the remaining rows. To do this quickly, double click on the fill handle of the prison cell which contains the formula.

Pros

• Piece of cake to implement.
• Like shooting fish in a barrel for someone to understand what the formula is doing.
• You tin copy and paste the formula down when new rows are added.

Cons

• If you delete any row, you lot will go #REF! errors for all rows below as you are deleting a cell that is referenced by those cells below.
• The formula is not consequent for the unabridged cavalcade since the get-go row needs to contain a hard coded value of 1.

Utilize a Relative Reference Named Range

This method is going to use the aforementioned idea where you add together 1 to the previous serial number, only information technology is going use a clever flim-flam to make information technology more robust.

What you need to do is create a named range that will ever refer to the cell above information technology.

Then you can reference this named range instead of a direct cell accost like B3.

Go to the Formulas tab and click on Define Name.

This will open the New Proper name carte. Give your named range a name like In a higher place, this is how y'all will reference it inside formulas later.

          = INDIRECT ( "R[-1]C", Imitation )        

Add the higher up formula in the Refers to section of the New Proper name menu then printing the OK button.

This formula uses the INDIRECT part with row and cavalcade annotation. R[-1] indicates the reference is one row in a higher place the electric current cell.

At present when you employ the To a higher place name inside a formula it will reference the prison cell directly above the cell which yous are entering the formula.

          = SUM ( Above, 1 )        

Enter the to a higher place formula into the first row and so copy and paste it to the end of your data.

Using the SUM part is a pocket-size improvement to the previous method as at that place is no demand to enter a value in the first row. The formula tin be practical consistently for each row.

The SUM office will count text values every bit zero, then you lot can safely reference the column headings when the formula is in the first row without producing a #VALUE! error.

Pros

• You tin copy and paste the formula down when new rows are added.
• Apply the same formula for the entire row.
• Works well inside Excel tables.
• Serial numbers will dynamically adjust when y'all delete or insert a row.

Cons

• Takes more time to set.
• The formula might not exist every bit obvious to someone else looking at your spreadsheet,
• The named range uses the INDIRECT role which is a volatile Excel function. They tin slow downward your Excel workbook because they are recalculated more oftentimes.

Use the ROW Function

There is an Excel function that can return the current row number and it'due south perfect for creating serial numbers.

          = ROW ( ) - ROW ( $B$2 )        

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 electric current row when no argument is passed to it. In order to start the serial numbers at one, we then need to subtract off the row number of the cavalcade heading jail cell. This is accomplished using an absolute reference to the jail cell with the column heading.

Pros

• Easy to implement.
• Inserting or deleting a row won't cause any errors and series numbers will update accordingly.
• Works inside Excel Tables.

Cons

• The formula needs to reference the cavalcade heading cell with an absolute reference.

Utilize the COUNTA Function

This is another formula option that will rely on counting previous rows of information.

In lodge to apply this formula, you will need a column that will never comprise bare values as the COUNTA function does non count empty cells.

          = COUNTA ( $C$3:C3 )        

Enter the higher up formula into the first row and then copy and paste it to the end of your data. In this formula C3 is a jail cell in the outset row simply some other column which volition not contain whatsoever blank values.

Detect the range reference in the formula contains a partial accented reference with the $ symbol. This causes the formula to include all records above the electric current record in its count as it gets copied downwards the information set.

Pros

• Piece of cake to implement where the but catchy role is adding the fractional accented reference.
• You can insert or delete rows without errors.
• Works with Excel Tables.

Cons

• You demand to refer to some other column which can't have any bare values.

Use the SUBTOTAL Function

The SUBTOTAL function is interesting because information technology can render values based on what cells are visible.

Information technology tin too be used to perform other aggregations like a count and not just a sum.

This means yous tin utilize it to create serial numbers that change based on filtered information.

          = SUBTOTAL ( 3, $C$iii:C3 )        

Add the above formula into the first row and then copy and paste information technology downwardly the data.

This formula works exactly like the COUNTA method, considering information technology is the same. The first argument in the SUBTOTAL function tells Excel to use a count type aggregation.

The merely difference is when you filter your data, the serial numbers will update based on what is still visible.

Pros

• Same pros as COUNTA method.
• You tin employ filters on your data and they will dynamically change the serial numbers.

Cons

• Same cons equally COUNTA method.
• The SUBTOTAL function is less known by nigh Excel users.

Use the SEQUENCE Function

The SEQUENCE part is a new dynamic assortment function. This means i formula can produce an array of values.

It tin exercise a lot more, merely this function can also generate a single column of increasing numbers that start at 1. Perfect for serial numbers!

          = SEQUENCE ( COUNTA ( C3:C8 ) )        

Add the above formula into the first row of information. In this example C3:C8 is the entire range of a column. This will determine how many serial numbers the SEQUENCE office volition return.

          = SEQUENCE ( half-dozen )        

Another approach is to hard code the row count inside your SEQUENCE function like the to a higher place formula. Unfortunately, this method would hateful y'all need to manually accommodate the count each time you add or remove rows of information.

Pros

• Y'all just demand 1 formula. No demand to copy and paste a formula down to the end of your data ready.
• Inserting or deleting any row other than the first one won't produce any errors and your serial numbers will adjust accordingly.

Cons

• Deleting the starting time row volition delete all the series numbers.
• Tin can't exist used within Excel Tables.
• If yous add rows at the bottom of your information set, you volition demand to conform the range reference in the SEQUENCE function to include these.
• The COUNTA function will require non blank values.

Apply Ability Pin

This one is a bit weird since it volition create series 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.

          = RANK.EQ( Data[E-mail], Information[Email], ASC )        

This will open up the ability pivot add-in and you'll be able to add the above formula into the table.

In this instance, the tabular array has been named Information and we are ranking the Email column which contains unique text values. This results in column that tin be used equally a serial number.

To use this calculated cavalcade within a pin table.

1. Go to the Insert tab.
2. Click on PivotTable.
3. Cull From Information Model from the options.
4. Cull the location yous would like to place the pivot table.
5. Add add the fields into the Rows area of your new pin table including the Rank calculated column.
6. Go to the Design tab ➜ Report Layout ➜ Show in Tabular Form. This will make information technology expect more than like a dataset instead of a pivot table.

Pros

• Y'all can create serial numbers inside a pin table.

Cons

• You demand a cavalcade with unique values like an electronic mail.
• Results are not ordered.

Apply VBA Code

VBA has been in Excel and other Office applications for a long time.

It was the get to method for automating any task in Excel. So you can certainly employ 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 final resort.

          Sub AddSerial()  Dim cell As Object Dim count Every bit Integer count = 0  For Each cell In Choice     count = count + 1     cell.Value = count Side by side cell  End Sub        

Add together the higher up code into the visual bones editor.

1. Pres Alt + F11 to open up the visual basic editor.
2. Correct click inside the VBAProject window.
3. Select Insert from the carte.
4. Select Module from the submenu.
5. Paste the lawmaking into the module.

The to a higher place lawmaking will add an increasing sequence of numbers starting at 1 into any selected range.

Now yous can run the lawmaking.

1. Select the unabridged column which you want to add serial number into.
2. Press Alt + F8 to open up the Macro dialog box.
3. Select the macro to run.
4. Press the Run button.

This volition fill your selected range with a sequence of numbers starting at i.

Pros

• Adds static numbers into any range.

Cons

• Uses VBA and will require the workbook to be saved as an xlsm file.
• Hard to prepare and run.
• You will need to re-run the lawmaking if yous add together or insert rows to your data set up.

Utilise an Index Column in Power Query

Ability Query is an amazing data transformation tool found in Excel and other Microsoft products.

If you're already using information technology to import your information from an external source into Excel, then it might be the perfect solution to calculation serial number to your data.

If your data is already in Excel, you can still utilize power query to add serial numbers merely you will need to add together your data into an Excel table first.

I wrote a detailed article about Excel Tables, where you can larn all near them including how to catechumen your data into a table.

Add together your information into query past using a From Sail query. Select a cell within your table ➜ get to the Data tab ➜ choose From Sheet.

This will open the ability query editor and y'all will exist able to add together a cavalcade with serial numbers from here.

Become to the Add Cavalcade tab and click on the Index Cavalcade command. Click on the small arrow icon of the Index Column button to choose to commencement the index From ane.

Now you tin can get to the Home tab ➜ Shut & Load ➜ Close & Load To ➜ Choose to load the results into an Excel table and pick the location to load information technology to.

Your data volition be loaded into another table with an extra alphabetize column.

If you add or remove information from the source, you lot will demand to refresh the query output to see the updated results. You tin can refresh the query by right clicking on the table and choosing Refresh from the menu options.

Pros

• Great option if you're using ability query to import your data from an external source already.
• Like shooting fish in a barrel to refresh once it is fix.

Cons

• Power query can be intimidating to someone using it for the get-go fourth dimension.
• It doesn't add the serial numbers into the data source, it will simply add them to the output of a query on the data source.

Apply the Connexion Backdrop for Continued Tables

If your data has been loaded into a table from an external source like ability 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 volition open the External Data Properties menu and you can enable the choice 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 volition run across a new cavalcade in the table called _RowNum which starts the count at 0.

Pros

• You lot can delete or insert rows in the source information and the row number will update when you lot refresh the connection.

Cons

• This feature just works with connected data tables.
• The row numbers setting is hard to find.
• The row number column tin can simply appear as the very left cavalcade in the tabular array.
• The row numbers outset at 0 and can't exist changed to commencement at 1 instead.
• The column heading for your serial numbers can't be inverse from _RowNum.

Employ Office Scripts

Office Scripts is a new TypeScript based scripting language which is currently just available in Excel online for Enterprise Microsoft 365 plans.

This is the replacement for VBA and you tin certainly employ information technology to auto generate a set of row numbers.

You volition need a few things in order to use this method first.

• Your Excel file has to exist saved in SharePoint.
• Yous need to open your Excel file in the Excel spider web application.
• You lot need to be on an enterprise level Microsoft 365 plan.
• The Role Scripts feature needs to be enabled by your Information technology admin.

Open up your file in Excel online then go to the Automate tab and click on New Script.

          office main(workbook: ExcelScript.Workbook) { 	allow selectedSheet = workbook.getActiveWorksheet(); 	let myID = selectedSheet.getRange("Data[ID]"); 	permit myCellCount = myID.getCellCount(); 	let mySerialNumbers = new Array(Array(myCellCount)); 	for (var i = 0; i < myCellCount; i++) { 		mySerialNumbers[i] = [i+1] 	} 	selectedSheet.getRange("Data[ID]").setValues(mySerialNumbers); }        

Paste in the above code into the Code Editor. You can rename the script and press Save Script to save the script.

Printing the Run button in the Code Editor to run the script.

This script relies on your data being within an Excel Table named Data with a column named ID.

Pros

• One time this is fix, its very easy to apply.

Cons

• Excel file needs to be saved in SharePoint.
• Part Scripts are only available in Excel online for Enterprise Microsoft 365 plans.
• You tin can only run this script from Excel online.

Use Ability Automate

Now that you accept an Office Script to add together serial numbers to Excel, you lot tin can employ Power Automate to automate the running of this script.

Y'all can use power automate to run this script on a schedule, so you can automatically update your series numbers every day or fifty-fifty every hour.

1. Log into https://flow.microsoft.com/
2. Click on Create in the left hand navigation pane.
3. Click on Scheduled cloud menstruation.
4. Give your catamenia a name like Add together Serial Numbers.
5. Ready the start engagement to today's date and time.
6. Ready the frequency which y'all would like the menstruum to run.
7. Click on the Create button.

Now add a Run script step to the catamenia and select the file and script to run so Save the flow.

This volition at present automatically run the script to add serial number at your desired frequency.

Pros

• The solution automatically runs in the background.
• You tin can work on the file in the desktop or web app while the menses runs.

Cons

• Requires a complex setup.
• Needs Microsoft 365, SharePoint and Office Scripts.

Conclusion

There are a ton of options when it comes to calculation serial numbers to your data in Excel.

Each method will accept diverse pros and cons that might brand them a better pick for your utilize case. It's worth exploring them all to see which volition work best for you.

Whatsoever your skill set at that place is an option for y'all that will get you your desired results.

Did I miss your favourite fashion to add together row numbers in this post? Let me know in the comments if you take another method you apply!

Nigh the Author

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Ability BI, Ability Automate, Power Apps and SharePoint. You lot can find other interesting manufactures from John on his web log or YouTube channel.

mccaffreysyclee1962.blogspot.com

Source: https://www.howtoexcel.org/serial-numbers/

Share this post