One of the most useful features of Excel Tables is that they create a dynamic range. A dynamic range automatically expands to handle new data, so it works well for reports, pivot tables, or charts that need to show the latest information. To illustrate, I’ll add some formulas to this worksheet to report on the size of this table. The first formula counts rows with the ROWS function. The second formula counts columns with the COLUMNS function. Both formulas use only the table name as a reference. I’ll also add another formula to calculate a count of all cells in the table. You can see there are 10 rows and 4 columns, for a total of 40 cells. This corresponds directly to the table range, which I can verify by selecting the table with the name box. Now I’ll make some changes to the table. First, I’ll add 2 new rows. Notice the table automatically expands to include the new rows, and the row count increases. If I delete the last two rows, the formulas show the original counts. Now I’ll add a new column with a formula to calculate a total. Again, notice the table expands, and the column count increases by one. I’ll add one more formula to sum the Total column with the SUM function. Tables create structured references. Notice when I select the Total column, Excel enters a reference that includes both the table and the column name. Structured references are powerful. Watch what happens when I paste in data from the second sheet. First, the formula in the Total column was extended to cover the new data, since Total is a calculated column. Second, the table range has was automatically expanded to include the new data. And, because all of these formulas refer to the table by name, they now reference the expanded range as well. This means all results are up-to-date. There is no need to manually adjust any of the ranges. This is the real power of Tables. Because they automatically adjust to changes in data, they’re perfect for charts, pivot tables, dashboards, or any report that needs to show the latest information.
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.
