While working with a large dataset, you may find it very boring and monotonous to write the serial number manually. The **Autofill/Fill Handle** is a very familiar way to solve this problem. But if you need to omit a row in the dataset, this method becomes faulty as it shows errors. Excel shines here by providing us with some extraordinary functions e.g. **AGGREGATE function** to make serial numbers automatically. In this article, we’re going to show you how to use the **AGGREGATE formula **for generating serial number in Excel. We’ll also discuss some other functions to make serial numbers in Excel. Let’s get started.

**Table of Contents**hide

## Download Practice Workbook

Download the following practice workbook. It will help you to realize the topic more clearly.

## 3 Methods to Use AGGREGATE Formula for Adding Serial Number in Excel

You can use Excel’s **AGGREGATE** formula for serial number. You have to give both the **COUNT** and **COUNTA **functions as arguments and create serial numbers. We can also use the **SUBTOTAL** function instead of the **AGGREGATE **formula for serial number in Excel. We have taken a dataset of **Student’s CGPA** of a university. Now, we will show you the way to use the formula for adding serial numbers in Excel.

Not to mention, we have used *Microsoft 365* version. You may use any other version at your convenience

### 1. Using AGGREGATE Function with COUNTA Function as Argument

You may use the **AGGREGATE **function where the **COUNTA function** will be an argument. By applying this function, you can easily find the serial number in your dataset. It will reduce your difficulties and save time. Follow the below steps to do that.

**📌**** Steps:**

- First of all, go to cell
**B5**and write down the formula.

**=AGGREGATE(3,5,$C$5:C5)**

Here,

**C5**= The cell from where the counting starts.

**Formula Breakdown:**

**AGGREGATE(3,5,$C$5:C5)→ **The **AGGREGATE** function will take the **COUNTA** function as **function_num** and it takes **5 **as the reference to** Ignore hidden rows**. Then select the first cell of your text column.

- Subsequently, press
**ENTER**and drag down the**Fill Handle**tool for other cells.

- Finally, you will get the serial numbers for your worksheet.

**Read More: ****How to Use Excel AGGREGATE Function with Multiple Criteria**

### 2. Utilizing AGGREGATE Function with COUNT Function as Argument

We also use the **AGGREGATE** function with the **COUNT function** as an argument to make serial numbers in Excel. Basically, it refreshes automatically when you delete a row or any cell. For getting a proper visualization, please follow the steps we have discussed below.

**📌**** Steps:**

- Initially, select cell
**B5**and write up the formula.

**=AGGREGATE(2,5,$B$4:B4)+1**

Here,

The **AGGREGATE(2,5,$B$4:B4)+1 **syntax will take the **COUNT **function as **function_num** and it takes **5 **as the reference to** Ignore hidden rows**. Then it will take the option array of **$B$4:B4**. We enter a +1 for completing the formula immediately after cell** B4**.

- Press
**ENTER**and drag down the formula for other cells.

Eventually, you will get the result.

**Read More: ****How to Aggregate Data in Excel (3 Easy Ways)**

### 3. Generating Serial Number Ignoring Blank Cells

Sometimes, your dataset may contain blank cells where you don’t need to apply for the serial number. But if you use the **Fill Handle** tool for creating serial numbers, then it will cover the blank cell as well as the number. To avoid this consequence, we will use the **IF function** nested with the **AGGREGATE** function for serial numbers in blank cells.

**📌**** Steps:**

- In the very beginning, enter the formula in cell
**B5**.

**=IF(C5=””,””,AGGREGATE(3,5,$C$5:C5))**

**Formula Breakdown:**

**AGGREGATE(3,5,$C$5:C5)→** The **AGGREGATE** function will take the **COUNTA** function as **function_num** and it takes **5 **as the reference to** Ignore hidden rows**. Then select the first cell of your text column.

**IF(C5=””,””,AGGREGATE(3,5,$C$5:C5))→ **This **IF** function will check the logics of blank cells for cell **C5**. If it is **TRUE** then it will return a serial number. Otherwise, it will return a blank cell.

- Press
**ENTER**and continue this formula for other cells using**Fill Handle**. - Finally, you will get the serial number excluding blank cells.

**Read More: ****Combining AGGREGATE with IF Function in Excel (4 Examples)**

## Using Shortcut for Adding Serial Number in Excel

The easiest way to enter the serial number in your sheet is to apply the **AutoFill shortcut**. Though it is a simple way, it becomes difficult for the customer to handle in the case of a blank cell or in the operation of deleting the rows. Putting this aside, we show you the steps to follow for using this method.

**📌**** Steps:**

- First of all, give the serial number
**1**in your first cell and then go to the next cell**B6**and simply enter

**=B5+1**

- Press
**ENTER**. - Drag down the
**AutoFill**feature or press**CTRL**+**D**as a keyboard shortcut to copy the same formula to other cells.

- Lastly, you will get the results.

## Using SUBTOTAL Formula as an Alternative to AGGREGATE Formula

The **SUBTOTAL function** is an alternate of the **AGGREGATE** function to create the serial numbers in your sheet. It also takes the **COUNTA** function as an argument. All you need to put the argument and select the cells where you want to give the serial numbers.

**📌**** Steps:**

- Primarily, select
**B5**and write down the formula.

**=SUBTOTAL(3,$C$5:C5)**

Here, the syntax **SUBTOTAL(3,$C$5:C5) **will take **function_num** i.e. **3** as the argument of **COUNTA** function. Then select the first **$C$5:C5 **cell for the **option array**.

- Consequently, press
**ENTER**and drag it down. - Finally, you will get the serial numbers.

**Read More: ****AGGREGATE vs SUBTOTAL in Excel (4 Differences)**

## How to Auto Generate Serial Number Based on Another Column in Excel

You may give the serial numbers based on other columns. To do this, we will use the **SEQUENCE** and **ROWS** functions. It will create sequential serial numbers in your dataset. Follow the simple steps stated below.

**📌**** Steps:**

- Firstly, select cell
**B5**and insert the formula.

**=SEQUENCE(ROWS(C5:C11),1,G4,G5)**

**Formula Breakdown:**

**ROWS(C5:C11),1,G4,G5→ ** This function will take a data range of **C5:C11 **where **1** stand for the exact same match, and then it will search for the **Starting Serial No** in cell **G4 **and the **Increment **in cell** G5**.

- Press
**ENTER.** - Finally, you will get the result.

## Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.

## Conclusion

That’s all about today’s session. And these are some easy methods for using the **AGGREGATE** formula for adding the serial number in Excel. Please let us know in the comments section if you have any questions or suggestions. For your better understanding please download the practice sheet. Visit our website **Exceldemy**, a one-stop Excel solution provider, to find out diverse kinds of excel methods. Thanks for your patience in reading this article.