Pivot Charts from the Data Model Don't Allow Dynamic Titles

[pjmorris]

If the statement in the title is genuinely incorrect - please tell me what I'm doing wrong.

Explanation:

I created a table with two columns and 8 rows. First Column has Locations, the second Amounts. I added the table to the Data Model (Powerpivot - Add to Data Model). From the Insert menu I added a PivotChart, worth noting that excel created both a chart and a pivot table. I added the Locations as rows and Amounts as the value. I was then able to put a formula in the chart title to allow dynamic titles. It worked fine. But, I don't actually want the pivottable and so I deleted it. The chart no longer updated when I made changes to the source data; though the dynamic title continued to work (ie it matched whatever was typed in the cell to which its formula pointed). This makes me think that when inserting a PivotChart in this way you actually only create a Pivottable and a normal chart which draws its data from the table.

In PowerPivot I then created a PivotChart - this time there was no pivottable created only a chart area. In this chart I am unable to create a Dynamic Title. The only way to change it is programmatically.

Hence, I believe there are two flavours of chart in Excel, the first is a normal chart created either from the a normal range or pointing to a pivot table, the second is what I would call a genuine Pivot Chart created from the data model with no associated pivottable. Dynamic titles can be created in the first type of chart but not the latter.

I'm using Excel 2016.

Grateful for comments/thoughts.

 

[GraH]

Hi,
A dynamic title can be put in a text box on top of any chart. So I'm wondering how you create the title. I might missunderstand something here.
Is the title supposed to react on a cell input value, a filter/slicer?

 

[pjmorris]

Hi Grah,

Strictly, putting a title in a text box is not making the title dynamic as the text box could be moved to anywhere on the sheet. However, it would be a work around.

In a normal chart you can simply click the title box and then enter a formula in the formula bar and the title will then show the result of the formula. In a PivotChart that behaviour doesnt work.

I just wondered whether anyone had come across this situation and know a solution; the textbox would be one way of doing it.

All the best.

 

[GraH]

It is what I meant: using the camera-trick to refer to a cell, in which you have the dynamic title, inside the textbox.

However the formula trick works on a pivot chart as it does on a normal chart. Try by clicking the border of the Title box 2x, not double click but select and select again.

 

[pjmorris]

Hi GraH,

I assume that from the Insert Menu you've selected PivotChart. In that circumstance you're correct you can link the title to a cell (not quite sure what you mean by camera-trick though). In fact it doesn't need any second click, a single click on the title and then type '=' and the cell reference (or simply then click on the cell as normal). What I think happens here is that a PivotTable is created and despite the field list saying PivotChart the axis and values choices are actually applied to the PivotTable. If you delete the pivotTable the chart no longer functions, thought the dynamic title will respond to changes whichever cell its linked too.

My problem occurs if you follow the following steps:

1. Click in your source data and choose 'Add to Data Model' from the PowerPivot ribbon.
2. In the PowerPivot window select PivotChart (need to click the little down arrow under the PivotTable option)
3. You can then set the axis and values as before, but I can't under any circumstances get the title to link to a cell.

If you're 'camera-trick' is something more than pointing to a cell can you explain as it will be something else I need to learn.

Many thanks for engaging in the discussion. Its much appreciated.

 

[JustLearningMyWay]

I have the same issue where I cannot link the PivotChart title to a cell.
I've been experimenting and from what I can tell, it depends on the data source for the PivotTable.

If I make a PivotTable from an Excel table, I can link the title and axis labels.
If I make the same PivotTable, but use the data model to access the same table, I can't link the title or axis labels.

Not sure that helps, but something to try.
I suspect isn't an Excel glitch.

 

[JustLearningMyWay]

Sorry, new here and can't figure out how to edit my previous response.
I meant that if it make PivotCharts. Obviously there's no title to link with a PivotTable.

 

[GraH]

My problem occurs if you follow the following steps:

1. Click in your source data and choose 'Add to Data Model' from the PowerPivot ribbon.
2. In the PowerPivot window select PivotChart (need to click the little down arrow under the PivotTable option)
3. You can then set the axis and values as before, but I can't under any circumstances get the title to link to a cell.

If you're 'camera-trick' is something more than pointing to a cell can you explain as it will be something else I need to learn.
Many thanks for engaging in the discussion. Its much appreciated.

Hi Peter, sorry for the delay, I've been busy with an Excel project for work. Can't motivate myself to come to the forum on top of that and do some more Excel. Sometimes enough is enough

Yes, I figured later you did start with inserting the chart from PowerPivot window. Learned the hard way you can't feed a dynamic title to such a chart. And no, my camera-trick is the same as yours. Nothing new here.
So I stick to the workaround with a textbox, hovering the chart, which refers to a cell (with formula) or a formula in the name manager, to create a dynamic title.

 

[pjmorris]

I think the conclusion is that genuine PivotCharts don't have dynamic titles. My solution has been to use VBA, but I'd not thought about GraH's use of a Textbox, which would be easier.

Regards