In this video, we'll introduce the idea of a dynamic range and show you why you might want to use one.
Let's take a look.
In this first worksheet, we have a list of 10 properties set up in a normal way. If we check the formulas that summarize this data to the right, you can see that each formula refers to a standard range of cells.
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 the original set of properties.
As you can see, none of the formulas has calculated new values. That's because none of of the references in the formulas have changed. They're all still pointing the their originally defined ranges.
We can of course update each formula manually and extend each range to include the new properties.
However, let's look at another way to approach this problem using a dynamic range. On the Dynamic tab, we have the same 10 properties. This time, however, the properties are defined as an Excel table named Table1.
If we check the formulas that are summarizing this data, you can see that they all refer to parts of the table using a special syntax called structural references. When you use tables in Excel, these type of references are automatic, and powerful.
One of the coolest features of tables is that they are dynamic. As you add or 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 into below the table. Now, since the table range is dynamic, you can see that the summary formulas are showing new results. In each case, the reference inside the formula has been updated to include the new property data.
Likewise, if I remove a couple of rows, you can see that the table responds instantly.
So, in short, a dynamic range is a range that automatically changes to fit the data. Its a powerful concept that you'll often see in advanced reports, dashboards, and other models.
Although there are several methods you can use to create a dynamic range, a table is by far the easiest way. And, unless you're building a professional model, a table is the logical place to start.