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.