While dealing with a large dataset, you may need to assign automatic numbers to your cells. However, manually applying the number is a tedious task. Also, it is not a time-efficient way to give the serial number manually. Excel doesn’t provide you with any features for automatic numbering. But there are some methods to assign automatic numbers to the cells in Excel. In this article, we’ll show you how to add automatic numbering in Excel. Let’s get started.
Table of Contents hide
Download Practice Workbook
12 Methods to Do Automatic Numbering in Excel
1. Using Fill Handle Tool
2. Using Fill Series Command
3. Adding 1 to the Previous Row Number
4. Applying Name Manager
5. Combining the IF and MAX Functions
6. Utilizing the OFFSET Function
7. Applying the ROW Function
8. Employing the COUNTA Function
9. Using the SEQUENCE Function
10. Creating a Table in Excel
11. Using SUBTOTAL Function
12. Incorporating VBA Code
How to Number Columns Automatically in Excel
Things to Remember
Practice Section
Conclusion
Further Readings
Download Practice Workbook
Download the following practice workbook. It will help you to realize the topic more clearly.
Automatic Numbering.xlsm
12 Methods to Do Automatic Numbering in Excel
For automatic numbering, we have taken a dataset of Country wise Sales lists for different Products. Now, we want to put the automatic serial number for the list.
Not to mention, we have used the Microsoft 365 version. You can use any other version at your convenience.
1. Using Fill Handle Tool
The Fill Handle feature can easily catch the pattern from a few filled cells and then auto-fill the entire column. It is the most widely used and common feature.
Steps:
- In the “Serial No.” column, first, fill the first and second cells with 1 and 2. Then select those two cells and move your mouse cursor to the bottom right corner of the selected cell until you see this icon + (plus sign).
- When you see the cross icon, double-click on this icon and it will automatically number the cells to the end of the data row. You can drag it down also with the mouse.
Finally, you will get the automatic number, as shown in the image below.
Read more: How to Autofill Numbers in Excel
2. Using Fill Series Command
The Fill Series feature provides you with a lot more control over your datasheet than the Fill Handle. You can do the operation both for the column and the rows. To do so, follow the steps below.
Steps:
- We use the same dataset. To use the Fill Series method, first, insert 1 in your cell from where you want to start the auto number. Then go to the Home tab >> click on the Fill option>> and select Series.
- Apparently, A new Series window appeared. Select Column as we want to auto number the columns, Step Value is 1, and Stop Value is 14 because we have 14 rows to number. Click OK to continue.
Eventually, your automatic numbering will be ready (see the image).
Note: You can also auto-number rows by using the same procedures. Just change the cell option from “Column” to “Row”.
Read more: How to Autocomplete Cells or Columns From List in Excel
3. Adding 1 to the Previous Row Number
You can add 1 to the previous row and drag it down for the other cells. It will assign automatic numbering to your cells.
Steps:
- By adding one in the simple form of “Sum” we can do automatic numbering in excel. First, insert 1 in the first cell from where you want to start automatic numbering (B5). In the second cell, input this formula,
=B5+1
This formula adds “1” to every previous cell.
Consequently, you get the below result after dragging it down.
4. Applying Name Manager
You can set a name for your worksheet and then use the SUM function to create the automatic function. We use the INDIRECT function here to make the reference cell. Follow the below steps to perform the operation.
Steps:
- Firstly, go to the Formulas tab and select Define Name from the Defined Names section.
- Apparently, a New Name dialog wizard appears. Write a random name in the Name box. In our case we put AutoNumber.
- After that, write the following code in the Refers to the box.
=INDIRECT("R[-1]C", FALSE)
Here, the above function reference is for the active cell, and [-1] indicates one row above the reference cell.
- Press OK.
- Now, go to cell B5 and insert the formula.
=SUM(AutoNumber,1)
It will add 1 to the previous number.
Finally, you will get the result after dragging it down.
5. Combining the IF and MAX Functions
Sometimes, you may have blank cells in the column from where you want to create automatic numbering. Obviously, you don’t want to assign a number to the blank cells. In this method, we have combined the IF and MAX functions to create the automatic numbering for dealing with such a situation. The MAX function returns the maximum value, and the IF function checks for the given argument whether the cell is blank or not. Follow the below steps to use the method.
Steps:
- Primarily, go to cell B5 and insert the formula.
=IF(C5="","",MAX($B$4:B4)+1)
Formula Breakdown
MAX($B$4:B4)+1→ The function returns the maximum value in the B4 cell. As there is a text value so the function returns 0 and the +1 has been added to the result.
IF(C5=””,””,MAX($B$4:B4)+1)→ The function returns the blank cells if there is a blank, then the auto numbering stays blank for that cell.
- Eventually, press ENTER and drag it down.
As a result, the serial number is auto-numbered, as shown in the image below.
6. Utilizing the OFFSET Function
You can use the OFFSET function to use automatic numbering. The function starts off with a particular cell reference, moves to a specific number of rows down, then to a specific number of columns right, and then extracts out a section from the data set having a specific height and width.
Steps:
- Firstly, go to cell B6 and insert the formula.
=OFFSET(B6,-1,0)+1
Where Reference is B5, Row is -1, Cols is 0
The OFFSET function is an array function that takes the reference cell as B5 and the row number is -1 which is before that row and the column number is 0.
Sequentially, you get the result like the image below.
Read more: How to Use Autofill Formula in Excel
7. Applying the ROW Function
We use the ROW function to give automatic numbers. But we have to input a reference cell first. The ROW function returns the row number for a given reference. The reference may be a cell or cell range. If the reference is not specified (as the argument is optional), the ROW function automatically considers the cell containing the formula as a reference. To get a proper realization, follow the steps below.
Steps:
- In cell, B5 insert the below formula.
=ROW()-4
As the ROW function will give you the cell number from the starting row (B1), subtract 4 from the function so that we can get the desired number from the function.
Eventually, the numbers will be added to your dataset.
Similar Readings:
- Autofill Dates in Excel (3 Suitable Methods)
- How to AutoFill Cell Based on Another Cell in Excel (5 Methods)
8. Employing the COUNTA Function
The COUNTA function will allow you to auto-number only the filled cells and ignore the blank cells. Let’s learn!
Steps:
- Initially, go to cell B5 and input the formula.
=IF(ISBLANK(C5),"",COUNTA($C$5:C5))
Where Logical_test is ISBLANK(C5), [value_if_true] is ( ” ” ). For the true value, the formula will return a Blank space, [value_if_false] is COUNTA($C$5:C5). For the false value, the formula will return an auto number.
The IF function tests whether the adjacent cells in column “Serial No.” are empty or not. If the cell is empty, it returns a blank, but if it’s not, it returns the auto count of all the filled cells.
The result is in front of you. See the screenshot below for a better understanding.
9. Using the SEQUENCE Function
Using the same dataset, we will apply the basic SEQUENCE function to complete our task. Follow the below steps to do that.
Steps:
- Firstly, go to cell B5 and enter the following formula.
=SEQUENCE(14)
Where, “14” is the number of rows.
After pressing ENTER, you get the result.
- You can also do the same operation for the column number.
- For doing that, go to cell B3 and write up the formula.
=SEQUENCE(1,6)
The SEQUENCE(1,6) syntax gives you the sequence of columns. Where 1 is the Row number and 6 is the Column number.
10. Creating a Table in Excel
You can create a table from which you can assign the automatic numbers to your dataset. A table in Excel has some unique features, including automatic numbering. So, creating a table does our job.
Steps:
- For creating a table, first of all, select the entire dataset and hover over to the Insert tab >> pick Table.
- A Create Table dialog box appears. Check the My table has headers box and hit OK.
- Consequently, in the B5 cell, input the formula.
=ROW()-ROW(Table3[#Headers])
It will add 1 in Serial No. column.
Finally, you get the result after dragging it down.
- This method has a unique ability that the number in the table can auto-update. To check this, delete some rows from the table.
Boom! The table has been updated automatically, as shown in the image below.
11. Using SUBTOTAL Function
There is another function by which you can count the cells, and that is the SUBTOTAL function. There are several function numbers in the SUBTOTAL function from which you can do different operations.
Steps:
- Initially, go to cell B5 and write up the formula.
=SUBTOTAL(3,$C$5:C5)
Here, the 3 in the function stands for the func_num which is basically the COUNTA function, and the $C$5:C5 cell is the reference cell.
Subsequently, you get the result like the image below.
12. Incorporating VBA Code
A VBA Macros makes your dataset’s auto numbering quite easy. For this reason, you will get the auto number for the cells. Follow the steps.
Steps:
- Firstly, hover over to the Developer tab >> choose Visual Basic.
- Consequently, select the Insert tab >> Module >> Module 1.
- In Module 1, write down the code in the General box.
Sub AutoNum()Dim i As Integeri = InputBox("Put Value", "Automatic Numbering")For i = 1 To iActiveCell.Value = iActiveCell.Offset(1, 0).ActivateNext iEnd Sub
- After that, Run the code with the F5 key and the Enter Serial Numbers dialog box appears. Enter the last serial number 14 in the box and hit OK.
Eventually, you will have obtained all of the auto-numbers from the active cell.
How to Number Columns Automatically in Excel
There are 3 suitable ways for automatically numbering columns. One of the most useful ways to do this is by using the COLUMN function.
- Firstly, go to the C4 cell and insert the formula.
=COLUMN(B4)-COLUMN($A$3)
Here, the function subtracts the $A$3 cell from the reference column.
- After that, press ENTER and drag it to the right side of the columns where you wanted to end the serial.
Finally, you get the result below.
Things to Remember
➤ While using the “OFFSET” function, you must keep a blank cell above the starting cell. Otherwise, the function won’t operate correctly.
➤ While using the “ROW” function, subtract the number of rows from the starting row. By doing this, the automatic numbering will start at 1.
➤ The “SEQUENCE” function is available for “Excel 365” and other updated versions.
Practice Section
We have provided a practice section on each sheet on the right side for your practice. Please do it yourself.
Conclusion
That’s all about today’s session. And these are some easy methods to generate automatic numbering in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding, please download the practice sheet. Visit our website, Exceldemy, a one-stop Excel solution provider, to find out about diverse kinds of Excel methods. Thanks for your patience in reading this article.
Further Readings
- How to Auto Populate Cells in Excel Based on Another Cell
- Fix: Excel Autofill Not Working (7 Issues)
- How to Perform Predictive Autofill in Excel (5 Methods)
FAQs
How do I get Excel to continue numbering automatically? ›
- Select the first cell in the range that you want to fill.
- Type the starting value for the series.
- Type a value in the next cell to establish a pattern. ...
- Select the cells that contain the starting values. ...
- Drag the fill handle.
- Right-click target cell, and then click Format Cells.
- On the Number tab, select Text, and then click OK.
- Then type a long number. ( Be sure to set the cell format before you type the number)
- If you do not want to see the warning arrows, click the small arrow, and then click Ignore Error.
- Select one or more cells you want to use as a basis for filling additional cells. For a series like 1, 2, 3, 4, 5..., type 1 and 2 in the first two cells. ...
- Drag the fill handle .
- If needed, click Auto Fill Options. and choose the option you want.
Go to File > Options > Proofing. Select AutoCorrect Options, and then select the AutoFormat As You Type tab. Select or clear Automatic bulleted lists or Automatic numbered lists.
How do I automatically continue numbering in sheets? ›Use autofill to complete a series
Highlight the cells. You'll see a small blue box in the lower right corner. Drag the blue box any number of cells down or across. If the cells form a series of dates or numbers, the series will continue across the selected cells.
Click File > Options. , and then click Excel Options. Click Advanced, and then under When calculating this workbook, select the Set precision as displayed check box, and then click OK.
How do I avoid numbers getting truncated in Excel spreadsheets? ›- Select the cell(s) in Excel.
- Click on Format -> Cells from the top menu. Notice that Scientific is chosen by default.
- Select Number as the format, and set Decimal places to 0. Click OK to Save.
Extract first n characters from string
Select a blank cell, here I select the Cell G1, and type this formula =LEFT(E1,3) (E1 is the cell you want to extract the first 3 characters from), press Enter button, and drag fill handle to the range you want.
Dynamic Number Insertion (DNI) is a call tracking feature where a unique phone number is tied to each ad source. This helps marketers analyze offline behavior much in the same way they track online behavior with the help of cookies.
What is dynamic number formula in Excel? ›Dynamic Formulas allow you to insert Excel's formulas into cells even when the formula must reference rows that will be inserted later during the merge process. They can repeat for each inserted row or use only the cell where the field is placed. Dynamic Formulas begin with &== and are followed by an Excel formula.
What is the dynamic SEQUENCE function in Excel? ›
The SEQUENCE function in Excel is used to generate an array of sequential numbers such as 1, 2, 3, etc. It is a new dynamic array function introduced in Microsoft Excel 365. The result is a dynamic array that spills into the specified number of rows and columns automatically.
Can you do predictive patterns in Excel? ›When you create a forecast, Excel creates a new worksheet that contains both a table of the historical and predicted values and a chart that expresses this data. A forecast can help you predict things like future sales, inventory requirements, or consumer trends.
How to AutoFill a sequence of numbers in Excel with or without dragging? ›Select the cell with the formula and the adjacent cells you want to fill. Click Home > Fill, and choose either Down, Right, Up, or Left. Keyboard shortcut: You can also press Ctrl+D to fill the formula down in a column, or Ctrl+R to fill the formula to the right in a row.
How do I AutoFill a series in Excel? ›Put the mouse pointer over the bottom right-hand corner of the cell until it's a black plus sign. Click and hold the left mouse button, and drag the plus sign over the cells you want to fill. And the series is filled in for you automatically using the AutoFill feature.
Why is automatic numbering not working in Excel? ›The first thing to check when AutoFill isn't working is your settings. Make sure that the AutoFill option is turned on in Excel. To do this, go to the File menu, click on Options, and then select Advanced. Scroll down to the Editing Options section and make sure that the Enable AutoFill option is checked.
How to do automatic numbering in a table? ›- Select the table cells that you want to number. To number the beginning of each row, select only the first column in the table by clicking the column's top border.
- On the Home tab, in the Paragraph group, click Numbering.
Click Set Numbering Value. In the Set value to: box, use the arrows to change the value to the number you want. Tip: Tempting as it is to manually change the numbers in a list, don't do it. Always use the command options to make the changes.
How do I create a repeating pattern in sheets? ›- Type “=REPT(” or go to “Insert” → “Function” (or directly navigate to the “Functions” icon) → “Text” → “REPT”.
- Input text to repeat by manual input or cell reference and define the number of repetitions.
- Press the “Enter” key.
- Click on the cell where you want to input the formula, A1 in the example.
- Write the beginning of the formula, which is =SEQUENCE(
- Write the number of rows that you wish to create. ...
- Now, enter the closing bracket to finish the formula.
- Press Enter to execute the formula.
- On your computer, open a spreadsheet in Google Sheets.
- Select the range of cells you'd like to format or modify.
- Click Format. Number.
- Select the format to apply to the range of cells.
What is the maximum number precision in Excel? ›
For Excel, the maximum number that can be stored is 1.79769313486232E+308 and the minimum positive number that can be stored is 2.2250738585072E-308.
What is the numeric precision in Microsoft Excel? ›Precision in Excel means that any number exceeding 15 digits is stored and shown with only 15 digits of precision. Those digits can be in any combination before or after the decimal point. Any digits to the right of the 15th digit will be zeros.
Why is Excel truncating my 16 digit numbers? ›This is happening because of the number format of the cells in which you have the numbers. And to stop Excel from rounding these numbers, change the cell format so that it can show more numbers than what it's showing currently.
What does truncated mean in Excel? ›TRUNC removes the fractional part of the number. INT rounds numbers down to the nearest integer based on the value of the fractional part of the number.
What is Num_chars in Excel? ›The “num_chars” is the number of digits to be extracted from the string. Note: In Excel, the numbers string is right-aligned while the text string is left-aligned.
How do I extract certain digits from a cell in Excel? ›Select all cells with the source strings. On the Extract tool's pane, select the Extract numbers radio button. Depending on whether you want the results to be formulas or values, select the Insert as formula box or leave it unselected (default).
How do you extract everything to the left of a specific character in a field? ›- Click in the cell where you want to insert the function.
- Click the Formulas tab.
- Click the Text Function button.
- Select LEFT. ...
- In the Text field, select the cell containing text you want to extract.
- In the Num_chars field,enter the number of characters you want to extract.
- Click OK.
Intelligent numbering is a practice in which product numbers contain meaningful information. For example, an apparel brand might have a shirt labeled MSS20J012-BLU-L.
What is sequential numbering? ›Sequential numbering is a Subroutine process of Format Control that automatically adds identifying numbers to database records as they are created. Sequential numbering is used for Configuration Management, incident records, or employee records. Sequential numbers are defined for appropriate fields in any data record.
What is sequential numbering system? ›Sequential Numbering is a popular feature on custom-printed forms Sequential Numbering, also known as Consecutive Numbering, refers to the printing of ascending or descending identification numbers so that each printed unit receives its own unique number.
What is random number generator in Excel? ›
RAND returns an evenly distributed random real number greater than or equal to 0 and less than 1. A new random real number is returned every time the worksheet is calculated.
What function generates sequential numbers in Excel? ›The SEQUENCE function allows you to generate a list of sequential numbers in an array, such as 1, 2, 3, 4. In the following example, we created an array that's 4 rows tall by 5 columns wide with =SEQUENCE(4,5).
What is a dynamic SEQUENCE? ›Dynamic Sequence is a company dedicated to solving this problem for those who want to use a data-driven approach to improve their video ad performance and grow their business. We are a Premium Facebook Marketing Partner. Visit our site to learn more about the impact Dynamic Sequence has generated for its customers.
What is Excel autocomplete feature in Excel? ›Microsoft Office Excel completes text entries that you start to type in a column of data — if the first few letters that you type match an existing entry in that column. If you want to stop automatic completion, you can turn this option off.
What is the formula for prediction in Excel? ›The FORECAST function in Excel is used to predict a future value by using linear regression. In other words, FORECAST projects a future value along a line of best fit based on historical data. Where: X (required) - a numerical x-value for which you want to predict a new y-value.
How do I AutoFill and AutoComplete in Excel? ›On the Excel menu, click Preferences. Under Formulas and Lists, click AutoComplete. In the Show the menu only after I've typed box, enter the number of letters that you want to type before the AutoComplete menu appears. Tip: You can also show the AutoComplete menu by pressing CONTROL + OPTION + DOWN ARROW .
How do you continue numbers in Excel without dragging? ›Select the cell with the formula and the adjacent cells you want to fill. Click Home > Fill, and choose either Down, Right, Up, or Left. Keyboard shortcut: You can also press Ctrl+D to fill the formula down in a column, or Ctrl+R to fill the formula to the right in a row.
How do I enable fill series in Excel? ›Click File > Options. In the Advanced category, under Editing options, select or clear the Enable fill handle and cell drag-and-drop check box.
How do I force Excel to recognize numbers? ›- Select the cell that is formatted as text that you want to convert to a number. ...
- On the Error Checking Options button, click the down arrow. ...
- Click Convert to Number.
The SEQUENCE function allows you to generate a list of sequential numbers in an array, such as 1, 2, 3, 4. In the following example, we created an array that's 4 rows tall by 5 columns wide with =SEQUENCE(4,5).
What is flash fill in Excel? ›
Excel Flash Fill is a special tool that analyzes the information you are entering and automatically fills data when it identifies a pattern. The Flash Fill feature was introduced in Excel 2013 and is available in all later versions of Excel 2016, Excel 2019, Excel 2021, and Excel for Microsoft 365.
Why did Excel stop auto filling? ›In the Excel Options dialog box, go to the Advanced tab and scroll down to the Editing options section. Make sure that the “Enable AutoComplete for cell values” option is checked. If this option is already checked and you're still having trouble with autocomplete, you could try repairing your Office installation.
What are the arithmetic operators in Excel? ›Arithmetic operator | Meaning | Example |
---|---|---|
+ (plus sign) | Addition | =3+3 |
– (minus sign) | Subtraction Negation | =3–1 =–1 |
* (asterisk) | Multiplication | =3*3 |
/ (forward slash) | Division | =3/3 |