How to make different worksheets in an Excel workbook viewable to some users and not others?

Question

I've been building out an operating model for a company I work with and we're at a point where we want to share this model with their broader team. We're aiming for a few things...

1) Some worksheets are relevant to some users, but not others. Moreover, some worksheets contain sensitive data that should only be viewable to certain users. So we really need to be able to control view and edit settings at the worksheet and user level.

2) We'd like multiple users to be able to work on the workbook at the same time.

What's the best way to accomplish this? Is SharePoint the optimal solution here, or is OneDrive or something else just as good or better? (In case it's relevant, I use Excel via Office 365.)

Answer 1

Have you considered converting your Excel worksheets in to Sharepoint lists instead?

You can still export data from Sharepoint to Excel and keep a link between Sharepoint-Excel so that when data is updated in Sharepoint, it can be pulled in to the Excel file. Once you have the data in Excel, you can refresh tables, charts etc.

You would need to create a new List. Then add custom columns to recreate your Excel columns in Sharepoint.

You can then use Sharepoint groups to manage security on your new Sharepoint Lists - you can control who can edit, who has read-only access and who has no access at all. You can make use of many other cool features like version history to keep track of 'who changed what'.