Solved

Dynamically updating charts in Excel

Posted on 2011-02-22
7
615 Views
Last Modified: 2012-05-11
I have some graphs in excel which I update on a weekly and monthly basis.

Everytime I add a new data point, how can I have the chart automatically update so that it only shows the last e.g. 10-data point rolling period?

Example:
I have 12 monthly stock prices, one price each month.  Now that we are in Feb, I might want the chart to show Feb 2010 to Feb 2010 after I have added a new column in the worksheet.

At present, I have to right click on the chart, select Source Data and then incrementally increase the the cell referencing values by one letter (column) to reflect the rolling period.  

How can this be done automatically somehow?
0
Comment
Question by:hedgeselect
  • 3
  • 2
  • 2
7 Comments
 
LVL 45

Assisted Solution

by:patrickab
patrickab earned 200 total points
ID: 34953109
hedgeselect,

You need to use dynamic ranges - which update automatically as you add or delete data.

Here are some notes about using dynamic ranges with charts:

Dynamic Ranges for Charts

1. Select/highlight the data in column A, go to Insert/Name/Define and in the top box put Sprockets and in the bottom box put:

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$65536),1)

press OK

2. Select/highlight the data in column B, go to Insert/Name/Define and in the top box put Widgets and in the bottom box put:

=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$65536),1)

press OK

Stages 1 and 2 create the dynamic ranges. They now need to be used in the chart so on to stage 3.

3. Select/highlight the data in columns A and B, press the Charting button and this time select XY chart, press Finish

4. Right click on the Chart and select SourceData and in the X-values box put:

='Sheet1'!Sprockets

and in the Y-values box put

='Sheet1'!Widgets

press OK - and that's it.

You can now add data to columns A and B and the chart up-dates automatically. The confusing part of the whole process is that Excel in its wisdom changes the references in the chart data series from a sheet to a file name like this:

=kayhustle01.xls!Sprockets

and this

=kayhustle01.xls!Widgets

now if that isn't confusing or what. Just accept it and remember how to put it in, in the first place!

Hope that helps.

If you'd like to upload one of your files with charts we can do our best to make the charts work with dynamic ranges.

Patrick
0
 

Author Comment

by:hedgeselect
ID: 34953399
In my chart, I have two trending lines against a date axis and against a 1-10 price axis.

If the formula for one of the company lines is =SERIES(Graphs!$B$31,Graphs!$BX$2:$DY$2,Graphs!$BX$54:$DY$54,2) and the other =SERIES(Graphs!$B$1,Graphs!$BX$2:$DY$2,Graphs!$BX$23:$DY$23,1), are you able to tell me how I can amed that line to include a reference?

I have inherited the spreadsheet (which is sensitive so I cannot post it) and not sure how I can do what you have described above.

Thx.

0
 
LVL 45

Expert Comment

by:patrickab
ID: 34953487
hedgeselect,

Stage 1 is to define a set of data as a named range, but also make it a dynamic range at the same time - as per my instructions above.

Stage 2 is to use the named dynamic ranges in the Source data for the chart.

As for the file itself, can I suggest you put in random numbers instead of the data that is curently there and then upload the file. At least that way you are not revealing any confidential numbers.

Patrick
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:hedgeselect
ID: 35048152
Patrick,

Apologies for the delay.  I've attached anonomised data in this spreadsheet.

Could you please add the dynamic rolling returns to column to column G on the first worksheet and also create a COPY of the chart on the third worksheet so that the new chart dynamically updates only the last 12-months of rolling returns, as I add a new monthly return figure for each month?  This would also need to reference the cum returns for the benchmarks in columns C, E and H on worksheet 2 please.

If you wish, please also feel free to participate in my other question which can be found at:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26867086.html

Thanks.
Master-Statistics-template-1.xls
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35049512
@Hedselect - dynamic rolling returns for all return columns of fund and 3 benchmarks - also updated with your updated graphs from prior question.

Dave
Master-Statistics-template-1-r5.xls
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35049712
Unlike the last question, this one appears to want the last 12 entries (last 12 months for a calendar year).  If so, then the attached modified to achieve that.

Dave
Master-Statistics-template-1-r6.xls
0
 
LVL 41

Accepted Solution

by:
dlmille earned 300 total points
ID: 35049893
Updated to version 6a to correct TABLE on col 26-27 to use new FUND_M1 range - monthly return data...

Dave
Master-Statistics-template-1-r6a.xls
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now