Exceljet

Quick, clean, and to the point

This video is part of our video training for Excel.

How to build a simple dynamic chart

Tags 
Summary 
In this video, we look at how to build a chart that automatically includes new data, and dynamically highlights the top and bottom values. This is an easy way to create an interactive chart for dashboards and other reports.
Video Transcript 

In this video, we'll look at how to build a simple dynamic chart in Excel.

First, let's look at the problem we're trying to solve.

Here we have some monthly sales data. At the moment, we only have 5 months, but we'll be adding more data over time.

Now, if I insert a column chart, everything works fine.

But notice that if I add new data, the chart doesn't change...it still plots the original data. 

To update the chart, I need to expand the range manually

How can I make the chart automatically include new data?

Well, one way to do it is with an Excel table.

I'll delete the chart add the table.

To create a table, just put the cursor anywhere in the data, and use the shortcut control T. 

I'll hide gridlines here to make the table easier to see.

Now, I'll create the chart based on the table.

It looks the same, but now if I add new data, the chart automatically includes it.

Now let's add another dynamic element to the chart.

What I want to do is automatically highlight the best and worst months in the data, by changing the worst month to orange and the best month to green.

I'll start by adding two new columns to the table, one called High and one called Low.

Because we're using a table, these columns are added as data series in the chart.

Now I'll add formulas to calculate both columns.

To get a top value, I'll use the MAX function with IF:

=IF([@Sales]=MAX([Sales]),[@Sales],NA())

What this formula says is ...if this sales value in this row is equal to the max value in the column, then use it, otherwise return NA.

We use the NA error here because the chart automatically ignores these.

For the low, or minimum value, I'll use exactly the same formula, but change MAX to MIN.

=IF([@Sales]=MIN([Sales]),[@Sales],NA())

The logic is the same...if the value in this row is the minimum value, use it, otherwise NA.

So at this point we have our chart, but it's not looking so hot. Let's clean things up.

I'll start by making the chart bigger and adding a Title.

Next, I'll select the low and high series and change the colors.

Now I'll select the original data series and change the overlap to 100%. This causes the high and low columns to plot on directly on top of the original bars.

I'll also beef up the columns by reducing the gap.

Finally, with the original data series still selected, I'll add data labels, then get rid of the gridlines and vertical axis.

Now we have a decent looking chart that dynamically responds to new data, and always clearly highlights the best and worst months.

Author 
Dave Bruns

Related shortcuts

CtrlC
C
CtrlT
T
CtrlSpace
Space
Alt
I needed a quick restart of my knowledge and your courses are exactly what I needed: Short, focused, systematic and high quality.
Excel foundational video course
Excel Pivot Table video training course
Excel conditional formatting video course
Excel formulas and functions video training course
Excel Shortcuts Video Course