Forum Discussion
i want to share some sheets from my workbook
Hello,
I want to share a workbook with some of our franchiseorganisations, but there are three sheets that lead with pivot tables (powered by power query). And if they click those pivot tables they can see the other franchiseorganisations their record sales etc. Is it possible to just share some sheets in the workbook with them, or make it impossible for them to open the sheets that i dont want to share?
With kinds regards
Bas Jonkers
If Power Query loads data to data model and PivotTable is built on data model, franchisee in any case could have access to all data, even if separated PivotTables will be built.
If PivotTable is created from Power Query that's only to create set of queries, one for each franchisee (perhaps by referencing and filtering master query) and create separate PivotTable for such franchisee. Yes, they could be placed in separate sheets, with hiding for this franchisee other sheets with password protection of workbook structure and other sheets. But that's lot of manual work and in any case you have different files for franchisees. That's not reliable solution. Perhaps approximately that's how you work now. It looks like you send to each franchisee the same PivotTable but filtered for this concrete franchisee. The only to add here is sheet protection not to allow filter PivotTable. Again, here is better to filter Power Query for concrete franchisee.
Even better to create franchisee files from scratch. But that depends on how many franchisees you have, e.g. 3 or 300. With very few such files could be created manually. With lot of them that doesn't work. In general, in such case entire process could be fully automated, from creating separate Excel files to email distribution of them. All by one button or by scheduled process. That's not easy task and depends on what is included in your 365 subscriptions.
In Excel, you have several options for sharing specific sheets from a workbook while keeping other sheets private. Here is how you can achieve this:
- Protect Sheets with a Password: You can protect specific sheets in your workbook with a password. This way, even if you share the workbook with others, they will not be able to access or modify the protected sheets without the password.
Here is how to protect a sheet:
- Right-click on the sheet tab you want to protect.
- Select "Protect Sheet."
- Set a password and choose the options you want to restrict (e.g., preventing users from selecting locked cells or making changes).
- Click "OK."
Be sure to keep the password secure, as you will need it to make changes to the protected sheet.
- Share Specific Sheets: If you want to share specific sheets with certain users, you can do so by saving those sheets as separate workbooks and then sharing those files. This way, you can provide access only to the sheets you want to share without exposing the others.
Here is how to save specific sheets as separate workbooks:
- Right-click on the sheet tab you want to save as a separate workbook.
- Select "Move or Copy."
- In the "Move or Copy" dialog box, choose "(new book)" in the "To book" dropdown.
- Click "OK."
Save the new workbook with a different name and share it with the intended users.
- Use SharePoint or OneDrive for Business: If you have SharePoint or OneDrive for Business, you can upload your workbook to these platforms and manage sharing and permissions at a more granular level. You can share specific sheets or sections of a workbook with different users or groups.
SharePoint and OneDrive for Business offer more control over document sharing and access permissions, allowing you to specify who can view and edit specific parts of a document.
Remember that protecting sheets with a password or sharing separate workbooks provides a level of security, but users with sufficient Excel expertise may still be able to access and manipulate the data. For more advanced security and access control, consider using SharePoint or OneDrive for Business to manage document sharing and permissions.The text and steps were created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
If Power Query loads data to data model and PivotTable is built on data model, franchisee in any case could have access to all data, even if separated PivotTables will be built.
If PivotTable is created from Power Query that's only to create set of queries, one for each franchisee (perhaps by referencing and filtering master query) and create separate PivotTable for such franchisee. Yes, they could be placed in separate sheets, with hiding for this franchisee other sheets with password protection of workbook structure and other sheets. But that's lot of manual work and in any case you have different files for franchisees. That's not reliable solution. Perhaps approximately that's how you work now. It looks like you send to each franchisee the same PivotTable but filtered for this concrete franchisee. The only to add here is sheet protection not to allow filter PivotTable. Again, here is better to filter Power Query for concrete franchisee.
Even better to create franchisee files from scratch. But that depends on how many franchisees you have, e.g. 3 or 300. With very few such files could be created manually. With lot of them that doesn't work. In general, in such case entire process could be fully automated, from creating separate Excel files to email distribution of them. All by one button or by scheduled process. That's not easy task and depends on what is included in your 365 subscriptions.
