Format pivot chart trendline, Actuals vs forecast

After a couple of days, I will venture to respond. I don't quite understand what you are trying to do here. Your sample file shows a pivot table with 3 columns (plus the grand total column) and those 3 columns plotted in a pivot chart. Your request states that you are trying to distinguish in the chart between "actual" and "forecast", but I don't see anything in the pivot table or chart or source data to indicate what values are actual values and what values are forecast values. I also cannot tell if the charted values are the trendlines you are referring to, or if you are adding trendlines to these three series in the pivot chart, and your request for formatting refers to the chart trendlines that you have not added in your sample.

My first suggestion is that, if the chart is going to be able to distinguish between "actual" and "forecast", there must be some way in the spreadsheet to distinguish between actual and forecast. My other expectation is that, in order to different formats for "actual" and "forecast" in a chart, you will likely need to add the actual and forecast values as separate data series, which will likely mean some kind of change to the source data and/or pivot table. Since I am not certain exactly what you are trying to do, I cannot yet make any specific recommendations.

Possible? I tend to be optimistic that most anything is possible with enough time, effort, and ingenuity. Using separate data series for conditional formatting is general the easiest way to do it, not the only way.

The easiest, albeit tedious, way without VBA is to manually format each "forecast" data point in the chart. To select a single data point in a series, click on the series once, then click on the desired data point. Bring up the format data point dialog and format as desired. Repeat for each "forecast" data point (the redo ctrl-y command will likely be invaluable in performing this task). It will be a bit tedious the first time. And will need to be repeated each time you update the pivot table (with monthly data, that would likely mean changing the format of several data points each month), but it should be straightforward and easy.

This process can be automated in VBA, if desired. I will refer to Jon Peltier's tutorial: http://peltiertech.com/vba-condition...ategory-label/ This tutorial shows an approach using VBA to format each data point. He stores the "format" color in a table in the spreadsheet, which can be useful, but is not necessary for the macro to work. The key part of the procedure is the "For icategory...Next" loop that loops through every data point in the data series, checks the x axis data, and formats the data point accordingly. If you want to pursue this, I would suggest you spend some time with Jon's tutorial until you understand how this loop works.

This link occurs near the top of the VBA conditional formatting post previously linked to: http://peltiertech.com/conditional-f...-excel-charts/