How-to Make Dynamic Excel Dashboard Charts Using Tables

For almost every Excel Dashboard you will want to make a Chart template Dynamic.  Meaning that as you add new data, the chart updates itself.  Wouldn’t that be wicked cool?

This is a major complaint that most Excel Dashboard users and creators have when making Dashboard Charts in Excel.  Once they have made the chart it is too difficult to maintain.  Perhaps you just want to add another data point as time moves on but you have to change every series to add the new data.  This can be daunting especially when you have lots of chart series in your Excel Dashboard.

In this example you want a Dashboard Template that builds the chart with the most current data as you enter new data.  Lets say you want the chart to look like this and Dynamically update the chart when data is added:

DynamicExpandingChart1

Instead of a Dynamic chart, most users make a chart that simply has placeholders for future data points in their Company Dashboard like this:

NotDynamicExpandingChart2

This means you don’t have to modify the chart until January, however, it is not a practical Dashboard Design for several reasons.  The biggest of which is that you create lots of empty white space in your dashboard chart as a place holder for future data.  And as you may already know, Dashboard Real Estate or Space in your Dashboard is really important and you want to maximize it as much as possible.  This solution would be a waste of space in your Excel Company Dashboard.

Instead, we want to make the Excel Dashboard Template Dynamic!  Picture a chart that updates itself when you add data, wouldn’t that be great?

Even better yet, how about a chart that only shows the last 12 months on a rolling 12 month basis.  That would be Incredible!!  Well lets walk before we run, but it can be easily done.  We will cover that in future posts. Smile

So how can I EASILY make a dynamic chart?

There are several ways, here is the easiest way first and then we will get more complex in the next posting until we have a rolling 12 month chart.  I am showing you all the ways that it can be done, so that you can use the one that is right for you.

The easiest way in my opinion to make a Dynamic chart is available in Excel 2007 and beyond.  If you are using Excel 2007 or beyond, you can put your data in an Excel Table before you make your chart and then the chart will dynamically update when you add new data series.  Here is a brief demonstration of the Dashboard Template technique:

DynamicTableLineChartGif

As you can see, once you have made your data range into a table and then create a chart from the table, it automatically becomes dynamic without any VBA coding or advanced Excel formulas.

Wasn’t that simple?  I use tables whenever I can since it is so easy.

Now there are some limitations as your data must be uniform and might make it tricky to customize beyond a regular chart.  However, this may be just fine for many data applications and might be the right thing for you.

In most Excel Dashboards or just for a normal executive or company presentation, you may only need to create a basic chart that needs to be updated easily without a lot of detail and thought.  A Dynamic table based chart for your Dashboard may be the perfect fit.

You can download this 2007 Microsoft Excel Dynamic Dashboard Template for Free at this URL:

Excel-Dynamic-Table-Chart-Sample-File.xlsx

 

Watch an extended YouTube video demonstration here:

The next post will build on this concept so that we can ultimately build a chart for rolling 12 amount of months of data.

Don’t forget to subscribe to the newsletter so that you get the next post delivered directly to your inbox.

Steve=True