Numbering in Excel (Number Rows, Columns, Filtered Data, Alphabetical Order etc.)

Get FREE Advanced Excel Exercises with Solutions!

Numbering in Excel is essential for organizing, sorting, and referencing data, facilitating efficient data entry, and calculations, and enhancing clarity in communication and reporting.

In this Excel tutorial, we will show you various methods, such as utilizing the Fill Handle tool, employing functions like ROW, SEQUENCE, and OFFSET, and generating serial numbers based on criteria.

Here, We have shown a dataset of Serial Number, Student Name, and Attendance in B3, C3, and D3 cells. We have numbered the serial number by Fill Handle and checked the Fill Series from B4 to B13 after inserting 1 in B4.

1- Numbering in Excel

Moreover, we will learn how to automate numbering in alphabetical and Roman formats, handle filtered data, and keep row numbers updated dynamically. Discover tips and tricks for customized formatting, leading zeroes, and even adding bullet points to Excel cells.

Note: We have used Excel for Office 365 to prepare this tutorial.

What is Numbering in Excel?
Why Do We Need Numbering?
How to Number Rows?
  ⏵Using Fill Handles
  ⏵Using Fill Series Command
  ⏵Applying ROW function
  ⏵Using SEQUENCE Function
  ⏵Using OFFSET function
Numbering Rows That Contain  Data
Numbering Columns Automatically
How to Generate Serial Numbers in Excel?
  ⏵Creating Serial Number Sequence Based on Criteria
  ⏵Serial Number Based on Another Column
  ⏵Auto-Filling Repeated Sequence
Numbering in Alphabetical Order
Numbering with Roman Format
Numbering Filtered Data
Update Row Numbers automatically
Auto-Numbering When New Data Added
Numbering with Customized Format
Numbering with Leading Zero
Adding Bullet-Points in Excel
  ⏵Adding Single Bullet Point in a Cell
  ⏵Adding Multiple Bullet Points within a Single Cell
Tips for Numbering Rows

1. What Is Numbering in Excel?

Numbering in Excel is the process of assigning sequential or customized identifiers to rows, columns, or data entries. It enhances data organization, aiding in sorting, filtering, and referencing for efficient data management within spreadsheets.

2-What is Numbering

2. Why Do We Need Numbering?

Numbering in Excel is essential for streamlined data organization and efficient data management. It provides a structured way to identify and reference rows, columns, or specific data entries. Numbering facilitates sorting and filtering tasks, making it easier to analyze and comprehend large datasets. Whether you’re generating serial numbers based on certain criteria, automating alphabetical or Roman formats, or updating row numbers dynamically, this practice enhances the clarity and accessibility of information in your spreadsheet. In essence, numbering is a fundamental tool that simplifies navigation, improves data integrity, and contributes to overall spreadsheet functionality.

3. How to Number Rows in Excel?

Numbering rows in Excel is a vital skill for organized data. This guide covers quick techniques like the Fill Handle and Fill Series, along with functions like ROW, SEQUENCE, and OFFSET, offering versatile approaches to streamline row numbering and enhance data structure.

3.1 Using Fill Handle Tool

Here is the dataset for Student Name, Attendance along with Serial Number which we have numbered 1 to 10 using Fill Handle.

3-Dataset for Using fill handle

  • First, we have to fill up the first two serial numbers inserting 1 and 2 in the B6 and B7.


5-Numbering using fill handle

3.2 Using Fill Series Command

Here, we have given a dataset of Student Names and Attendance respectively in C5 and D5 cells. We have to use Fill Series in the Serial Number to auto-generate number sequence.

6-Dataset for fill series command

  • First, we wrote 1 in B6 and select Home>> Fill>> Series….You can give your step and stop value.

7-Numbering using Fill Series

3.3 Applying ROW Function

Here we have used the ROW function to automatically number rows in Excel. Here we have used the formula below and drag and drop the Fill Handle:


Here ROW() returns 6 and 6-5=1 is the numbering.

8-Using ROW function

3.4 Using SEQUENCE Function

Here, we have used the same data as before but we have used only the SEQUENCE function for row numbering without dragging, We have to use the formula below:


It generates sequence 1 to 10 sequence.

9-Numbering Using Sequence Function

3.5 Using OFFSET Function

Here we have used the dataset for the data like before but used the OFFSET function for automatic numbering in Excel.

10.Dataset for numbering using OFFSET function

  • Now we have to write the formula below in the B7 cell and drag and drop the Fill Handle:


11-Numbering Using Offset function

By using the formula above we can increment the row number in Excel.

4. How to Number Rows That Contain Data in Excel?

