Thứ Hai, Tháng Hai 10, 2025
spot_img
HomeTechnology8 ways to number Excel numbers, number Excel numbers with text

8 ways to number Excel numbers, number Excel numbers with text

Numbering in Excel is no longer a complicated operation but one of the most basic operations when working with Excel. However, numbering in Excel does not have to be done manually, but you will have a way to automatically number in Excel, saving more time and also showing professionalism when working. Below are some ways to number sequentially in Excel for you to choose the appropriate method.

1. Numbering in Excel with the mouse

This is a very simple way to help you type numbers automatically in Excel with just one mouse click, and you can type as many numbers as you want.

First you will Number the first 2 boxesLater delineate the first 2 cells there and see the plus symbol in the bottom right corner of the blacked out area.

Circle the number entered

Then you just need scroll down Go to the serial number you want to use and you're done.

Serial number typed in Excel

In case you Odd-even numbering then do the same. Enter the number for the first 2 boxes according to the rule you want to play, then highlight those 2 boxes and drag them down. The result of the automatically entered serial number still follows the order principle in the first 2 cells.

Parity ordinal numbers in Excel

2. How to number Excel numbers using formulas

We will use it formula =First order cell position + n according to the numbering rule you want. If numbering is consecutive, n=1 will be set.

For example, according to the image below, there will be the formula =C1+3 to automatically number in Excel according to the formula with n=3. Press Enter and the second cell is 4.

Numbering in Excel using formula

Now you will Drag from the 2nd box to the remaining boxes to number them according to the rules you have established.

Numbering formula in Excel

3. Use the SEQUENCE function to number in Excel

When you use the SEQUENCE function in Excel to number sequentially, it is very simple and not complicated. Note, the SEQUENCE Excel function only applies to Office 365 and Excel online versions.

You just need to follow along formula =SEQUENCE(Number of rows to number) then press Enter.

SEQUENCE function in Excel

Immediately the row is returned with the value you entered for the SEQUENCE function in Excel.

Xem thêm  Full latest Dai Hiep Tap Tap giftcode and how to redeem the code

The SEQUENCE function in Excel numbers sequentially

4. How to use the ROW function for sequential numbering in Excel

To use the ROW function for sequential numbering in Excel, you will need to follow these steps ROW() formula. For example, in the image below, you will start entering number 1 from row number 5, then you will need to enter the formula =ROW() – 4. The result of cell number 1 will be displayed.

The ROW function numbers sequentially in Excel

Now you just need to scroll down from number 1 below to enter the next number.

When using the ROW numbering function, if rows are added or deleted, the numbering will be automatically adjusted.

ROW function in Excel

5. How to number Excel sequentially except for blank rows

There will be cases where you need to number the Excel data table, when there are blank rows if we do not delete Excel blank lines. Then you will have to implement the SUBTOTAL function nested with the IF function.

We will do this with the data table below when we need sequential numbering, but minus the blank lines.

Number sequentially except for blank rows

We have the box function syntax as =IF(logical_test; [value_if_true]; [value_if_false]; SUBTOTAL(function_num; ref1;…). In there:

  • logical_test: Condition of IF function.
  • [value_if_true]: Return value if condition is true.
  • [value_if_false]: Return value if condition is false.
  • function_num: The function is understood as a numeric value (Enter 3 is equivalent to the COUNTA function).
  • ref1;…: Area containing sequence numbers.

Applying to the table, we will have the formula =IF(B2=””,””,SUBTOTAL(3,$B$2:B2)). Where SUBTOTAL(3,$B$2:B2)) means:

  • 3 is function_num which is the COUNTA function in SUBTOTAL that counts the number of non-empty cells.
  • $B$2:B2 is a numbered area, you must use the $ value to lock the first cell.

IF(B2=””,””,SUBTOTAL(3,$B$2:B2)) means:

  • B2=””,”” If cell B2 is empty, it will not be numbered.
  • SUBTOTAL(3,$B$2:B2)) If cell B2 has a value, the SUBTOTAL function will be used.

Now at first order cell A2 Enter the formula =IF(B2=””,””,SUBTOTAL(3,$B$2:B2)) and then press Enter.

The SUBTOTAL function is nested with the IF function

Immediately after that, the number 1 will be displayed. You will continue to drag to the remaining cells to number them automatically in Excel.

As a result, we will see that the Excel numbers are ignored and empty Excel lines do not display numbers, and the cells with data are given the next number.

Xem thêm  Fix Windows + Shift + S shortcut not working on Windows 10

The SUBTOTAL function is nested with the sequential numbering IF function

So you have many ways to number in Excel. Depending on how the data table you work with, we choose the appropriate numbering style.

6. Numbering in Excel using Fill

Step 1:

First, you enter all the data into the table, leaving the columns that need to be numbered sequentially. In the example table below, there will be sequential numbers in the STT column. Next enter the first cell as 1 in the STT column, then proceed Highlight the area that needs to be numbered starting from 1 as shown below.

Highlight the number area you want to type

Step 2:

Look up at the Ribbon and find it Home tab. Then look forward Editing section select Fill then select next Series…

Click on Editing

Step 3:

In the Series dialog box that appears, users take turns customizing the following items:

  • Series in: check Rows if numbered in rows, or Columns if numbered in columns.
  • Type: Here there will be a number of options for sequential numbering including Linear to number sequentially in ascending style, Growth sequential numbering to be progressive, Date serial number to date, AutoFill to automatically fill in sequential numbers. .
  • Step value: choose the distance between 2 ordinal numbers in the column.
  • Stop value: number the line to which position.

According to the requirements of the article, section Series print I will check it Columns to number sequentially by column or section Type select Linear ascending number type, Step value Here will enter is 1 and Stop value The final position is 5. Press OK to save the settings.

Customize the serial number in Series

Finally you get the result that the cells are numbered automatically.

Results are automatically numbered

7. Numbering in Excel using Fill Series

Another way is also used Fill Series feature on Excelbut should be applied in cases where you do not need to customize the numbering much like the method above. This method requires dragging the sequential number column. In the STT column, enter the first cell as 1 and then drag down the remaining cells. Thus, the remaining order cells will all be 1.

Enter the first serial number

Click here plus icon as shown below to open the item Auto Fills Optionsthen select Fill Series. The order number will immediately be automatically filled in to the last order number position.

Xem thêm  Latest Dragon Pow Train Your Dragon code and how to change the code

Click to select Fill Series

Instead of dragging the mouse to the position where the serial number is needed, through the Fill Series feature, the serial number will be entered automatically. Besides, users also have an additional custom number when numbering in columns such as sequential numbering, numbering for columns or rows,…

8. How to number Excel numbers with letters

In addition to the usual Excel letter numbering, many data also require you to number with letters. For example, you will see Excel numbering with letters like A1, A2, A3, etc. If used according to normal operations, it will not display the order with letters you want, but must have a few more. other operations.

Method 1: Number Excel numbers with letters via Format Cells

Step 1:

User first Number the data cells as usual using the above methods. Next, highlight the entire number box and press Right-click and select Format Cells in the displayed list.

Select Format Cells

Step 2:

Show new dialog box, user Click the Custom tabLater Type “A”# in the Type box Click OK to finish. If you add more letters to the numbered box, enter them in quotation marks.

Enter the order with letters

Step 3:

As a result, we will get a series of numbered boxes with text as shown below.

Enter the order with Excel letters

You continue to scroll down to continue typing the serial number box with other letters for the data.

The first cell with letters in Excel

Method 2: Use the sequential numbering formula with Excel letters

First, you will manually type the order box with the letter B1 in cell E2 as shown below. Note that you cannot type the order cell in the first cell in each column. Then enter the formula =”B”&RIGHT(E2,LEN(E2)-1)+1. In there:

  • Function RIGHT(text,[num_chars]).
  • Text: The text string containing the characters you want to extract.
  • Num_chars: Specify the number of characters you want the RIGHT function to extract.
  • LEN function (text)
  • Text: is the data cell you want to count

Then press Enter to display the results.

The function combines sequential numbering with Excel text

Then you will also get the next numbered box. We just need to drag from this cell down to the remaining cells to get a sequential numbering sequence with letters as shown below.

Numbering with Excel text

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisment -
Google search engine

Most Popular

Recent Comments