Cannot change one pivot table without affecting the others

[USAMax]

My data is not that large, about 1000 rows but I want to look at it in different ways.

One pivot tables shows the average auction price for each hour.

One pivot tables shows the average auction price for each month.

I have several more but you get the idea. The problem is that when I group one by month the other one changes and when I change the other back it affects the other. I want to have several tables on the same sheet. I tried to click the box, "Defer Layout Update" but that did not help.

Can someone help me?

 

[Marcelo Branco]

See this

Unshare a data cache between PivotTable reports - Excel - Office.com

This is what I think you need to do:
. Unshare the data cache between PivotTable reports that are based on a cell range by temporarily redefining the data range to force Excel to unshare the data cache.

Hope this helps

M.

 

[USAMax]

The data is within the same workbook so I don't think there is a way to unshare it except to make the data static, but I want to keep adding data to it and have it update the table.

 

[Marcelo Branco]

The data is within the same workbook so I don't think there is a way to unshare it except to make the data static, but I want to keep adding data to it and have it update the table.

With data within the same workbook I tried
..temporarily redefining the data range to force Excel to unshare the data cache.
and it worked for me

M.

 

[Marcelo Branco]

Maybe this can help
Excel Pivot Table Tutorial -- Pivot Cache

See the topic (NOT TESTED)
Create New Pivot Cache for Selected Pivot Table

M.

 

[USAMax]

Thank you Marcello but I think it is best if I just share the workbook.

https://www.cubby.com/pl/My+Cubby/_dbf9a6ec9a7e467b951e76e7a342fad0

On Sheet2 and Sheet4 there are three Pivot Tables. You will see that Grouping one of the Pivot Table's, "Auction Date" by hours or months changes all of the Pivot Tables. I also need a way to group these by the Day-Of-The-Week.

I cannot believe I am the only one with this problem.

 

[Marcelo Branco]

You can recreate the Pivot Tables following these steps
(see the link I provided in #2)


Create a PivotTable report that is based on the same cell range as another report without sharing the data cache
1.Ensure that there is an existing PivotTable report that is based on the same range that you want to use for the new PivotTable report.
2.Click any blank cell in the worksheet outside the PivotTable report.
3.To start the PivotTable and PivotChart Wizard, press ALT+D+P.

------------------------------------------------------------------------------------------
Tip
To add the PivotTable and PivotChart Wizard to the Quick Access Toolbar, do the following:
1.Click the arrow next to the toolbar, and then click More Commands.
2.Under Choose commands from, select All Commands.
3.In the list, select PivotTable and PivotChart Wizard, click Add, and then click OK.
--------------------------------------------------------------------------------

4.On the Step 1 page of the wizard, click Microsoft Office Excel list or database, and then click Next.
5.On the Step 2 page of the wizard, select the range of data on which you want to base the new PivotTable report, and then click Next.
6.When the PivotTable and PivotChart Wizard displays a message asking if you want to share the data cache, click No.
7.On the Step 3 page of the wizard, select a location for the new PivotTable report, and then click Finish.

Hope this helps

M.

 

[USAMax]

Thank you Marcelo, that did the trick and I have added the shortcut to the Ribbon.

Microsoft should offer a check box that says, "Do you want to share the data cache?"

A truly detailed and thorough answer, thank you!

 

[Marcelo Branco]

Thank you Marcelo, that did the trick and I have added the shortcut to the Ribbon.

Microsoft should offer a check box that says, "Do you want to share the data cache?"

A truly detailed and thorough answer, thank you!

Great that it worked!

You are welcome and thanks for the feedback.

M.

 

[LucyJ]

Thank you Marcelo! It worked wonderfully and simply!