We often need to work with huge datasets in **Excel**. **AutoFilling** numbers with **Filter** can make our task much easier. So, we should know how to **AutoFill numbers in Excel **with **Filter**. In this article, I will show you two methods on how to **Autofill** numbers in **Excel** with **Filter**.

## Download Workbook

This is our dataset. We have the **Name** of some students along with their **Department**. In the **Serial**Â column, we are going to list the students serially with **Filter** activated.

## 2 Methods to AutoFill Numbers in Excel with Filter

### 1. AutoFill Numbers in Excel with Filter Using the SUBTOTAL Function

In this section, I will describe how to **AutoFill** numbers in **Excel** with **Filter** using **the ****SUBTOTAL**** function**.

- First, select the cell range
**B5:B14**. Then, open**Home**tab >> go to**Editing**>> from**Find & Select**>> select**Go To Special**

A **dialog box** will pop up.

- Then, select
**Visible cells only**>> select**OK**.

- Then, write the formula in the
**first selected cell**or in the**Formula bar**.

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

**Formula Breakdown**

**$C$5:C5** >> This denotes expanding **range**. The first element of the range is locked, so the first element will always remain the same. The second element will change if we drag down the formula.**Â Â Â Â Output is**>> â€śTomâ€ť**Â Â Â Â Explanation**>> Here, it is the cell value of **C5** cell.**3,$C$5:C5** >> In this case, **3** denotes the **COUNTA** function. In the picture below we have given a list of numbers that denote different functions in the case of the **SUBTOTAL** function.

**SUBTOTAL(3,$C$5:C5)** >> Gives us the total of cells with values in the selected range.**Â Â Â Â Output is **>> 1 2 3 4 5 6 7 8 9 10**Â Â Â Â Explanation**>> It returns a range of numbers for the selected cell range

- Then press
**CTRL + ENTER**as itâ€™s an array formula.**Excel**will return**number of series**for the selected range.

- Now
**Filter**the dataset with**Physics**and**Finance**in the**Department**First, select the**drop-down icon**.

- Then select
**Physics**and**Finance**.

**Excel** will rearrange the **Serial** sequentially, even if they are not in a sequence in the initial dataset.

**Read More:**** Excel Formulas to Fill Down Sequence Numbers Skip Hidden Rows**

### 2. Using the AGGREGATE function to AutoFill Numbers with Filter

Now, I will show how to **AutoFill** numbers in **Excel** with **Filter** applying **the AGGREGATE function**.

**STEPS**

- First, apply
**Visible cell only**to the range you are going to work with following**method 1**. - Then write down the formula in the
**Formula**bar

`=AGGREGATE(3,7,$D$5:D5)`

** **

**Formula Breakdown**

**$C$5:C5** >> This denotes expanding **range**. The first element of the range is locked, so the first element will always remain the same. The second element will change if we drag down the formula.**Â Â Â Â Output is **>> â€śTomâ€ť**Â Â Â Â Explanation **>> Here, it is the cell value of **C5** cell.

**3,7,$C$5:C5** >> In this case, **3** denotes the **COUNTA** function. I have shown the list of functions that Excel shows in the image below.

In this case, **7** denotes the option **Ignore hidden rows and error values**. In the picture below we have given a list of numbers that denote different **options** in the case of the **AGGREGATE** function.

**AGGREGATE(3,7,$C$5:C5)** >> It returns us the **Serial** numbers for the selected **range**.**Â Â Â Â ****Output is **>> 1 2 3 4 5 6 7 8 9 10**Â Â Â Â ****Explanation**>> It returns a range of numbers for the selected cell range

- Then press
**CTRL + ENTER**as itâ€™s an array formula.**Excel**will return a**number of series**for the selected range

- Then
**Filter**from the**Department**column by selecting**Physics**and**Finance**following**method 1**.**Excel**will rearrange the**Serial**

**Read More:** **How to Auto Number or Renumber after Filter in Excel (7 Easy Ways)**

## Practice Workbook

As you see, it is tricky to **AutoFill** numbers with **Filter**. Thatâ€™s why you should practice how to **AutoFill** numbers in **Excel** with **Filter**. I have attached a practice sheet for you to practice.

## Conclusion

In this article, I have explained two methods to **AutoFill** numbers with **Filter**. I hope you will find this helpful. Lastly, if you have any thoughts or suggestions, please leave them in the comment box below. You can check out our website for any type of **Excel** related queries.