Entering chart titles for chrats created using pivot tables.

[Aditya Jadhav]

Hello,

Have created chrats using pivot tables and charts for a huge data set and want to name the chrats respectively, is it possible to name all the chrats using some kind of formula. Because naming them manually is consuming huge time as they are around 3k charts.

Looking forward for some solution.

Have attached a file and the querry too in the excel sheet please have a look at it.

Regards,

Aditya.

[#2]

Hi Aditya,

Yes, sure. Combine dynamic title with formulas in any cell and after that link chart title with this cell through formula bar. How to do that is, for example, here http://blog.contextures.com/archives/2012/11/06/link-pivot-chart-title-to-report-filter/

- first what i find. Google will give much more.

[#3]

Hello Sergei,

Thank you for the link will surely go through it.

Regards,

Aditya

[#4]

Hi,

Tried a lot of things when i have a single slicer as a filter the chart titles can be named perfectly but as it is that i do have a slicer already in my pivot table which is making it difficult to name those tiltes just with the above given link need help in doing it correctly.

Attaching a file below which will show a clearer picture what im facing right now.

Regards,

Aditya

[#5 · Accepted solution]

HI Aditya,

Sorry, i didn't catch how you structure your data (merged columns in main table, is one ID = one Title or not, why not separate table if so, etc) and why do we spaeak about two slicer = i found only one slicer fo ID here.

Anyway, back to titling - there are two main approach to pickup slicer value. Use cube formulas and use helper pivot table. More details for example here http://chandoo.org/wp/2015/06/24/introduction-to-slicers/

Since you don't load your pivot to data model, we may use helper pivot.

In attached file i added one more pivot table with value of min dates (my guess your dates are linked to ID:s, but you may use any other vakue here);

linked your ID slicer to that table through slicer connections;

formatted value in new pivot table;

linked chart title to first row of new pivot table as link in previous table

That's just sample, you may add checking if more than one row selected, if nothing is selected, if you have  more connected slicers.

The idea is this helper pivot table returns exactly the values selected in slicers (not but slicers). 

[#6]

Hi Sergei,

Actually im very new to the pivot tables and slicers. One question i have now in mind is how did you create another pivot table in the same worksheet ? Because when i try to add the table it doesnt add up it shows some kind of error. Now i found the connecting slicers option but didnt find the way to create another pivot table in the same worksheet.

Now can you please guide me step by step how you did that would be more helpful and appreciated.

Regards,

Aditya

[#7]

HI Sergei,

Found out how to do it and connect the slicers could do it now a new issue is coming up while naming charts as it is that the prvious one was for every day so the titles were very correct but now i need to give range of some dates say 01 feb to 7 feb and so on how can that be done here do you have any idea.

Please help me this is the last output of my file then .

And let me thank you for all the help that you have provided me in this issue.

It has helped me a lot.

Regards,

Aditya

[#8]

Hi Aditya,

Here https://support.office.com/en-us/article/Create-a-PivotTable-to-analyze-worksheet-data-a9a84538-bfe9... is step by step insruction how to create Pivot Table and some other usefull stuff.

In brief, select Existing worksheet option (instead of default New worksheet) in Create PivotTable window.

[#9]

It depends on how you would like to select your data, still using ID or using Range Title slicer; and what is the logic for your ranges' names.

For example, to expand previous example, you may add one more cell which will be calculating something like this

="Week # " & WEEKNUM(<helper pivottable cell>,11) & " of " & YEAR(<helper pivottable cell>)

and link your chart title to this cell.

Alternativelly you may add simular formula into your main data table as one more column. Important what you define the Range title for each record (other words against each ID here it shall be some text in Range title). You may use any formula you prefer or add this table manually, or copy/past from external source - doesn't matter.

After that create Pivot Table with only this Ranges' titles in row, connect it to slicer with ID and link chart title first new PivotTable cell.

Or you may instead of ID slicer use Range Title slicer, rest of procedure is simular.

[#10]

Hey Sergei,
I have found the links very useful but none of them says or shows how can i give the chart titles in range that too automated using or combining the various table or inter linking them somehow as you showed it earlier that worked very well and has brought me upto the last output for my work.
Just need this lat help that how do i bring in the date range from the data set which i need them on the chart titles. Because the table created takes the first date as the chart title which is not what i need it to be displayed.
For e.g, in the month of Feb i have a chart showing the data from 1st Feb to 7th Feb and the chart title for that comes as 1st feb 2016 now, which i need to be shown as 1-7 Feb 2016 or something like that but need this range because its to complicated to just show only one starting date even for different sets of combinations, that doesn't make any sense.
Please suggest me something that i can do further in it.

Attaching below the another file which explains the facts and the required output for the same have put a note in the sheets as well.

[#11]

Hi Sergei,

Really found useful the help and support you gave me i have completed my final output i could do it and thanks a ton.

Have already done all needy things and have done it quite well, again thank you for the help.

Regards,

Aditya

[#12]

Hi Aditya,

Great to know you sorted everything out, good luck.

[#13]

Hey Sergei,

I'm now facing some problem when i create another pivot table and try to connect it with the slicer it doesnt show up the newly created pivot table so have i done something wrong ? Because if they are not connected im still stuck up with the same issue then.

Please help me.

Regards,

Aditya

[#14]

Hi Sergei,

Now i'm able to do all the final touch ups for the desired ouput and have been able to correct all my mistakes that i did previously so its all done just a small question is it any how possible to copy all the created charts using pivot be exported using some automation?

I really dont know that so just asking.

Regards,

Aditya

[#15]

Hi Aditya,

If i understood you correctly you generated few dozens of Charts in your Excel file and you'd like automatically copy/paste each of it into another Office application (e.g. Word) or save each of it as picture file - instead of copy/paste each of it manually. Or, alternatively, generate such charts based on some parameter and export as above.

If so, i guess that possible using, for example, powershell, but i have no ready to use solution.

If that's something else please clarify what exactly. In any case afraid automation of copy/pasting will require some more or less complex coding, which tool to use and what kind of coding depends on goals.

[#16]

Hi Sergei,

Searched a lot on web and found out that it needs to be done using VBA which will help a lot to save time and get the desired output. Searching for more options and trying to figure it out how to use it.

Regards,

Aditya

[#17]

VBA or Powershell that doesn't matter, depends mainly on your usage scenarios. In any case some coding.

[#18]

Hey Sergei,

Have been looking for some option taking some help form people for writing the code because that is the only option to do that.

Aditya

[#19]

Hi Aditya,

In our corporate environment we don't share macro-enabled files, thus i practically don't work with scripts and can't help here.

[#20]

Hi Sergei,

I totally understand that hence i'm not asking for the code help. Just trying to figure out the way ,have already started working on it manually because of this issue, i'm really not sure how much time it will take to first learn the codes and then apply them so have done it manually and still doing it.

Regards,

Aditya