Here in this section, we have a dataset with blank rows in Excel. We will discard the blank rows from numbering but when new data is added, it will automatically add the row number. We will use the IF, ISBLANK, and COUNTA functions to create a formula to auto-update row number.

  • We have to write down the formula below in the B6:


12- Numbering blank rows dataset

This formula counts the non-blank cells in the range from C6 to the current row, displaying the count. If C6 is blank, it shows nothing.

13-Inserting formulas to add numbering with blank rows

  • Now, we can add new data or old data, it will automatically number the rows.

14-After adding or removing data, it accurately number the Serial number

5. How to Number Columns Automatically in Excel?

Here, we have a dataset for Product No, Product Name, and Year in B5, B6, and B7 cells respectively. Here we number the C, D, E, and F columns 1,2,3,4. We will use Fill Handle through C, D, E, and F to number them.

15-Dataset for numbering column

16-Result for Numbering Column

6. How to Generate Serial Numbers in Excel?

In this section, we will generate serial numbers in Excel by creating sequences based on criteria or another column and streamline the process by auto-filling repeated number sequences, providing a structured and efficient approach to data organization.

6.1 Create Serial Number Sequence Based on Criteria

We have a dataset of Student Name and Mark in Test. We are going to rank them with RANK.EQ function to rank them according to  Mark in Test.

17. Dataset for creating new dataset based on cruteria


The formula =RANK.EQ(D6,$D$6:$D$15,0) in Excel calculates the rank of the value in cell D6 compared to the range D6:D15. The 0 as the third argument indicates a descending order, so the highest value gets the highest rank. In short, it assigns a rank to the value in D6 based on its position in the specified range, considering higher values as having a higher rank.

18.Giving formula to insert numbering

  • Now we are going to sort the data by selecting the dataset >> Home>> Sort & Filter >>Sort to Smallest to Largest.

20>Sorting the dataset

  • Finally, we get the sorted data.

21. Numbering in Excel based on Mark after sorting

6.2 Create Serial Number Based on Another Column

In this guide, we have a dataset of Serial Number, Student Name, and Marks in Tests. We also have data on Starting No and Increment. We will create a serial number based on another column.

22-Numbering with anither column

  • Now, we have to write the formula below in the B6 cell to get the serial number based on the Starting No and Increment.


The formula =SEQUENCE(ROWS(B6:B15), 1, G5, G6) in Excel creates a column of sequential numbers. The length of the sequence is determined by the number of rows in the range B6:B15, starting from the value in cell G5, and incrementing by the value in cell G6.

23. Result for numbering according to another cell

6.3 Auto-Filling Repeated Number Sequence

Here, we auto-fill the repeated number in Excel. We have a dataset of Serial Numbers, Item and Type. When a cell has consecutive 3 same types of item, the number will begin from 1. We will use nested MOD and SEQUENCE functions

24. Auto numbering using repeated sequence

=MOD(SEQUENCE(9, 1, 1, 1)-1, 3)+1

25-Auto numbering repeated sequence

7. How to Do Numbering in Alphabetical Order in Excel?

In this section, we will use the formula in the B6 cell and drag and drop the fill handle to get alphabetical order. We will use CHAR and ROW functions to create the formula.

=CHAR(ROW() + 59)

59+6=65 (which is the row number) is the ASCII code for A.

26-Numbering with alphabetical worder

8. How to Do Numbering with Roman Format in Excel?

In this section, we have to write the formula below in the B6 cell  to add the Roman sequence, we have to use ROMAN and SEQUENCE functions to create the formula below:


27-Numbering with ROMAN sequence

9. How to Number Filtered Data in Excel?

Here we have a dataset where different student lives in different states. Here we will use numbers with filters.

28-Dataset for filter sequencing


29.Numbering Using Subtotal formula

  • Here, after selecting the whole dataset, press Ctrl + T to turn the dataset into a Table. Now, select any state it will automatically number the rows.

30-Turning dataset into table

  • Now, we can see the result of changing numbering According to living location.

31.Finally numbering using Filtered data

10. How to Automatically Update Row Numbers in Excel?

In this section, we will show you how to update data if a row is deleted. If you have read this article from the beginning then you have seen that we used the ROW function before. The benefit of this method is that it automatically updates the row number if you delete any row from the dataset. Look at the images below to understand that:


32-Dataset for auto update number

  • Then, you will see when the row is deleted, it will automatically number the row numbers.

33-Numbering auto updated

11. How to Do Auto-Numbering in Excel When New Data Added?

In this guide, we will add new data to the dataset. It will automatically number the added data, To do so you have to turn the dataset into Table by selecting the whole dataset and pressing Ctrl+T or Ctrl+L.

