Split Data Range into Multiple Chart Series without VBA
by Jon Peltier
Friday, May 23rd, 2008
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
In VBA to Split Data Range into Multiple Chart Series I shared a VBA procedure that split a range into separate series in a chart. In fact, this is fairly easy to do using conditional chart formatting techniques I describe on my web site (see Simple Conditional Charts). The way these techniques work is to use formulas in separate columns to capture values from the main data column when certain conditions are met.
The data used in the other post is shown below, with the conditional columns added.
| A | B | C | D | E | F | G | |
| 1 | City | X | Y | Atlanta | Boston | Chicago | Detroit |
| 2 | Atlanta | 4 | 15 | 15 | #N/A | #N/A | #N/A |
| 3 | Atlanta | 5 | 18 | 18 | #N/A | #N/A | #N/A |
| 4 | Boston | 6 | 16 | #N/A | 16 | #N/A | #N/A |
| 5 | Boston | 6 | 16 | #N/A | 16 | #N/A | #N/A |
| 6 | Boston | 7 | 12 | #N/A | 12 | #N/A | #N/A |
| 7 | Boston | 11 | 11 | #N/A | 11 | #N/A | #N/A |
| 8 | Chicago | 10 | 13 | #N/A | #N/A | 13 | #N/A |
| 9 | Chicago | 13 | 10 | #N/A | #N/A | 10 | #N/A |
| 10 | Chicago | 15 | 8 | #N/A | #N/A | 8 | #N/A |
| 11 | Detroit | 10 | 9 | #N/A | #N/A | #N/A | 9 |
| 12 | Detroit | 15 | 5 | #N/A | #N/A | #N/A | 5 |
| 13 | Detroit | 13 | 3 | #N/A | #N/A | #N/A | 3 |
| 14 | Detroit | 14 | 6 | #N/A | #N/A | #N/A | 6 |
The unique items from column A are entered into row 1 of columns D through G. The following formula is entered into cell D2 and filled into the entire range D2:G14:
=IF($A2=D$1,$C2,NA())
The formula compares the label in column A with the header in row 1: if they match, the formula returns the value in column C otherwise it returns #N/A, which is not charted in a line or XY chart (for a bar or column chart, use the empty string “” instead of NA()).
Select the X Values (B1:B14), then hold CTRL while you select the conditional Y values (D1:D14), then create a chart. After formatting, the result is identical to the chart processed by VBA approach:
Related Posts:
- Conditional XY Charts Without VBA
- Physics Lesson
- Category Axis Tricks for Line and Area Charts – 1
- VBA Conditional Formatting of Charts by Series Name
- VBA Conditional Formatting of Charts by Category Label
- Area Chart – Invert if Negative
- VBA Conditional Formatting of Charts by Value
- Dynamic Chart with Multiple Series
- VBA to Split Data Range into Multiple Chart Series
- Pivot Table Conditional Formatting with VBA
Posted: Friday, May 23rd, 2008 under Data Techniques.
Comments: 7
Comments
Comment from Frank R.
Time: Monday, August 18, 2008, 9:38 am
This is exactly what I was looking for. Thanks.
Comment from Richard
Time: Saturday, November 1, 2008, 2:53 pm
This works well as long as you are only plotting points.
If you want to interrupt a line between the points it seems that you now have to write a macro to copy out the data, leaving blank cells where gaps are needed.
What a nuisance! It used to be so easy! Or am I missing a trick, here?
Comment from Jon Peltier
Time: Monday, November 3, 2008, 7:49 am
Richard -
This behavior is unchanged since Excel 97 (or earlier).
Comment from Ryan
Time: Monday, April 26, 2010, 3:03 pm
What about using an if statement to the macro I recored below?
ActiveChart.Legend.Select
ActiveChart.Legend.LegendEntries(1).LegendKey.Select
With Selection.Border
.ColorIndex = 3
.Weight = xlThin
.LineStyle = xlContinuous
End With
How could I add an if statment to account for if the series does not exist then don’t do anything? I am not very good with If statments in VBA…
Thanks!
-Ryan
Comment from Jon Peltier
Time: Monday, April 26, 2010, 10:00 pm
You have to be careful with legend entries, as they don’t always correspond with series the way you’d expect.
Anyway, you have to check for the existence of the legend. If it exists, it certainly has one legend entry.
If ActiveChart.HasLegend Then
With ActiveChart.Legend.LegendEntries(1).LegendKey
.ColorIndex = 3
.Weight = xlThin
.LineStyle = xlContinuous
End With
End If
Comment from Ryan
Time: Tuesday, April 27, 2010, 12:44 pm
What I am really trying to solve with my if statement is say there are only three sereies but I want the code to check if there is a forth and if so modify it how I want. So I tried the following but it won’t work for some reason:
If ActiveChart.Legend.HasLegendEntries(4) Then
With ActiveChart.Legend.LegendEntries(4).LegendKey.Select
Selection.Border
.ColorIndex = 44
.Weight = xlThin
.LineStyle = xlContinuous
End With
End If
Which the first line was orgionally:
ActiveChart.Legend.LegendEntries(4).LegendKey.Select
Comment from Jon Peltier
Time: Tuesday, April 27, 2010, 10:05 pm
Change this
With ActiveChart.Legend.LegendEntries(4).LegendKey.Select
Selection.Border
to this
With ActiveChart.Legend.LegendEntries(4).LegendKey.Border
and try again.
But why are you using the legend key to format the series instead of the series itself?
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.