We often use Filters in Excel to sort data. Sometimes we may need to arrange data from smaller to bigger or vice versa. It is tiresome to arrange rows manually after using the Filter. We can arrange rows automatically after filtering. We have shown 7 different ways on how to auto number or renumber after Filter in Excel. These ways will help you save time and improve your efficiency.
Suppose a company has employee records in a worksheet. The worksheet has four columns: Serial, Name, Department, and Joining Date. For the purpose of analysis, the company may require to arrange the data according to departments or joining dates. If we Filter according to Joining Date, the serial numbers get dishelved. To arrange in normal order we may require to use functions or other features of Excel. We will see how to do so using 7 different ways.
How to Auto Number or Renumber after Filter in Excel: 7 Easy Ways
We will now walk you through 7 easy ways to auto fill number or renumber after Filter in Excel.
1. Using SUBTOTAL to Auto Number or Renumber after Filter in Excel
We can auto number rows using the SUBTOTAL function in Excel. To do so, instead of numbering the rows manually we will use this function to number rows.
STEPS:
- In cell B5 we will type,
=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>> C5:C5
  Explanation>> Here, only one cell contains in the range.
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
    Explanation>> There is only one cell with a value in the C5:C5 range.
- Pressing the Enter key we will get the result in the B5 cell.
- Now by dragging down or double-clicking the Fill Handle to use the AutoFill feature of Excel we will get values in corresponding cells.
Now, if we arrange the cells according to Joining Date using Filter(s), we will see that the serial numbers get sorted automatically.
- To Filter, we click on the arrow in the header of the table.
- To get Filter, we will need to go to the Data tab and click Filter.
- If we filter data and choose the Finance department, the resultant rows will be numbered automatically.
Read More: How to AutoFill Numbers in Excel with Filter
2. Auto Number or Renumber after Filter by Combined Formula
We may not always have data in continuous cells. In this case, the data may have been sorted by department. We will use the IF function and the LEFT function first to ascertain whether the entries are of a person or not. Then we will use the SUBTOTAL function to determine the serial.
In this case, we will first determine whether the entries in the Name column are of persons or not. Notice that in the sheet, before the name of every department there are two spaces. So, any entry that is not a name will have two spaces on the left. Using this condition, we can determine whether the entry is the name of a person or not.
STEPS:
- To do so, in the F5 cell, we type,
=IF(LEFT(C5,2)="Â ","",1)
Formula Breakdown
LEFT(C5,2)
>> Fetches two characters from the left of cell C5.
    Output is>> Double space
LEFT(C5,2)="Â "
>> This logical test denotes whether the left two characters of cell C5 are two spaces or not.
    Output is>> TRUE
    Explanation>> The left two characters are equal to double spaces.
IF(LEFT(C5,2)="Â ","",1)
>>Â The IF function returns a value blank if the above condition is satisfied. Otherwise, it returns the value 1.
    Output is>> Blank
    Expanation>> In this case the left two characters are double space.
- Pressing the Enter key we will get the result in the E5 cell.
- Now by dragging down (double-clicking won’t work as there are not always values in the contiguous cells) the Fill Handle to use the AutoFill formula of Excel, we will get values in corresponding cells.
After determining whether the name is of a person or not, we can easily number the Serial column. To do so, we type in the B5 cell,
=IF(F5="","",SUBTOTAL(3,$F$5:F5)-1)
Formula Breakdown
F5=""
>> Determines whether the value in F6 cell is blank.
   Output is>> 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 1.
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 is>> Blank
  Explanation>> F5 cell is blank.
- Pressing the Enter key we will get the result in the B5 cell.
Now by dragging down or double-clicking the Fill Handle to use the AutoFill feature of Excel, we will get values in corresponding cells.
We may want to put serial from scratch in case of every department. In that case, we just have to change the moving range for each of the departments. For the Finance department, we will change the range to,
$F$8:F8
- Pressing the Enter key we will get the result in the B8 cell. Now by doing so for all the departments and dragging down the Fill Handle, we will get serial numbers starting from 1 for each of the departments.
3. Auto Number or Renumber after Filter Using AGGREGATE Function
The AGGREGATE function in Excel is used associating with different functions in Excel like AVERAGE, COUNT, MAX, MIN, SUM, PRODUCT, etc., with the option to ignore hidden rows and error values to get certain results. We can use this function to perform different operations. In this article, we will show how can we use the AGGREGATE function to number rows after filtering.
STEPS:
To do so, in cell B5 we type,
=AGGREGATE(3,7,$D$5:D5)
Formula Breakdown
$D$5:D5
>> Denotes the expandable range
3>> Denotes COUNTA function
7>> Denotes Ignore Hidden Rows and Error Values
AGGREGATE(3,7,$D$5:D5)
>> Denotes the sum of COUNTA excluding hidden rows and error values
- Pressing the Enter key we will get the result in the B5 cell.
- Now by dragging down or double-clicking the Fill Handle to use the AutoFill feature of Excel, we will get values in corresponding cells.
- We can filter data using the steps shown in method 1. If we filter data and choose the Finance department, the resultant rows will be numbered automatically.
Read More: [Fix] Excel Fill Series Not Working
4. Using ROW Function to Auto Number or Renumber after Filter
The ROW function in Excel denotes the number of rows. We can use this function to auto number or renumber after Filter in Excel.
STEPS:
- To do so, in cell B5 we type,
=ROW()-4
Formula Breakdown
ROW()
>> Denotes the number of rows.
   Output is>> 5
   Explanation>> B5 cell  is in row 5
