I have a range slider to show forecast amounts on my chart, but I want the color of the forecast bars to be grayed so that there is an obvious difference between actual amounts and forecast amounts. So here’s the Tech Tip:
We accomplished this by using Alerts for the column colors!
First off, we set up the calculations for capturing the forecast amount (in this case, if an actual amount exists, use that, otherwise, take an average of the actuals and multiply by the forecast). The column chart points to the calculated fields for display on the General tab of the column chart properties.
Then, on the Alerts tab, check the “Enable Alert” option. Point the “As a Percent of Target” range to the ACTUAL values, including any blank rows that will be added over the next year. Remove one of the alert colors, and set the lowest value to 100%. Next, uncheck the “Enable Auto Colors” option, and click on the color square of the lowest alert to change the color to the color you would like. The “No Data” color is what will be used for the forecast bars. On the first series, we left the “No Data” color, but on the second series, we changed the “No Data” gray color to a darker gray, to show contrast.