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

Lookup and Reference Examples

  • How to use Excel VLOOKUP Function
  • How to get last row in numeric data in Excel
  • How to use Excel OFFSET function
  • How to get address of last cell in range in Excel
  • Convert text string to valid reference in Excel using Indirect function

Data Analysis Examples

  • How To Compare Two Lists in Excel
  • How to Use Solver Tool in Excel
  • How To Filter Data in Excel
  • Excel Pie Chart
  • Create Scatter Chart in Excel

Data Validation Examples

  • Excel Data validation don’t exceed total
  • Excel Data validation allow uppercase only
  • Excel Data validation must contain specific text
  • Excel Data validation must not contain
  • Excel Data validation date in specific year

How to get first row number in range in Excel

by

You can get the first row (i.e. the starting row number) in a range with a formula based on the ROW function.

Formula

=MIN(ROW(range))

Explanation

In the example shown, the formula in cell F5 is:

=MIN(ROW(data))

where data is a named range for B5:D10

How this formula works

When given a single cell reference, the ROW function returns the row number for that reference. However, when given a range that contains multiple rows, the ROW function will return an array that contains all row numbers for the range. In the example shown the array looks like this:

Worked Example:   Check if multiple cells have same value in Excel

{5;6;7;8;9;10}

If you want only the first row number, you can use the MIN function to extract just the first row number, which will be the lowest number in the array.

Worked Example:   Highlight every other row in Excel

Simple version

Entered in a single cell, the ROW function will display only the first row number, even though it returns an array. This means, in practice, you can often just use the ROW function alone:

=ROW(range)

However, inside formulas more complex formulas, it’s sometimes necessary to make sure you are dealing with only one item, and not an array. In that case, you’ll want to use MIN to pull out just the first item.

Worked Example:   INDEX function: Description, Usage, Syntax, Examples and Explanation

Index version

Since ROW (range) actually returns an array of every row number in the range, you can also use INDEX to fetch the first item:

=ROW(INDEX(data,1,1))

Not tested, but this may be slightly faster than the MIN(ROW) formula in very large ranges.

Post navigation

Previous Post:

How to return TRUE when all cells in range are blank in Excel

Next Post:

How to get address of named 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 pad text to match equal length in Excel
  • REPT function: Description, Usage, Syntax, Examples and Explanation
  • Put names into proper case — Manipulating NAMES in Excel
  • CHAR function: Description, Usage, Syntax, Examples and Explanation
  • LEN, LENB functions: Description, Usage, Syntax, Examples and Explanation
  • Two ways to sum time over 30 minutes in Excel
  • Calculate total hours that fall between two times in Excel
  • Get days, hours, and minutes between dates in Excel
  • Get first day of previous month in Excel
  • Find Last Day of the Month in Excel
  • How to calculate compound interest in Excel
  • XIRR function: Description, Usage, Syntax, Examples and Explanation
  • Calculate interest rate for loan in Excel
  • DURATION function: Description, Usage, Syntax, Examples and Explanation
  • SLN function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles