Multiple Columns in Pivot Table (but don't show in Pivot Chart)

[Gideon1973]

Hi All!

I'm creating a pivot table that feeds data into a pivot chart and it's all working just fine, but I'm having a problem formatting the pivot chart.

In my pivot table, I want to have multiple value columns displayed in the table. However I only want one of the value columns to be displayed in the related pivot chart (and not all of the value columns).

I tried looking for a way to change the data source for the chart and various properties of the value column, but don't see anything.

Is there a way I can do this? I can provide screenshots or an example file if that's helpful...

Thanks!

PS: question also posted at http://www.excelforum.com/excel-cha...pivot-table-but-dont-show-in-pivot-chart.html.

 

[Andrew Poulsom]

Welcome to MrExcel.

Create another pivot table and pivot chart based on your original pivot table and with only one column.

 

[Gideon1973]

Welcome to MrExcel.

Create another pivot table and pivot chart based on your original pivot table and with only one column.

Thanks so much for the idea!

I thought about that earlier (basically a way to "trick" the pivot table/chart to have the appearance I'm looking for.) However, and I guess I should of mentioned this earlier, I want to also have the benefit of being able to filter the pivot table below the pivot chart and then seeing the changes refresh in real-time. From a print/presentation-perspective, this would work, but then if someone wants to filter the data on the fly directly in the workbook, they'd have to go to the "other" or hidden pivot table rather than the one under the pivot chart...

I guess I'm wanting the best of both worlds -- show multiple columns in my table, but only one of them plotted on the chart and also be able to filter on the table at the same time.

I vaguely remember seeing that in Excel 2003, but can't find it in 2007...

It's not the end of the world as the audience will be using the printed version of the charts mostly, but I know they'll eventually ask (hey...why doesn't the chart refresh whenever I change the pivot table below it?!?!)

 

[Gideon1973]

[SOLVED]: Multiple Columns in Pivot Table (but don't show in Pivot Chart)

Welcome to MrExcel.

Create another pivot table and pivot chart based on your original pivot table and with only one column.

I think I figured it out...

So, after I add my second column to my pivot table, I select the series in the chart, and change it to plot on the secondary axis. Then on both the vertical and horizontal axis options for my series, I chose to not display them. (In my pivot chart, this new series showed up multiple times for each unique value, so I had to repeat this process multiple times for each value to "hide" the series in the chart).

At this point, I still had the series I wanted to hide showing up in the legend, so I deleted each of them.

It took a few steps to get it right and hopefully my chart format won't reset if I refresh data or do other minor tweaks to the pivots, but it looks good so far.

I may end up painting myself in a corner if I have to eventually have a "real" secondary axis, but I'll deal with that later, I guess...

Thanks!

 

[jdnkr1]

Re: [SOLVED]: Multiple Columns in Pivot Table (but don't show in Pivot Chart)

It doesn't look like this topic has been addressed recently on forums, but a workaround I found was to format the unwanted series in the pivot chart to 1) have 100% overlap, 2) no fill, and 3) no outline. That resulted in the appearance of removing the unwanted series while still maintaining the data link. I'm using excel 2013.