How to automatically number columns in Excel quickly and simply

You can quickly number columns in Excel by dragging the mouse, using formulas or functions such as SEQUENCE, ROW. These methods help save time and limit errors when working with large data.

Table of Contents:
1. Drag the mouse to number the order
2. Use Fill/Series
3. Use a formula
4. Use the SEQUENCE function
5. Use Auto fill.
6. Use the SUBTOTAL function nested with IF.
7. Some things to note
8. Other ways.

How to automatically number columns in Excel

1. Drag the mouse to number in Excel

Suppose we have a list of 7 people as below with employee codes from M01 to M07, so how to quickly type M01 to M07 without having to fill in each line. Because in reality, if there are only 7 people, filling in each line is simple, but with a list of up to hundreds of people, it is a different story. Let's solve the above problem with this example with TipsMake.

How to automatically number columns in Excel quickly and simply Picture 1

Step 1: Here you see the plus arrow when you point to the corner of a cell, click on it and drag down to where the code M07 is.

How to automatically number columns in Excel quickly and simply Picture 2

Step 2: And when you release it, Excel will automatically number the columns in order, jumping by 1 number.

How to automatically number columns in Excel quickly and simply Picture 3

Step 3: However, sometimes you follow the instructions but get a series of similar numbers out of order, that's because when dragging the mouse in step 1 you accidentally held or pressed Ctrl at that time.

How to automatically number columns in Excel quickly and simply Picture 4

2. Use Fill/Series to number in Excel

Step 1 : Enter the first order number: 1

How to automatically number columns in Excel quickly and simply Picture 5

Step 2 : In the Editing section, select Fill / Series.

How to automatically number columns in Excel quickly and simply Picture 6

Step 3 : The Series dialog box appears. You perform the following steps in turn:

- Series in : Select Rows if numbering by row, Columns if numbering by column.
- Type : Select + Linear : Numbering in ascending order

                        + Growth: Progressive type
                        + Date : Date type
                        + Auto Fill: Auto fill type

- Step value : Distance between two serial numbers.
- Stop value : Only enter status up to this column position.

Click OK.

How to automatically number columns in Excel quickly and simply Picture 7

We have the result: Display automatic column numbering in Excel.

How to automatically number columns in Excel quickly and simply Picture 8

3. Use formula to number in Excel

Step 1: Open the Excel file you want to fill in the order. Enter the order for the first cell => Enter the formula for the second cell with the formula = Position of the cell just filled in + 1 .

For example, the third row in the table below would be entered as =A2 + 1 .

How to automatically number columns in Excel quickly and simply Picture 9

Step 2:  Use the mouse to place the small square in the corner of that cell and drag it down to the position you want.

How to automatically number columns in Excel quickly and simply Picture 10

- Result:

How to automatically number columns in Excel quickly and simply Picture 11

4. Use the SEQUENCE function to number in order in Excel

Select the starting cell of the sequence => Enter the formula " =SEQUENCE(Number of rows to number) " => Press Enter .

For example: To number 7 rows, use the following formula:  =SEQUENCE(7) .

How to automatically number columns in Excel quickly and simply Picture 12

5. Use Auto fill

To use the Auto Fill feature, follow these steps:

Step 1: Open an Excel file that you want to create a serial number for, then select File .

How to automatically number columns in Excel quickly and simply Picture 13

Step 2: Next select Options

How to automatically number columns in Excel quickly and simply Picture 14

Step 3: In Excel Options in the Customize section => Select filter All commands => Find Filter series or Pattern .

How to automatically number columns in Excel quickly and simply Picture 15

Step 4: In the Main Tabs section in the Home section , right-click and select Add New Group .

How to automatically number columns in Excel quickly and simply Picture 16

Step 5: Continue to select Filter Series or Pattern  => Click Add => Click OK .

How to automatically number columns in Excel quickly and simply Picture 17

Step 6: Go back to the table where you want to enter the serial number, enter the serial number for the first cell => Select Fill => Select Series .

How to automatically number columns in Excel quickly and simply Picture 18

Step 7: The Series table appears and you have options such as:

+ Linear: Numbering in ascending order.
+ Growth: Progressive order.
+ Date: Date order.
+ Auto Fill: Auto fill order.

In the Step value section you can enter the starting number and the Stop value is the ending number.

Then press Ok to execute.

How to automatically number columns in Excel quickly and simply Picture 19

- Result:

How to automatically number columns in Excel quickly and simply Picture 20

6. Using the nested SUBTOTAL function with IF

In this way, I will nest 2 functions together, which are the IF function and the SUBTOTAL function with the following syntax:

=IF(logical_test; [value_if_true]; [value_if_false]; SUBTOTAL(function_num; ref1;.))

Formula meaning:

+ logical_test: Condition of the IF function.
+ [value_if_true]: Return value if the condition is true.
+ [value_if_false]: Return value if the condition is false.
+ function_num: The function is understood by numeric value (Here the number 3 is equivalent to the COUNTA function).
+ ref1;.: Area containing the serial number.

How to automatically number columns in Excel quickly and simply Picture 21

Enter the function with the syntax " =IF(B2="","",SUBTOTAL(3,$B$2:B2)) ".

Function explanation:

For the function SUBTOTAL(3,$B$2:B2)) we have:

+ function_num = 3: Understands as the COUNTA function in SUBTOTAL - Counts the number of non-empty cells.
+ ref1 =$B$2:B2: Is the numbered range (Note: Always lock the first value, here is B2, if not locked, when running the function will fail for the cells below).

For the function IF(B2="","",SUBTOTAL(3,$B$2:B2)) we have:

+ C3="";"": This means that if the value in cell C3 is empty, the serial number will also be empty.
+ SUBTOTAL(3,$B$2:B2)): If cell C3 is not empty, the SUBTOTAL function will be used.

How to automatically number columns in Excel quickly and simply Picture 22

7. Some notes when numbering in Excel

With the mouse drag method, you can easily apply it to tables with little data and when using a table with more than 1000 rows, manual dragging will be affected and when deleting a row, the serial numbers will not automatically jump back.

For how to use the SEQUENCE function. This function is only supported on Excel 365.

8. Other quick ways to number in Excel

In addition to the above 2 methods, Free Download also introduces to you 5 other ways to automatically number in Excel such as using the ROW, COUNTA, SUBTOTAL functions. which you can refer to in the article here: HERE

In Excel, automatic numbering saves you time, especially when working with large data tables. You can use the Fill Handle tool by entering the first number, then dragging or double-clicking the right corner of the cell to automatically fill in the number. If you encounter errors, check the cell format or use the ROW() formula for more flexible numbering.

In addition, Excel also provides many popular calculation functions such as SUM, IF, COUNT, which help process data effectively. For those who work with large lists, the Vlookup function will support quick information search. If you are not familiar with VLOOKUP, please refer to the detailed instructions from Free Download to apply correctly.

Micah Soto

Update 23 February 2025