We often use **F****ilters** 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.

**Table of Contents**hide

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

- 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 **F****ilter(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 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,

`=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**.

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

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`

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

`=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**.

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

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

**Read More:** **How to Number Rows Automatically in Excel (8 Methods)**

### 5. Auto Number or Renumber after Filter by COUNTIF Function

The **COUNT****IF **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.

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