Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • Extract data with helper column in Excel
  • Get nth match with INDEX / MATCH in Excel
  • How to get address of last cell in range in Excel
  • Last row number in range

Data Analysis Examples

  • Conflicting Multiple Conditional Formatting Rules in Excel
  • How to Create Column Chart in Excel
  • Conditional Formatting Color Scales Examples in Excel
  • Conditional Formatting New Rule with Formulas in Excel
  • How To Create Pareto Chart in Excel

Data Validation Examples

  • Data validation must not exist in list
  • Excel Data validation must begin with
  • Excel Data validation exists in list
  • Excel Data validation date in specific year
  • Excel Data validation allow uppercase only

How to add sequential row numbers to a set of data in Excel

by

To add sequential row numbers to a set of data with a formula, you can use the ROW function. See example below:

Formula

=ROW()-offset

Explanation

In the example shown, the formula in B5 is:

=ROW()-4

How this formula works

When not given a reference, the ROW function returns the row number of the current row. In cell B5, ROW returns 5, in cell B6, ROW() returns 6, and so on:

=ROW() // returns 5 in B5
=ROW() // returns 6 in B6

So, to create sequential row numbers beginning with 1, we subtract 4:

=ROW()-4 // returns 1 in B5
=ROW()-4 // returns 2 in B6

This formula will continue to work as long as rows are not added or deleted above the first row of data. If rows are added or deleted above the data, the hardcoded offset value 4 will need to be adjusted as needed.

Worked Example:   How to convert text string to array in Excel

Row numbers in a Table

If we convert the data to a proper Excel Table we can use a more robust formula. Below, we have the same data in “Table1”:

Worked Example:   Circular Reference Problem Solved

The formula in B5, copied down, is:

=ROW()-ROW(Table1)+1

Here, instead of subtracting a hardcoded offset, we calculate the correct offset with this expression:

ROW(Table1)+1

Because the ROW function returns the the first row of table data (not including the header), we need to add 1 to start row numbering at 1. This formula will continue to work correctly when the table is moved, or when rows are inserted or deleted above the table.

Worked Example:   How to get address of first cell in range in Excel

Post navigation

Previous Post:

To count total rows in a range in Excel

Next Post:

Check if range contains a value not in another range in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to check if cell contains number in Excel
  • How to abbreviate names or words in Excel
  • How to convert numbers to text using TEXT function in Excel
  • Manipulating text strings using Left, Mid, Right, Len, Substitute in Excel
  • How to convert text string to array in Excel
  • MONTH function: Description, Usage, Syntax, Examples and Explanation
  • Display the current date and time in Excel
  • Steps to create Dynamic calendar grid in Excel
  • Convert decimal minutes to Excel time
  • Calculate years between dates in Excel
  • Calculate interest rate for loan in Excel
  • Calculate loan interest in given year in Excel
  • CUMPRINC function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate annual compound interest schedule in Excel
  • Example of Future value of annuity in Excel
© 2022 xlsoffice . All Right Reserved. | Teal Smiles