First, let’s look at the problem we’re trying to solve.
Here we have 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 if I add new data, the chart doesn’t change.
It still plots the original data.
Well, one way to do it is with anExcel Table.
I’ll delete the chart and 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.
Because we’re using a table, these columns are added as new data series in the chart.
Now I’ll add formulas to calculate both columns.
We use the NA error in this case because charts automatically ignore these.
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 pick the low and high series and change the colors.
Now I’ll go for the original data series and change the overlap to 100%.
This causes the high and low columns to plot directly on top of the original bars.
I’ll also beef up the columns by reducing the gap.
