Excel is a great tool to quickly store and calculate data and is probably more used than a real database system. Why? Because everyone has it, you don’t need to install and configure a database system, no real database design needed. You just start with a new document and start entering the data. And then you invite your colleagues to also use the file because of its great content. But then, you run into a problem. When someone has the Excel file open on his computer, no other person can open the same file at the same time. They get an error that the file is in use, like this screenshot below.
So, by default, Excel worksheets are intended to be single-user documents. And we can change that. In the older versions of Excel, there was an option to share a worksheet for multiple users. That has always been there, as an option or button in the Excel ribbon.
Since Excel 2016, this option was removed from the visible buttons by Microsoft. And we see the same happening with Office 365 and Office 2019.
To add this Share workbook option back to the ribbon, click File and click Options.
On the left menu, click the Customize Ribbon button.
Pick any group from the existing groups at the right. We prefer the Data group since this is most related to what we are trying to achieve. So select the group that you want to use and click the New Group button.
Give the new group a name, and optionally choose an icon. Click OK to save.
This new group is added to the existing Data group at the right. Click at the left, open the Choose commands from list box and set it to All Commands.
- From the long list of all commands at the left, navigate to the option named Share Workbook (Legacy). Then click the Add >> button from the middle. This will add that forgotten button to the group we had selected at the right.
Click a few times OK to save all the changes. Then, at the Excel ribbon, click to open the Data option. You will find the newly added command named Share Workbook (Legacy) totally at the right of the ribbon.
- Click that Share Workbook (Legacy) button and check the option Use the old shared workbook feature instead of the new co-authoring experience.
Click OK to close. Excel will ask you to save the file now.
As of now, you can open this document or worksheet by multiple users and the same time, read data and do updates on the data.
Very clear explanation. Thanks
Thank you very much for such easy explanation
Thanks for letting us know! Glad it helped.
After several confusing sites, finally a clear, detailed user friendly explanation! Thank you!!!!!!!!!!
Super to read Michelle! Glad we could help with our info.
Does this effect all workbooks going forward or just the workbook currently being utilized. I may need to use this feature only with some users.
Patricia, the option that you set to show the sharing icon, that will remain active in your Excel application. The fact that the file is shared, that is per file.
Very clear explanation
Dank je wel Hedwig!
Do each of the users need to have the share option enabled following these instructions or are they able to collaborate once they have received the invite?
Also, if multiple team members are in the file at the same time editing, will they be able to track the others edits? For example: Two team members have the file open at the same time and are attempting to add a new project to the bottom of the file (Which would be the same cells) does one persons edit over ride the others?
No because that sharing option is written to the file and available then for everyone that opens.
There are some more option to track and influence the merging of changes. Look at the Advanced tab for this.
Please elaborate on options to track and influence merging of changes for multiple users making changes at the same time.
RD, there is an advanced tab where you have some more options for this.
Excellent explanation very clear, first I try to find something in Spanish but this is very good even for people like me don´t speak english.
Thanks for your time and share this
Eres muy bienvenido Alf!
Wont work for me as when I put this file in one drive to share (i also tried a shared drive) … people could only open it read-only 🙁
It would instruct them to download and make a copy. But then how does that copy update the original ?
Avi, onedrive is of course another story since that is sharing too. This only works on a local network!
I got as far trying to make my worksheet a Shared one. However, when I try, I get a dialog box saying “Shared workbooks do not have access to many features, including threaded comments. To convert this workbook to a shared workbook, you must first delete all the threaded comments in your file.” I have to admit I don’t even know what a “threaded comment” is. Any way to get around this?
If the content of your Excel file, got too complex, then I would start with a new file and copy over the information that you really need from the old file. Good chance that you are not copying that other information.
And start by sharing the file from the beginning. When you have a complex file, you could also copy over your data in blocks, and test if you still can save the file.
Very helpful! Easy to understand – thank you!
Great! Thanks for the feedback.
Why they made all this complicated?! Thanks god to you who wrote this explanation. Well done
Thanks for this positive feedback!
Very nice & fruitfull. Thank you.
Very clear instructions and useful information. Thank you very much.
Thanks Flor for your feedback. Appreciated.
Thank you so much. Its really very helpful
Very useful. thank you
I’ve been asking my IT manager for months to give shared access to an integral file used by multiple departments. I’ve managed to achieve what I needed following this method!
There are some important things to note, such as the need to remove comments and tables in order to use the shared workbook.
Thank you so much. It’s very easy understand and very helpful.