How to auto number a column based on cell value on another column in Excel?
Auto number a column based on values on another column
Use VBA to auto number rows based on advanced logic
Auto number a column based on values on another column
If you want to automatically number rows in a column, but only when certain criteria in another column are met (for example, when a column value does not equal "Total"), you can do this easily with a formula. This technique is suitable for small to medium datasets and provides a straightforward way to skip numbering unwanted values, such as subtotals or summary rows.
1. In the first cell of the numbering column (for example, A1), manually enter 1. This acts as the initial value for your numbering sequence. See screenshot:
2. In the second cell where you want automatic numbering to continue (for example, A2), enter the following formula:
=IF(B2="Total","",COUNTIF($A$1:A1,">0")+1)Then press the Enter key. This formula will insert the next number in the sequence if the corresponding value in column B is not "Total." If column B contains "Total," that row will remain blank (not numbered).
Parameter explanation:
- B2: This cell in column B is checked against the condition. You can adjust this reference to match your real data column.
- "Total": Replace "Total" with any value you wish to exclude from numbering.
- $A$1:A1: This range counts the previous numbers in your numbering column. Make sure the starting cell reference matches where you typed 1 in step 1.
Tip: This method is ideally suited for continuous ranges and where you want to skip numbers for rows that meet a specific condition. Be careful when copying formulas beyond the last row of your data, as it may introduce unwanted blank rows or formatting. For dynamic or filtered datasets, consider using helper columns to stabilize the logic if you encounter unexpected numbering gaps.
3. Use the autofill handle to drag the formula down to cover the column range where you want your numbers. The formula will automatically check, row by row, whether the corresponding value in column B warrants a sequence number or should be skipped. See screenshot:
Error reminder: If the referenced columns are sorted or filtered after numbering, check whether your formulas and ranges still align correctly. Accidental misalignment can cause duplicate or missing numbers.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Use VBA to auto number rows based on advanced logic
In situations where formula-based numbering is not flexible enough—for example, if you want to number only visible rows in a filtered table, skip specific cell values, or implement custom logic—a VBA solution is recommended. Using a macro allows you to create dynamic numbering that adapts to filter settings, ignores blanks or specified keywords, and updates as data changes. This is especially useful in larger workbooks or datasets with frequent structural changes.
Advantages:
- Can number only visible (filtered) rows, while skipping hidden rows.
- Supports complex skip logic, such as skipping blank cells or user-defined values.
- Flexible for one-time or repeated numbering across sheets.
Cautions: Macros require you to enable VBA in your workbook, and users should save their files before running any code. Unexpected interruptions or incorrect range selection may cause incomplete numbering, so always review results after execution.
To create a macro for advanced auto numbering, follow these steps:
1. Click Developer > Visual Basic to open the Microsoft Visual Basic for Applications window, then click Insert > Module. Copy and paste the following code into the module:
Sub AdvancedAutoNumbering()
Dim ws As Worksheet
Dim lastRow As Long
Dim numCol As String
Dim critCol As String
Dim skipValue As String
Dim currentNum As Long
Dim i As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
' Set your sheet and columns here
Set ws = ActiveSheet
numCol = "A" ' Column to contain numbering
critCol = "B" ' Column with criteria values
skipValue = "Total" ' Value to skip, can adjust as needed
' Get the last used row in the sheet
lastRow = ws.Cells(ws.Rows.Count, critCol).End(xlUp).Row
currentNum = 1
For i = 1 To lastRow
If ws.Rows(i).Hidden = False Then ' Only number visible rows
If ws.Cells(i, critCol).Value <> skipValue And ws.Cells(i, critCol).Value <> "" Then
ws.Cells(i, numCol).Value = currentNum
currentNum = currentNum + 1
Else
ws.Cells(i, numCol).Value = ""
End If
End If
Next i
End Sub2. Once the code is entered, close the VBA editor. Back in Excel, press the F5 key, or click the Run button. The macro will number the specified column based on your chosen logic—only for visible rows, skipping any rows where the criteria column matches "Total" or is blank.
You can customize the numCol, critCol, and skipValue variables at the top of the macro to match your data layout. This macro can be easily extended—for example, to support multiple skip values, or dynamic column selection using InputBox prompts.
Troubleshooting tips:
- If you encounter "Subscript out of range" errors, confirm column references (for example, column "B" should exist in the sheet, and row numbers match your data).
- If no numbering appears, make sure the worksheet is active and check if your filters are hiding all rows.
- For best results, review your data for merged cells or non-standard formats that may disrupt macro execution.
Summary suggestion: Formula-based solutions are suitable for simple, static numbering requirements, while VBA macros provide enhanced flexibility for larger or dynamic datasets, especially when you need to respect filters or ignore specific values. Before running any VBA solution, always save your work and test on a copy if possible.
Relative Articles:
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