Excel multi-colored line charts are a little gimmicky, after all we don’t need color to tell if a line is going up or down. However, it can be useful for encoding data that is outside a threshold and can also speed up interpretation, especially if you use familiar traffic light color encoding.
We have a few different approaches available for creating Excel multi-colored line charts.
Excel Multi-colored Line Charts Workbook
Enter your email address below to download the sample workbook.
Watch the Video
Option 1: Multi-colored line chart with Gradient Fill
The first is to use a gradient fill on the line. This is the simplest as it only requires a single series:
With the line selected press CTRL+1 to open the Format Data Series Pane. In the Format menu (bucket icon) for the line, choose ‘Gradient Fill’:
Adjust the gradient stops, adding and removing stops as required with the +/- icons to the right of the gradient bar. Select each stop to set the color.
The limitation with the gradient is that it’s based on percentages, as opposed to absolute values. Which means you can’t set values above or below a threshold with a specific color, and this makes updating the gradient stops for new data a (potentially) manual task. It really depends if you plan to update your chart with new data or not.
Option 2: Multi-colored line chart with multiple series
The second option for Excel multi-colored line charts is to use multiple series; one for each color. The chart below contains 3 lines; red, yellow and green. They are sitting on top of one another to give the appearance of a single line.
This requires your source data to be set up with each series in its own column, like so:
The #N/A values aren’t plotted and that allows the lines underneath to show through where appropriate.
To ensure a continuous line the series must overlap, hence row 16 above has the same value in both columns D and E. Without the value in column E, there would be a gap in the line.
For ease of calculation the green series plots every value, but it is covered by the yellow and orange series where appropriate. This requires the series to be in the right order in the legend entries (image below), with green at the top of the list, then yellow, then orange:
Tip: Use the up/down arrows to rearrange the order of each series as required.
Option 3: Threshold bands with line
The third option uses a stacked area chart for the threshold bands, with a white line to show the position of the value at each interval:
This requires 4 series; one for each band + the line (column B):
Insert a Stacked Area Chart to start and then right-click the series you want as the line > Change Series Chart Type…:
In the Change Chart Type dialog box set it to a Line chart:
Then you can go about setting the line and area colors for each series.
Option 4: Multi-colored columns with multiple series
Ok, so it’s not a line chart, but it has a similar effect because we can easily get a feel for the trend from the height of the columns:
Again, like option two, this requires three series to support the different colored columns.
Don’t get carried away using gradient fills, multi-colored lines and columns etc. Please only use them where they aid interpretation otherwise they fall into the ‘Chart Junk‘ category. At best they can make you look unprofessional and at worst make it difficult for your audience to interpret the chart.
If you liked this please click the buttons below to share.