# 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.

Q=Quantity , R=Revenue

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

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.

### Assigned Tags

You must be Logged on to comment or reply to a post.

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)

ðŸ™‚

Sara G Ayer
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

Nice pictorial representation there. Good document.

Sara G Ayer
Blog Post Author

Thanks Srinivasan

Regards,

Sara

Innovative Representation.. ðŸ™‚

Sara G Ayer
Blog Post Author

Thank you subbarao

Regards,

Sara

Informative...Thanks.

Best Regards,

Naresh K.

Sara G Ayer
Blog Post Author

Thank you Naresh

Regards,

Sara

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

Sara G Ayer
Blog Post Author

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

Regards,

Sara

thanks .. nice trick  ðŸ˜‰

Sara G Ayer
Blog Post Author

Thanks Nethaji Paramaguru .

Regards,

Sara

Really Appreciatable post.,.

Most of the clients need colorful Report output,.,

Regards,

J.sakthikumar

Sara G Ayer
Blog Post Author

Thank you Sakthikumar

BR,

Sara

Hi Sara.

Nice work!

Keep it up

Regards,

Hari Suseelan

Sara G Ayer
Blog Post Author

Thank you  Suseelan

BR,

Sara

Hi Sara,

Keep posting many blogs and documents.

I am waiting to see that!

Regards,

Hari Suseelan

Nice work ...Keep Posting

Sara G Ayer
Blog Post Author

Thanks Naveen Kumar

Will keep posting.

BR,

Sara

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

Regards,

Chandrakanth.

Sara G Ayer
Blog Post Author

Thank you Chandrakanth

BR,

Sara

Wow...Its kol Sara

Sara G Ayer
Blog Post Author

Thanks Arshiyan - ðŸ™‚