• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel ROWS and COLUMNS Functions

You are here: Home / Excel Formulas / Excel ROWS and COLUMNS Functions
Excel ROWS and COLUMNS Functions
January 12, 2016 by Mynda Treacy

You'll typically find the ROW(S) or COLUMN(S) functions nested inside other functions because they make formulas more efficient to write. However, many users get caught out by these functions because you don’t edit the cell references in the same way you might with other formulas.

Watch the Video

Subscribe YouTube

Download Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook and follow along. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Excel ROWS and COLUMNS Functions Explained

You’ve probably seen a formula like this before:

=VLOOKUP($B16,$B$4:$D$13,COLUMNS($B4:C4),0)

Or this:

=INDEX($C$3:$H$8,ROWS($A$3:A4),COLUMNS($A$1:B1))

And you may have even emailed me to ask “what is the ROWS/COLUMNS function doing in this formula”. The short answer is it’s returning a number, or in multi-cell array formulas, a series of consecutive numbers.

Why do you use them? Because it’s an efficient way to write formulas that require copying down columns, across rows, or both.

Just take the VLOOKUP formula above; the COLUMNS function is returning the col_index_num argument. Remember the syntax for VLOOKUP is:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Here’s the formula again:

=VLOOKUP($B16,$B$4:$D$13,COLUMNS($B4:C4),0)

COLUMNS evaluates to 2 because it returns the number of columns in a range:

=VLOOKUP($B16,$B$4:$D$13,2,0)

And when you copy that formula across to the next column it becomes:

=VLOOKUP($B16,$B$4:$D$13,COLUMNS($B4:D4),0)

Which evaluates to:

=VLOOKUP($B16,$B$4:$D$13,3,0)

So, you can see that using COLUMNS in the col_index_num argument of your VLOOKUP formula enables you to copy and paste it across the row and it will automatically increase the next col_index_num by 1, without so much as an F2 to edit the formula.

Now we have a rough idea of how these functions work and why you’d use them, let’s understand the four functions you can use.

ROW, ROWS, COLUMN and COLUMNS

ROWS Function

The ROWS function returns the number of rows in a range:

=ROWS(A3:A6) returns 4 because there are 4 rows in the range A3:A6.

Note; the column reference is actually irrelevant, which means you could also write this formula as ROWS(3:6) or more likely, ROWS($3:6). That way row 3 is absolute (anchored), and when you copy the formula to consecutive rows the formula will become ROWS($3:7) and so on.

ROW Function

The ROW function returns the row number of a reference:

=ROW(A3) returns 3 i.e. A3 is on row 3.

Note: you can’t write this as ROW(3) but you can write ROW(3:3). And ROW() will return the number of its own row.

COLUMNS Function

The COLUMNS function returns the number of columns in a range:

=COLUMNS(B2:D2) returns 3 because there are 3 columns in the range B2:D2.

Similar to ROWS the row number for the COLUMNS function is irrelevant, which means you could also write this formula as COLUMNS(B:D) or COLUMNS($B:D).

COLUMN Function

The COLUMN function returns the column number of a reference:

=COLUMN(C2) returns 3 because column C is the third column.

Note: you can’t write COLUMN(C) but you can write COLUMN(C:C). And COLUMN() will return the number of its own column.

The key with getting the desired results from these functions is the correct use of Absolute Cell Referencing.

Use Excel ROWS and COLUMNS Functions in Array Formulas

Typically people get caught out when editing array formulas which contain one of these functions.

Let’s take this example below where the formula in column C is a multi-cell array formula that is returning the 3 smallest values from cells A1:A6:

Excel ROWS and COLUMNS Functions in a array formulas

The SMALL formula evaluates like so:

=SMALL(A1:A6,ROW(A1:A3))
=SMALL({12;31;25;46;28;50},{1;2;3})

In this case ROW is returning a series of sequential numbers from 1 to 3 and as a result cell C2 returns the smallest value, cell C3 returns the second smallest and cell C4 returns the third smallest.

Problems arise when people try to edit formulas like this that they’ve copied them from somewhere, maybe another workbook, or somewhere they’ve found in a Google search.

