Let’s look at the behavior of line charts and the category axis. Here is some simple data I made up just for this exercise, and a typical line chart plotting the data.

Okay, not too boring. Let’s see what happens if we split the data into two series, red and blue. The red series will include points 1 to 5, and the blue will include points 5 to 11. This is how we might plot actual and predicted data on the same chart, or perhaps data by quarter or year in separate series.

That didn’t do what we wanted or expected. In a chart type that relies on a category-type axis (line, area, column charts), all series use the same category data as the first series; in an XY chart, each series can have independent X values, and the X axis is a value-type axis. The first series has five data points (five category values, 1 to 5), while the second series has seven data points (5 to 11). The chart then has seven category values across the X axis, but only the first five (defined by the first series) have labels to display. The second series starts at the first category, like the first series, even though we would expect them to line up according to category values; an XY chart lines up in the way we would expect. We could just use an XY chart for this, but there are times when a line chart is preferred.

To align the two line series appropriately, we need the same number of category values, and we need to make sure the Y values are lined up with the corresponding category value. We accomplish this by splitting the values into two columns, red and blue; the red range has values in the top part and blanks below, while the blue range has blanks at the top and values below.

Table Arrangement for Two-Toned Line Chart, with Chart

The line chart works fine, one series starting where the other leaves off. By default, no points are plotted where there are blanks in the table. If you are using formulas to generate this data, you need to adjust the formula to use NA(), which is not plotted in a line or XY chart, and not “”, which is plotted as a zero value.

In fact, this is the basis for conditional charts, charts which display different formatting based on certain conditions being met. There is no conditional formatting built into Excel charts, but it can be simulated using multiple series and formulas or named ranges.

To get our two-color chart, we can design a table as follows. The original data is in columns A and B. Columns C and D will have formulas to define the chart series data. Cell E2 contains a cut-off value, below which we want to see the red series, above which the blue. The formulas in cells C2 and D2 are:

=IF(A2<=$E$2,B2,NA())
 

and

=IF(A2>=$E$2,B2,NA())
 

These formulas are filled down through row 12. Both <= and >= are used in the formulas, because we want both series to plot the shared point; if we used <= and =, we would have a gap after the cut-off point; if we used = and >=, the gap would occur before the cut-off. If we create the chart with the data in columns A, C, and D, we will get a chart just like the one plotting the previous data arrangement. However, if we change the value in cell E2, the point that divides red from blue will move left or right.

Table for Conditional/Dynamic Chart

Conditional/Dynamic Chart for Cut-Off Values of 3, 5, and 8.

This technique is extended to area charts in Category Axis Tricks for Line and Area Charts - 2