How to Edit Series Formulas
by Jon Peltier
Wednesday, December 17th, 2008
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
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, and I provided a utility that does the task for you. I’ve just recently updated the utility to account for glitches in Excel, and to streamline using the utility.
The Change Series Formula Utility
The new utility is located in PTS_ChangeSeriesFormula.zip. You can install it following the protocol in Installing an Excel Add-In.
Upon installation, the utility creates a toolbar (In Excel 2007, the toolbar buttons are buried on the Add-Ins tab of the ribbon).
Not too fancy or complicated. Suppose I have the following scenario, a chart showing data for Alpha in column B, and I want to show Beta from column C instead.
If it’s only one series in one chart, I can drag the colored highlight rectangles in the sheet, or edit the series formula in the formula bar. But if there’s more, why waste time? With the chart selected (I like to select the series, so the formula bar is visible and I don’t have to remember what to change), click on the Change Series Formula button on the toolbar. Enter the old and new text in the dialog. In the old text entry box, I like to explicitly use punctuation like the dollar signs, to make sure I’m changing a column designation and not some other text in the formula. In the new text box, it doesn’t matter, because Excel capitalizes and adds dollar signs automatically.
Click the Change Active Chart Only button (the other button is inactive because there are no other charts on this worksheet).
Magically, the series formula, the colored highlights, and the chart itself now reference the new data.
Here is how I would extend a chart’s data from row 6 to row 12. In this case there are multiple charts on the worksheet, and you have a choice of changing the active chart or changing all charts on the worksheet.
This dialog shows how to change references from Sheet1 to Sheet2. In addition, the buttons reflect that multiple charts have been selected using Shift+Click. The options are to change all selected charts or all charts on the worksheet.
It’s a pretty smart utility, and it has saved me tons of time. Download it, install it, and use it. Give me feedback, make suggestions, and report errors in a comment below.
Related Posts:
- Adding Excel Chart Data
- Make a Copied Chart Link to New Data
- A Linked Table to Sort Data for Charting
- Stack Columns In Order Of Size
- LOESS Utility – Awesome Update
- UDF to Calculate an Arbitrary Formula
- Chart Series Data Highlighting
- Dynamic Chart with Multiple Series
- Deming Regression Utility
- Gas Prices – Interactive Time Series
Posted: Wednesday, December 17th, 2008 under Data Techniques.
Comments: 40
Comments
Comment from Steve
Time: Wednesday, December 17, 2008, 4:17 pm
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?
Comment from Jon Peltier
Time: Wednesday, December 17, 2008, 5:59 pm
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.
Comment from JP
Time: Wednesday, December 17, 2008, 10:23 pm
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?
Comment from Jon Peltier
Time: Wednesday, December 17, 2008, 10:56 pm
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.
Comment from Tom
Time: Thursday, December 18, 2008, 9:52 am
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.
Comment from Jon Peltier
Time: Thursday, December 18, 2008, 10:17 am
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.
Comment from Jon Peltier
Time: Thursday, December 18, 2008, 12:02 pm
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.
Comment from Zach
Time: Monday, December 22, 2008, 10:49 am
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.
Comment from Jon Peltier
Time: Monday, December 22, 2008, 11:05 am
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.
Comment from Manfred Simonet
Time: Friday, January 9, 2009, 9:59 am
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
Comment from Jon Peltier
Time: Friday, January 9, 2009, 10:21 am
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:
For Each srs In ActiveChart.SeriesCollection : Debug.Print srs.AxisGroup, srs.Formula : Next 1 =SERIES([Book4]Sheet1!$C$5,[Book4]Sheet1!$B$6:$B$10,[Book4]Sheet1!$C$6:$C$10,1) 2 =SERIES([Book4]Sheet1!$D$5,[Book4]Sheet1!$B$6:$B$10,[Book4]Sheet1!$D$6:$D$10,2)
Something else must be going on. I don’t use the Watch window, so I can’t comment on your last observation.
Comment from Dolores O’Connor
Time: Sunday, February 8, 2009, 5:13 am
Just used your Change Series Formula in Excel 2007 and find it brilliant. Thank you so much for sharing your knowledge and expertise.
D
Comment from Denis Brennan
Time: Friday, February 20, 2009, 12:50 pm
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?
Comment from Jon Peltier
Time: Friday, February 20, 2009, 1:38 pm
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?).
Comment from gurs
Time: Saturday, March 7, 2009, 3:53 pm
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…
Comment from Jon Peltier
Time: Saturday, March 7, 2009, 4:06 pm
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.
Comment from gholly
Time: Wednesday, March 18, 2009, 2:24 pm
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!
Comment from Jon Peltier
Time: Wednesday, March 18, 2009, 3:03 pm
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.
Comment from Jon Peltier
Time: Wednesday, March 18, 2009, 3:05 pm
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.
Comment from Rudyard
Time: Friday, April 3, 2009, 9:31 am
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…?!
Comment from Jon Peltier
Time: Friday, April 3, 2009, 10:08 am
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.
Comment from Rudyard
Time: Monday, April 6, 2009, 9:34 am
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
Comment from Rudyard
Time: Monday, April 6, 2009, 9:50 am
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
Comment from Jon Peltier
Time: Tuesday, April 7, 2009, 5:49 am
Rudyard -
Open the source file. I have encountered problems with long formulas like this.
Comment from Jon Peltier
Time: Tuesday, April 7, 2009, 5:53 am
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)?
Comment from jo
Time: Wednesday, April 29, 2009, 10:54 am
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
Comment from Jon Peltier
Time: Wednesday, April 29, 2009, 12:12 pm
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.
Comment from jo
Time: Wednesday, April 29, 2009, 12:30 pm
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)
Comment from Jon Peltier
Time: Wednesday, April 29, 2009, 3:00 pm
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″.
Comment from Mark
Time: Friday, June 19, 2009, 11:13 am
Awesome add-on Jon, it’s saved me a whole heap of time!
Thanks,
Mark
Comment from Stuart
Time: Thursday, June 25, 2009, 12:43 pm
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
Comment from Jon Peltier
Time: Thursday, June 25, 2009, 1:30 pm
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.
Comment from Stuart
Time: Thursday, June 25, 2009, 4:16 pm
Oh well, thanks anyway. Small “price” for me to pay for a very useful function.
Stuart
Comment from Karen
Time: Thursday, July 30, 2009, 4:50 pm
Brilliant! This is just what I needed! Thank you!
Comment from Nile
Time: Tuesday, August 11, 2009, 3:26 pm
Mine worked once and then it stopped! Using Excel 2003.
Comment from Nile
Time: Tuesday, August 11, 2009, 3:33 pm
I see, it is case sensitive…no problems.
Comment from Ari
Time: Monday, November 9, 2009, 4:49 am
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
Comment from Jon Peltier
Time: Monday, November 9, 2009, 8:19 am
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?
Comment from N J Moore
Time: Monday, November 9, 2009, 8:41 am
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.
Comment from aras
Time: Saturday, January 9, 2010, 9:02 pm
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
Write a comment
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.