• 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

SbusyCR

New Member
Hi,

In regard to the attachment sheet "bar charts" when I enter = D2 and hit Enter by first selecting the Chart Title of the chart at the right of the table, it does not work. Text does not change when it should change to Gerardo Campos.
 

Attachments

  • excercise bar charts control dev ping pong.xlsx
    337 KB · Views: 9
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.
 
I have no problem with linking pivot chart titles to cells.
Could you elaborate a little? Perhaps attach a file where you have been able to do this?
What version(s) of Excel have you found you can do this?
 
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

  • Linked Pivot chart title.xlsx
    17.4 KB · Views: 8
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?
 
I can link the title on that chart using 2010. It doesn't appear to be a pivot chart?
 
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.
 
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:
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.
 
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.
 
@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.
 
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!! ;)
 
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.
 
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
 
Back
Top