Skip to Content
Author's profile photo Sara G Ayer

Differentiating Forecast and Actual in the same chart(Multiple Coloring in Charts)

Here is a trick to show Sales and forecast in different colors in the same chart. This can also be used to have different colored Columns in the same Chart.

Well there are two ways of doing it.

  1. Using Progress Bar
  2. Using 2 Series

1. Using Two Series:

Use one series for the for the actual data, leave the rest column (predicted months) Blank.
Now in one another row keep only the predicted month’s data.
Leave the current months empty.

Capture.PNG

Q=Quantity , R=Revenue

You can map these 6 series to your chart. (I have taken only the revenue , which means 3 series-es )

Capture.PNG

2. Using Progress Bar:
Keep Your date in MMYYYY Format as displayed above.

In cell :
A1 = TEXT(TODAY(),”yyyymm”)
//To note the year and month.

One cell (drag it over to the whole row) = IF(B1=””,””,IF(B1>=$A$1,0,1))
//This will check if the month there is less than the today’s month. (Actuals)
=Sum(this row)   //This will be the data for the progress bar.

One cell (drag it over to the whole row) =IF(B1=””,””,1)
//To check the number of columns populated.
=Sum(this row)   //This will the max limit of the progress bar.

In progress Bar:
General Tab: Scale-> Manual
Behavior Tab: Limits all fixed
Appearance Tab : Layout-> disable ticks ; Text -> uncheck everything ;
Color -> Different colors for marker and track.
Map data to the chart. In behavior tab of the chart , uncheck Ignore blank series.
Place this progress bar right below the chart.

Capture.PNG

Assigned tags

      23 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      That's a very useful trick!

      how about making the line change its colour? (if the line was to show the actual&forecast sales figure)

      🙂

      Author's profile photo Sara G Iyer
      Sara G Iyer
      Blog Post Author

      Thank you Matt,

      For that you ll have to follow the same technique as mentioned in the first technique.

      1. Using Two Series:

      Use one series for the for the actual data, leave the rest column (predicted months) Blank.
      Now in one another row keep only the predicted month’s data.
      Leave the current months empty.

      This way you ll have 2 series for the line chart, Which eventually can be shown in two different colors. 🙂

      Cheers,

      Sara

      Author's profile photo Former Member
      Former Member

      Nice pictorial representation there. Good document.

      Author's profile photo Sara G Iyer
      Sara G Iyer
      Blog Post Author

      Thanks Srinivasan

      Regards,

      Sara

      Author's profile photo Former Member
      Former Member

      Innovative Representation.. 🙂

      Author's profile photo Sara G Iyer
      Sara G Iyer
      Blog Post Author

      Thank you subbarao

      Regards,

      Sara

      Author's profile photo Former Member
      Former Member

      Informative...Thanks.

      Best Regards,

      Naresh K.

      Author's profile photo Sara G Iyer
      Sara G Iyer
      Blog Post Author

      Thank you Naresh

      Regards,

      Sara

      Author's profile photo Former Member
      Former Member

      Informative. 4/5 stars just because the color schema for the chart doesn't look too good.

      Author's profile photo Sara G Iyer
      Sara G Iyer
      Blog Post Author

      Thanks Ananda Krishnan , I dint concentrate on the colors or looks here much, so it might look that good.

      Regards,

      Sara

      Author's profile photo Former Member
      Former Member

      thanks .. nice trick  😉

      Author's profile photo Sara G Iyer
      Sara G Iyer
      Blog Post Author

      Thanks Nethaji Paramaguru .

      Regards,

      Sara

      Author's profile photo Former Member
      Former Member

      Really Appreciatable post.,.

      Most of the clients need colorful Report output,.,

      Regards,

      J.sakthikumar

      Author's profile photo Sara G Iyer
      Sara G Iyer
      Blog Post Author

      Thank you Sakthikumar

      BR,

      Sara

      Author's profile photo Former Member
      Former Member

      Hi Sara.

      Nice work!

      Keep it up

      Regards,

      Hari Suseelan

      Author's profile photo Sara G Iyer
      Sara G Iyer
      Blog Post Author

      Thank you  Suseelan

      BR,

      Sara

      Author's profile photo Former Member
      Former Member

      Hi Sara,

      Keep posting many blogs and documents.

      I am waiting to see that!

      Regards,

      Hari Suseelan

      Author's profile photo Former Member
      Former Member

      Nice work ...Keep Posting

      Author's profile photo Sara G Iyer
      Sara G Iyer
      Blog Post Author

      Thanks Naveen Kumar

      Will keep posting.

      BR,

      Sara

      Author's profile photo Former Member
      Former Member

      Thanks, Nice trick......Useful Info..........

      Regards,

      Chandrakanth.

      Author's profile photo Sara G Iyer
      Sara G Iyer
      Blog Post Author

      Thank you Chandrakanth

      BR,

      Sara

      Author's profile photo Former Member
      Former Member

      Wow...Its kol Sara

      Author's profile photo Sara G Iyer
      Sara G Iyer
      Blog Post Author

      Thanks Arshiyan - 🙂