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.