How Many Rows and Columns in a Range

Two functions to the recue

Excel has two functions to answer these questions.

When you work with ranges you may need to know how many rows are in a range. That range may be a table.

Luckily Excel has a function that counts rows in a range. It is called ROWS. You simply refer to the range between the brackets of the function. See the image below for an example.

The formula works only on the range. If I add entries below the range it won’t update.

If I use the Format as Table icon (Home ribbon) on this range, I can make the calculation dynamic.

You can refer to the structured reference for the whole table. Structured references are names relating to formatted tables.

You don’t have to type this in, simply select the whole table including the headings and Excel will enter the structured reference for you.

This formula is more flexible because as rows are added to the table the table range will expand to include them and the ROWS function will also update automatically – see image below.

There is also a COLUMNS function that counts how many columns in a range. It too works well with the structured references.

 

 

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.