How to AutoFill Numbers in Excel with Filter (2 Methods)

Get FREE Advanced Excel Exercises with Solutions!

We often need to work with huge datasets in Excel. Auto Filling 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.


How to AutoFill Numbers in Excel with Filter: 2 Methods

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.

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

STEPS:

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

How to AutoFill Numbers in Excel with Filter

A dialog box will pop up.

  • Then, select Visible cells only >> select OK.

How to AutoFill Numbers in Excel with Filter

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

How to AutoFill Numbers in Excel with Filter

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 a number of series for the selected range.

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

How to AutoFill Numbers in Excel with Filter

  • Then select Physics and Finance.

How to AutoFill Numbers in Excel with Filter

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

Read More: How to Auto Number or Renumber after Filter in Excel


2. Using the AGGREGATE function to AutoFill Numbers with Filter

Now, I will show how to AutoFill formula in Excel with Filter by 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)

How to AutoFill Numbers in Excel with Filter

Formula Breakdown

$C$5:C5 >> This denotes an 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

How to AutoFill Numbers in Excel with Filter

  • 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 Cells in Excel


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.


Download Workbook


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.


Related Articles


<< Go Back to Autofill Numbers | Excel Autofill | 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.
Akib Bin Rashid
Akib Bin Rashid

AKIB BIN RASHID, a materials and metallurgical engineer, is passionate about delving into Excel and VBA programming. To him, programming is a valuable time-saving tool for managing data, files, and internet-related tasks. Proficient in MS Office, AutoCAD, Excel, and VBA, he goes beyond the fundamentals. Holding a B.Sc in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, MD AKIB has transitioned into a content development role. Specializing in creating technical content centred around Excel and... Read Full Bio

2 Comments
  1. I have a list of library books with various headings subheadings and comments interspersed with the data.
    I can filter these out by selecting a column that only contains data if the line is an entry for a library book.
    So I want to apply the filter (which I can do using your methods) but I want that number to be permanent regardless of what filter i apply.

  2. Hi David
    As far as I understand the case, you want to apply a filter to a row but keep the numbers in the cell permanent.
    Unfortunately, when Excel executes a filter, it hides the entire row. If any cell contains numbers, excel will hide them too. However, as this article explains, you can maintain the serial when applying a filter.
    In case I have misunderstood your requirement, please mail us the excel file at [email protected] and explain the problem a bit more.
    Thank you.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo