• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Reference chart title to cell not working in excel 2019

r2c2

Active Member
Also some of the new fanged charts like sunburst, map charts don't work with linked cells or only support it for certain parts of the chart.
 

Debaser

Active Member
2010 and prior certainly. I can test later versions when I get home. Here is a sample.

I'm fairly sure Debra Dalgleish has a relatively recent article on doing this, so I tend to assume it still works, unless they broke it in current versions.
 

Attachments

p45cal

Well-Known Member
Yep, when I create pivot table charts in later versions of Excel I can link the title to a cell no problem, however I still can't do this in SbusyCR's Chart 1 on his bar charts sheet.
SbusyCR, can you walk me through how you created that chart?
 

p45cal

Well-Known Member
It doesn't appear to be a pivot chart?
Here, in Excel 2019, it does appear to be a Pivot Chart.:
63354

Inserted, I suspect, using Insert Pivot chart here:
63355
which surprisingly allows you to select a normal range for a pivot chart.
But I can link a chart's title to a cell if I create a new chart that way, hence why I asked the OP to describe how the chart had been inserted.
 

Chihiro

Excel Ninja
No it is PivotChart. It's just not linked to any pivot table and is made from data model directly (notice the orange cylinder in the icon. That indicates table in data model and not in sheet range).

In Excel 2010/2013. Data model created in later version can't be used since engine changed in Excel 2016.

Hence, the oddities that's being observed.

FYI - If you create pivot table. Then use Pivot Table ribbon tool to add pivot chart based on the table (i.e. linked), then you can use sheet range as source for dynamic chart title.
 
Last edited:

Chihiro

Excel Ninja
Or replace chart title with a textbox linked to the cell.
I wouldn't recommend it.

Without linked pivot table, title is automatically linked to Legend (Series) fields (will show dynamically as long as there is only single pair selection).
Ex: July - Enrique NG.

Even if you use textbox... your only option to construct dynamic title linked to data, is to read slicer selection is via VBA or CUBE function.

By using linked pivot table. It will give you more option to construct your title via formula in a cell.
  • Adding field to Page Field and read it directly
  • Construct DAX measure to serve as dynamic title (housed in separate Pivot from chart source but linked via slicer).
  • Read value off of Pivot Table itself using various formulas.
etc.
 

GraH - Guido

Well-Known Member
Don't see the issue to be honest. What is the difference?
Or the chart title is linked to a cell containing a formula or the chart title is replaced by an empty textbox which is linked to a cell containing a formula.
What am I missing? I've done that a zillion times - slightly exaggerated - where the formula is using a single/multiple slicer selection value(s). However I try to avoid pivot charts all together, to keep more control over the visual, but that is just a personal reference.
 

Chihiro

Excel Ninja
@GraH - Guido

My comment is specifically when textbox method is applied to OP's current set up (without linked PivotTable).
If single slicer, not much of an issue. But using CUBE functions on multi-selection, multi-slicer set up is pretty complicated.
It's far simpler to set linked pivot table with classic layout with repeating label. Then use traditional formula on the pivot table range.
 

SbusyCR

New Member
Hi all and thanks for each input!

p45cal I do not remember how I created the chart :(

Chihiro Am I understand correctly that the fact that reference to the text cell is not working is due to table is in data model? Nice to know about the orange cylinder in the icon!! ;)
 

Chihiro

Excel Ninja
Using Data Model in itself isn't the cause. But lack of linked Pivot Table is the cause here. When you create PivotChart linked with PivotTable, it will give you slightly more control over chart elements.
 

SbusyCR

New Member
Using Data Model in itself isn't the cause. But lack of linked Pivot Table is the cause here. When you create PivotChart linked with PivotTable, it will give you slightly more control over chart elements.
Absolutely! Have a great day
 
Top