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:
This is exactly what I was looking for. Thanks.
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?
Richard –
This behavior is unchanged since Excel 97 (or earlier).
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
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.
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
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?
Hi Jon
You’ve been so helpful before, I am hoping for a innovative solution (using vba or not). Im using Excel 2003.
I have an area chart (standard normal distribution) that looks like a bell curve. I use dynamic ranges to break my data into 3 specific (variable) x-axis sections (e.g., 0-2,000, 2,000-7,000, 7,000-8,000), and would like to either distinctly shade these vertical sections of the curve, add 2 drop lines solely at 2,000 and 7,000, or somehow otherwise differentiate the 3 sections.
I know I can do this be creating 3 DUMMY data series from the original, based upon my dynamic ranges, but would prefer NOT to replicate 1 very large data column into 4. I considered replicating the x-asis as a secondary axis and plotting the 2 points against that axis, but I’ve not been successful creating the 2nd axis, and would prefer not to have to go this route in any event.
I’ve trolled the web, and spent considerable time looking through your site in particular, but haven’t found a solution. Any insight you can provide will be appreciated.
Thanks in advance for your help.
-Paul
Paul –
Excel plots the data you give it. Trying to do something fancy like plotting multiple series from one data range sometimes can be done, but saving a few cells is usually not worth spending hours setting it up and more hours later fixing it when something changes. Add three ranges for three dummy series, and pat yourself on the back for being more clever in the long run.
Thanks Jon. Wise words.
Thank you :) with your tips I resolve my problem in 10 minute