Ever have a situation where you have spent time getting your chart or charts to look nice, then try to use different data ranges in these charts? You’ve discovered that there’s no easy way to approach this change. At least there’s no easy way built into Excel, but there is a utility that can make the task easier.
Scenario 1
You’ve made a gorgeous chart of the data in Sheet1. You copied the chart from Sheet1 to Sheet2 so you could plot Sheet2’s data in the same splendor. And you hit a snag: the chart on Sheet2 refers back to Sheet1’s data. There are two ways to correct this:
- Create a copy of Sheet1 including the chart, so the chart on the copied sheet refers to the data on the copied sheet. Then copy Sheet2’s data and paste it over the copied sheet’s data.
- Edit the series formulas of the copied chart in Sheet2, changing all instances of one sheet name to the other. This becomes tedious if there are multiple series in the copied chart, or if you’ve copied multiple charts.
Scenario 2
You’ve charted data in rows 1 to 100 of your worksheet. Then you’ve updated the data so it reaches down to row 150. Your wonderful chart only shows data down to row 100. There are three ways to correct this:
- Before updating the data, convert the data range to a List (Excel 2003) or a Table (Excel 2007). Then update the data. The list/table will expand to include all of the data, and all formulas that refer to all rows in the list/table will update accordingly. This includes the chart’s SERIES formula.
- Create dynamic named ranges for the X and Y data ranges in the chart, and apply them to the chart series. This is described in Dynamic Charts in this blog and in a number of Dynamic and Interactive Chart examples described in this web site. However, this is an involved procedure that you never have time for.
- Edit the series formulas of the chart, changing all instances of one row number to another. This becomes tedious if there are multiple series or multiple charts to correct.
Scenario 3
Your charts show the data for product Alpha beautifully, but you want to show the data for product Beta instead. The data is listed in another column. You can:
- Select the series, then drag the highlighted rectangles on the worksheet to reflect the new data range. This becomes a laborious process for multiple charts and series.
- Edit the series formulas of the chart, changing all instances of one column to another. This becomes tedious if there are multiple series or multiple charts to correct.
The Solution
These suggested means for editing your charts all leave out one approach. In Change Series Formulas I showed how to programmatically change the series formulas in your charts. I’ve just recently updated the utility to account for glitches in Excel in Change Series Formula – Improved Routines.
The Change Series Formula Utility
These new algorithms have been incorporated into the Change Series Formula function of the Peltier Tech Charts for Excel 3.0, both Standard and Advanced Editions.
Steve says
I have Excel 2007 and multiple standalone charts within one workbook.
I used to use the ChangeSeriesFormulaAllCharts macro for Excel 2003 and it worked brilliantly.
Unfortunately it doesn’t work for me in Excel 2007. I imported the same macro.
What I find odd is the ChangeSeriesFormula (for single active charts) works fine in Excel 2007, but the all charts one does not.
I see that the add-in addresses multiple charts within ONE sheet, but not multiple standalone charts in a workbook like the ChangeSeriesFormulaAllCharts macro did for me in Excel 2003.
Any insight?
Jon Peltier says
Hi Steve –
There is an issue in Excel 2007 with multiple chart objects in a sheet. If there are more than one chart objects and you have selected more than one, Excel tells VBA that you have selected all of them. The bug has been reported.
There is another issue with Excel 2007, in that the use of defined range names in the series formula breaks down for some names, particularly those beginning with the string “chart”. There are also some other series formula irregularities in Excel 2007. These issues have been reported.
The ChangeSeriesFormulaAllCharts routine in Change Series Formulas only works on all chart objects embedded in one sheet. It does not work on chart sheets (is this what you mean by “multiple standalone charts”?) or on charts embedded in multiple sheets. However, the routine is easy to manipulate, and I have given suggestions to people who wanted to adjust its functionality. I suspect you are thinking of an altered version of this routine.
JP says
Jon —
“I’ve just recently updated the utility to account for glitches in Excel”
What glitches are you referring to, the ones in the scenarios, or something else?
Also, are the scenarios you described limited to just one Excel version, or are these problems across versions?
Jon Peltier says
Jimmy –
The glitches have to do with irregularities in how VBA reads the series formulas. These were problematic in Excel 2003 and earlier but are fixed in these versions. I have not tested this utility much in 2007, but I do know that the series formula is more friable in 2007 than in 2003, both in VBA and in the regular user interface. I have not had the time (nor really the inclination) to attack the 2007 series formula issues.
The scenarios could describe any version of Excel as far as I know.
Tom says
I have Excel 2003. I have about 100 data point pairs. To select them as source data I have to use 100 columns in two rows. That works fine but to look at the data requires 14 pages. I can fit all the pairs on one sheet, but how to designate them as data for one graph? I.e., instead of 100 things, I want to plot 1-10, 11-20, 21-30, etc., all as one graph.
Jon Peltier says
Tom –
Why not put the data into A2:B101 and plot as a single XY series? If you need to label individual points, put these labels in C2:C101 and use Rob Bovey’s Chart Labeler to assign these as data labels.
Or you could use A2:C35, E2:G34, and I2:K34 so you can fit the data onto one printed page. However, this complicates charting by multiplying the number of series needed. Instead make a list of 100 points as chart source data, then on a second sheet, make links to the chart source data, but lay it out in three columnar regions, and format as desired.
Jon Peltier says
Tom replied via email:
I currently have the A2:B101 format, and it gives me the one XY series I
want.
If I do the A2:C35, E2:G34, etc., that gives me 10 separate graphs on the
chart.
What I’m doing is comparable to: Plot, by month, yielding one graph line,
the DOW Industrials from 1925 to 2008. So, I would have about 750 x,y pairs.
I don’t want to deal with A2:B751. Can I concatenate small sets of pairs?
Tom –
Certainly you could plot sequential sets of pairs on the same chart, but not on the same line chart, because each line series is assumed to use the same X values even if they are assigned separate X value ranges. You could do this in an XY chart.
Maybe I don’t understand something, but I don’t get why you don’t want to just use a 750-row data range. The data is generally available in that layout, and I find it nicer to deal with as few series as possible in a chart.
Are you trying to concatenate the series formula, like this:
=SERIES(Sheet1!$B$1,(Sheet1!$A$2:$A$35,Sheet1!$C$2:$C$35),(Sheet1!$B$2:$B$35,Sheet1!$D$2:$D$35),1)
This works to a point. For example, I can merge ten ranges like this:
=SERIES(Sheet1!$B$1,(Sheet1!$A$2:$A$35,Sheet1!$C$2:$C$35,Sheet1!$E$2:$E$35,Sheet1!$G$2:$G$35,Sheet1!$I$2:$I$35,
Sheet1!$K$2:$K$35,Sheet1!$M$2:$M$35,Sheet1!$O$2:$O$35,Sheet1!$Q$2:$Q$35,Sheet1!$S$2:$S$35),(Sheet1!$B$2:$B$35,Sheet1!$D$2:$D$35,Sheet1!$F$2:$F$35,Sheet1!$H$2:$H$35,Sheet1!$J$2:$J$35,
Sheet1!$L$2:$L$35,Sheet1!$N$2:$N$35,Sheet1!$P$2:$P$35,Sheet1!$R$2:$R$35,Sheet1!$T$2:$T$35),1)
But this becomes awkward, and the more ranges there are, the greater chance of selecting them incorrectly. Also, after a point, the formula exceeds the length allowed for the series formula. The stated limit is 1024 characters, but it’s much less than that. A more effective rule of thumb is not to let either the X or Y value section of the formula exceed around 250 characters. In my second formula, each is around 180 characters, and the entire formula is 386 characters.
Zach says
Jon,
In the utility, if I click the “X” in the upper right corner, I get an error: “Run-time error ‘-2147418105 (80010007)’:
Automation error
The callee (server [not server application]) is not available and disappeared; all connections are invalid. The call may have executed.”
This error does not occur if I click the Cancel button.
Jon Peltier says
Zach –
Doh! I forgot to Repurpose the Red X Close Button on the VBA UserForm, which I covered some time ago on the blog. I have made the adjustment and uploaded the new version of the utility to PTS_ChangeSeriesFormula.zip.
Manfred Simonet says
Hi,
I have problems with graphs using secondary axis. Can you confirm? For series using the secondary axis, the formula property is not available (in VB Editor in the Watches pane you see “”).
Any ideas? Using fully patched Excel 2003.
Thank you
Manfred
Jon Peltier says
Manfred –
The series formula is available no matter which axis group the series belongs to. To verify this I just created a two-series chart, moved one series to the secondary axis, and executed this command in the Immediate window:
Something else must be going on. I don’t use the Watch window, so I can’t comment on your last observation.
Dolores O'Connor says
Just used your Change Series Formula in Excel 2007 and find it brilliant. Thank you so much for sharing your knowledge and expertise.
D
Denis Brennan says
May I ask if this will work where the original series reference is to a worksheet in a different workbook? If not, I guess my follow-on question is how can one copy a chart from one workbook to another and update the series?
Jon Peltier says
Denis –
The utility should work in this case. If you tried it and are having issues, you may have to be more clever about how you are entering your Change From and Change To text. Take care with the single quotes and square brackets. Also, the ChangeFrom text must be entered in the same case as it appears in the formula (I should make it case insensitive, shouldn’t I?).
gurs says
Great utility! I have always longed for this feature. Unfortunately, it didn’t work for me.
I am running Excel 2007 SP1 on a Vista SP1 box. I am able to install your add-in file, and have access to the add-in via the toolbar. When I select a chart tab and click the “Change Series Formula” button I get a dialog box titled “Change Series Formula 10/06/2008 BTM”. I enter my old text and replace text and click “Change Active Chart Only” (the other option is dimmed). I get an error that says “Unknown Error”, “Type Mismatch”.
I have made sure that the type case matches and that the text I am search for actually exists. I have also tried several different search/replace strings. Any ideas?
Thanks…
Jon Peltier says
The problem is you have standalone chart sheets, and this utility only works for embedded charts.
It wouldn’t take much work to change. The hard part is finding the time.
gholly says
Hi Jon – your site is amazing, and this tool is great! However, I am having problems… I have Excel 2003, and I’m trying to use your tool and I keep getting an error msg & Excel has to restart.
What I’m essentially trying to do is remove the [Book Name]Sheet1! with just Sheet1!. I copied the standalone chart & the sheet with all the data from another file…so I want the chart to pull the data from this file instead the other file.
Using your tool, in ‘Find old text:’ I entered ‘C:\Documents and Settings\gy\Desktop\Plans\[Master DATA TEST.xls]Opt Sun Data’! to Replace with: Opt Sun Data!
Then I click the magic “Change Active Chart Only” button (it’s the only one highlighted), and the I get the wonderful MS Excel error message window.
Can you please tell me if I’m doing something wrong? Or is there some bug…
Thanks so much!
Jon Peltier says
I think we’ve just found a bug. When the formula is like this, either long, or referring to a closed workbook, the formula in the formula bar is:
=SERIES(‘C:\Documents and Settings\Jon Peltier\My Documents\test\[ChartSource.xls]Sheet1’!$C$1,
‘C:\Documents and Settings\Jon Peltier\My Documents\test\[ChartSource.xls]Sheet1’!$B$2:$B$6,
‘C:\Documents and Settings\Jon Peltier\My Documents\test\[ChartSource.xls]Sheet1’!$C$2:$C$6,1)
But VBA thinks the formula is:
=SERIES(‘C:\Documents and Settings\Jon Peltier\My Documents\test\[ChartSource.xls]Sheet1’!$C$1,
‘C:\Documents and Settings\Jon Peltier\My Documents\test\[ChartSource.xls]Sheet1’!$B$2:$B$6,,1)
Note the missing third argument?
When I open the source workbook, the formula bar and VBA both agree that the formula is:
=SERIES(‘[ChartSource.xls]Sheet1’!$C$1,
‘[ChartSource.xls]Sheet1’!$B$2:$B$6,
‘[ChartSource.xls]Sheet1’!$C$2:$C$6,1)
The third argument, the Y value range, has been restored to the formula.
Nothing I can do about it, the bug in present in Excel 2003 and presumably earlier versions. 50-50 whether it’s still in 2007.
Jon Peltier says
So, oh yeah, if possible open the source workbook, then the change is easy and my utility works fine. You may have to include the workbook name with the sheet name in the ‘Change To’ box.
Rudyard says
Hi Jon,
Great tool and it will really come in handy as I need to replace around 50-60 charts.
I am having some problems though- I’ve installed the add-in successfully and I’m trying to run the tool (tried it in Excel 2000 and 2003) and (although I’ve got it to work once) it doesn’t seem to work now. It either appears to have completed successfully but when I check the series text it has not been replaced, or I get the error message “Unknown error. Invalid procedure call or argument”.
I’m trying to replace text such as =’One Analysis’!$A$4:$A$12 with ‘=’Two’!$A$4:$A$12 but I get the above errors occur.
I’m sure I’m doing something wrong but can;t figure out what!
Any ideas…?!
Jon Peltier says
It’s very important to match up your strings exactly. I just made a sample workbook with your sheet names, and ran a few tests. The utility worked as expected. Here is what to enter in the dialog.
Old Text:
‘One Analysis’
New Text (depending on the precise name of the sheet):
‘Two’
‘Two Analysis’
‘Two Analyses’
You must match the single quotes exactly, and note that you need to use straight quotes, not the curly ones that WordPress imposes here. If you include in at the beginning or end of the Old Text, you must also include it in the New Text.
Rudyard says
Hi Jon,
Have matched the text exactly and included the straight quotes as you metioned but I still get the same error messsage. One thing I have noticed is that the source values in the graphs includes the full path name, i.e.
‘H:\ABC\ABC Projects\ABC Programme\2. Project A\Business Analysis\Data\[Department A v0.3.xls]Department A Raw’!$D$3:$D$10)
rather than simply
‘Department A Raw’!$D$3:$D$10
Do you think this is casuing the problem? If so, is there any way of getting round this?
Any help gratefully appreciated…as ever!
R
Rudyard says
Hi Jon,
One other issue I have…!
When I have updated the graphs with the correct source string, the graph graphics update, however the data values don’t i.e. the bar on the bar chart reflects the correct and updated value but the data value appended to the bar is wrong (i.e. it still reflects the old value even though the bar is pointing to the updated value).
Do you know of a quick way of resolving this other than going into each graph individually and reassigning the data value?
Any help much appreciated as ever…!
R
Jon Peltier says
Rudyard –
Open the source file. I have encountered problems with long formulas like this.
Jon Peltier says
I haven’t seen this. How are the labels applied? Do you manually edit them at all, or do you use one of the built-in options (i.e., show values)?
jo says
I’m using Excel 2003, SP3. I have both the source and the chart spreadsheets open. My series formulas include the Data source even though it is open. When I try to change text in the string (e.g., series has “=SERIES'[…]April’!$A$…” and I want to change “April” to “May”), I get “Unknown error Type Mismatch”. I’ve tried everything I can think of (single quotes, double quotes, etc.) and have read all the above and am stumped. The full series string is 277 characters long. Any ideas? thx
Jon Peltier says
Jo –
I just tested this using this utility, freshly downloaded.
Could you tell me what the entire series formula is? Copy it and paste it into a comment. Also, what are the exact sheet names? Every little character here can mess up the process. Are the data sheets both in the same workbook?
What kind of chart is it? If it’s a line or XY chart, does the series currently have data? That is, are there actual data points visible in the chart? If the series data consists of all blanks, for example, you can’t edit the formula using VBA.
jo says
3D Bar chart. Has Data — lots of zeros, but that’s still data. : )
=SERIES(‘[DCF Count Totals-2009.xls]August’!$A$26,{“DCFs Sent”,”Unreviewed Discrepancies”,”CRA Review Discrepancies”,”INV Review Discrepancies”},(‘[DCF Count Totals-2009.xls]August’!$B$26,'[DCF Count Totals-2009.xls]August’!$K$26:$L$26,'[DCF Count Totals-2009.xls]August’!$N$26),5)
Jon Peltier says
That turns out to be One Of Those Things. I tested with a similar formula and it worked fine. Then I realized your formula was longer, so I made my sheet names 31 characters long, and the program crashed. I started shaving off characters and stepping through the code. It turns out that until the series formula is a manageable length (only around 200 or so characters), Excel doesn’t pass the entire formula to VBA. Therefore, the formula that VBA edits and passes back to Excel is not valid.
Workaround: temporarily change the sheets to something short, like “Sheet 1” and “Sheet 2”.
Mark says
Awesome add-on Jon, it’s saved me a whole heap of time!
Thanks,
Mark
Stuart says
Thanks for the utility. Very useful.
But, how do I get the floating menu that comes up when Excel starts to locate itself in the menu bars. I have to move it out of the way everytime.
Thanks,
Stuart
Jon Peltier says
Stuart –
For such a simple utility, I did not build in the ability to remember where the user left the toolbar. It always comes up in the same place.
Stuart says
Oh well, thanks anyway. Small “price” for me to pay for a very useful function.
Stuart
Karen says
Brilliant! This is just what I needed! Thank you!
Nile says
Mine worked once and then it stopped! Using Excel 2003.
Nile says
I see, it is case sensitive…no problems.
Ari says
Hey Jon, thanks for a great utility, it seemed to be JUST the thing I had been looking for.
And it did work on some of graphs, but not most. Upon looking closer, I found that most of the graps have a blank Data range field in the “Data Range” tab of the Source Data dialog. At the bottom it says “The Chart Data Range is too complex to be displayed…”
Instead I have to switch tabs to “Series” and find the data ranges there, for each individual Series. But it seems your utility doesn’t work for the ranges within the Series tab. Is there any way to fix this?
(by the way the graps are not very complicated, just a series or two and a date range)
Thanks
Jon Peltier says
Ari –
My utility only works for the ranges in the Series tab of the dialog, which are reflected in the series formulas. Whether the Data Range tab shows a range or not is immaterial.
What do your series formulas look like? What are you using for the Change From and Change To strings?
N J Moore says
Hey John,
Just used your macro, works a treat and I can now happily copy 12 charts 10 times with new data with minimum fuss.
Thanks a lot.
Noel.
aras says
Hallo,
In excel 2003 to Define Name I write
Names in workbook: Values
Refers to: =Sheet1!$B$7:$C$7-Sheet1!$B$3:$C$3
and to SERIES formula I write
=SERIES(;;Book1!Values;1)
It works perfect in excel 2003, but doesn’t work in excel 2007.
Also other function
=SUM(Sheet1!$B$2:$D$8)
or
=AVERAGE(Sheet1!$B$2:$D$8)
or
=MAX(Sheet1!$B$2:$D$8)
or another function
and create Chart with SERIES formula:
=SERIES(;;Book1!Name;1)
It works perfect in excel 2003, but doesn’t work in excel 2007.
Why?
Maybe it is excel 2007 bug or anything else?
Thanks
Karl Williams says
This is an excellent utility and will save me a lot of tedious work.
Thanks very much.
mikezang says
Hi,
Your site is great! that is very useful! I have a question about using VBA to scroll chart.
I use code as below to scroll chart range:
Set oData = oSheet.Range(“A” & iRow1 & “:” & “A” & iRow2)
ActiveChart.SeriesCollection(1).XValues = oData
ActiveChart.SeriesCollection(7).XValues = oData
Set oData = oSheet.Range(“B” & iRow1 & “:” & “B” & iRow2)
ActiveChart.SeriesCollection(1).Values = oData
Then we can use Name to change data range as below:
ActiveWorkbook.Names.Add Name:=”name”, RefersToR1C1:=”=Sheet1!R1C1″
My question is which way is more faster?
Jon Peltier says
You would have to test the two versions of code to see which is faster.
If you were able to define the name using a dynamic formula in the Refers To box of the Define Name dialog, I’m sure that would be faster than any VBA approach.
Hero says
You saved me about 12 hours worth of work. Thanks.
Rene says
I used the change series formula for the first 10 charts on a worksheet and it work wonderful. When I went back to finish making changes on the rest of the charts it shuts down excel and when I restart excel the add on is no longer available to use. What do I need to do to correct the problem? Using excel 2007 and vista
Jon Peltier says
Hi Rene –
I don’t know what causes the shut-down of Excel. If the crash occurred during the same session in which you installed the add-in, you have to reinstall the add-in.
Rene says
I haven’t a clue as to what is happening with excel. I re-installed the add-in, still crashing, re-installed excel, still crashing.
The find and replace for changing the workbook doesn’t work for me…not holding my jaw right is my guess. I am beginning to think my file is really corrupted.
Jon Peltier says
Rene –
Did the workbook originate in Excel 2003 before you tried changing the series in 2007? Sometimes (rarely) charts and highly complex worksheets may be corrupted by the conversion, I don’t know why, and I don’t know how to fix them.
Rene says
To be honest I don’t think so, although it is an old workbook. The chart was created recently.
I just got an error message
runtime error ‘-2147417848 (80010108)’ : method ‘count’ of object ‘countobjects’ failed
Jon Peltier says
Rene –
You mean “method ‘count’ of object ‘CHARTobjects’”, don’t you? It sounds like something is corrupted, because even if there were no chart objects, the answer would be zero.
Rene says
That is what I meant to type
What are my options? Excel ran diagnostics…all ok now. I don’t know what it fixed…I was just happy. Excel has crash again. I’ve changed 2 numbers in 12 charts and no crashes. Crossing my fingers
Rene says
urghh crashed after changing 2 numbers with the add-in.
Microsoft diagnostic page is unavailable. The one after running the initial diagnostics.
Jon Peltier says
If you’re on SP2 and you’ve run diagnostics, I don’t know what else can be done. I think you’ll have to recreate the workbook, including the sheets and the charts.
Rene says
Can I copy the formulas by changing = to ` copy and change back to = to keep the formulas? Then recreate the charts from scratch? SP2 was re-installed Wed after I re-installed excel. After the diagnostics in the excel options/ resource it won’t bring up the web page after you press the continue button. Could that additional diagnostics help? Or is it a lost cause? I really do appreciate your help.
Jon Peltier says
I think you could change the = to ‘# (single quote then hash mark), then copy the used range of the sheet, and paste special values into the new workbook. The single quote doesn’t get pasted. Then change # to = to restore the formulas.
Rene says
Thanks, its working. Can the add-in change selected multiple charts on a page? I’m getting a unknown error type mismatch error. I’m trying to change 3 to 28 on 9 charts. The row # changes but column remains the same.
Jon Peltier says
Rene –
The add-in should change any and all selected charts in the workbook. But I just tested in 2007, and it misbehaved when trying to change a subset of the charts on a sheet. I’ve just uploaded a new version that seems to work in this context, at PTS_ChangeSeriesFormula.zip. I apologize for the problem.
Rene says
Thank you. It worked for me.
Rene says
It just might be me. When I changed 3 to 53 the add-in changed 3 to 53 and 23 to 253. When I tried 3 to “53” it didn’t work.
Jon Peltier says
To change an exact number, enter $3 in the “From” box, and it will only change 3, not 23. No need for the $ in the “To” box.
Rene says
Thanks. Now to figure out why the workbook crashes when I use the macro.
Bhavik says
Awesum stuff mate saved heaps of time.
Alex Kerin says
Jon, awesome, I was annoyed by this a few days ago, and lo behold…
Now, in some of that spare time you have, I would like drop downs of the sheet names prefilled, ideally with the change to box prefilled with the current sheet, and the from box filled with sheet information from the series….
Alex.
Jon Peltier says
Alex –
You want a hierarchical set of dropdowns. The first lists workbooks, the second lists worksheets in the workbook selected in the first. Then in case the workbook you want isn’t available, you want a file open button to select a closed workbook. Then maybe a US port connected to your cranium so the program can use the data you’re thinking of.
Alex Kerin says
Perfect! When can I expect the beta?
Jon Peltier says
Is 2:30 soon enough?
Stern says
Jon- Using 2003 – shows in Add-ins “Change Series Formula Addin” with check mark. But not in View- Toolbars. I don’t see the Change series Formula toolbar. Please help.
Jon Peltier says
Stern – Look for a menu called PTS Charts on the Worksheet and Chart menu bars.
stern says
Jon- you’re my hero!! Duh, was right on top, looked in the wrong place for it. This utility is a dream. Stern
Foort de Jong says
Worked fine for one time only. Now I get:
Unknown error
Invalid procedure call or argument.
Any suggestions?
Jon Peltier says
“Unknown Error” often indicates a problem with Excel versions. Make sure Excel is updated and you’re using the latest service pack (SP3 for Excel 2003, SP2 for 2007).
It may mean you’ve entered something invalid, though the program should ignore user errors.
Neil Dingwall says
Thanks for a great utility. On a related note, is there any way to do something similar to linked pictures of charts on the same page as the chart (e.g. created by “copy as pictures”)?
Foort de Jong says
I have Excel 2003 with SP3 installed so that should not be the cause of the problem. Does the code require all files to be open (even the ones that I contain the data which I want to replace the link for)?
Jon Peltier says
Neil –
If you update the charts that the linked pictures display, the linked pictures should reflect the changes.
Why are you using linked pictures? Why not use copies of the charts? The data the copied chart links to is the same as in the original chart, and charts are much more reliable than linked pictures of charts.
Jon Peltier says
Foort –
I believe that the files containing the chart data have to be open when you are manipulating the series formulas in this way.
Neil Dingwall says
Thanks Jon.
The situation is that I have a sheet that has some charts and linked pictures of areas that I use to simplify printing and amalgamate ranges. Once everything is set up to my satisfaction, I need to make six or seven sheets which use the same charts and pictures but on different individual data sets. I copy the sheet and the new sheet name defines the data that is populated on that particular sheet. I can the use your add in to update the charts to point to the current sheet but the pictures remain pointing to the original sheet. What I was thinking was needed was an ability to do a find and replace on the source link for each linked picture on the page just like you do for charts?
Neil
Jon Peltier says
Neil –
I got it. You can assign a name to each range you want to show in a linked picture, so the link looks like
=Sheet1!RangeName1
You can then edit the link for each linked picture, changing it, for example, to
=Sheet1!RangeName2
Neil Dingwall says
Thanks again Jon.
I can change the linked picture link for each picture by manually adjusting it in the formula box but the link doesnt “show up” to Find and Replace and therefore there is still no obvious way to mass change all linked pictures on one sheet. I guess I will need to continue with the manual process.
On a perhaps related note, in my version of excel 10 it does not seem possible to paste link a copy of a chart – when I select a chart and copy the only available option is paste as picture. Is this normal?
Jon Peltier says
Neil –
I suppose the links can be bulk edited using VBA. I’ve never tried that.
When you copy a chart, don’t you have a regular Paste option? That would be the option that has links to the original data.
Bryan says
Does this work with MS 2010?
Bryan says
Jon,
You just saved me from another long weekend. The change series formula tool worked perfectly in MS Excel 2010 – Amazing! Where can I send a donation – Seriously?
Thanks again.
Bryan
Jon Peltier says
Bryan –
Thanks for testing this for me. I’ve tested all of my commercial products, but these fun little ones are sometimes forgotten.
Bob says
I am plotting up to 255 XY scatter series with Excel 2007, using VBA to add the chart and to modify the plot once made. One of the modifications is to change the X axis values for each series, keeping the Y values the same. To make this change, I replaced the X data range in each formula with the new X data range (different ranges for different series), and then applied the whole formula to the series:
xSeries.Formula = ABCstringNewArr(ixSeries
This works, but takes minutes to execute; the time required per series increases with the number of series in the chart. Screen updating is not the issue, nor is the time required to manipulate the formula string.
I found your web page while looking for ways to turn off/on chart updating (and have not found a way), but after reading your page and cited references I decided to try just replacing the new X data range instead of composing the whole formula and replacing that:
xSeries.XValues = Range(ABC_XValue(ixSeries))
This brought execution time down from minutes to seconds.
Of course I’ll make use of this improved time efficiency, but I am at a loss to explain it. Can you shed any light?
Jon Peltier says
Bob –
A lot of the things that were fast in 2003, and a lot of options that were equally fast, don’t perform the same in 2007. I don’t know why; it’s just the way the new charting module is.
Sometimes you have to do things in a particular order in 2007, which never mattered before. Sometimes you have to turn screen updating on in 2007, or the chart will not update to reflect changes made by the code. 2007 seems to require more waiting and more cursing.
DDJ says
Hi Jon,
Great website, and a great utility that has saved me lots of work. I do have a suggestion for an enhancement though.
On my charts I use the JWalk Chart Tools add in to apply labels that are linked to the cells that contain the labels. So, if I change the labels in the cell, it reflects into the chart as well.
On testing your utility I found that it does not change the formula for the labels when it changes the data range formula. i.e. my labels are still pointing to the original worksheet.
Is there any chance you could update the utility to handle labels as well?
You could perhaps have it as a tick box option in case people don’t want to change their label locations, but for me I’m having to do it manually which is slow and potentially introduces errors.
Interested to know what you think.
Jon Peltier says
Jason –
Data labels are completely independent of the series formula. The data label tools do something completely different. When you change the series formula with my utility, you will have to reapply labels with JWalk’s tool. Why do you need to redo the labels manually?
DDJ says
Thanks for the quick response.
I have a series of column charts each one showing positive, neutral and negative feedback percentages from a survey, as well as a second set of data points which show the average score from a wider group for each question. You can then see whether the score is above / below the average. On each of my columns I apply a data label driven by a rule. So, if the positive score is more than 70%, it is a strength, and I put a windings tick at the base of the column. For the neutral and nagative columns, if they score above a certain percentage they are regarded as an area of concern, and I put a windings cross at the base of their columns.
All in all I have 4 regions to report on. 20 charts in each region covering 100 questions. When I copy region 1’s sheet to get region 2, I run your utility to change the data in the region 2 charts, but the labels stay pointing at region 1.
Both the JWalk and the XY Chart Labels tools only let me do one label set at a time. I have to apply 3 label sets to each chart…that’s a lot of labels to re-apply so I was hoping there’d be a quicker way to do it!
I’ve got some pretty basic VBA skills, so if you’ve come across something like this and can point me to it, that would be great. Or, if your interest is piqued, you writing it would be even better!!!!
Jon Peltier says
Jason –
I understand. Find & Replace won’t work with labels, since you cannot see the link formula in a label via VBA. I am thinking of something a little different than find & replace. How about a dialog with three data entry boxes: one for the new X values, one for the new Y values, and one for the labels?
Are your charts on the worksheet with the data? If so, copy the whole sheet, charts and all. The charts on the copied sheet should then link to the data on the copied sheet. Then paste the new data onto the copied data. No need to adjust any links. I wrote about this technique in Make a Copied Chart Link to New Data.
DDJ says
I tried the copy technique this morning, but with slight differences between 2 of my charts. I used the JWalk tool to label one chart with the “create links to the label cells” ticked, and on the other chart I used the JWalk utility for the labels but without the “create links to the label cells” ticked.
Interestingly, neither set of labels reflected the new data. This suggests to me that the link that is created is in the form of “[sheet name]!range”, rather than just “range”.
I have looked at the XY Chart Labels utility and this doesn’t give you the option of creating a link so that doesn’t help. There is an option to call this from VBA so I will have a look and see whether I can make this work.
I’m not sure if your dialog idea really advances my particular problem, although others may benefit from it. I can run your current utility across all of my charts at once for find and replace. The labels I still have to do manually if you can’t get the link string in VBA.
So, despite your help I am still at square one – manually re-doing labels for now. :-(
Thanks Jon.
Jon Peltier says
Jason –
Two factors are making this difficult. (1) Data labels in Excel charts are not linked to a worksheet range in the same flexible way that the data point values are linked. (2) The VBA object model for dealing with linked text (data labels as well as chart and axis titles) in charts is deficient.
JWalk’s Chart Tools provides an option to link the labels to cells. Bovey’s Chart Labeler always links the labels to cells. The link is of the form =Sheet1!$A$1, that is, absolute range references in a specified sheet. This is how all links from chart to worksheet have to be specified.
My series formula editing tool only edits the series formula, not other links within the chart. It is possible to see what the link formulas are in a data label, using the ancient Excel 4 Macro (XLM) language, if you want to do a little coding of your own.
To read the formula of a data label, select it, then run this command in the VBA Immediate Window:
?ExecuteExcel4Macro(“GET.FORMULA(SELECTION())”)
It returns a formula in R1C1 notation, or the text of a label if there is no link. To set the label you can select it and use:
ExecuteExcel4Macro(“FORMULA(“”=Sheet1!R1C1″”)”)
(note the R1C1 style address) or use regular VBA without having to select the label:
MySeries.DataLabels(1).Text = “=Sheet1!R1C1”
where MySeries is a VBA variable that references the series with the labels. Note again the R1C1 notation and that you need to do the labels one by one.
Shain Allen says
Fantastic little program. Has saved me a few hours work for the 50 odd charts I have just had to change. Thanks so much. keep up the good work!
Jo Azary says
Love this utility! Thank you!! Have a question that is probably not related, but I’m hoping you can answer. I have two workbooks, one 12 tabs of tables with monthly counts, the other is charts from those monthly counts. I only need the charts once a month, which is why I’ve put them in a separate workbook. When a new year starts, sometimes the line items are drastically different. I rework the first set of chart tabs(4), then copy those for each month and edit the formulas (did I mention I love this utility?) :)
I get an interesting error when I copy the charts: “A formula or sheet you want to move or copy contains the name ‘x’, which already exists on the destination worksheet. Do you want to use this version of the name?” There are actually four errors (four names). I have no idea where these names are defined. I can’t find them in the monthly counts workbook. Any ideas? I’m using E 2007, but I think I also had this error in E 2003. I can send a stripped down sample version (after first of year) if you need to see it.
Have a very Merry Christmas, and thanks!
j
Jon Peltier says
Jo –
This is typical behavior of a copied sheet that contains names (also called named ranges). A name is a label applied to a range in the worksheet, to help with organizing the sheet and managing calculations. You can use Ctrl-F3 to open the Define Names dialog to see where the names are defined. If you can’t find them there, they may have been hidden by some clever programmer. Jan Karel Pieterse’s free Name Manager utility (http://jkp-ads.com) can help you manage all names, even hidden ones.
Danièle says
Hi Jon,
I am currently working on graphs where your little add in would come very handy.
The graphs are bubble graphs with location points ( x and Y) and strength of response in that location ( bubble size).
I need to change the column for the “strength values”, depending on the method of measuring the strength values, but if I change the column letters, such as “$BF$” to “$BG$” or “BG”) I get a message “Unknown error; type mismatched” and the OK button does not stop anything….
On a good note, I used your trick of changing markers using any shapes.
It works very well with the bubble charts so that I could use different markers and their sizes changes just as with the bubbles.
I am not using that type of graph very often ( actually practically never), but it was perfect choice in this instance.
The graphs are for illustration and relative sizing within each group so that works really well in this case.
I have also used your trick of using a dummy series to keep all sizes relative to a chosen size using the dummy series and removing the format so that it is not visible.
All in all, the graphs look great! Thanks!
Danièle
John says
How hard to include a third button “Change Charts on ALL Sheets”? Or otherwise to allow a workbook–having 46 chart sheets that plot data from 23 columns of a spreadsheet two different ways–to be used for a different spreadsheet? In a different workbook? Things that could change: data file source folder; filename; spreadsheet name; number of rows of data; titles of plotted columns (thus, of plots).
Jon Peltier says
John –
The first item, a button to extend the changes throughout the workbook, would be relatively straightforward. Maybe it’s time for an update. The Find/Replace functionality works on filename, sheet name, row numbers and column letters, as long as you are careful when entering your strings.
The major changes you then describe sounds like a rather large custom chart wizard. It might actually be as much a data management project as a charting utility.
Matthias says
Exactly what I looked for, works great and saves me a lot of time.
Thanks al lot, Matthias
Philip Hodson UK says
Hi Jon,
From a row of stock prices, lets say, in A1:Z1, I want to calculate Simple Moving Averages of different lengths.
So, e.g. in A2, for a seven-day SMA, I can enter the formula =SUM(A1:G1)/7.
But I want to adjust this to 11-day, 12-day, 13-day SMA’s, or any other length.
Here is my legend in row 3:- “Enter a number of days for your SMA:-”
and into A4 I put a cell, heavily bordered, in which I enter a value to represent a number of days.
Let us say I enter “12” in cell A4.
How can the formula in A2 pick up the “12” and adjust (A1:G1) to (A1:L1) so that the whole formula now reads =SUM(A1:L1)/A4 ?
or again, if I want an 8-day SMA, the formula will change to =SUM(A1:H1)/A4, etc.
Regards,
Philip
Maurizio says
Hi John
Your tool is a fantastic add -in for excell, but I have s serious Problem.
I am running Excell 2003, I have multiple XY scatter graph that compares datas from differents workbooks, the sheets where datas are stored have all the same name, so In the SERIES formula I have [Book1.xls]Sheet1, [Book2.xls]Sheet1,….,[Book#.xls]Sheet1 and so on. I have to change the reference sheet and obtain a graph with [Book1.xls]Sheet2, [Book2.xls]Sheet2,….,[Book#.xls]Sheet2.
When I use yor add – in to change the Sheet1 to Sheet2, I lose the reference to the correct Book so all curvers are like [Book1.xls]Sheet2, [Book1.xls]Sheet2,….,[Book1.xls]Sheet2.
What is happening?
I use Microsoft Excell 2003 SP3
Thank you very much for your Time
Jim says
Hi John,
Great add-in and I’ve put lots of mileage on it over the last few years. Having a bit of an issue in Excel 2007, though. It doesn’t seem to work if the series includes a reference to a named range. I’ve solved my immediate problem by not using names in the series formulas (a copy of the sheet to a new workbook removed the names, replacing them with absolute references, which is fine for my needs), I just wanted to let you know in case you’re working on a new release.
Cheers,
-Jim
Jon Peltier says
Jim –
Thanks for the bug report. I’ll look into it.
No new version just yet…
Brien says
Hi Jon,
You have boosted my productivity no end. This is great! Thank you.
Muhammad Abduhu says
Hi Jon,
This is a fantasic add-on that should ship with Excel. Thank you very much.
Dharmpal Rawat says
I am facing the issue in excel 2007
I have put the series function in my charts and its work perfectly, but when I reopen the file the series function is not working and its also not shown in the formula bar. please let me know the reason for that or tell me the solutions.
regards
Jon Peltier says
Dharmpal –
Make sure you’ve installed all the service packs for Excel 2007 (I think it’s up to SP3). Prior to SP2, there were many strange problems with Excel 2007 charts.
Christina Mikhail says
I have two workbooks that are indentical, except the project data is different. Each has 18 tabs with 3 charts each. You have just saved me HOURS of work. Thank you!!!!
Tony says
Oh my God, thank you so much!
JOy says
Works like a charm. Thanks
oli says
Hi Jon,
This tool is awesome!!
It just saved me hours of agony!
Installed it in Excel 2010 and it works great.
Excellent work and thanks for sharing!
best,
oli
Jed says
Love this add-in. What a time Saver. Thank you very much for posting it.
David says
Hi,
I have a question re excel charts. If I want to graph cumulative sales data in a month, how can I automatically get the chart to graph only up to a certain day? For example, if today is the 11th how do I only show values up to that date. When data populates for the 12th I want to include that data as well with amending the series values.
Thanks,
David
Jon Peltier says
David –
Check out Dynamic Chart Review and Easy Dynamic Charts Using Lists or Tables.
Karla says
Your my hero. Your Change Series Formula utility works like a CHAMP. I am amazed and you have just saved me hours of work. I have 29 charts and needed to remove one thing in all of the charts. This utility did in a second. Thank you so much for sharing. I will let everyone know about your site.
David Plakos says
Genius. Ten points for Team Jon.
Gill says
Fantastic Add On – I’ve got 11 worksheets each with 23 charts so it’s just saved me HOURS!!!!
THANK YOU!!!