Subscribe!

One of the most time consuming parts of producing a scheduled report is updating all of the charts in the report. Many times, an analyst only wants to show the last 7 days or the last 6 months of data and not the entire data set. To achieve this, the analyst changes the the Y and X axis ranges to encompass only the data he/she would like to display. There are much more efficient and easier ways.

There are two common ways of creating dynamic auto updating charts. One is to use auto-filter which can be seen here and the second more powerful way is to use named ranges, this process is detailed here. In this article I will talk about the most simple and least talked about method that works great in some situations.

The method I am writing about is similar to the dynamic named ranges method but easier to understand for most users. This method works great if you have a data sheet in a workbook that is hidden that feeds your workbook charts. To get started…Lets assume you have a column of successive dates in column A and in column B you have sales figures for each of those days. You would like to chart the last seven days of data but you do not want to continually update the chart. The solution is to add two columns to your data worksheet. The first column (column C) will be the date (last seven days), Column D will contain the last seven days of sales data. In C1 type the formula below:

=INDEX(A:A,(MATCH(9.99999999999999E+307,A:A)-6))

The result will be the date from 7 days prior. In C2 enter the same formula but change the -6 to -5. Continue in C3 with -4 and so on down to C7 where you have -0 or just leave off the argument. D1 through D7 will have the same formulas except the column references need to be changed to B:B, for example:

=INDEX(B:B,(MATCH(9.99999999999999E+307,B:B)-6))

Now simply graph the data in C1:D7. As you add dates and sales figures to columns A and B, C1:D7 will update to show the most recent seven days of data and thus your chart will as well.

This method of “dynamic charting” is a simple solution to a time consuming problem.

Joseph Harris
Excel Help
More Excel Help

If you found this article helpful, buy me a cup of coffee!

Related posts:

  1. Scrolling Excel Charts – Scroll Bar Graphs There are many times when you may have a lot...
  2. Images and Pictures in Charts – Excel Graphs Quick an easy method of making your Excel graphs more...
  3. Excel to PowerPoint Converter Subsidium Business Analytics has released its Excel to PowerPoint converter....
  4. How to Create a Pareto Chart in Excel Creating Pareto Charts...
  5. Excel Version Differences I try to make all of the advice I offer...

This entry was posted on Monday, February 16th, 2009 at 1:30 pm and is filed under Business Analyst, Excel Hints. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to “Automatic Updating “Dynamic” Charts in Excel”

  1. Excel Yogi’s » Blog Archive » Scrolling Excel Charts - Scrollbar Graphs on March 13th, 2009 at 12:06 pm

    [...] charts! I will show you in this article how to create a scrolling chart without using dynamic ranges. This is a more simple technique for most Excel users, but for the more advanced, I would [...]

  2. Pirsey on April 21st, 2009 at 11:58 pm

    I follow your posts for a long time and must tell you that your articles are always valuable to readers.

Leave a Reply

You must be logged in to post a comment.