ROW()-4
>> Denotes the number after subtracting 4
   Output is>> 1
   Explanation>> 5-4
- Pressing the ENTER key we will get the result in cell B5
- Now by dragging down or double-clicking the Fill Handle to use the AutoFill feature of Excel we will get values in corresponding cells.
- We can filter data using the steps shown in method 1. If we filter data and choose Finance department, the resultant rows will be numbered automatically.
5. Auto Number or Renumber after Filter by COUNTIF Function
The COUNTIF function in Excel counts the number of cells containing texts in the selected range. We can use this function to auto number rows after using Filter(s) in Excel.
STEPS:
- To do so, in cell B5 we type,
=COUNTIF($D$5:D5,D5)
Formula Breakdown
$D$5:D5
>> Denotes the expandable range
D5
>> Denotes the value in D5 cell
     Output is>>  Finance
COUNTIF($D$5:D5,D5)
>> COUNTIF function counts the number of cells with the value of D5 cell
- Pressing the ENTER key we will get values in the Serial column of the table.
Notice that the values look jumbled. But if we choose any department by filtering the Department column, we will get the correct serial.
We can filter data using the steps shown in method 1. If we filter data and choose Finance department, the resultant rows will be numbered automatically.
Read More: How to Autofill Numbers in Excel Without Dragging
6. Using Fill Series to Auto Number or Renumber after Filter
We can use the Fill Series feature of Excel to auto number the rows.
STEPS:
- To do this we need to have the Serial column blank.
- Then we will select the entire Serial column.
Put 1 in the first cell, in this case, cell B5.
Fill all the cells of the column using Fill Handle. By dragging down or double-clicking the Fill Handle to use the AutoFill feature of Excel we will get values in corresponding cells. We will use the AutoFill Options as shown in the picture below. If your autofill option is not showing you can turn it on from the Options Menu.
- We need to select Fill Series.
- After clicking the Fill Series, the correct order of serial will appear.
- We can filter data using the steps shown in method 1. If we filter data, and choose Finance department, the resultant rows will be numbered automatically.
7. Auto Number or Renumber after Filter Using VBA
We can use VBA code to the auto number or renumber after filtering in Excel.
STEPS:
To use the VBA editor,
- Open the Developer tab >> select Visual Basic
- A new window will open.
- Go to Insert and select Module.
- A new Module will open.
- Copy the code below and paste it into the Module.
Sub Autonumbering()
Dim X As Range
Dim Y As Range
On Error Resume Next
xTitleId = "Autonumbering"
Set Y = Application.Selection
Set Y = Application.InputBox("Range", xTitleId, Y.Address, Type:=8)
Set Y = Y.Columns(1).SpecialCells(xlCellTypeVisible)
xIndex = 1
For Each X In Y
X.Value = xIndex
xIndex = xIndex + 1
Next
End Sub
Here, we have initiated a Sub Procedure named Autonumbering. We have declared two variables X and Y. Both variables are Range type. We have used For Loop to go to the next cell.
Also use On Error Resume Next to continue while occurring any error.
To run the code from the Run tab select Run Sub/UserForm. Or you can also use the F5 key to run the code. A new dialogue box will appear. Type the range over which you want to put a correct serial number.
- You will get the correct serial in the Serial column.
We can filter data using the steps shown in method 1. If we filter data, and choose Finance department, the resultant rows will be numbered automatically.
Things to Remember
We can use different methods to auto number or renumber rows after Filtering. However, each of the methods has its specific purpose. Be sure to determine which method suits your job.
Practice Section
We have included a practice section in the worksheet provided so that you can exercise the methods yourself.
Download Practice Workbook
Conclusion
In this article, we have demonstrated 7 different ways on how to auto number or renumber after Filter in Excel. Hope these methods will guide you to perform this operation in Excel. Please leave your comments and feedback below. Our team would be happy to help you out.