Ever make a copy of a chart, and all you want is do is change the worksheet containing the source data? Or change the X values of all series from column A to column B? It sure is a pain to edit each series’ source data range of series formula one by one.
The series formula is a simple text string, but there’s no Search and Replace feature in Excel that can access these formulas. But you can use some very simple VBA code to make wholesale changes to chart series formulas. Or you can skip all of the noise, scroll to the end of this article, and download the new Change Series Formula Utility.
About the Chart Series Formula
A more detailed description of a chart and its series formula is presented in The Chart Series Formula and How to Edit Series Formulas elsewhere on this site, but a brief description here is in order.
Every chart series has a formula which describes the data in the series. The simple chart in this example is shown below its data range; the series is selected to highlight the data used to plot the series and to show the SERIES formula in the formula bar.
=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$11,Sheet1!$B$2:$B$11,1)This formula can be broken up into four elements as follows:
=SERIES([Series Name],[X Values],[Y Values],[Plot Order])In our example:
=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$11,Sheet1!$B$2:$B$11,1)Sheet1!$B$1 contains the Series Name
=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$11,Sheet1!$B$2:$B$11,1)Sheet1!$A$2:$A$11 contains the X Values or Category Labels
=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$11,Sheet1!$B$2:$B$11,1)Sheet1!$B$2:$B$11 contains the Y Values
=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$11,Sheet1!$B$2:$B$11,1)and the series is plotted first (1) among the chart’s series collection.
The Series Name can be blank, a text string in double quotation marks, a reference to a worksheet range (one or more cells), or a reference to a named range (named formula). The X Values can be blank, a literal array of numeric values or text labels enclosed in curly braces, a reference to a worksheet range (one or more cells), or a reference to a named range (named formula). The Y Values can be a literal array of numeric values enclosed in curly braces, a reference to a worksheet range (one or more cells), or a reference to a named range (named formula). The Plot Order can only be a whole number between 1 and the number of series in the chart.
Editing the Series Formula
The series formula is a text-based Excel formula like any other. You can click in the formula bar, and edit the formula manually, to change aspects of the charted series. Select part or all of the formula, type in the text you want there instead, and press Enter to apply the changes (or sometimes to cause an error!). Alternatively you can select one range address in the formula, then click and drag with the mouse to insert the address of another range in the worksheet.
But if you have to do this for every series in your chart, and every chart on the worksheet, you will be editing in the formula bar all day. So let’s make VBA do some of the tedious work for us.
Editing the Series Formula with VBA
The meat of this technique involves this very simple VBA command:
Series.Formula = WorksheetFunction.Substitute(Series.Formula, OldString, NewString)Basically, Substitute works by replacing every instance of OldString in the input text string (Series.Formula) by NewString. You could use Replace instead of WorksheetFunction.Substitute, but this would not be compatible with Excel 97.
There are a few tricks I’ve learned to improve the reliability of this technique. First, OldString should be entered using the same case as is found in the formula. For example, “b” will not find any references to column B in the formula. Second, to make sure you don’t trash any worksheet name in the formula, use “$C$” rather than “C” if you are replacing references to column C.
In the simplest version of this utility, you can use a couple input boxes to ask the user for OldString and NewString. A little error trapping makes sure that there is an active chart and that OldString is valid.
Program to Modify the Active Chart
Sub ChangeSeriesFormula()
''' Just do active chart
If ActiveChart Is Nothing Then
'' There is no active chart
MsgBox "Please select a chart and try again.", vbExclamation, _
"No Chart Selected"
Exit Sub
End If
Dim OldString As String, NewString As String, strTemp As String
Dim mySrs As Series
OldString = InputBox("Enter the string to be replaced:", "Enter old string")
If Len(OldString) > 1 Then
NewString= InputBox("Enter the string to replace " & """" _
& OldString & """:", "Enter new string")
'' Loop through all series
For Each mySrs In ActiveChart.SeriesCollection
strTemp = WorksheetFunction.Substitute(mySrs.Formula, OldString, NewString)
mySrs.Formula = strTemp
Next
Else
MsgBox "Nothing to be replaced.", vbInformation, "Nothing Entered"
End If
End SubProgram to Modify All Charts on the Active Sheet
Adding another loop to iterate through a sheet’s chart objects will change every chart on the sheet.
Sub ChangeSeriesFormulaAllCharts()
''' Do all charts in sheet
Dim oChart As ChartObject
Dim OldString As String, NewString As String
Dim mySrs As Series
OldString = InputBox("Enter the string to be replaced:", "Enter old string")
If Len(OldString) > 1 Then
NewString = InputBox("Enter the string to replace " & """" _
& OldString & """:", "Enter new string")
For Each oChart In ActiveSheet.ChartObjects
For Each mySrs In oChart.Chart.SeriesCollection
mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, OldString, NewString)
Next
Next
Else
MsgBox "Nothing to be replaced.", vbInformation, "Nothing Entered"
End If
End SubHow the program works
Here is our chart again, bet we’ve got an extra column of data. The chart shows column B plotted, but we want to plot column C instead.
Using the second version of the program above, all we need to do is run the code. I usually like to select one chart’s series, to remind me what string I want to change.
The program’s first input box asks for the string we want to replace ($B$ for column B):
The second input box asks for the string we want to replace that string with (“c”):
We see that the program has changed the series formula, while the chart now highlights the new range and shows the new data.
Warning: Blank Line or XY Series
When a line or XY series has no plottable points (that is, the data range has no numerical values to plot), VBA can’t read the formula. This example shows the problem.
We have no trouble changing the chart’s Y values from column B
to column C
even though column C has no data to plot. The chart merely looks blank, and we cannot select the series.
When we try to change from column C
to column D
Excel chokes, because it can’t read the series formula we want it to change.
Excel can’t read the formula for a line or XY series with no data, but fortunately it can read other types, so we can do just fine if we change the series temporarily to a column chart, edit the series, and change the series back. Here’s the code:
Sub ChangeSeriesFormulaAllCharts()
''' Do all charts in sheet
Dim oChart As ChartObject
Dim OldString As String, NewString As String
Dim mySrs As Series
Dim iChartType As XlChartType
Dim sFormula As String
OldString = InputBox("Enter the string to be replaced:", "Enter old string")
If Len(OldString) > 1 Then
NewString = InputBox("Enter the string to replace " & """" _
& OldString & """:", "Enter new string")
For Each oChart In ActiveSheet.ChartObjects
For Each mySrs In oChart.Chart.SeriesCollection
sFormula = ""
On Error Resume Next
sFormula = mySrs.Formula
On Error GoTo 0
' change to column chart if series is inaccessible
If Len(sFormula) = 0 Then
iChartType = mySrs.ChartType
mySrs.ChartType = xlColumnClustered
End If
mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, OldString, NewString)
If Len(sFormula) = 0 Then mySrs.ChartType = iChartType
Next
Next
Else
MsgBox "Nothing to be replaced.", vbInformation, "Nothing Entered"
End If
End SubThe intermediate chart still looks blank, but if we are attentive we might notice the horizontal axis has changed from the typical XY value-style axis to a typical column category-style axis.
After the series is changed, the columns reflect the new values.
Finally, the chart is reverted to its original XY style.
Warning: VBA Error
If the X value argument of a series formula consists of a Name instead of an array or a range address, VBA incorrectly reads the series formula, by enclosing the Name in single quotes. When assigning a formula to a series with a Name as its X value argument, VBA fails whether or not the erroneous single quotes are included. This is apparently a bug in Excel VBA, and it has been reported to Microsoft. The utility below has been upgraded with a workaround to avoid this error.
Essentially the workaround involves checking for a Name in the X values argument of the formula, temporarily removing the argument altogether from the new series formula, then reassigning the Name using the Series.XValues property.
The Change Series Formula Utility
I’ve updated the utility to take into account the funny VBA behavior when Names are used in the formula, and the problem with XY and Line charts that have no data to plot. The updated 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).
This utility incorporates all of the editing code. In addition, it replaces the separate old and new text input boxes with a single dialog with two text boxes. It also has two buttons, which based on what is selected and active, allows you to change the active chart, change any charts which you’ve selected using Ctrl+Click, or change all charts on the active sheet. It also remembers the inputs in the two text boxes, so you can reuse them for charts on another worksheet. Here is the Change Series Formula dialog:
Try this new utility, and please let me know if it doesn’t work in the comments below.
Worked Great! Thanks!
“Excel chokes, because it can’t read the series formula we want it to change”
As Microsoft says: http://support.microsoft.com/default.aspx?scid=kb;en-us;213379
Is a great bug… I´m trying to find new ways for a clean workaround. I think I have clean one and would like to share it.
Ok, when you try to do things like:
with activechart.seriescollection(1)
.Name = “Imgoingtocrash”
.Values = blabla
.Xvalues = blabla
end with
it will crash if the series cant find any value (in my case I deleted the cells from which Values and Xvalues were taken)
Well I´ve designed this (for the moment) “clean” workaround:
With ActiveChart.SeriesCollection.NewSeries ‘(in this case will be seriescollection(2))
.Name = “Im not going to crash”
.Values = blabla
.Xvalues = blabla
End With
With ActiveChart
.SeriesCollection(1).Name = .SeriesCollection(2).Name
.SeriesCollection(1).XValues = .SeriesCollection(2).XValues
.SeriesCollection(1).Values = .SeriesCollection(2).Values
.SeriesCollection(2).Delete
End With
With that, SeriesCollection(1) is safe and restored
Hope it helps over there for someone.
Cheers and thanks for your nice web.
This wroked fantastically – saved hours of my life!
John, you have helped me before with some other charting questions, but this is by far the greatest invention since sliced bread! This will shave hours off my updates for my monthly reports! Thank you for sharing this great piece of coding!
Great little utility, saved many hours for me!
Only little issue I had is my sheet had a comma in the name and this caused an endless error loop, once I figured that out, all worked perfectly!
Yes, there are a few things I haven’t yet written around. Commas in the sheet name, single quotes in the sheet name, etc. But these are things I don’t do much myself, so my motivation is limited.
I am running into a problem with this add-in. I am attempting to change x and y values location from a global scope (NEW ASR DATABASE.xls) to a worksheet scope (CF-DATA). Shown below is an example of one of my series formulas:
=SERIES(‘CF-DATA’!$AL$25,’NEW ASR DATABASE3.xls’!CF_X23,’NEW ASR DATABASE3.xls’!CF_Y23,17)
Shown below is an example of what I would like it to change to:
=SERIES(‘CF-DATA’!$AL$25,’CF-DATA’!CF_X23,’CF-DATA’!CF_Y23,17)
As you can see I am attempting to swap out the ‘NEW ASR DATABASE3.xls’! with ‘CF-DATA’!. I just can’t seem to get it to work, no such change occurs.
Richie -
I just tried this on my machine, in both Excel 2003 and Excel 2007, and it changed workbook name (in single quotes) to worksheet name in the series formula with no problem.
Note that the text entered into the “Find old text” box must exactly match capitalization of the text in the series formula.
Thank you for your quick response. I must be doing something wrong as I cannot get it to change from workbook name to sheet name. Maybe the issue is when I localised my named ranges, which in turn caused invalid named ranges on my chart? When I then run the add-in, perhaps it cannot find the invalid named ranges?
Richie -
Good point. If the current ranges are invalid, and the chart is a line or XY type, VBA cannot access the series formulas. At least it couldn’t in Excel 2003 and earlier. I did a quick test in 2007, and it seems to work regardless of source data validity.
The old trick in 2003 was to change the series to column, make the change, then change back.
Both versions worked great. Thank you very much for posting these solutions!
Great utility that saved me loads of time.
Is there anyway of working it to search and replace all graphs within a workbook rather than just the active sheet?
Works great for data series in charts – any way to extend the idea to error bar series and embedded text in charts that is linked to the worksheet as well?
great tool – worked nice for me! i’d have the same question as jeff – any way to extend this tool to error bar series?
thanks!
this is a great piece of work.
This is great for changing columns, do you have an equal way for changing rows using same column?
I have worksheets with 100.000+ rows and like to change the xvalues and values so the user can scroll using buttons.
I have everything ready except an effective way to only change the row part of each seriescollection in chart.
=SERIES(‘sheet1′!$Q$1,’sheet1′!$E$2:$E$10000,’sheett’!$Q$2:$Q$10000,1)”
=SERIES(‘sheet1′!$Q$1,’sheet1′!$E$10000:$E$20000,’sheett’!$Q$10000:$Q$20000,1)”
So far I have been working on the formula string using split, mid, instr etc but it’s a lot of iterations to replace the row numbers from each seriescollection and keep the row reference.
Thanks for any good ideas
Kenneth -
Go through twice, first changing 10000 to 20000, then changing 2 to 10001.
Thanks, works fine if I know the chart range, and if the sheet name does not have the same number in it’s name.
I have written some code evaluating the each chartseries formula and replacing minimum row and maximum row.
Sub ChangeChartRowRange(minRow As String, maxRow As String)
Dim oldCF As String
Dim newCF As String
Dim X, Y
Dim ch As ChartObject
Dim s As Series
Dim t1 As String, t2 As String
Dim i As Integer, j As Integer
Dim shN As String
Dim R(1) As String
R(0) = minRow
R(1) = maxRow
For Each ch In ActiveSheet.ChartObjects
For Each s In ch.Chart.SeriesCollection
oldCF = s.Formula
X = Split(oldCF, “,”)
For i = 1 To 2
Y = Split(X(i), “:”)
shN = Left(Y(0), InStr(Y(0), “!”))
For j = 0 To UBound(Y)
t1 = Mid(Y(j), InStr(Y(j), “$”))
t2 = Mid(t1, InStr(2, t1, “$”) + 1)
Y(j) = WorksheetFunction.Substitute(t1, t2, R(j))
Next j
Y(0) = shN & Y(0)
X(i) = Join(Y, “:”)
Next i
newCF = Join(X, “,”)
s.Formula = newCF
‘Debug.Print oldCF
‘Debug.Print newCF
Next s
Next ch
End Sub
But I also found a class module written by Walkenbach, ChartSeries that writes and reads among several chartseries properties, the XValues and Values as a range. Not an array. Probably much more effective….
You could use $10000 as the “find” string, and it will not match a number in a sheet name.
Great Utility! Thank you!
I’ve tried to get this to work with R1C1 notation, and it has not worked. Does it work with R1C1 notation? Is there something I’m missing?
I tried to replace C13 with C12 and nothing happened.
Thanks again!
David -
Good point. I rarely use R1C1 notation, so I don’t think of it. I’ll have a look when I get a chance.
Hi
What is the correct separator for one chart series using data series on different worksheets using Excel 2003?
This gives me an error
=’2011′!$B$8:$M$8+’2010′!$B$8:$M$8+’2009′!$B$8:$M8
as does this
=’2011′!$B$8:$M$8,’2010′!$B$8:$M$8,’2009′!$B$8:$M$8
Thanks,
Michael
Michael -
You can’t use data from different sheets this way. Your X Values, Y Values, and Series Name don’t need all to be on the same sheet, but each must be contained within one sheet.
Is it possible to change all charts on all sheets (ignoring sheets with no charts)?
Thanks
I have files built from scratch in Excel 2007 with many Stacked Area charts (1 per sheet) with as many as 25 series in each chart. I was updating these monthly, so you can imagine how long that was taking. Your add-in worked perfectly, so many, many thanks!!
Thanks, Alan
Just wanted to say echo Alan’s sentiments above, such a time saver! Thank you!
Here’s an interesting puzzle:
A series with constants for the 1st 11 y-values, but a cell reference in a worksheet
for a 12th y-value which is to be a variable value. Have not been able to get an error free syntax for this.
Anybody got the answer?
=SERIES(“Series5″,,{10000,20000,30000,40000,50000,60000,70000,80000,90000,100000,110000,Histories!M31},5)
Jim -
Excel doesn’t let you mix constant values and cell references within a single argument in the series formula. What you need to do is use another range, say Q1:Q12. Put your constants in Q1:Q11, and in Q12 put the formula =M31. Then use Histories!Q1:Q12 for your Y values.
Hi Jon, just tried this in 2010 as I have 120 charts I need to update, It works great on an individual chart, but when I tried to update about a dozen on the active sheet, it only did the first 3 and then destroyed the rest. Can you help?
Cheers, Dave.
This tool is amazing. Saving me loads of time, and I can’t express how much I appreciate you offering it! Thanks thanks thanks!
Dear Jon,
This program is really nice and helps a lot! (Sorry for writing on the wrong post in the first place). My question was, if you think this program could also function for the error bar series. This question was asked before but there was no reply. What do you think?
Cheers,
Michael
Michael -
Unfortunately, there is no way to get the error bar source data range using VBA. It’s a write-only variable. Since I can’t read it, I can’t use such a nice approach to rewrite it. I wish I could.
This is awesome!!!! Love it and have been looking for something like it since going to Excel 2007. Thanks!
I found a “little” problem. I have my own set of procedures and they do work fine .. in most cases. However, recently I wanted to replace series formulas (chart copies pasted on another computer with different paths, so I could not open those originals) by new formulas:
eg. series:
=SERIES(‘D:\wojtek\docs\Research\Magda\kin_single\woda\2,4-D_RIB500\[2,4D_RIB500_fitonly-ex5N2e-12mix.xls]fit’!$D$41, ‘D:\wojtek\docs\Research\Magda\kin_single\woda\2,4-D_RIB500\[2,4D_RIB500_fitonly-ex5N2e-12mix.xls]fit’!$E$38:$IJ$38, ‘D:\wojtek\docs\Research\Magda\kin_single\woda\2,4-D_RIB500\[2,4D_RIB500_fitonly-ex5N2e-12mix.xls]fit’!$E$41:$IJ$41, 2)
I wanted to replace D:\wojtek\docs by W:\docs (actually change of location).
I opened excel files with series data in new location (must be open to make it work), but my procedures would not work.
I still can change this series manually, but (in VBA) Series().Formula returns
=SERIES(‘D:\wojtek\docs\Research\Magda\kin_single\woda\2,4-D_RIB500\[2,4D_RIB500_fitonly-ex5N2e-12mix.xls]fit’!$D$41, ‘D:\wojtek\docs\Research\Magda\kin_single\woda\2,4-D_RIB500\[2,4D_RIB500_fitonly-ex5N2e-12mix.xls]fit’!$E$38:$IJ$38, , 2)
So Y data was missing!
I do not know whether it is a problem with formula length (over 256 chars), or commas in paths, file names or sheet names (I do not use commas in file name – this is “inherited” problem; a person who supplied the data created such names and I wanted to keep this original tree structure for easier comparisons).
I tried also .FormulaLocal (in Polish version it uses semicolons as separators, so should be less susceptible to comma-related problems, buy the result was exactly the same – missing Y data (perhaps it should not be a surprise, I presume that internally it all works with commas as separators and is later translated to a specific locale).
I tried it on a simpler case (shorter path/file/sheet strings, no commas) and it worked fine.
It is Excel 2002 sp3 (fully patched). I will try it on 2003 and on 2007 later.
So the problem is not changing path names but how Series().Formula works
AWM
PS. I usually use a couple of worksheets for kinetic data analysis etc., but once I update a sheet by adding some new plot (but leaving worksheet structure intact) I copy such plots to other/older worksheets and remove all file references (if the ws/sheet structures are the same) or change all chart data references to the current sheet. Those 2 kinds of chart manipulations are the most useful for me and my macros work fine.
You hero! Thank you so much for “The Change Series Formula Utility”
:)
The problem with partially empty chart series formulas (see my post above: long paths with commas – results in missing y-data) may be solved in some way by changing worksheet Links instead. It is much quicker than changing chart series one by one, but does not allow to change sheet and data range references (file/workseet references only)! However, it changes ALL referenes to a given worksheet – all charts, alll cell formulas – so you cannot restrict it to some charts or sheets. But… you may move your sheet (or copy chart) temporarily to a new empty worksheet, change links and move/copy back to the original location.
Here is an example of complete solution with comments etc. (it does not check path validity):
Sub ChangeLinks()
dbg = False ‘ change to True only when sth is wrong
oldWarn = “”
oldMsg = “Replace LINKS” & vbCrLf & vbCrLf & _
“Partial path to Replace ” & vbCrLf & vbCrLf & _
” (start with Drive letter and root dir, L:\path1 ) ”
Do
oldPath = Trim(InputBox(oldWarn & oldMsg, “old path”))
If (Len(oldPath) = 0) Then
If (dbg) Then Debug.Print “Cancelled: oldPath”
Exit Sub ‘ cancelled
End If
If (dbg) Then Debug.Print “old = “; oldPath
If (InStr(1, oldPath, “:\”) > 0) Then
Exit Do
Else
oldWarn = “Wrong old path!!! – start with Drive letter, L:\path1 ” & vbCrLf
End If
Loop
newWarn = “”
newMsg = “Old Partial path to replace: ” & oldPath & vbCrLf & vbCrLf & _
” (start with Drive letter and root dir, N:\path2 )” & vbCrLf & vbCrLf & _
“CAUTION!!! New file(s) must be already opened!!! ” & vbCrLf & _
“CAUTION!!! New file(s)/sheet path(s) must be valid!!! ” & vbCrLf & vbCrLf & _
“New partial path: ”
Do
newPath = Trim(InputBox(newWarn & newMsg, “new path”, oldPath))
If (Len(newPath) = 0) Then
If (dbg) Then Debug.Print “Cancelled: newPath”
Exit Sub ‘ cancelled
End If
Debug.Print “new = “; newPath
If (InStr(1, newPath, “:\”) > 0) Then
Exit Do
Else
newWarn = “Wrong new path!!! – start with Drive letter, N:\path2 ” & vbCrLf
End If
Loop
‘ path change example (note: procedure does not check path validity!)
‘ oldPath = “D:\wojtek\docs” or “D:\wojtek\”
‘ newPath = “W:\docs” or “w:\”
Call replace_xlLinks(oldPath, newPath, “xlOLELinks”, xlOLELinks) ‘ necessary for Excel Chart series or cell data links
Call replace_xlLinks(oldPath, newPath, “xlDDELinks”, xlDDELinks)
Call replace_xlLinks(oldPath, newPath, “xlExcelLinks”, xlExcelLinks)
End Sub
Sub replace_xlLinks(oldPath, newPath, LinkTypeName, LinkType)
‘ LinkType values: xlExcelLinks, xlOLELinks, xlDDELinks
‘ xlExcelLinks is good for Chart series and cell data links
aLinks = ActiveWorkbook.LinkSources(LinkType)
If Not IsEmpty(aLinks) Then
Debug.Print UBound(aLinks); LinkTypeName ‘ ” xlExcelLinks”
‘ Debug.Print “aLinks is : “; TypeName(aLinks)
For i = 1 To UBound(aLinks)
‘MsgBox “xlExcelLink ” & i & “:” & Chr(13) & aLinks(i)
‘ Debug.Print “xlExcelLink ” & i & “: ” & aLinks(i)
Debug.Print LinkTypeName & i & “: ” & aLinks(i)
Next i
Else
Debug.Print “Empty ” & LinkTypeName ‘ “xlExcelLinks”
Exit Sub
End If
nlinks = UBound(aLinks)
ccount = 0
For i = 1 To nlinks
If (InStr(1, aLinks(i), oldPath, vbTextCompare) = 1) Then
aaLink = Replace(aLinks(i), oldPath, newPath, 1, 1, vbTextCompare)
ActiveWorkbook.ChangeLink Name:=aLinks(i), NewName:=aaLink, Type:=LinkType ‘xlExcelLinks
ccount = ccount + 1
End If
Next i
Debug.Print “Changed: “; ccount
MsgBox (“Found ” & nlinks & ” and Changed ” & ccount & ” ” & LinkTypeName)
End Sub
Enjoy.
AWM
AW -
I’m sure my routine falls down because of the commas in the file names. I use a simple Split(string, “,”) to split the series formula at the commas. If the filename contains commas, then there are commas separating SERIES formula arguments and commas within SERIES formula arguments, and the Split command returns an array of gibberish.
John Walkendbach shows a more detailed formula parsing algorithm in A Class Module To Manipulate A Chart Series. This goes character by character, and doesn’t split the string at a comma which is within quotes.
Of course, using commas and other strange characters in filenames violates the Keep It Simple Stupid principle, for what it’s worth.
Jon,
I do not use your routines and the problem is not *parsing strings* (if there is a problem, it is internal Excel/VBA problem), but the result given by Series.Formula() in VBA (partally empty). Anyway, I will try the same without commas but with the same path lenghts.
BTW. I myself follow the Way (i.e. Keep It Simple Stupid principle), however, sometimes one has to deal with data produced by someone who thinks in purely “human” not “computer” way.
AW -
Parsing strings would be problematic if the strings contained more commas than just those that separated the arguments. Since you use Excel’s Edit Links feature, so commas in the workbook names are not a problem.
Now that I reread your comments, I get what the problem is, missing arguments. ISTR this in the distant past, and if memory serves, it is probably related to length of the strings, and not on any special characters in those strings.
And yeah, sometimes it’s hard to instruct mere mortals to think in ways that make programming more efficient.
HI Jon, I left a comment /question up above that you did not respond to re lots of charts on one sheet not working in v2010, can you help?
Cheers, David R.
I downloaded your addin to change chart series and it worked great. However I have multiple worksheets with one chart per worksheets that I need to change the series range across all worksheets. Is this possible? i would prefer vba code rather than an add in.
Chris -
It’s possible to do charts on multiple sheets. You need to nest the code one level deeper:
Jon,
I used the code you posted to extend to multiple sheets and it worked perfectly. Thank you so much.
This has just saved me a full days work of changing data ranges. Thank you!
This code works GREAT!!! No problems at all! Thanks so much!!
Jon,
I was excited I bumped into this, but I still get the “Unknown Error” Type Mismatched.
Tried it in Excel 2007 and 2010. Is there any fix to it? Please advice.
I have 250 series plotted on one bubble chart and need to change a column reference for each series from $A$ to $B$.
Bubble charts are strange, with an extra argument in the SERIES formula, which isn’t treated exactly the same as a regular range reference. I never tested this program with bubble charts, so I don’t even know how strange their behavior is.
At some point I’ll probably look into bubble chart SERIES formulas.
re. David Rootes’ issue with Excel 2010 charts being “destroyed” after only 3 were updated with the series change. I too experienced that issue. I found that if I scroll down past the last chart on the sheet BEFORE clicking on the “Change All Charts on Active Sheet” button, chart series were updated and no charts were “destroyed.” I have no idea why this worked.
Hi, This utility is a great idea unfortunately I get “Unknown Error Type Mismatch” when I use it with Excel 2007, I have a Scatter plot with 3 series -
=SERIES(“AWGN”,’B9′!$F$6:$F$10,’B9′!$I$6:$I$10,1)
=SERIES(“Rician, 12/200″,’B9′!$F$16:$F$20,’B9′!$I$16:$I$20,2)
=SERIES(“Rician 9/10″,’B9′!$F$26:$F$30,’B9′!$I$26:$I$30,3)
and trying to replace $F$ with L
Not sure what I am doing wrong. Please advise. Thanks in advance!
My algorithm interprets the formula by splitting the series formula into its component parts at the commas in the formula. It isn’t smart enough to know that the comma in “Rician, 12/200″ is in the middle of the series name, so it breaks when it tries to interpret 12/200 as the X values.
A future version will correct this shortcoming.
Thank You so Much for the chart formula utility! I can’t thank you enough!
Background Story:
I am a disabled ‘Nam Era USMC vet who works as a volunteer at the local VA hospital.
I can tell you that I have undertaken developing a means of tracking and graphing items between the main VA hospital , and 10 satellite hubs in the state in monthly and yearly charts. I ended up with sovereign data (after a few iterations), but needed 840 charts to display all the data on 10 worksheets!
Needless to say changing chart references was genuinely, seriously, painful and time consuming to the extreme.
This utility probably saved me four full days worth of work manually editing the formulas…
As a VA volunteer, I sincerely thank you. It worked like a charm!
Thank you again…… And my old fingers and eyes thank you!
Sincerely and respectfully,
Dave
I feel that Excel 2010′s charting functions are worse off than in 2003 version. I have used this technique sucessfully for years, now, I have defined my ranges, both x and y, but the Chart series function gives me an error, “Function is not valid” yet it actually locates the correct data set in my lookup table. HELP
Hi Dustin -
How have you defined your ranges in the past (that doesn’t work now)? Hopefully it only requires a small tweak.
Thank you, thank you, thank you, THANK YOU! :)
Hi,
I have Excel 2010 and one, two or four series per chart. This add-in works like a chart for charts with only one series, but not for those with two series (haven’t tried with four, yet) Excel crashes and is forced to close.
Any updates for this problem? I would find this VERY USEFUL in my data analysis…
Marko -
This works fine on the multi-series charts I use all the time.
What chart type are you using?
Reply to this thread, and include the two series formulas for a chart that can’t be updated without crashing.
I have several pages of several complex graphs containing multiple data series. The dead line is HOT!
If only I could set up a page and copy it. But, all the links point to the original data. It’s mind numbing pain to hunt down each reference and change it by hand.
Well, now I CAN copy! This should have been a feature in Excel.
I’ll sleep better tonight and wanted to thank you from the bottom of my heart!
Dean
Hi,
I am unable to get the change series formula utilty to work in Excel 2013. Can you tell me if it does work in Excel 2013?
Thanks
I’ve been using it in Excel 2013 without a problem. Well, I have a newer in-house version that has some enhancements, but I don’t remember any problems when I first got 2013.
1. Make sure you’ve installed it properly.
2. Look on the Add-Ins tab for the buttons.
This is exactly what I have been looking for! I can’t get the add on to work for me though it keeps saying it can not find the macro. I still wanted to use it though so I used your VBA code and I still got an error on the
mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, OldString, NewString)
line. It says application defined or object defined error. Do you have any idea how I could solve either problem? I made sure my macros were enabled so it is not because of that. Thank you very much.
Is the add-in properly installed?
Using the code in your own project: Is mySrs properly declared and assigned to a series? What is mySrs.Formula before this line? What are OldString and NewString? What is the result of the Worksheet.Substitute operation?
Hi Jon,
The Change Series Formula Utility does seem to be working in Excel 2013 but only when the Add-Ins Tab is visible at the top of my worksheet. When I open a file and then do file open and go back and double click the file PTS_ChangeSeriesFormula.xla that I have downloaded and saved under /AppData/Roaming/Microsoft/Addins/, the Add-ins tab appears at the top of my Excel sheet and the add-in works. I save and close the file. When I reopen the same file or create a new file or open a different file the Add-Ins Tab is no longer at the top of my screen. I must go back to Change Series Formula Utility file and double click it every time so that the Add-in Tab appears at the top of my screen for the add-in to work. Note that when I go to File Options Add-Ins the Change Series Formula Add-In is correctly listed as an Excel Add-in, and the Customized Ribbon has Add-ins correctly selected.
Saving a file in the blahblah\Add-Ins directory only makes it available to be installed, but does not install it.
In Excel, go to the add-ins dialog (Office button or File tab > Options > Add-Ins > Go) and check the box in front of the add-in’s name. If the add-in is not shown in the list, click Browse, navigate to the file, and click OK a couple times to return to Excel.
Now the add-in will be always available.
The box in front of the add-in Change Series Formula Add-In is listed and it is checked. However, it is not available unless I double click the add-in file where it is saved in order to get the Add-Ins button to appear at the top my Excel worsheet for the add-in to work.
I got it to work properly in my own program. I don’t know how but it works now and it is great! Thank you so much for replying so quickly and for giving this program out to use! It is amazing and saves so much at work! I can’t thank you enough!
Jon,
This is such a time saver! I have a workbook with four charts on each tab and about 50 tabs, I just need to change one thing in every chart so part of the name would go from May to June. Is it possible to modify this so it would change all the charts in the workbook?
Thank you very much!
In the “ChangeSeriesFormulaAllCharts” routine:
1. Declare this variable
Dim oWksht As Worksheet
2. change this one line
For Each oChart In ActiveSheet.ChartObjects
to these two lines
For Each oWksht In ActiveWorkbook.Worksheets
For Each oChart In oWksht.ChartObjects
3. Before “Else”, insert one more “Next”
Rick -
I don’t know what’s not working. The checkbox tells you the file is open, but the lack of buttons on the Add-Ins tab indicates that it is not. Try this:
Uninstall the utility (uncheck the box), move the file to a different directory, then reinstall it from the new directory (using Browse to locate the file).
Hi Jon,
As suggested I uninstalled the utility (unchecked the box), moved the file to a different directory, and then reinstalled it from the new directory (using Browse to locate the file). Unfortunately It still does not work in Excel 2013. I then tried it in Excel 2010 and it works fine but not in 2013 which still does not display the add-in button in the toolbar unless the file is double clicked each time you want to use it in order to activate the add-in. Is it possible that xla files work in 2010 but not 2013 and that only xlam files work in 2013? I also played with the macro settings under the Trust Center in 2013 and it did not help. Note that I do not have the entire 2013 suite installed (I only have Excel 2013 the rest of my programs are 2010) and I have both the 2010 and 2013 versions of Excel installed. I don’t know if that makes a difference.
Rick -
The behavior you describe is not normal. Both .xla and .xlam files work the same in Excel 2013 (and 2010 and 2007). It should not matter which pieces of which version of Office are installed, or that multiple versions of Excel are installed. Once installed, the add-in should be available and its buttons should be visible, without having to also open the add-in file.
I wonder whether reinstalling Excel 2013 would fix the problem.