How to Use Number with Filter in Excel (5 Useful Methods)

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for how to use number with filter in Excel, then you are in the right place. In our practical life, we often need to filter our data and the numbering of the filtered data becomes distorted. Excel offers us various methods so that we can sort or filter our data with proper numbering. In this article, we’ll try to discuss how to number in Excel with filters.


How to Use Number in Excel with Filter: 5 Useful Methods

Excel offers various methods to number with filters. To show this we have made a dataset named Company Sales in Different Branches. It has column headers as Serial, Company Name, Name of Employee, Branch Location, and Sales. The dataset is like this.

how to number in excel with filter

Let’s see the methods to number in Excel with filters.


1. Utilizing SUBTOTAL Function to Use Number with Filter in Excel

We can use the SUBTOTAL function when we need to add numbering as well as filter our desired data. We have to add numbers in the selected cells of the following picture. Another purpose of ours is to filter Amitech, Consortium, Spectrum, and Huawei from Company Name. Let’s follow the instructions below to use the SUBTOTAL formula in Excel for creating a serial number with filter.

how to number in excel with filter using SUBTOTAL function

Firstly, write the following formula in the B5 cell.

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

 Formula Breakdown

  • $C$5:C5→ This denotes expanding range. The first element of the range i.e. $C$5 is locked, so the first element will always remain the same. The second element will change if we drag down the formula.
    • OutputC5:C5
  • 3,$C$5:C5 → 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 → 1
[/wpsm_box]

how to number in excel with filter using SUBTOTAL function

Secondly, press ENTER to get the output as 1.
Thirdly, use Fill Handle to get the other values in Cells B5:B14.

how to number in excel with filter using SUBTOTAL function

Consequently, we’ll get the output like this as added numbers in the B Column.

how to number in excel with filter using SUBTOTAL function

Fourthly, to filter the required Company Names, select the whole dataset > go to Data > click Filter.

how to number in excel with filter using SUBTOTAL function

As a result, we’ll have the filter icons in every Column Header.
Fifthly, select the icon in the Company Name column header > select Amitech, Consortium Plus, Spectrum > click OK.

how to number in excel with filter using SUBTOTAL function

Eventually, we’ll get the output like this where specified Company Names are filtered and Serial Numbers in a chronological way.

how to number in excel with filter using SUBTOTAL function


2. Using AGGREGATE Function

The objective of usage of the AGGREGATE function is the same as the SUBTOTAL function with different strings. We need to add numbers in the selected cells and filter Jane, Nicolas, and Thomas from the Name of Employee column.

how to number in excel with filter using AGGREGATE function

Firstly, write the following formula in the B5 cell.

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

Formula Breakdown

  • $D$5:D5 → Denotes the expandable range
  • 3 → Activates the COUNTA function
  • 7 → Denotes Ignore Hidden Rows and Error Values
  • AGGREGATE(3,7,$D$5:D5) → Denotes the sum of the COUNTA excluding hidden rows and error values

 using AGGREGATE function

Secondly, press ENTER and use Fill Handle to get the output of numbers in a chronological manner.

 using AGGREGATE function

Thirdly, to filter the Name of Employee create a filter option in the Column Headers as we have done before.
Fourthly, click the filter icon in the column of Name of Employee > select Jane, Nicolas, Thomas > click OK.

how to number in excel with filter using AGGREGATE function

As a result, we’ll get an output like this where specified names are filtered and numbers are added in a chronological manner.

how to number in excel with filter using AGGREGATE function

Read More: How to Create a Number Sequence in Excel Based on Criteria


3. Applying ROW Function to Use Number with Filter in Excel

We can add the ROW function to the number in Excel. We need to number the following selected cells and will try to filter California from the Branch Location column.

how to number in excel with filter using ROW function

To do this, firstly, write the following formula in the B5 cell.

=ROW()-4

Formula Breakdown

  • ROW() → Denotes the number of rows (Here, it is B5 Cell)
    • Output 5
  • ROW()-4 Denotes the number after subtracting 4
    • Explanation → 5-4 =1
    • Output → 1

how to number in excel with filter using ROW function

 Similarly, as before, press ENTER and use Fill Handle to get numbers in the Serial column.

how to number in excel with filter using ROW function

Thirdly, to filter California, select the filter mark in the Branch Location column > select California > click OK.

how to number in excel with filter using ROW function

Eventually, we’ll get the output like this.
Here, we can see that the numbers in the B Column are not sorted chronologically.

