Let’s take a look. In this first worksheet, we have a list of ten 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. As you can 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. If we check the formulas that are summarizing this data, you can see that they refer to parts of the table using a special syntax called “structural references.” When you use a table in Excel, these type 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. 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 formulas has been expanded 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. It’s a powerful concept that you’ll often see in advanced reports, dashboards, and models. Although there are several methods you can use to create a dynamic range, a table is by far the easiest. And, unless you’re building a professional model, a table is the logical place to start.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
