How do I customize a pivot chart legend and title

[Jen Severado]

I am trying to create a custom title and legend for a chart generated from a pivot table. I would like the legend to be dynamic so that if there is a category that falls off the pivot due to a zero count that corresponding legend item disappears as well. I have attached an example.
thank you,
Jen

 

[Luke M]

Hi Jen,

check out cell B23 in the attached. Used a simple formula to build the text for your chart title. Then, select the chart title, go to formula bar, type the "=" sign, and link to the cell you want.

As for the other issue, I think it becomes a moot point. Since you have long labels, more than 2 items, and want to know values, let's just create a bar chart. Much easier to read our values, don't have to worry now if user filters/changes # of series, and friendlier to color-blind people.

 

[Jen Severado]

Luke, thank you so much. this is helpful. I showed my boss and unfortunately he wants to keep the data in a Pie chart. Yuck Is there anyway to achieve this with a pie chart?

 

[Luke M]

Boss is wrong, but oh well.

We can...but have to get fancier. We can make a dynamic chart. We'll build our own labels and values to create a regular pie chart. Setup some named ranges that will dynamically grow/shrink as the PivotTable changes. I just copied down the formulas a few rows, you should copy them as far down as you'll think you really need.