How to auto number or renumber after filter in Excel?
When working with large datasets in Excel, it's a common situation to assign a series of numbers as row identifiers to organize or track entries. However, after applying a filter to display specific rows, the visible series or row numbers remain the same as in the original dataset—they do not update to reflect the filtered, visible order. This can create confusion and make it difficult to present a clearly numbered list for reporting or presentation purposes. Sometimes you may want the series to be dynamically re-sequenced so that only the visible (filtered) rows display sequential numbering, as illustrated in the screenshots below. In this article, we explore how to auto number or renumber after filter in Excel to help improve clarity and manageability of your filtered data views.
Auto number or renumber after filter with VBA code
Auto number or renumber after filter using Excel formula (SUBTOTAL and ROW)
Auto number or renumber after filter with Kutools for Excel
 |  |  |
Auto number or renumber after filter with VBA code
In Excel, when your data is filtered, the standard Autofill handle does not fill series numbers according to visible rows. It instead continues the sequence including hidden rows, which often means numbering will reference the original row positions instead of just the filtered data. Manually typing numbers for each visible row quickly becomes tedious and is not practical for large lists. In such cases, using a simple VBA code can efficiently renumber rows in your filtered data range, saving considerable manual effort and reducing errors.
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window. This is the main interface for writing and running VBA code in Excel.
2. In the VBA window, click Insert > Module to add a new module. Paste the following macro into the Module window.
VBA code: Auto number or renumber after filter
Sub Renumbering()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type: = 8)
Set WorkRng = WorkRng.Columns(1).SpecialCells(xlCellTypeVisible)
xIndex = 1
For Each Rng In WorkRng
Rng.Value = xIndex
xIndex = xIndex + 1
Next
End Sub
3. After pasting the code, press the F5 key to run it. A prompt box will appear to ask you to select the range of cells that you want to auto number or renumber for the visible filtered rows. See screenshot:
4. Click OK after selecting your target range. The visible (filtered) cells within your selected range are now renumbered in sequence – as shown below:
Notes and Tips: This code rewrites the numbering in your selected range and will overwrite original series numbers. If you later remove the filter, the original (pre-filtering) order will be lost, and only the filtered sequence numbers will remain. To avoid loss of original data, it is recommended to make a backup of your worksheet or the relevant column before running this macro. Additionally, ensure that macros are enabled for your Excel workbook, and exercise caution if you wish to undo changes (as actions in macros cannot always be undone with Ctrl + Z).
Auto number or renumber after filter using Excel formula (SUBTOTAL and ROW)
For many users, a formula-based solution is preferable because it remains dynamic—automatically updating the row numbers whenever you change the filter criteria. The combination of Excel’s SUBTOTAL and ROW functions can generate a visible-only sequence, so only the rows displayed after filtering are numbered sequentially. This solution is especially useful when you need to repeatedly apply filters or expect your data to change over time, as there is no need to re-run VBA or re-enter numbers—everything updates in real time.
Applicable scenarios: Use this approach if you want numbering to update automatically whenever you apply or alter a filter, without the need for manual re-application or VBA scripts. This is ideal when presenting dynamic reports or shared spreadsheets.
1. In the first cell of your desired numbering column—for example, suppose your data range starts at row 2 (with headers in row 1) and your numbering should begin in cell A2—enter the following formula:
=SUBTOTAL(3, $B$2:B2)Here, $B$2:B2 refers to a column in your data that is always filled (such as the name or ID column). You may need to adjust the column reference to point to a suitable column in your table. Keep the first part absolute ($B$2) and let the row in the second part increment (e.g., B2, B3, ...).
2. Press Enter to confirm. To apply the numbering to the whole list, drag the fill handle down along your numbering column to cover all rows.
After filtering your data, only visible rows will display ascending numbers (1, 2, 3, …), while hidden rows are automatically excluded. This method does not require manual updates after each filter change—the sequence will always match the number of visible rows.
Additional tips: Ensure the reference column used in the formula does not have blanks in the part of the data you wish to number. If your data starts in a row other than row 2, adapt the absolute cell reference accordingly (e.g., $C$5:C5 if your data begins in row 5 and you are using column C as your reference). If you encounter any numbering gaps, check for hidden rows or blank cells in your reference range. If you are using an Excel Table, the formula can be further simplified using structured references (e.g., =SUBTOTAL(3,OFFSET([@Column],0,0,ROW()-ROW(Table1[#Headers])))), but the standard approach above works for most situations.
Auto number or renumber after filter with Kutools for Excel
If you have installed Kutools for Excel, the Insert Sequence Number feature enables you to quickly and easily insert sequence numbers into filtered data. Unlike the default Excel functionality—which cannot recognize only visible rows for filling numbers—Kutools can automatically adapt to your filtered selections, significantly speeding up the numbering process and minimizing manual work. This feature is especially helpful for complex datasets or when numbering needs to be customized, such as specifying prefixes, starting numbers, or step increments.
After installing Kutools for Excel, follow these practical steps:
1. Select the cells you want to renumber in your filtered data view. It's best to select only the visible rows to avoid confusion.
2. Go to Kutools > Insert > Insert Sequence Number, as shown:
3. In the Insert Sequence Number dialog, click New to define your numbering pattern.
4. Create your sequence list using the configuration controls in the left-bottom section, including starting number, increment, number of digits, and other options as needed.
5. When satisfied, click Add to save your sequence pattern, which now appears in the sequence list in the dialog box.
6. Click Fill Range. The sequence will be instantly applied to your specified (visible) data range. Only rows visible after filtering will be numbered in order, as shown here:
Note: The Insert Sequence Number feature of Kutools for Excel not only saves time for large datasets but also reduces errors by applying numbering only to filtered rows. You can repeat or update the numbering anytime—especially useful if your filter changes frequently. If numbering across merged cells or irregular ranges, review the preview in the dialog for the final appearance.
Download and free trial Kutools for Excel Now !
Demo: Auto number or renumber after filter with Kutools for Excel
Related article:
How to auto number merged cells in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in