how to number in excel with filter using ROW function


4. Using COUNTIF Function

The COUNTIF function is another function to number with filters. We need to add numbers in the B Column and filter California from Branch Location.

how to number in excel with filter using COUNTIF function

Firstly, write the following formula in the B5 cell.

=COUNTIF($E$5:E5,E5)

Formula Breakdown

  • $E$5:E5 → Denotes the expandable range
  • E5) → Denotes the value in the D5 cell
    • OutputCalifornia
  • COUNTIF($E$5:E5, E5) →The COUNTIF function counts the number of cells with the value of D5 cell.

how to number in excel with filter using COUNTIF function

Secondly, press ENTER and use Fill Handle and get the output like this.
Importantly, we can see that the numbers are not sorted chronologically. But it will be chronological when we’ll filter our required place.

how to number in excel with filter using COUNTIF function

Thirdly, select the filter mark in the Branch Location column > click California > click OK.

 using COUNTIF function

Eventually, we’ll find our output like this where the numbers are no more unsorted and filtration of California has been done.

 using COUNTIF function

Read More: How to Add Automatic Serial Number with Formula in Excel


5. Utilizing Combined Formula

We can use the combination of IF, LEFT, and SUBTOTAL functions to number Excel Rows. We have made the following dataset to number the marked Rows.

combined function

Firstly, we’ll use the combination of IF and LEFT functions to be sure that the entities are a person or not. After this surety, we’ll apply the SUBTOTAL function to the output.
In this case, we will first determine whether the Company Name/Employee Name column entries are of people or not. There are two spaces in the sheet before the name of each department. As a result, any non-name entry will have two spaces on the left. Using this condition, we can determine whether the entry is a person’s name or not.
Eventually, write the formula in the F5 cell like this.

=IF(LEFT(C5,2)=” “,””,1)

Formula Breakdown

  • LEFT(C5,2) Fetches two characters from the left of cell C5.
    • Output → Double space
  • LEFT(C5,2)=” ” This logical test denotes whether the left two characters of cell C5 are two spaces or not.
    • Output → TRUE
    • Explanation → The left two characters is equal to double spaces.
  • IF(LEFT(C5,2)=” “,””,1) The IF function returns a blank if the above condition is satisfied. Otherwise, it returns the value
    • Output → Blank
    • Explanation → In this case the left two characters are double space.

combined function

 After pressing ENTER and using Fill Handle, we’ll get the output like this.

combined function

After determining whether the name is an employee or not, we’ll add numbers easily in the B Column by writing the following formula.

=IF(F5=””,””,SUBTOTAL(3,$F$5:F5)-1)

Formula Breakdown

F5=”” → Determines whether the value in the F6 cell is blank.
Output → TRUE
Explanation → F5 cell
is blank.
SUBTOTAL(3,$F$5:F5)-1 →
Returns value of the expandable range $F$5:F5 as shown in method 2.
IF(F5=””,””,SUBTOTAL(3,$F$5:F5)-1) → 
The IF function returns the value blank if the above condition is satisfied. It gives the value of SUBTOTAL(3,$F$5:F5)-1 if the corresponding cell is in Person the column is not blank.
Output → Blank
Explanation → F5 cell
is blank.

combined function

 Eventually, pressing ENTER and using Fill Handle we find the following output.

combined function


Things to Remember

  • We can use the Row function to number Rows This function is not related to filtering. That’s why after filtering, we can see from Method 4 that numbering is no more in a chronological manner. Because the ROW function doesn’t have any string which will detect the hidden or unused Rows like the SUBTOTAL, AGGREGATE, or COUNTIF functions do. So, the ROW function is not the best option when we need both numbering and filtering.
  • We can use the Combined formula to number individual types such as numbering the names in Amitech or Spectrum or Consortium Plus.For example, in the case of Spectrum type, we just need to change the formula from IF(F10=””,””,SUBTOTAL(3,$F$5:F10)-1) to IF(F10=””,””,SUBTOTAL(3,$F$9:F10)-1).

Download Practice Workbook


Conclusion

We can number Excel Rows as well as filter effectively if we study this article properly. Please feel free to comment any queries or suggestions down below.


Related Articles

What is ExcelDemy?

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

Hello, myself Shajratul Alam Towhid. Basically, I am a Naval Architect who wants to expand knowledge in the field of Microsoft Excel. I wish all of my articles will be beneficial for the readers.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo