fbpx
Search
Close this search box.

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

Share

Facebook
Twitter
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.

show-forecast-values-in-different-color-on-charts

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.

    fill-color-transparency-settings

  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:

show-forecasts-in-different-color

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

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

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

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

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

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

9 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???

  7. Great tutorial. Thanks for the tutorial!

Leave a Reply