How to Auto Number or Renumber after Filter in Excel (7 Easy Ways)

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


Download Practice Workbook


7 Easy Ways to Auto Number or Renumber after Filter in Excel

We will now walk you through 7 easy ways to auto 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 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.

how to auto number or renumber after filter in excel

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

how to auto number or renumber after filter in excel

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.

  • Then we select, Finance.

how to auto number or renumber after filter in excel

  • 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 Methods)


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.

how to auto number or renumber after filter in excel

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

how to auto number or renumber after filter in excel

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

how to auto number or renumber after filter in excel

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 the 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

how to auto number or renumber after filter in excel

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

how to auto number or renumber after filter in excel

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


3. Auto Number or Renumber after Filter Using AGGREGATE Function

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. You can learn about other options for the AGGREGATE function here.

how to auto number or renumber after filter in excel

  • Now by dragging down or double-clicking the Fill Handle to use the AutoFill feature of Excel we will get values in corresponding cells.

how to auto number or renumber after filter in excel

  • 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: How to Autofill Numbers in Excel without Dragging (5 Quick Methods)


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.

how to auto number or renumber after filter in excel

  • 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 Number Rows Automatically in Excel (8 Methods)


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)

how to auto number or renumber after filter in excel

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.

how to auto number or renumber after filter in excel

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.

how to auto number or renumber after filter in excel


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.

how to auto number or renumber after filter in excel

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

  • 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

how to auto number or renumber after filter in excel

  • A new window will open.

how to auto number or renumber after filter in excel

  • Go to Insert and select Module.

how to auto number or renumber after filter in excel

  • 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

how to auto number or renumber after filter in excel

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 used  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. But 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.

how to auto number or renumber after filter in excel


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 comment and feedback below. For any of your Excel-related queries please have a look at our website. Our team would be happy to help you out.


Related Articles

Lutful Hamid
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo