Bring Only Filtered Data to Pivot Table

marylynn

Board Regular
Joined
Oct 2, 2008
Messages
62
How do you retain the data that is filtered in the main worksheet (we filter on 5 or more columns) when creating a Pivot Table?

Are we having trouble because we are using a Shared Workbook?

Doesn't the administrator (first person in) have rights to make all changes?

If Shared Workbook restricts this, how can we get around this limitation?

;)
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Hello marylynn and Welcome to the Board!

I'm not sure I entirely understand what you're trying to do, but here goes...

Let's say your dataset cover Cells A1:E100. If your Pivot Table was built using these Cells as its source data, it doesn't matter whether you then filter on this source data - your Pivot Table will still pull in all of the information held there.

Is there a reason you're not you're filtering on your source data and not within the Pivot Table itself?

Matty
 
Upvote 0
Thanks for the welcome!

Our main spreadsheet contains 52+ columns where we filter out data from columns that we do not need in the pivot table. We want to keep the pivot table a simple summary, uncluttered if possible.

I saw on another thread that Pivot Tables may not refresh with Shared Workbooks. Is bringing filtered data over to pivot tables another limitation of Shared Workbooks?

Thank you.
 
Upvote 0
Hello marylynn,

I can't say I have ever used a Pivot Table within a shared Workbook, but I suspect problems will arise if you attempt to refresh the data when another individual is in that Workbook, potentially updating data that the PT is (trying) to use.

Another trick I have recently learned is to use MS Query (which is built into Excel) to filter a large dataset so that your PT only displays the data you want to see.

Have a look here:

http://www.exceluser.com/explore/msquery1_1.htm

Hopt this helps.

Matty
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top