Expand charphs like possible in a pivot

[clairh2011]

Hi

I wonder if somebody can help me please?

I have been putting together a dashboard. There is one graph that is linked to so much data it would be useless/unreadable.

So I have included some slicers, which have gone down well.

My boss has asked if it would be possible to click on a column on one of the graphs and be taken to fuller information chart that column/bar on the graph is representing.

I know its possible in pivots, can double click figures and be taken to the full list, and these are pivot charts.

I said I'd ask the experts :-)

Would really appreciate any help or if its not possible I can feed that back.

Thank you
Clair

(I'm using Excel 2010)

[dflak]

If you select the data point on the chart and right click on it, you get a drop-down menu. One of the items is Show Detail. This does what you want.

[clairh2011]

26-05-2017 09-07-03.jpg

I have taken a print screen of the options I get when I right click on the columns (they are the data points aren't they)?

I don't have the "show detail" option.

I am using Excel 2010, is it an additional feature with newer versions?

Thank you for replying so quickly dflak I really appreciate it.

Clair :-)

[xlnitwit]

Hi,

You have the whole series selected, which is why you don't see the option. You need to click once to select the series, pause, then click again to select the specific point and then right-click it.

[clairh2011]

26-05-2017 12-55-26.jpg

Thank you for your patience, I'm really not sure why this isn't working I am going to try on my pc at home aswell over the weekend.

Thank you
Clair

[dflak]

This is what you should be seeing.

[dflak]

I decided to record a macro to emulate the events. I will not go into details, but it does not work when you play it back. ShowDetail does not work with a chart selection.

However, I noticed that the selection has a name: SxPy which refers to series and point. So S2P4 is series 2, point 4.

This corresponds to the data's position in the pivot table. ShowDeatail does work with a pivot table selection.

The following code, gets the selection name from the chart and parses out the series and point. The series corresponds to the columns in the pivot table and the point corresponds to the row. I find the base cell (the topmost, leftmost cell of the data portion of the pivot table) and find the cell in the pivot table that corresponds to the selected data point on the chart. Then I do a show detail on that point.

The code should be flexible enough to work with any pivot table (I haven't tried it with pivot tables with multiple headers). You will have to tell the code what sheet has the pivot table and what the pivot table name is. I suggest assigning the macro to a CTRL-key or you can link it to a button on the sheet with the chart.

Please Login or Register  to view this content.

[xlnitwit]

I am using Excel 2010, is it an additional feature with newer versions?

Apologies- yes, it is. The option is not present in 2010.

[clairh2011]

Thank you fdflak that is very kind of you I will try it :-)

[clairh2011]

Which version on Excel would enable me to advance my excel skills please?

Excel 2013? Would that allow me to select data on a right click on a forum column?

But would only I be able to or would those that I actually create the reports for also need 2013?

Thank you
Clair

[clairh2011]

Hi Ya I have had 2016 installed today and this is the first thing I have looked at.

But I must still be doing something wrong, as if I click a single column, right click, select data, it just tells me where it is pulled from (source data), my boss was hoping that column on a graph
could be clicked on and then taken to the details below, so they can see what is making up that particular column/bar.

Is Excel able to that, I know you can in a pivot.26-09-2017 16-09-09.jpg26-09-2017 16-09-43.jpg

Hope I have attached print screens OK.

Thank you so much
Clar

[sandy666]

maybe try Show Detail ?

[clairh2011]

Thank you so much yay :-) you are a true gem sandy666

woohoo

Clair :-) (very happy bunny)

[sandy666]

You are welcome