Skip to Content

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

To report this post you need to login first.

23 Comments

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

  1. 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)

    ๐Ÿ™‚

    (0) 
    1. Sara G Iyer 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

      (0) 

Leave a Reply