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.

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,

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

• Then we select, Finance.

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

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,

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

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

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

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,

• Pressing the Enter key we will get the result in the B5 cell. You can learn about other options for the AGGREGATE function here.

• 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: 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,

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

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,

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

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.

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

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

• Excel Auto Generate Number Sequence (5 Simple Ways)
• How to Repeat Number Pattern in Excel (5 Methods)
• How to Add Sequence Number by Group in Excel (2 Formuals)
• Fill Column in Excel with Same Value (9 Tricks)