Excel experience tells us to adjust range references to suit our data, so let’s say your data is in cells B2:B7 like so:

adjust ROW function references

You might be tempted to copy the formula above and edit it to suit your data like this:

=SMALL(B2:B7,ROW(B2:B4))

After all, if you're changing A1:A6 to B2:B7 for SMALL shouldn't you also change A1:A3 to B2:B4 for ROW? NO. If you do you'll end up with this:

=SMALL({12;31;25;46;28;50},{2;3;4})

Which would be correct for the values in cells B2:B7 but not for the ROW function reference, which as you can see above now incorrectly returns 2, 3, 4.

The correct formula is:

=SMALL(B2:B7,ROW(B1:B3))
=SMALL({12;31;25;46;28;50},{1;2;3})

Because we want the 3 smallest values, not the 2nd, 3rd and 4th smallest.

So, now you know why ROW, ROWS, COLUMN and COLUMN are typically used you’ll be able to use them in your own formulas and not get caught out editing formulas that already use these clever functions.

Caution

Pay particular attention to how these functions adjust when rows or columns are inserted above (or to the left for COLUMN/COLUMNS), or in between cells containing these formulas as it can yield unexpected results (read; formula errors).

Absolute referencing is your ally but the approach differs depending on whether you're using these functions in an array formula or a regular formula.

Here are some tips; the formulas below will adjust as a result of rows/columns being inserted above/to the left but they will still return the same results:

Regular Formulas

=ROWS($A$1:A1) or =ROWS($1:1)
=COLUMNS($A$1:A1) or =COLUMNS($A:A)

Copy and paste (or fill) the above formulas to extend the range.

Array Formulas

=ROW(A1:A10)-ROW(A1)+1
=COLUMN(A1:J1)-COLUMN(A1)+1

The above formulas will return the numbers 1 to 10. Adjust the range referenced to suit your needs.

Examples of Formulas that use ROW(S) or COLUMN(S) Functions

VLOOKUP with dynamic column index

Summarise monthly data into quarters

Lookup and return multiple matches

List missing numbers in a sequence

Find a column containing a specific value

Remove blank cells from a range

Excel ROWS and COLUMNS Functions

More Excel Formulas Posts

top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.

Top 10 Intermediate Excel Functions

Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.
Category: Excel Formulas
Previous Post:slicer selection in formulaUse Excel Slicer Selection in Formulas
Next Post:Excel Multi-cell Array FormulasMulti-cell array formulas

Reader Interactions

Comments

  1. hameed

    December 5, 2018 at 3:50 pm

    hello…Hello..!

    Am have on doubt related to VBA, am trying to help for my friend dad business. Actually the requirement is am have three sheets
    Sheet 1- client details
    Sheet 2- project details
    Sheet 3- queries
    If am update new project and client name mean in sheet 2 or 3 it want to be automatically update in the sheet 1 without duplicating . if the client already in the sheet 1 mean it should not duplicate. Is it possible in excel formula or any feature or VBA.. if it possible mean will you guide me to do this work … thank you

    Reply
    • Catalin Bombea

      December 5, 2018 at 9:44 pm

      Hi Hameed,
      It does not look like a simple project, most probably you have lots of specific rules applied.
      I suggest you should open a new topic on our forum, upload a sample file with your data structure, and then we can contribute with solutions.
      Here is a link to the forum.

      Reply
  2. simlaoui

    January 16, 2016 at 3:09 pm

    very good explanations!
    thank you very much for your desire to share your knowledge.
    another tip of using ROW function in excel is to fix rows.
    let’s assume that we have the range A1:B10 and we don’t let users to insert any row between A1 and A10;
    we can do this by following those steps:
    1-select A1:A10
    2- write this formula =ROW()
    3- ctrl+shift +enter to make array formula
    4-done!
    bonus : we can make the police white or even do this in the last colonne XFD and hide this colonne
    thank you

    Reply
    • Mynda Treacy

      January 16, 2016 at 9:07 pm

      That’s a unique approach to worksheet protection 🙂 Thank for sharing.

      Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x