• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Multi-colored Line Charts

You are here: Home / Excel Charts / Excel Multi-colored Line Charts
Excel_Multi-colored_Line_Charts_thumb
May 8, 2018 by Mynda Treacy

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.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file. Please ensure your browser doesn't change the file extension on download.

Watch the Video

Subscribe YouTube


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:

Excel Multi-colored Line Charts with Gradient Fill

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’:

format data series pane

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.

multi-colored line chart with multiple series

This requires your source data to be set up with each series in its own column, like so:

#N/A values

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:

select data source - 3 series

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:

stacked area chart for threshold bands

This requires 4 series; one for each band + the line (column B):

stacked area chart - 4 series

Insert a Stacked Area Chart to start and then right-click the series you want as the line > Change Series Chart Type…:

insert stacked area chart

In the Change Chart Type dialog box set it to a Line chart:

change chart type

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:

multi-colored columns with multiple series

Again, like option two, this requires three series to support the different colored columns.

Warning

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.

Please Share

If you liked this please click the buttons below to share.

email icon twittericon fb icon LI icon
Excel_Multi-colored_Line_Charts_thumb

More Excel Charts Posts

picture fill excel charts

Picture Fill Excel Charts

Using a stylish picture fill in your Excel Charts is a simple way to make your data visualizations more captivating and memorable
excel speedometer charts

Excel Speedometer Charts

How to build Excel Speedometer Charts or Gauge Charts as they're also know, why they are BAD and what to use instead.
burn up burn down charts

Excel Project Management Burn Down and Burn Up Charts

Excel Burn Down and Burn Up Charts are easy to make with line or scatter charts. They are useful for monitoring the progress of a project.
wee people font charts

Excel WeePeople Font Charts

Excel WeePeople Font Charts are a nice change from generic shapes for waffle charts, bar/column charts and more.
excel dot map charts

Excel Dot Map Charts

Interactive Excel dot map charts are not built-in, but with some creative use of Excel’s built-in tools we can create something unique.
Excel S Curve Charts

Excel S-Curve Charts

Easy Excel S-curve Charts made with PivotTables for project management. Track progress by including budget amounts.
chart axis switch

Excel Chart Axis Switch

Use radio button form controls to create an Excel chart axis switch enabling you to toggle pannel charts between same axis and own axis.

Excel Charts with Shapes for Infographics

Excel Charts with Shapes for Infographic styling and increased interest in your charts. Easy to insert but there are a few tricks required.
excel pyramid chart

Excel Pyramid Charts

Excel Pyramid charts are useful for visualising demographic data across multiple categories. Let’s look at 3 ways we can build them in Excel.

Highlighting Periods in Excel Charts

Highlighting Periods in Excel Charts helps your users interpret them more quickly and or focus their attention on a point or area.


Category: Excel Charts
Previous Post:Excel 3D MapsExcel 3D Maps
Next Post:Excel Mini Charts

Reader Interactions

Comments

  1. Michael G

    December 11, 2021 at 7:05 am

    My chart changes every three months due to it being done quarterly. With that being said, one month might go up, then the next month down, then the next month up, then the next month the same as the previous month, etc. Mine is a series formula to make it much easier to get the data updated. With all of that being said, is it possible to “atomically” have the colors change from point to point (month to month) with the following three colors: Red if it’s higher than the month before, yellow if it’s the same as the month before, and green if it’s less that the month before?

    Also, I can’t seem to find a way to actually change between points. There are 12 measuring points (12 months). I can do a gradient like in your instructions, but that won’t do me any good for what I want as I just want it to be colored based on the trend (up (red), same (yellow), or down (green))

    Thanks for any advice you can provide!

    Reply
    • Catalin Bombea

      December 11, 2021 at 3:08 pm

      Hi Michael,
      I assume you are talking about a line chart. I don’t know a way to change colors between 2 data points, you can only change the entire line color unfortunately.
      Normally, for trends we can use a pivot table with conditional formatting with red-green-yellow icons to visually indicate the trend.
      You have a great tutorial here:
      https://www.myonlinetraininghub.com/how-to-use-excel-conditional-formatting

      Reply
      • Michael G

        December 12, 2021 at 12:08 am

        Unfortunately, no. This is a series formula which is:

        =SERIES(Inc_Trend_1_2!$B$1,Inc_Trend_1_2!$C$28:$O$28,Inc_Trend_1_2!$C$25:$O$25,1)

        So, to make it super simple to “redraw the chart” I am using C28 to O28 which contain whole numbers less than 30 which represent how many that month. With that being said, I have a macro built that’s assigned to a button which will “Move to next quarter” which basically gets rid of the first three months, then moves the next 10 months left three columns, thereby leaving the last three months blank.

        To give you a simple example, lets say the 13 numbers are currently:
        1 2 3 4 5 6 7 8 9 10 11 12 13
        after that button is pressed, it will become:
        4 5 6 7 8 9 10 11 12 13 empty empty empty
        So, if I enter the new numbers (replacing empty’s with real numbers), it becomes:
        4 5 6 7 8 9 10 11 12 13 14 15 16

        After I manually enter the last three numbers it “automatically” draws the last three sections of the line. This is because the formula is based on C28 to O28 when populated. Does that make more sense?

        Reply
        • Catalin Bombea

          December 13, 2021 at 3:21 pm

          Sorry, there is no way to change the line color between 2 data points.
          The only chart that looks similar to what you want is a candlestick financial chart, only there up and down will have different colors.

          Reply
          • Michael G

            December 14, 2021 at 7:52 am

            OK, that may work. I have created that type of chart, but am unsure about how to make it look like the bottom half of what you sent. What I mean by that is that in the bottom half, if has up/down arrows that are what appears to be red going up and green going down. Mine has no arrows at all I haven’t been able to figure out how to make them look like yours does which should, in theory, work.

            Thanks

          • Catalin Bombea

            December 14, 2021 at 10:51 pm

            Hi Michael,
            Try our forum(open a new topic after sign-in), you’ll be able to upload a sample file there, will be easier to help you.

  2. Tyler

    April 23, 2021 at 6:54 am

    Is there a way to create a threshold bands with negative values? My line has negative values but if I try to create a 0 area or negative area I can’t get that lower level to highlight properly.

    Thanks!

    Reply
    • Mynda Treacy

      April 23, 2021 at 9:32 am

      Hi Tyler,

      Stacked area charts aren’t great with negative values, but this post has a workaround.

      Mynda

      Reply
  3. Brian

    March 18, 2020 at 6:48 am

    excellent!! thank you so much

    Reply
    • Mynda Treacy

      March 18, 2020 at 8:38 am

      Glad you liked it, Brian 🙂

      Reply
  4. omid razzaghi

    November 8, 2019 at 2:19 am

    thank you very much

    Reply
  5. Khatia

    September 10, 2019 at 4:41 pm

    Thank you thank you thank you !!!! <3

    Reply
    • Mynda Treacy

      September 10, 2019 at 9:09 pm

      🙂 my pleasure, Khatia!

      Reply
  6. Matt Haft

    March 30, 2019 at 12:48 am

    Love it! You are so great at what you do. A true Excel master.

    Reply
    • Mynda Treacy

      March 30, 2019 at 8:55 am

      Thanks, Matt!

      Reply
  7. Joseph Horling

    June 9, 2018 at 12:36 am

    Hi,
    On the column chart, in option four, how did you alternate colors on the bars that is associated with the time axis? Not sure how to do that. Thank you. Joe, Michigan.

    Reply
    • Mynda Treacy

      June 10, 2018 at 9:40 am

      Hi Joseph,

      The same as option 2, each colour is a separate series, so you just colour each series separately. If you download the file you’ll see how it is structured.

      Mynda

      Reply
  8. SunnyKow

    May 26, 2018 at 1:32 pm

    It is interesting to note that if you have an NA() or a “” in your range of values, line charts will join the line from the values just before and after the NA() even though we expect it to have a gap. It will also treat “” as 0 instead of a blank cell. This is unfortunate as we usually use formulas like IF(A1>100,A1,””) or IF(A1>100,A1,NA()).to create gaps when we need to create multiple series for multi-colored line chart. For example lines that is Green when the value increase and Red when the value is decreasing.

    One workaround that is worth exploring is to plot the data as a Pivot Line Chart. You must use NA() instead of “” for the formulas. You will need to change the PivotTable-Options-Layout & Format-Format and tick the For Error Values Show as blank.

    The Pivot Line Chart will now show a gap for NA() values.

    Reply
    • Mynda Treacy

      May 26, 2018 at 5:07 pm

      In Excel 2016 we have the luxury of showing #N/A as an empty cell and then we can choose to show a gap, connect the line or zero: Excel 2016 chart hidden and empty cells

      Reply
      • SunnyKow

        May 26, 2018 at 6:41 pm

        Yes Mynda,I am aware of that. Too bad I am still stuck with Excel 2010 ☹☹☹

        Reply
  9. SUNNY KOW

    May 15, 2018 at 6:21 pm

    In option 3, it would be good if the line is transparent and the color bands are behind the chart.
    This would allow the line to appear in different colors according to its threshold, something that option 1 is unable to produce because it is based on percentage.

    I have experimented by copying the chart and paste-link as image and then set the line color to be transparent. This will allow me to place the linked image on top of some color bands (either on the sheet or images). It involves some adjustments but will work..

    Reply
    • Mynda Treacy

      May 15, 2018 at 7:54 pm

      Very creative, Sunny. I like the idea. It’s a shame we have to use the ‘paste link as image’ option as it often doesn’t render clearly. The only other way I can think is to place two charts on top of one another and make the line 100% transparent with an outline. I’ve not tried, so may not be possible.

      Mynda

      Reply
      • SunnyKow

        May 15, 2018 at 9:42 pm

        Hi Mynda
        I actually overlayed the line chart over a 100% stacked column chart making only the line to be transparent. Need to fix the y-axis of the line chart.
        So far have not encountered any problem.

        Reply
        • Mynda Treacy

          May 15, 2018 at 10:13 pm

          Sounds like a winner, Sunny. Maybe you can use a ghost series to ‘fix’ the Y axis.

          Reply
          • SunnyKow

            May 15, 2018 at 10:51 pm

            Most definitely I will use a ghost series to “fix” the Y axis to make it more dynamic.

  10. Chris

    May 11, 2018 at 11:57 am

    Beautiful… Excel makes it so simple.

    Reply
    • Mynda Treacy

      May 11, 2018 at 2:34 pm

      Thanks, Chris! Glad you like it 🙂

      Reply
  11. Steve Askins

    May 11, 2018 at 2:19 am

    Hi Mynda,

    We face the coloured line issue frequently when charting health and safety data – particularly for dynamic charts which are updated monthly or for different businesses. Thus I was excited to get your email on this topic…

    As you say it is pretty clear whether a line is going up or down but for us it is not always clear if up is good or bad. For example an increase in safety training hours per head would be positive so green but an increase in accident frequency would be negative so red.

    I am working on accident frequencies so down is good = green.

    Your comment about letting the green series plot every value is helpful, then overwriting with red where necessary. The problem comes where there are inflexion points (e.g. positive performance in one period followed by negative followed by positive) and the need to have the series overlapping.

    To help make this work I have added a second green line, but I just cant get the logic to work in all change of direction scenarios.

    I looked into Sunny Kows suggestion but am not quite certain whether he is referring to the same thing?

    Any suggestions would be much appreciated!

    Steve

    Reply
    • Mynda Treacy

      May 11, 2018 at 11:20 am

      Hi Steve,

      Glad you found this topic relevant. Please post your sample Excel file and question on our Excel Forum and we’ll help you out.

      Mynda

      Reply
  12. Giorgio

    May 11, 2018 at 12:12 am

    Right, in the sample workbook, on the Multi-colored Line – 3 Series chart I select a series, then go to the Format tab on the Excel ribbon and change the fill colour using the Shape Fill command but what attributes the change to either the negative or positive parts?
    If instead, on the Multi-colored Columns – 3 Series chart I select a series then press CTRL+1 to open the Format Data Series Pane, in the Format menu (bucket icon) I see the option “Invert if negative” but I doubt that’s what I’m after. Are there any negative parts in the sample workbook so one can see the concept in action?
    Also I’m a bit confused by the fact the source data for Option 2: Multi-colored line chart with multiple series, as shown in your explanation, doesn’t use the CPU load column while it does in the sample workbook.

    Reply
    • Mynda Treacy

      May 11, 2018 at 11:29 am

      Hi Giorgio,

      For the 3 series multi-colored line chart (Option 2) the formulas in the source data (columns C:E) determine which values are color coded for which line. You can modify them to suit your data/needs. Essentially columns B (CPU Load) and column E (80-Green) are the same. I just tried to show the flow from source data to the 3 series.

      In other words, you assign the values to a series (using formulas) and then color code the series accordingly.

      Mynda

      Reply
      • Giorgio

        May 11, 2018 at 11:19 pm

        Thanks Mynda but what do you do to show the positive line in green and the negative part in red.(or what ever color)?

        Reply
        • Mynda Treacy

          May 12, 2018 at 7:31 am

          Hi Giorgio,

          There are 3 lines in the chart. Just left click to select one at a time and go to the Home tab > Fill color and choose the color you want for the line. Or you can do this in the chart formatting pane, it’s the same end result.

          Mynda

          Reply
          • Giorgio

            May 12, 2018 at 7:00 pm

            Consider one single line, if there’s some part of it that’s above zero and some below it, how do you format it so the positive section is, say, green and the negative one red?

          • Mynda Treacy

            May 12, 2018 at 8:11 pm

            Your only choice with a single line is the gradient fill example.

  13. Giorgio

    May 10, 2018 at 8:09 pm

    How do you set the colours for the negative and positive parts?

    Reply
    • Mynda Treacy

      May 10, 2018 at 8:18 pm

      Hi Giorgio,

      On the 3 series line and column charts you just select the series (left click with your mouse), then go to the Format tab and change the fill color.

      Mynda

      Reply
  14. jim

    May 10, 2018 at 8:01 pm

    Some really good ideas (as usual), always nice to see some different thinking to spice up our output!

    The final warning should be in big bold type at the top – PLEASE use effects like this sparingly

    a few minor observations:
    There is a danger in the 2nd method (3 line series) if there is a midday lull, then higher lines may “bridge the gap” (don’t know how to explain it better – try setting 12am to 75 to see the effect). Sorry, but I can’t see how to fix that one
    The 4th method (multi columns) didn’t show on the e-mail/browser but, IMO, looks slightly better if you reduce the Gap width to zero but I think it’s a little garish (another, slightly gentler variant is to use an area plot)

    Occasionally the am/pm indicator can look untidy by breaking on some x-axis labels and not others; you can force it to break by setting up a number format with a line feed (ascii character 0010) and then selecting that as the number format for the axis – can’t be created directly as the axis format (not in my version – 2010)

    thanks for the ideas

    jim

    Reply
    • Mynda Treacy

      May 10, 2018 at 8:17 pm

      Hi Jim,

      Thanks for your suggestions. I agree with your warning 🙂

      Not sure what you mean by the midday lull. If you change the value in column B all the lines adjust accordingly.

      Mynda

      Reply
      • jim

        May 10, 2018 at 9:12 pm

        will send you an example

        Reply
        • Mynda Treacy

          May 10, 2018 at 9:17 pm

          The example file is available for download above. I just changed the value in cell B28 on sheet ‘Line or Column’ to 75 and the line dips down in green. I guess if you don’t want the contiguous line you could change it to show markers only.

          Reply
  15. SUNNY KOW

    May 8, 2018 at 2:11 pm

    The gradient fill method can also be applied to show the positive line in green and the negative part in red.(or what ever color) although it may not be so elegant.

    Sunny

    Reply
    • Mynda Treacy

      May 8, 2018 at 2:14 pm

      Yes, good point, Sunny!

      Reply
    • jim

      May 10, 2018 at 8:03 pm

      excellent idea, Sunny!

      Reply
      • sunnykow

        May 11, 2018 at 3:34 am

        Thanks Jim

        Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.