In this post I will show how to create a dynamic chart in Excel, ie. a chart that updates automatically. In many situations you collect data every day (sales figures, stock prices etc.) for months and years, but you only want to show the last couple of weeks in the chart. I’ve seen many people spending a lot of time updating the chart manually every day, so here I am going to show you how to make a dynamic chart in only 5 minutes!
First, let’s get an overview of what we have and what we want:
What we have:
– A table in column A and B with daily sales figures with one amount for each day.
What we want:
– A dynamic chart that displays the sales for the past 14 days.
– A dynamic chart title: “Sales from 10/23/2012 to 11/05/2012”
Step 1: Create a new table
This is the table that provides the chart with data.
The D column, “Days ago” shows how many days we whant to include in the chart.
In the E column we need a formula that calculates the date 14 days ago. Let’s use the TODAY function and subtract the number of days we have in the D column:
In the F column we need to find out how much we sold on that particular day. Let’s use VLOOKUP:
The formula =VLOOKUP(E2,A:B,2,0) works like this:
Look up what? E2
Look up where? A:B (Columns A and B)
Return what? 2 (the value in the second column)
And the zero means Excact match
Copy all the formulas down, and the table is ready!
Step 2 – Create a chart title
The text we want is “Sales from 10/23/2012 to 11/05/2012”, with the dates changing automatically.
This text has 4 elements:
“Sales from “ & 10/23/2012 & “ to “ & 11/05/2012, and that’s how we will write the formula as well, only replacing the hard-codes dates with cell references:
=”Sales from “&E2&” to “&E15
Unfortunately, the result of this formula is “Sales from 41205 to 41218”.
We have to tell Excel to format these numbers as dates! So, we wrap a little function around the cell references E2 and E15:
TEXT(E2,”mm/dd/yyy”). This converts the number of the date (41205) to the date format that is specified in the formula (mm/dd/yyy).
Here’s the final formula for our dynamic chart title:
=”Sales from “&TEXT(E2,”mm/dd/yyy”)&” to “&TEXT(E15,”mm/dd/yyy”)
Step 3 – Creating the chart
This is the easy part. Select the Date column and the Sales column and choose Line Chart from the Inset ribbon. Shortcut: Alt => N => N
Excel assumes that we want the Chart title to be the same as the header in column F; Sales. To change it, simply click on the title, click in the Formula Bar, type an Equal Sign and finally, click on the cell that has the title in it (D19):
Note: If you use comma as the decimal separator as default (applies to most non-English users) you have to replace the commas in the formulas with semicolons.