# 12 Automatic Ways of Numbering in Excel - ExcelDemy (2023)

Get FREE Advanced Excel Exercises with Solutions!

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.

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

(Video) How to Auto Generate Number Sequence in Excel

Things to Remember

Practice Section

Conclusion

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.

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.

(Video) How to Autofill Numbers in Excel Without Dragging

### 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

(Video) How to automatically add commas to numbers 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.

• 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.

(Video) Convert Excel Number Stored as Text

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

• 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? ›

Fill a column with a series of numbers
1. Select the first cell in the range that you want to fill.
2. Type the starting value for the series.
3. Type a value in the next cell to establish a pattern. ...
4. Select the cells that contain the starting values. ...
5. Drag the fill handle.

How do I keep 12 digit numbers in Excel? ›

To do this, follow these steps:
1. Right-click target cell, and then click Format Cells.
2. On the Number tab, select Text, and then click OK.
3. Then type a long number. ( Be sure to set the cell format before you type the number)
4. If you do not want to see the warning arrows, click the small arrow, and then click Ignore Error.
May 5, 2022

How do I AutoFill a number pattern in Excel? ›

Fill data automatically in worksheet cells
1. 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. ...
2. Drag the fill handle .
3. If needed, click Auto Fill Options. and choose the option you want.

How do I continue automatic numbering? ›

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.

How do you increase number precision in Excel? ›

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? ›

How to format Excel cells as numbers
1. Select the cell(s) in Excel.
2. Click on Format -> Cells from the top menu. Notice that Scientific is chosen by default.
3. Select Number as the format, and set Decimal places to 0. Click OK to Save.
May 4, 2023

How do I keep the first 10 digits in Excel? ›

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.

What is dynamic numbering? ›

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? ›

Number the cells in a table in Word
1. 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.
2. On the Home tab, in the Paragraph group, click Numbering.

How do you set a numbering value? ›

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? ›

How to use the REPT formula in Google Sheets
1. Type “=REPT(” or go to “Insert” → “Function” (or directly navigate to the “Functions” icon) → “Text” → “REPT”.
2. Input text to repeat by manual input or cell reference and define the number of repetitions.
3. Press the “Enter” key.

How do you use sequence in sheets? ›

Examples of the SEQUENCE Function in Google Sheets
1. Click on the cell where you want to input the formula, A1 in the example.
2. Write the beginning of the formula, which is =SEQUENCE(
3. Write the number of rows that you wish to create. ...
4. Now, enter the closing bracket to finish the formula.
5. Press Enter to execute the formula.
May 28, 2022

How do I fix numbering in sheets? ›

To format or change the format of numbers, dates or currencies in a spreadsheet:
2. Select the range of cells you'd like to format or modify.
3. Click Format. Number.
4. 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? ›

The LEFT Function
1. Click in the cell where you want to insert the function.
2. Click the Formulas tab.
3. Click the Text Function button.
4. Select LEFT. ...
5. In the Text field, select the cell containing text you want to extract.
6. In the Num_chars field,enter the number of characters you want to extract.
7. Click OK.

What is intelligent numbering? ›

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? ›

To convert cells that are formatted as text to numbers, follow these steps:
1. Select the cell that is formatted as text that you want to convert to a number. ...
2. On the Error Checking Options button, click the down arrow. ...
3. Click Convert to Number.

How do you use the sequence function 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 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 operators
Arithmetic operatorMeaningExample
– (minus sign)Subtraction Negation=3–1 =–1
* (asterisk)Multiplication=3*3
/ (forward slash)Division=3/3
2 more rows

## Videos

1. Auto Generate Serial Number Using Aggregate Formula in excel | Magic of Aggregate Formula 4 examples
(Computer Gyan Guruji)
2. How to Convert Number to Text with Leading Zeros in Excel
(ExcelDemy)
(ExcelDemy)
4. How to Convert Number to Words
(ExcelDemy)
5. How to write 16 digits number in single cell in excel
(Excel (A-Z))
6. How to Sum by Group in Excel
(xlpedia)

## References

Top Articles
Latest Posts
Article information

Last Updated: 10/30/2023

Views: 5919

Rating: 5 / 5 (60 voted)

Author information