VBA to Split Data Range into Multiple Chart Series
by Jon Peltier
Thursday, May 22nd, 2008
Peltier Technical Services, Inc., Copyright © 2012.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
A common problem is to make a chart from a list like this one. You want separate series for each item in one column (e.g., the cities in the first column), but your list is different every time, and you have to spend an hour assigning data to each series in the chart.
This is a job for VBA. It’s possible to write a simple loop that reads the first column of the range, grouping rows together by item. The VBA procedure is listed at the end of this post. Select a chart, run the procedure. A dialog pops up, asking for the data range.
The code puts each group of rows into its own series, based on the label in the first column of the range, and labels each point of the series with this label (which is also used as the series name). The chart is shown below:
Sub PopulateChartFromTable()
Dim cht As Chart
Dim rng As Range
Dim sPrompt As String
Dim iSrs As Long
Dim srs As Series
Dim iRow As Long
Dim iRowStart As Long
Dim iRowEnd As Long
Dim sSeries As String
If ActiveChart Is Nothing Then
MsgBox "Select a chart and try again.", vbExclamation
GoTo ExitSub
End If
sPrompt = "Select a three-column range with your data."
sPrompt = sPrompt & vbNewLine & " Column 1: Series title"
sPrompt = sPrompt & vbNewLine & " Column 2: X values"
sPrompt = sPrompt & vbNewLine & " Column 3: Y values"
sPrompt = sPrompt & vbNewLine & "Avoid blank cells"
On Error Resume Next
Set rng = Application.InputBox(Prompt:=sPrompt, Type:=8)
On Error GoTo 0
If rng Is Nothing Then GoTo ExitSub
Set cht = ActiveChart
Do
If cht.SeriesCollection.Count = 0 Then Exit Do
cht.SeriesCollection(1).Delete
Loop
sSeries = ""
iSrs = 0
For iRow = 1 To rng.Rows.Count + 1
If rng.Cells(iRow, 1).Value <> sSeries Or iRow > rng.Rows.Count Then
If iSrs > 0 Then
iRowEnd = iRow - 1
Set srs = cht.SeriesCollection.NewSeries
With srs
.Values = rng.Cells(iRowStart, 3).Resize(iRowEnd + 1 - iRowStart)
.XValues = rng.Cells(iRowStart, 2).Resize(iRowEnd + 1 - iRowStart)
.Name = rng.Cells(iRowStart, 1).Value
.ApplyDataLabels ShowSeriesName:=True, _
ShowCategoryName:=False, ShowValue:=False
End With
End If
iRowStart = iRow
sSeries = rng.Cells(iRow, 1).Value
iSrs = iSrs + 1
End If
Next
ExitSub:
Application.ScreenUpdating = True
End Sub
For a formula-based solution to this issue, see Split Data Range into Multiple Chart Series without VBA.
Related Posts:
- Extract Chart Data
- Export Chart as Image File
- Stack Columns In Order Of Size With VBA
- Robust VBA Save-As Technique
- Enhanced Export Chart Procedure
- VBA Conditional Formatting of Charts by Series Name
Posted: Thursday, May 22nd, 2008 under VBA.
Comments: 47
Comments
Comment from derek
Time: Thursday, May 22, 2008, 8:05 am
Excellent. This is the sort of thing I’d first think of using pivot tables for, only to then realise that pivot tables will only give me aggregates, not the individual data points.
Comment from derek
Time: Thursday, May 22, 2008, 8:52 am
Although I *can* do it with Pivot tables if I have to, using COUNTIF(C$3:C4,C4) to establish a unique serial number, and “Max of X” etc. for the summary values. It’s all a lot of work though.
Comment from Bruce Barrett
Time: Thursday, May 22, 2008, 8:53 am
Wow! I’ve looked all over for a way to label data points, and this is precisely the solution.
I previously tried a couple of labeling work-arounds, but they only add labels to data points in a single series. Ergo, they’re not robust if you make changes to the input data range. For example, if you delete one input row, the remaining data points are mis-labeled.
Jon’s solution takes the correct approach by creating multiple series, and then simply displaying the series name.
(Jon – please feel free to edit/rephrase this comment). Thanx.
Comment from Jon Peltier
Time: Thursday, May 22, 2008, 9:46 am
Bruce -
If you only need data labels on identically formatted points, there are other approaches. Excel does not natively support data labels from worksheet ranges, but you can individually modify labels and even link an individual label to a worksheet cell. Two free utilities that handle this for entire series of points are:
Rob Bovey’s Chart Labeler (http://appspro.com)
John Walkenbach’s Chart Tools (http://j-walk.com)
Comment from derek
Time: Thursday, May 22, 2008, 12:50 pm
I’ve recently thought that, fine as the Bovey and Walkenbach solutions are, a new tool needs to be written for more convenient labeling. It’s especially annoying to have to go through several click-and-drag stages again when I’m developing a chart and have changed e.g. the number of rows. I’d like an add-in with a “refresh” button to say “I’ve made some changes, but not fundamental ones, please re-apply the labels as before”. I’d also like a “apply this rectangular range to several series” instead of going through the menus for each series to select a single vector range each time.
Especially as MS has once again not incorporated this basic bit of fuinctionality into their latest version of charts :-(
Comment from Jon Peltier
Time: Thursday, May 22, 2008, 12:58 pm
Derek -
Hey, I’m in the choir you’re preaching to. The problem with the available programmatic solutions is that the individual labels are linked to individual cells (not a series of labels to a range of cells). If points are inserted or deleted (or hidden, as by a filter), the label linkage is not updated, and the wrong label now appears by each point.
On a case-by-case basis I’ve applied VBA-based solutions that either update the labels when the data has changed, or formula-based solutions that use various index or lookup functions to extract data from its range and place it into a ‘conditioned’ data range which the chart uses. These cannot easily be generalized.
Comment from derek
Time: Thursday, May 22, 2008, 2:57 pm
Oh, I just remembered the other feature I wanted from Labeler 2.0: select a vector range (i.e. one column wide or one row high) and ask for that to be applied to the nth point of each series. This would usually be the first or last, for curve labeling.
In practice, you can use the “select a rectangle” and let all but the last etc. be blank, but the special version would be elegant.
Comment from Jon Peltier
Time: Thursday, May 22, 2008, 3:01 pm
Derek -
This simple utility labels the last point of each series with the series name:
Comment from Colin Banfield
Time: Thursday, May 22, 2008, 9:05 pm
Nice. I’d make a few mods: 1) If the user selects a single cell, use the current region; 2) Sort the range (unless someone believes it will always be sorted beforehand); 3) Remove legend and gridlines if they exist (if the activechart is empty to begin with, these elements will be inserted when you create the chart); 4) Set the chart type to xlXYScatter (scatter without lines), since if the chart is empty, it’ll insert a scatter with lines.
Comment from Jason
Time: Thursday, May 22, 2008, 10:19 pm
I forget how I stumbled across this, but it is possible to data labels from a range to a chart, without programming.
http://support.microsoft.com/default.aspx?scid=kb;en-us;214040
Oddly enough, it uses Lotus 1-2-3 commands to do something that Excel cannot natively do.
Comment from Jon Peltier
Time: Friday, May 23, 2008, 6:44 am
Colin – There’s always room for improvements and error prevention. Regarding items 3 and 4, if the original chart meets these conditions, so will the adjusted chart.
Comment from Jon Peltier
Time: Friday, May 23, 2008, 6:58 am
Jason -
Interesting find. It seems to have been lost by the time Excel 2003 was released. I don’t have Excel 2000 on this computer: What happens if you create a chart using this technique, then delete a row from the middle of the data range? Do the labels adjust properly?
Comment from Colin Banfield
Time: Friday, May 23, 2008, 7:39 am
Jon, agreed. The beauty of code snippets is that they provide a good starting point for new functionality and can be readily modified to meet specific needs if required. (1) and (2) came to mind immediately while reading the post and (3) was based on my starting point, which was an empty chart. As for (4), even after the chart is set as a scatter without lines, every time the macro runs, in Excel 2007 it reverts to a scatter with lines.
Comment from Jon Peltier
Time: Friday, May 23, 2008, 7:59 am
Colin – If the XY Markers Only chart contains data before the procedure is run, it will be an XY Markers Only chart after the procedure is run. It’s simple enough to adjust. In the With srs block, just before End With, insert this line:
.ChartType = xlXYScatter
Comment from Colin Banfield
Time: Friday, May 23, 2008, 8:36 am
Jon, I’m missing something here. Why add the line if the chart is going to remain XY Markers only *after* the procedure is run? I added that line in the first place (point (4) of previous post) because every time I ran the macro, it added the lines to the chart, even if I manually changed the chart to Markers only between runs. The point I’m making is that in Excel 2007, this is what I see happening – the markers only chart isn’t sticking when the macro is run. At any rate, the discussion here is somewhat moot because the line you mentioned already solved the problem.
Comment from Jon Peltier
Time: Friday, May 23, 2008, 8:57 am
Colin – I was adding the line in response to your problem, which apparently occurs in Excel 2007 only. I have to admit that I didn’t test this in Excel 2007, only 2003. I don’t use Excel 2007 for any of my own important work, only for client work if needed and to check out bugs and irregular behavior.
Comment from Colin Banfield
Time: Friday, May 23, 2008, 9:17 am
Jon…yes, I just checked and the original macro works fine in Excel 2003.
Comment from Jason
Time: Friday, May 23, 2008, 1:07 pm
Jon – I used the Lotus 1-2-3 trick successfully in Excel 2002. We’ve since upgraded to Excel 2003. The chart I made still shows the data labels correctly, but if I delete a row as you suggested, it does not update the labels accordingly.
Comment from Jon Peltier
Time: Friday, May 23, 2008, 1:11 pm
Jason – Excel 2002 has ‘Help for 1-2-3 Users’ on the Help menu then? In Excel 2003 I could find no mention of this either in Tools > Options or in Tools > Customize.
Comment from Piotr Pikielny
Time: Tuesday, May 27, 2008, 9:30 am
Dear Sir,
Following your article from few years back about Gantt charts I wrote a VBA procedure (Procedure1) to make a Gantt chart. Everything works fine when I input series values as Ranges.
However, when I try – in Procedure2 – to change Ranges for Arrays in series input, I receive error 1004 for .MinimumScale and .MaximumScale properties, which says that these properties can’t be set. Why is it so?
Best regards,
Piotr Pikielny
Hundreds of lines of code deleted…
Comment from Jon Peltier
Time: Tuesday, May 27, 2008, 12:43 pm
Pyotr -
That was too much code to wade through without a little more guidance. The .MinimumScale and .MaximumScale properties are used at least twice in the procedure, so I don’t know where the error occurs. Also, 1004 is a very common error number, and it has a variety of corresponding error descriptions, so I don’t know the reason for the error. Step through the code and make sure the values being assigned to the scale properties are appropriate. Also make sure that when you set the maximum, it isn’t smaller than the existing minimum, and when you set the minimum, it isn’t larger than the existing maximum.
Pingback from The Olympic Medals by Country Chart – Improved now | Pointy Haired Dilbert – Chandoo.org
Time: Tuesday, August 12, 2008, 1:30 pm
[...] He does that by creating various named ranges and manually adjusting the color based on the range. Read more on how to this. Download and play with his [...]
Pingback from ggplot2: Split Data Range into Multiple Chart Series « Learning R
Time: Sunday, March 15, 2009, 3:13 pm
[...] Peltier proposes a VBA loop to simplify the process of creating the chart series: read the first column of the range, grouping [...]
Comment from Felix Marusic
Time: Monday, April 6, 2009, 3:57 pm
Jon,
I used your VBA successfully on the first 3 columns of my data which has co-mingled series data. Although I recieved a syntax error on the following lines:
sPrompt = sPrompt & vbNewLine & ” Column 1: Series title”
sPrompt = sPrompt & vbNewLine & ” Column 2: X values”
sPrompt = sPrompt & vbNewLine & ” Column 3: Y values”
sPrompt = sPrompt & vbNewLine & “Avoid blank cells”
If I remove the lines it obiously still works but minus the messages at the prompt. Not sure what the syntax prob. is, using Excel ’07.
Aside from that, if I also have an additional 4 columns of data (limit data – each a respective series with no formatting required), which needs to be incorporated into the same graph, how would I modify the code to do so while preserving what I already have?
I’ve tried to incorporate the techniques you described in your Dynamic Chart Source Data post as a seperate module but when I run it the entire chart gets updated with only the named range data. Please help!
PS – I’m a total Noob at VBA programming, but I guess you gotta start somewhere!
Thanks,
Felix
Comment from Jon Peltier
Time: Tuesday, April 7, 2009, 7:01 am
Felix -
Specifically which line fails, and what is the error? Make sure the ampersand and straight quotes come across properly.
If the limit data isn’t split into series at the same places that the first three columns are, then it’s an independent protocol. You’d have to modify the message so that (after you fix the syntax error) it describes the added columns. Then you’d have a separate loop after
For iRow = 1 To rng.Rows.Count + 1
which would loop over the last three columns (assuming your four columns are X, Ybar, Ymin, Ymax), assigning the X value to the first added column and the Y values to each of the last three columns. Something like:
Dim iCol As Long
For iCol = 5 To rng.Columns.Count
Set srs = cht.SeriesCollection.NewSeries
With srs
.Values = rng.Columns(iCol)
.XValues = rng.Columns(4)
.Name = ". . . whatever"
End With
Next
Comment from Felix Marusic
Time: Tuesday, April 7, 2009, 10:23 am
Jon,
The line it was failing on, was:
sPrompt = sPrompt & vbNewLine & ” Column 1: Series title”
However, when I re-applied the VBA in a “new” template, the problem did not replicate itself…don’t know why. It appears the template was corrupted.
I attempted to insert the additional loop after line,
For iRow = 1 To rng.Rows.Count + 1
, but the graph did not update properly.
My appologies, I probably should have given more details on the additional columns of data. They come across as follows:
Date Alert MIN Alert MAX Action MIN Action MAX
10/02/2008 0 5 0 3
10/10/2008 0 5 0 3
The X values are the dates(which are also shared with the original 3 columns of comingled series data), the series names are Alert MIN, Alert MAX, Action MIN and Action MAX, and the Y values are 0, 5, 0, 3, respectively.
-Felix
Comment from Jon Peltier
Time: Tuesday, April 7, 2009, 11:56 am
Sorry, I didn’t explain that very well, did I? You have to enter my block of code after the block of code in the original routine that begins with
For iRow = 1 To rng.Rows.Count + 1
and ends with
Next
Comment from Felix Marusic
Time: Tuesday, April 14, 2009, 10:47 am
Jon,
Thanks for the input thus far, you’ve been a great help. I know this may be a minor detail at this point but is there any way for the second loop, which adds the limits, to also label the series with the column headers in the chart?
-Felix
Comment from Jon Peltier
Time: Tuesday, April 14, 2009, 11:53 am
Felix -
Try inserting the red line into the loop:
With srs
.Values = rng.Columns(iCol)
.XValues = rng.Columns(4)
.Name = ". . . whatever"
.ApplyDataLabels ShowSeriesName:=True
End With
Comment from Felix Marusic
Time: Tuesday, April 14, 2009, 12:20 pm
Jon,
I tried it and got a “compile error: expected: identifier or bracketed expression” message for that line. Would have been nice but at this point it looks like they will just have to name those series manually. Thanks for your help though!
-Felix
Comment from Jon Peltier
Time: Tuesday, April 14, 2009, 12:37 pm
I don’t know why you got the error, but I misunderstood what you wanted. Remove the line I had you insert before, and replace the existing Name line as follows:
With srs
.Values = rng.Columns(iCol)
.XValues = rng.Columns(4)
.Name = "=" & rng.Cells(0, iCol).Address(True, True, xlR1C1, True)
End With
Comment from Felix Marusic
Time: Tuesday, April 14, 2009, 1:34 pm
Jon,
Thanks again…the code does exactly what I need.
-Felix
Comment from Doris
Time: Tuesday, April 14, 2009, 2:58 pm
I was looking at this example and I have a similar situation– what would be the vba command to set the series in the second loop as a different chart type other than the default line with markers?
Doris
Comment from Jon Peltier
Time: Tuesday, April 14, 2009, 3:58 pm
Doris -
Between With srs and End With, you need a line like:
.ChartType = xlChartTypeArea
Pingback from Excel’s Missing Factor « Charts & Graphs
Time: Thursday, April 16, 2009, 10:38 am
[...] his “VBA to Split Data Range into Multiple Chart Series“ (May, 2008) , Jon Peltier writes: “A common problem is to make a chart from a list [...]
Comment from kevin langdon
Time: Tuesday, April 21, 2009, 8:16 pm
HI
do you know an easy way to create multiple charts from an array of data and save it to multiple locations in a report style excel doc.
Comment from Jon Peltier
Time: Wednesday, April 22, 2009, 9:31 am
An “easy” way? Unfortunately, no. You need to write a VBA routine that steps through the data, making one chart per relevant subset, and placing thei chart where it belongs. Or you need a prebuilt report with the data and reports, and a VBA routine that helps the user update teh data and makes sure the charts update properly.
One thing that makes this hard is that each example of such a program is different from all others. My data is arranged differently than yours, and my report is laid out differently.
Pingback from Don’t Try This With Excel « Charts & Graphs
Time: Wednesday, April 22, 2009, 2:55 pm
[...] and subset() function can do the same thing as the VBA code that Jon Peltier illustrated in his May, 2008 post. The point of that post was to show that [...]
Pingback from ggplot2: Don’t Try This With Excel « Learning R
Time: Thursday, April 23, 2009, 8:21 am
[...] tags: chart, excel, ggplot2, plot, plyr, R by learnr Building on the original blog post “VBA to Split Data Range into Multiple Chart Series” by Jon Peltier, and the R version in this blog and in his blog, Charts & Graphs blog [...]
Comment from wynand
Time: Tuesday, May 11, 2010, 8:55 am
Jon,
Just two questions I couldn’t pick up or understand through the comments:
1. I would like to add an additional column of X values
2. How do I update the chart without changing any other formatting changes?
thanks
Comment from Jon Peltier
Time: Tuesday, May 11, 2010, 11:19 am
Wynand -
1. How would you use the second set of X values?
2. The code above deletes all series at the beginning of the routine, then adds back as many as it needs. New series added to a chart use default formatting. The new code below applies new data to the existing series, which reuses any customized formatting. If it needs more series, it adds them as needed. At the end, if it needed fewer series than were initially present in the chart, it removes the excess.
Sub PopulateChartFromTable2()
Dim cht As Chart
Dim rng As Range
Dim sPrompt As String
Dim iSrs As Long
Dim nSrs As Long
Dim xSrs As Long
Dim srs As Series
Dim iRow As Long
Dim iRowStart As Long
Dim iRowEnd As Long
Dim sSeries As String
If ActiveChart Is Nothing Then
MsgBox "Select a chart and try again.", vbExclamation
GoTo ExitSub
End If
sPrompt = "Select a three-column range with your data."
sPrompt = sPrompt & vbNewLine & " Column 1: Series title"
sPrompt = sPrompt & vbNewLine & " Column 2: X values"
sPrompt = sPrompt & vbNewLine & " Column 3: Y values"
sPrompt = sPrompt & vbNewLine & "Avoid blank cells"
On Error Resume Next
Set rng = Application.InputBox(Prompt:=sPrompt, Type:=8)
On Error GoTo 0
If rng Is Nothing Then GoTo ExitSub
Set cht = ActiveChart
nSrs = cht.SeriesCollection.Count
sSeries = ""
iSrs = 0
For iRow = 1 To rng.Rows.Count + 1
If rng.Cells(iRow, 1).Value <> sSeries Or iRow > rng.Rows.Count Then
If iSrs > 0 Then
iRowEnd = iRow - 1
If iSrs < = nSrs Then
Set srs = cht.SeriesCollection(iSrs)
Else
Set srs = cht.SeriesCollection.NewSeries
End If
With srs
.Values = rng.Cells(iRowStart, 3).Resize(iRowEnd + 1 - iRowStart)
.XValues = rng.Cells(iRowStart, 2).Resize(iRowEnd + 1 - iRowStart)
.Name = rng.Cells(iRowStart, 1).Value
.ApplyDataLabels ShowSeriesName:=True, _
ShowCategoryName:=False, ShowValue:=False
End With
End If
iRowStart = iRow
sSeries = rng.Cells(iRow, 1).Value
iSrs = iSrs + 1
End If
Next
If nSrs >= iSrs Then
For xSrs = nSrs To iSrs Step -1
cht.SeriesCollection(xSrs).Delete
Next
End If
ExitSub:
Application.ScreenUpdating = True
End Sub
Comment from wynand
Time: Wednesday, May 12, 2010, 9:05 am
Thanks, sorted!
Pingback from Tweets that mention VBA to Split Data Range into Multiple Chart Series – — Topsy.com
Time: Thursday, December 30, 2010, 9:17 pm
[...] This post was mentioned on Twitter by Teach Business. Teach Business said: RT @ThatBlokeSean: VBA to Split Data Range into Multiple Chart Series – http://peltiertech.com/WordPress/vba-to-split-data-range-into-mu … [...]
Comment from Anonymous
Time: Monday, August 8, 2011, 3:56 pm
Thank you, thank you, thank you!
Comment from m.garber
Time: Wednesday, October 12, 2011, 11:35 am
Dr. Peltier:
As a fan of Peltier Tech Chart tutorials of the 2003 era I am disappointed (whether or not I have a right to be) with 2007 VBA programming discussions in current tutorials. To be specific, the so-called enumeration constants appear frequently now with no introduction or discussion, for example in “Line and Fill Effects, etc.”, and in N. Hebbs’ guest article about Autoshapes. My concern is with X-Y Chart formatting, so when msoConstants crop up on p.582 of Walkenbach’s Power Programming without further or previous ado, a rank amateur is left bewildered.
It would be nice if you would write something on this if you consider it worth your while.
Sincerely,
M. Garber
Bellport, NY
Comment from Jon Peltier
Time: Wednesday, October 12, 2011, 9:41 pm
Those enumeration constants are nothing new. They were part of the earliest incarnation of VBA from Excel 95. You can find out about them from the Object Browser in the VB Editor, or via Google.
Pingback from Excel: Automatic series with scatter plot
Time: Saturday, April 14, 2012, 5:01 am
[...] VBA to Split Data Range into Multiple Chart Series Answered by Jon Peltier [...]
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.