Split Data Range into Multiple Chart Series without VBA

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:

Peltier Tech Chart Utility

Comments

  1. This is exactly what I was looking for. Thanks.

  2. 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?

  3. Richard –

    This behavior is unchanged since Excel 97 (or earlier).

  4. 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

  5. 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
  6. 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

  7. 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?

  8. 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

  9. 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.

  10. Thanks Jon. Wise words.

  11. Thank you :) with your tips I resolve my problem in 10 minute

Subscribe without commenting

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.

Subscribe without commenting

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.