AGGREGATE Formula for Adding Serial Number in Excel

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.


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.

Dataset

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.

Using AGGREGATE Function with COUNTA Function as Argument for serial number in Excel

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

Fill handle

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

Utilizing AGGREGATE Function with COUNT Function as Argument for serial number in Excel

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

Generating Serial Number Without 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.

Using AGGREGATE formula for serial number in Excel without 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.

Using Shortcut for Adding Serial Number in Excel

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

Using SUBTOTAL Formula As an Alternative to AGGREGATE Formula for serial number in Excel

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

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

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

Practice Section


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.


Related Articles

Fahim Shahriyar Dipto

Fahim Shahriyar Dipto

Hello! Welcome to my Excel blog! I am a big fan of MS Excel. I am learning new and exciting things in Excel and writing the process here. I think this will be helpful for you to get used to Excel. Keep visiting our website for new and updated Excel methods.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo