Show forecast values in a different color with this simple trick [charting]


Share on facebook
Share on twitter
Share on linkedin

Let’s say you made a chart to show actual and forecast values. By default, both values look in same color. But we would like to separate forecast values by showing them in another color.

If you are a seasoned Excel user, you may be thinking, “Oh, that’s easy. I will just create 2 sets of data (one for actual and one for forecast), make a chart from them and apply separate colors.”

But here is a really simple way to get the same effect.

Use a semi-transparent box to mask the forecast values. The end result is shown below.


Here is how the trick works:

  1. Create the chart from all values.
  2. Draw a rectangle (box) shape on your spreadsheet.
  3. Fill it with white color and remove outline (set the outline color to no line).
  4. Select the box, Go to Fill > more colors and set it to 50% transparent.


  5. Place the box on top of chart, adjust its size and position to overlap the forecast data.
  6. Your forecast looks in a different color!

See below demo to understand the process:


Learn more about forecasting

If your work involves trend analysis & forecasting, check out below resources:

How do you highlight your forecasts?

My personal favorite is to use dotted lines to separate forecasts. This involves either using Excel’s chart trendline option or adding a dummy series thru formulas to show the forecast line. When I am in a hurry, I usually add a semi-transparent mask to set aside the forecast values.

What about you? How do you highlight forecast values in your charts? Please post your technique in the comments area.

Share on facebook
Share on twitter
Share on linkedin

Share this tip with your colleagues

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Chandoo is an awesome teacher

– Jason

Excel formula list - 100+ examples and howto guide for you

100 Excel Formulas List

From simple to complex, there is a formula for every occasion. Check out the list now.

20 Excel Templates

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Extract words from sentence using FILTERXML - formula tip

#awesome trick – Extract word by position using FILTERXML()

This is CRAZY!!!. I stumbled on a weird use for FILTERXML() while reading a forum post earlier today. So I couldn’t wait to test it. I am happy to share the results.

Say you have some text (sentence / phrase / keyword etc.) in a cell and you want to extract the nth word. Unfortunately Excel doesn’t have SPLIT() formula. So we end up writing obscenely long array formulas or use gazillion helper columns.

Here is the super sneaky trick. Use FILTERXML() instead.

8 Responses to “Show forecast values in a different color with this simple trick [charting]”

  1. Jake says:

    While this works in a pinch, it clearly "lightens" the colors of the entire chart. Depending on where you use this, it will be blatantly obvious that you don't know what you are doing and present a poor looking graph.

    Why not separate the data into different segments when charting and have as many colors as you have data points? You might have to create a new legend and/or repeat the chart in "invisible ink", but it would be cleaner and more consistent when new or updated data becomes available.

    • Andy F says:

      While I think I agree that doing it "properly" via a second series is preferable, I don't necessarily agree that making the entirety of the "future" (data, gridlines, and even the axis) semi-transparent is "poor looking". I think it could be seen as adding more emphasis to the "future-ness" of the forecast data.

      In short, it's another tool for the toolbox, even if it's never needed.

  2. Kiev says:

    Quick & effective, cool. thanks.

  3. dan l says:

    I always use the dummy series.

  4. Peter Stratton says:

    Nice little trick, thanks very much!

  5. excel says:

    Two sets of data better. Control is much better.
    You can use the same chart next month to see what is actual and what is forecast.

    To use this trick, I think grid lines has to be removed, that will make the graphic much more sharp.

  6. gossip_boi says:

    to be honest, i dont understand why there is needed to do this way... in this case horizontal lines will be pale as well. then why a just can't change the color of the line partly???

Leave a Reply