How to show a selected PivotItem on a PivotChart in 2007 Excel

[feipezi]

Hi,

In MS Excell 2003, if you select a PT item, the item will show on the linked PivotChart, so users can clearly see what PT item he or she selected. But in 2007 Excel, you can select an item on the right side of the worksheet from the dropdown of a PT field, but you won't be able to see on the chart what exactly you selected.
Of course, I can write a Macro to show the item name or caption in the chart title. But is there any way to make the selected item show on the chart without Macros involved?

Thanks in advance.

 

[SkipVought]

Hi,

I cannot reproduce this condition.

When I create a PT/PC, I can see in the chart, EXACTLY what I have selected in the PT.

Please post 1) a sample of the source data, 2) the fields you have in ROW, COLUMN, VALUES, FILTER, and 3) PC type.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[rji1124]

Hi - we had this same observation at my place when we were all switched over to Excel 2007... We are heavily dependent on pivot tables here and it has been quite an adjustment. Funny enough, I downloaded the 2010 Beta at home and the functionality you are talking about has returned.

 

[feipezi]

Hi Skip,

You're right about what you see is what you select. But you have to remember what you select, unless you click the dropdown and you will be able to see the shaded PT item selected. But on the chart, you won't be able to see any flag or explicit indication of the shaded item. In 2003 version, you will.

Please let me know if you don't have access to the file uploaded.

Thnx to you all in advance.

 

[SkipVought]

Company security prohibits me from accessing your file.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[feipezi]

rji1124,
What's '2010 Beta'? Is that a new version of Office?
Thanks.

 

[SkipVought]

OK I downloaded your workbook.

P;ease tell me exactly what you select in the PT and what you expect to see and what you actually see.

Right off I have a big problem with your source data structure, as it is not NORMALIZED and will be difficult for you to get good results easily.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Another huge problem is that your 'dates' are NOT REAL DATES!!! They are merely STRING values, nearly totally useless!!!!

This workbook is a MESS!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Here are my results after spending about 5 minutes restructuring the source data into a normalized table of 250 rows...
[tt]
region 5Z:WEST

Sum of Value tag
Dte OU_PLAN OU_PLAN_PJ OU_TGT OU_TGT_PJ
Mar-09 0.4212 0.3762
Apr-09 0.4792 0.4265
May-09 0.4448 0.4256
Jun-09 0.4119 0.3880
Jul-09 0.4475 0.4229
Aug-09 0.4205 0.3706
Sep-09 0.4803 0.4215
Oct-09 0.4378 0.4227
Nov-09 0.4092 0.3891
Dec-09 0.4515 0.4471 0.4299 0.4248
Jan-10 0.4151 0.4245 0.3854 0.3948
Feb-10 0.4725 0.4909 0.4131 0.4296
Mar-10 0.4643 0.4522
Apr-10 0.4436 0.4281
May-10 0.4905 0.4682
Jun-10 0.4631 0.4335
Jul-10 0.5238 0.4624
Aug-10 0.4918 0.4796
Sep-10 0.4618 0.4462
Oct-10 0.5040 0.4817
Nov-10 0.4720 0.4423
Dec-10 0.5280 0.4666
[/tt]
and every filter seems to work in the attached PVt Chart.

BTW, my source table structure is, example...
[tt]
tag Dte Value region
OU_TGT Mar-09 0.427123232 1Z:NORTHEAST
OU_TGT Apr-09 0.426542633 2Z:SOUTHEAST
OU_TGT May-09 0.420936079 3Z:MIDWEST
OU_TGT Jun-09 0.418401545 4Z:SOUTH CENTRAL
OU_TGT Jul-09 0.422935316 5Z:WEST
OU_TGT Aug-09 0.418512088 1Z:NORTHEAST
OU_TGT Sep-09 0.42437682 2Z:SOUTHEAST
.....
[/tt]
How to NORAMLIZE... faq68-5287

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[feipezi]

Thanks a lot Skip, for your effort of manipulating the data. I really appreciate it.

But even before normalizing the data, the filters were still working. The problem was the selected item did not show on the PT chart.

You said: "every filter seems to work in the attached PVt Chart". Did you mean you can see the item you selected on the chart? For instance, if you picked "1Z:NORTHEAST" from the dropdown, would item "1Z:NORTHEAST" actually be showing on the chart? That's my whole point.

The data is a mess. Sure, I am not supposed to give you the actual data of the company. It's something that I made up. But you did get my point, didn't you?

Thanks again.

 

[SkipVought]

With the chart selected, in the PivotChart Layout Tab, Add a Chart Title.

Select the Chart Title, type the EQUAL SIGN in the Formula bar and then select the PiovtTable Region Value and hit ENTER.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

BTW, when I was commenting on the 'mess', I was not referring to the CONTENT of the data. I WAS referring to the STRUCTURE of your data in your workbook, which is why I posted a solution to this predicament.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[feipezi]

So there is no built-in dropdown on the PT chart. This really sucks. 2007 office is supposed to keep the useful features from the previous version. Of course, it's easy to set up a VBA Event to make the title change with the change of selection like what you specified. What if the user doesn't know VBA?

Your way may have another problem. The region value selected for the title must be always at the same location of the worksheet; or it will screw up the title or the title shows wrong info; unless resort to VBA.

Thanks again. At least your way can live up to an expedient to deal with the situation.

Take care.

 

[SkipVought]

Frankly, the 'old' pivot chart was a bit klunky, IMHO.

The new 'pivot chart' is just, I say 'just' but whoa, a run-of-the-mill CHART, so, in reality, the 'pivot chart' is not any different than any other chart, sans the pivot controls, which I never liked.

I'd be way more concerned about the data structure.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

I'm just getting acquainted with 2007 features.

Just discovered the 'PivotChart Filter Pane' that occured when I decided to chart a Pareto of my data.

Check it out!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]