34.Dataset for auto numbering when new data wwill be added

  • After that, when you add data, new numbering will automatically be added.

35. When new data added, numbering is updated

12. How to Do Numbering in a Customized Format?

In Excel, you can do numbering based on your preference. By changing the number format, you can easily create a customized format of numbering. It lets you know how you choose data that appears for clarity or to meet specific presentation needs.

  • Select the range of cells B6:B15 >> Press Ctrl+1.

36-Dataset for custom numbering

  • From Category, select Custom >> In the Type field, write \A0 >> Press OK.

37. Adding custom formatting

  • Now we type A1 in the B6.

38-Adding A1 in B6 cell

  • Finally, we will get the custom numbering of rows after dragging the Fill handle.

39-Finally custim number formatting added

13. How to Add Leading Zeroes When Numbering in Excel?

In this part, we will use the CONCATENATE function to add a leading zero before numbering. We have to use the formula below:


The formula =CONCATENATE(“00”,SEQUENCE(10,1)) in Excel adds leading zeros to a sequence of 10 numbers starting from 1, creating a list like “001, 002, 003, … 010”.

40-Numbering with leading zero

14. How to Add Bullet Points in Excel?

In this section, we will learn to add bullet points in Excel for clearer lists and organized information and improve visual appeal and readability in your worksheets with these simple techniques.

14.1 Adding Single Bullet Point in a Cell

In this section, we press ALT+7 (for the Numerical Keypad) to add bullet points in Excel.

42-Adding bullets

Note: Do not forget to enable Num Lock.

14.2 Adding Multiple Bullet Points Within  an Excel Cell

Here, we have a dataset of Student Name in one cell in the B6 cell.

43-Dataset for adding multiple bullets

  • We press ALT + Numpad 7 like before to add bullet points.

44. After giving bullets to one element

  • Finally, we get the data set with multiple bullet points.

45.After adding mutiple bullet points

15. Tips for Numbering Rows in Excel

Efficiently numbering rows in Excel involves a range of tips and tricks to streamline the process and enhance data organization.

  • Use Fill Handle and Fill Series: Leverage the Fill Handle tool and Fill Series command to quickly generate sequential numbers in Excel cells. This is particularly handy for straightforward numbering tasks.
  • Explore ROW, SEQUENCE, and OFFSET Functions: Familiarize yourself with Excel functions like ROW for simple row numbering, SEQUENCE for generating sequences, and OFFSET for more advanced numbering based on specific criteria.
  • Auto-Numbering Based on Criteria: Implement formulas such as =IF(ISBLANK(C6),””,COUNTA($C$6:C6)) to auto-number rows based on specific criteria, ensuring dynamic updates as data changes.
  • Automate Alphabetical and Roman Numbering: Learn how to automate numbering in alphabetical and Roman formats using functions like CHAR, RANK.EQ, and ROMAN.
  • Handle Filtered Data: Utilize Excel tables (Ctrl + L) to efficiently number filtered data, ensuring that the numbering adapts as filters are applied or removed.
  • Custom Formatting: Explore custom formatting options in Excel to tailor the appearance of your row numbers, adding visual appeal to your worksheets.
  • Leading Zeroes for Uniformity: When numbering sequences, use CONCATENATE or & to add leading zeroes for a consistent and visually appealing format.
  • Dynamic Row Number Updates: Implement formulas like =ROW()-5 to ensure that row numbers automatically adjust if rows are added or deleted, maintaining accurate sequencing

Download Practice Workbook

In conclusion, numbering in Excel is a fundamental practice that greatly enhances data organization and analysis within spreadsheets. This comprehensive guide explores various techniques, from using the Fill Handle tool and functions like ROW, SEQUENCE, and OFFSET, to generating serial numbers based on criteria or other columns. It covers advanced topics such as auto-filling repeated number sequences, numbering in alphabetical and Roman formats, handling filtered data, and automatically updating row numbers. Whether it’s creating customized formats, adding leading zeroes, or incorporating bullet points for visual clarity, mastering these techniques empowers users to efficiently manage and present data in a structured and visually appealing manner.

Numbering in Excel: Knowledge Hub

<< Go Back to Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.


Joyanta Mitra
Joyanta Mitra

Joyanta Mitra, a BSc graduate in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology, has dedicated over a year to the ExcelDemy project. Specializing in programming, he has authored and modified 60 articles, predominantly focusing on Power Query and VBA (Visual Basic for Applications). His expertise in VBA programming is evident through the substantial body of work he has contributed, showcasing a deep understanding of Excel automation, and enhancing the ExcelDemy project's resources with valuable... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF