Let’s take a look.
In this first worksheet, we have a list of ten properties set up in a normal way.
So, what happens if I add some more properties to this list?
Let’s try it out and see.
On the data worksheet, I have data for another six properties.
I’ll copy this data and paste it below.
As it’s possible for you to see, none of the formulas have calculated new results.
That’s because none of the references in the formulas have changed.
They’re all still pointing at their originally defined ranges.
We can, of course, update each formula manually and extend each range to include these new properties.
However, let’s look at another way to approach this problem using a so-called “dynamic range.”
On the tab called Dynamic, we have the same ten properties.
This time, however, the properties are defined as an Excel table called Table1.
When you use a table in Excel, these punch in of references are automatic and powerful.
One of the coolest features of tables is that they’re automatically dynamic.
As you add and remove data, the range defined by the table automatically grows and shrinks as needed.
To show you how this works, I’ll copy and paste the extra data below this table.
In each case, the reference inside the formulas has been expanded to include the new property data.
So, in short, a dynamic range is a range that automatically changes to fit the data.
It’s a powerful concept that you’ll often see in advanced reports, dashboards, and models.
And, unless you’re building a professional model, a table is the logical place to start.
