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.