remove access to Power Pivot

%3CLINGO-SUB%20id%3D%22lingo-sub-1887010%22%20slang%3D%22en-US%22%3Eremove%20access%20to%20Power%20Pivot%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1887010%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20report%20and%20I%20don't%20want%20people%20to%20be%20able%20to%20open%20the%20data%20tables%20in%20the%20Power%20Pivot%20-%26gt%3B%20Manage.%20I%20would%20still%20like%20to%20let%20people%20drill%20down%20into%20the%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20if%20I%20password%20protect%20the%20workbook%20it%20limits%20the%20access%20but%20then%20people%20cannot%20drill%20down%20into%20the%20data.%20Do%20you%20know%20a%20way%20around%20this%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1887010%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1888083%22%20slang%3D%22de-DE%22%3ESubject%3A%20remove%20access%20to%20Power%20Pivot%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1888083%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F827698%22%20target%3D%22_blank%22%3E%40Itchn2go%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%22%3E%3CSPAN%3EIf%20I%20understand%20correctly%2C%20from%20the%20translation%2C%20you%20would%20like%20...%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%22%3E%3CSPAN%3EThe%20users%20use%20the%20filters%20without%20being%20able%20to%20edit%20the%20cells%20in%20the%20protected%20area.%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%22%3E%3CSPAN%3ERight%20from%20the%20start%2C%20I'm%20not%20familiar%20with%20Office%20365%2C%20but%20I%20can%20send%20you%20this%20information%20that%20%22maybe%22%20might%20help%20you.%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%22%3E%3CSPAN%3EOur%20starting%20point%20is%20a%20pivot%20table%20(or%20a%20table%20format%20area)%20with%20connected%20data%20slicers.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%22%3E%3CSPAN%3EIf%20the%20worksheet%20is%20protected%2C%20we%20can%20no%20longer%20click%20anything.%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%22%3E%3CSPAN%3ETo%20get%20around%20this%20problem%20we%20have%20to%20do%20the%20following%3A%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%22%3E%3CSPAN%3E1.%20Edit%20the%20data%20slicers%20(of%20course%20removed%20with%20the%20sheet%20protection)%20with%20a%20right%20click%20of%20the%20mouse%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%22%3E%3CSPAN%3E2.%20Select%20size%20and%20properties%20(in%20the%20example%20this%20must%20be%20done%20for%20both%20data%20slicers!).%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%22%3E%3CSPAN%3E3.%20In%20the%20properties%20menu%2C%20uncheck%20Locked%20and%20select%20the%20option%20Independent%20of%20cell%20position%20and%20size.%204th.%20Then%20the%20options%20Use%20AutoFilter%20and%20Use%20PivotTable%20and%20PivotChart%20must%20be%20selected%20in%20the%20sheet%20protection%20menu.%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%22%3E%3CSPAN%3EWith%20these%20options%20set%20you%20can%20also%20use%20the%20data%20slicers%20with%20the%20sheet%20protection%20activated!%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%22%3E%3CSPAN%3EAll%20of%20this%20should%20also%20work%20with%20Office%20365%20with%20subscription.%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%22%3E%3CSPAN%3EIf%20this%20is%20not%20what%20you%20are%20looking%20for%20information%2C%20please%20just%20ignore%20my%20message.%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%22%3E%3CSPAN%3EBut%20if%20that's%20what%20you're%20looking%20for%20then%20...%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1888130%22%20slang%3D%22en-US%22%3EBetreff%3A%20remove%20access%20to%20Power%20Pivot%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1888130%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ppvt%20menu.jpg%22%20style%3D%22width%3A%201146px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F233629iB87F217790CB045D%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22ppvt%20menu.jpg%22%20alt%3D%22ppvt%20menu.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40Nikolino%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20response.%20No%2C%20i%20need%20to%20protect%20the%20data%20in%20a%20shared%20Pivot%20Table%20Report%3C%2FP%3E%3CP%3EI%20need%20to%20disable%20the%20Power%20PIvot%20Manage%20function%20-%20shown%20in%20the%20attachment%20while%20still%20letting%20people%20drill%20down%20into%20the%20filter%20data.%20all%20slicers%20and%20sheet%20protections%20are%20in%20place.%20If%20I%20protect%20the%20structure%20of%20the%20workbook%20I%20accomplish%20the%20goal.%20BUT%20I%20also%20cannot%20drill%20down%20which%20is%20what%20i%20want.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAbility%20to%20drill%20down%20but%20lock%20out%20the%20ability%20to%20manage%20the%20power%20pivot%20data.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I have a report and I don't want people to be able to open the data tables in the Power Pivot -> Manage. I would still like to let people drill down into the data.

 

I know if I password protect the workbook it limits the access but then people cannot drill down into the data. Do you know a way around this? 

3 Replies

@Itchn2go 

If I understand correctly, from the translation, you would like ...

The users use the filters without being able to edit the cells in the protected area.

 

Right from the start, I'm not familiar with Office 365, but I can send you this information that "maybe" might help you.

 

Our starting point is a pivot table (or a table format area) with connected data slicers.

If the worksheet is protected, we can no longer click anything.

To get around this problem we have to do the following:

1. Edit the data slicers (of course removed with the sheet protection) with a right click of the mouse

2. Select size and properties (in the example this must be done for both data slicers!).

3. In the properties menu, uncheck Locked and select the option Independent of cell position and size. 4. Then the options Use AutoFilter and Use PivotTable and PivotChart must be selected in the sheet protection menu.

 

With these options set you can also use the data slicers with the sheet protection activated!

All of this should also work with Office 365 with subscription.

 

If this is not what you are looking for information, please just ignore my message.

 

But if that's what you're looking for then ... Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

 

Thank you for your understanding and patience

 

 

Nikolino

I know I don't know anything (Socrates)

 

ppvt menu.jpg

@Nikolino 

Thanks for the response. No, i need to protect the data in a shared Pivot Table Report

I need to disable the Power PIvot Manage function - shown in the attachment while still letting people drill down into the filter data. all slicers and sheet protections are in place. If I protect the structure of the workbook I accomplish the goal. BUT I also cannot drill down which is what i want. 

 

Ability to drill down but lock out the ability to manage the power pivot data. 

 

 

 

Maybe this information will help you
Unshare a data cache between PivotTable reports
https://support.microsoft.com/en-us/office/unshare-a-data-cache-between-pivottable-reports-87188806-...

About the shared workbook feature
https://support.microsoft.com/en-us/office/about-the-shared-workbook-feature-49b833c0-873b-48d8-8bf2...

Thank you for your understanding and patience

Nikolino
I know I don't know anything (Socrates)