Dynamic Charts with Excel Tables

Dynamic Charts with Excel Tables

Create a dynamic chart using an Excel table. 

When to use

We format our data as an Excel table so that our chart will automatically update when new data is added. 

Instructions

A dynamic chart is one that updates automatically when new data is added to the dataset.

For example, maybe we have added a new product to our product line and we need to update the sales figures for the year.

Try adding a new row for the product ‘Mouse’. Add dummy values for each of the quarters.

Notice the chart has not updated.

If we click on the chart, we can see the cell ranges that the chart is using. They don’t include the new row.

Now this is a fairly easy fix. We can simply grab the handle in the bottom corner of the range highlighted in blue and drag it down to include the new row or rows.

The chart will now include the new row of data.

However, this isn’t very dynamic. It’s a perfectly valid method and if you are working with a dataset that does not change frequently, this method might be perfectly fine for you.

We are going to be updating our dataset frequently, so we need a more dynamic method. That is where Excel tables come in.

Format Data as a Table

To make our dataset dynamic we need to format it as an Excel table. Excel tables have auto-expand capabilities and will accommodate any new data added to the bottom. Anything that is using that table will automatically update.

• Click anywhere in the dataset.
• Press CTRL+T.
• Ensure the range selected is correct and ensure there is a tick in the box that says ‘My table has headers’.
• Click OK.
• From the Table Design tab, in the Table Styles group, click the drop-down arrow and choose No Style (the first option) in the gallery.
• From the Table Design tab, in the Properties group, give the table a name.

Now, when we add a new row to the table, the chart will automatically update.

Hints & tips

• We can also insert an Excel table by clicking the Format as Table button on the Home tab. 
• Try using a consistent naming convention for tables, e.g. all tables start with the prefix 'tbl'. 
• We can format our data as an Excel table either before or after we create the chart.