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.
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 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'll 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 new 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:
The IF function can perform a logical test and return one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions....