Excel on sharepoint permission to use pivot table but not change data

Dear @Quinn Cushing,

Thank you for reaching out to the Microsoft Q&A Forum.

I understand that you're looking for a way to share an Excel PivotTable report without letting users modify the source data. This is a crucial requirement for data governance, ensuring users can derive insight without compromising the original information.

Achieving this requires a two-step approach: first, locking the data inside the Excel file, and second, ensuring the user has the correct SharePoint access to interact with the file in the browser.

Here is the step-by-step method using the desktop version of Excel, which will carry over correctly to SharePoint.

Step 1: Prepare and Protect the Workbook (In Excel Desktop)

You need to apply protection at the sheet level to prevent changes to the raw data, while specifically allowing interactions with the PivotTable.

1. Separate the Data

For maximum security, it's best practice to keep your Source Data on one sheet and the PivotTable on a separate sheet.
Sheet 1: RawData (This sheet must be fully protected).

Sheet 2: PivotReport (This sheet will be partially protected, allowing PivotTable operations).

1. Protect the RawData Sheet

On the sheet that contains the underlying data (e.g., RawData):

1. Navigate to the RawData sheet.
2. Go to Review → Protect Sheet.
3. Enter a password (highly recommended).
4. Ensure that "Select locked cells" and "Select unlocked cells" are the only options checked. This prevents the user from clicking into any data cells.
5. Click OK. This sheet is now locked.

1. Protect the PivotReport Sheet

On the sheet that holds the PivotTable (e.g., PivotReport):

1. Navigate to the PivotReport sheet.
2. Go to Review → Protect Sheet.
3. Enter the same (or a different) password.
4. In the "Allow users of this worksheet to" list, you must check the following boxes:
   • Select locked cells (Often required).
   • Select unlocked cells (Often required).
   • Use PivotTable and PivotChart (This is the most critical setting).
   • Click OK.

By checking "Use PivotTable and PivotChart," you allow the user to filter, change layouts, expand/collapse fields, and interact with slicers/timelines, while the main content of the sheet remains locked from editing.

Step 2: Set SharePoint Permissions

Once the Excel file is correctly protected, you need to ensure the user has the right permission level in SharePoint to use the interactive features.

You must give the user at least Contribute or Edit permission on the file or the document library where the file resides.

Because you applied internal Excel Workbook Protection (Step 1), even with "Edit" permissions on SharePoint, the user will not be able to bypass the password and change the source data or modify the protected sheet layouts.

I hope this information helps clarify the situation and provides you with workable solutions. Should you have any further questions or need additional assistance, please don't hesitate to reach out. We're always here to help. Have a wonderful day! 

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".  

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.