Excel Dashboard / Pivot assistance

Hi,

I have a single Access Web App & need to create a Dashboard in Excel 2013 by using different tabs to display my info.

External data link is right & have created Pivot Table & chart.

My questions is how can I keep each tab independent? When I make a change (for example remove a field from a chart) that change replicates across the entire file.

How can I keep everything separate on the one excel 2013 file?

Thanks in advane

 

Question Info


Last updated February 26, 2018 Views 112 Applies to:
Answer

Hello,

if you create several pivot tables or pivot charts from the same source data, Excel will by default use only one Pivot Cache to store settings and some filters and groupings. 

In many cases, different pivot charts based on the same pivot cache will work fine and you can apply individual settings. 

In some cases you may need to force Excel to create a different pivot cache, for example if you want to do different groupings of data. To achieve this, you need to invoke the "Pivot Table and Pivot Chart Wizard", which used to be the starting point to create Pivot Tables in earlier versions of Excel. It still exists, but it is not in the 2013 ribbon. 

You can either customize your ribbon or your QAT and add the command, or you start it with the keyboard shortcut Alt-D-P

Create the first pivot table normally. Then use the wizard to create pivot tables that use their own pivot cache. In the first step of the wizard, select the data source and make sure that the option "Another PivotTable report or PivotChart report" is not selected. 

If you select the same data source that you already use in another pivot table in the same workbook, you may see a warning message that recommends to use the existing pivot table as a data source to save system resources. That is NOT what you want to do, though, since that would (again) use the same settings for the new pivot table. 

So make sure that you select the option that makes the two reports separate (most likely the "No" button. I have never run this with an Access data source).

Let me know if that helps.

___________________
cheers, teylyn
 
Community Moderator
www.teylyn.com

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.