Dynamic Ranges & Charts using Excel Tables

We frequently deal with data that is supposed to grow with time (product catalog, sales records etc), how do we write auto correcting formulas for such data? One very good solution lies in using excel tables to do this.

Suppose a stand-alone store owner decides to keep a record of all the items sold for the day in excel. The data looks like following:

Basic data that is likely to expand as we go

Now let’s one uses SUM() function to get the total sales till now. The following image reflects this setup:

Sum on the data

This neatly totals the Sales Value for the existing records(totalling to $968.00), but new records are likely to added with every sales. With the current formula, the sum() will only take the current data range and will not expand when we add new records.

Static Range Issue

So, what we really need is a SUM() function that will auto-expand to include new data as and when it is added, in other words, we are looking for a dynamic range.

Excel table provides an easy solution for doing this. With excel tables, we can use structured names to access column values. These structured names work as dynamic ranges for us, in short, such formulas will auto adjust to the current size of the table.

To see this in action, let’s go back to the data that we had initially.

To convert this data into table and other basic, please refer to our previous tutorial. We will anyway cover all the steps in brief here.

1.       Click on any cell inside the data range-> Go to Insert->Table

2.       Click OK on the confirmation pop-up, as our data has header row, keep the relevant field “My table has headers” as checked-in

3.       Done, the same data will now appear as a table.

Inserting a table

Now we give a proper name to our table, to do this select the whole table and go to DESIGN tab, click on Table Name field and enter your new name. In this case we have named this table as DAILY_SALES :

Naming an excel table

Now we write our SUM() formula using the named structures provided by the table. The formula is to be written as =SUM(DAILY_SALES[SaleValue])

Using structured names in excel formulas

You perhaps need not type it fully as excel will suggest relevant auto-fill options. The important thing to note is the syntax of using table columns, which is Table name[column name]

Now, the benefit of using [SalesValue] is that it will always refer to the whole of the SalesValue column. This auto-expanding nature of this kind of formula is shown below:

Dynamic range formula in action

WOW! So now you can keep updating the table with new records and all such functions (that refer to table structures) will be automatically adjusted to show the correct value.

This functionality improves the robustness of our excel sheets dramatically, just imagine the number of errors that occur in everyday excel usage due to incorrect/old references to other ranges.

 

Dynamic data ranges for Charts:

The benefits of dynamic ranges can also be used to create charts that auto-expands.

Below is an example of using rawdata to create a chart, such chart will not recognize any new data if it is added to original set of data.

Chart with a static data range

Now, if we want the chart to be dynamic(so it expands when new data is added), we can use excel tables.

Excel charts recognise tables as a set of structured data and will automatically accomodate if any new rows/columns are added to this set.

This is shown in the example below(taking the data from previous example), here we first convert the data to table andthen add another column, excel chart automatically recognize this and properly expands by adding a new quarter data.

Chart with dynamic range

No matter whether we add columns or rows, the chart will expand to include this properly. As you can imagine, this can be of massive utility when someone has to maintain continuously updating data (with records getting added/deleted). 

So, go ahead and make use of excel tables to handle some of such scenarios.

All examples used in this tutorial can be downloaded in the tutorial sheet.

Section: