1

Is it possible to unlink a pivot chart axis selection from the pivot table rows? Whenever I remove one of the pivot chart axis fields to display the chart correctly, excel also removes the field from the pivot table rows selection which is not what I need.

The images below may help show what I mean - if I remove field "Division" from the pivot chart axis selection, the field is also removed from the pivot table rows selection. I have a sample spreadsheet which can do this - link is here.

row sln

enter image description here

Thanks for any suggestions.

enter image description here enter image description here

Zeus
  • 1,496
  • 2
  • 24
  • 53

1 Answers1

1

A pivot chart is invariably linked to the underlying pivot table. Removing fields from the Rows field well will remove them in both the chart and the table. That's just how it works.

Edit after viewing sample file:

The sample file has just one pivot cache, but it has several pivot tables. The two pivot tables for the two pivot charts on the dashboard live on the "Pivots" worksheet and are not connected to the pivot table on the dashboard.

So, if you want to create a pivot chart and a pivot table with different fields, you need a separate pivot table for the chart. You may want to create the pivot chart first, on a helper sheet, then move the chart to the dashboard and leave the underlying pivot table on the helper sheet.

Then add another pivot table to the dashboard sheet and use columns that are independent from the pivot chart.

teylyn
  • 34,374
  • 4
  • 53
  • 73
  • I think there is a setting or work around. I have a sample spreadsheet which can do what I need, but I can't figure out how its done. – Zeus Jul 26 '16 at 02:31
  • can you post that sample file for inspection somewhere? Think file sharing service like Dropbox or OneDrive, etc. – teylyn Jul 26 '16 at 02:34
  • take a look at the screen shots in the question, if that doesn't help I'll upload to a file sharing app – Zeus Jul 26 '16 at 02:37
  • The screenshot just shows the field well with Division circled or not. That does not help. If you have a file that does what you want, share that for inspection. – teylyn Jul 26 '16 at 02:53
  • I agree, I'll see if I can remove confidential information and then add a link – Zeus Jul 26 '16 at 03:47
  • 1
    I'm thinking there must be two separate pivot caches to achieve that. – teylyn Jul 27 '16 at 00:06
  • I've added a link to the spreadsheet in the question. Please take a look - Teyln I think you're right – Zeus Jul 27 '16 at 01:12