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.

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
• Pivot Table Conditional Formatting with VBA
• Dynamic Chart with Multiple Series
• VBA to Split Data Range into Multiple Chart Series