Microsoft Office versions
The information below refers to Microsoft Office 2016, 2019 and Office 365. Click here for Office 2010 and 2013.
Many people and companies use Microsoft Excel datasheets to store data. It often starts as a quick document to share information, but the data of the file is growing and more information is added. More sheets, more calculations, more important and crucial content for the company.
And then you want to share this document with your colleagues, because the data and its calculations are so great. And the best part is that you don’t need to contact your IT department to set up a complex database, that would only take away your baby and lesser your flexibility.
But you run into the problem that a worksheet can be opened by one user at the same time but you need to work with multiple users on this datasheet. Or maybe you use the data of this Excel document to show this data in real-time on an information screen. Our DataPoint add-on for PowerPoint is a tool that is often used for real-time information screens in companies, factories or on the work-floor. So, whether it are colleagues or it is DataPoint reading out the information, multiple users or connections are needed on this single user document.
In older versions of Excel, it was possible to share a workbook so that multiple users or processes could access the data. But since Excel 2016 Microsoft has stopped supporting this sharing option. The shared workbook feature is no longer available on the Review tab.
Microsoft has removed this great feature because they have (what they call) a better option for that. Microsoft wants to move us to co-authoring for this. This means that you have to upload your document to OneDrive or SharePoint Online. But some companies don’t want to make use of these services, or make their operations depending on the availability of an internet connection. Good news for such users and companies. You can bring back that shared workbook for Excel 2016.
There are 2 options to bring back the Share Workbook functionality back to the Excel ribbon or menu. One is that you add its button to a new group at the Review tab and the second one is that you add it to the Quick Access Toolbar.
To bring it back to the Review tab
In Excel click File, Options, Customize Ribbon. At the Choose commands from combobox, select All Commands. Then, from the list of commands, locate ans select the option Share Workbook (Legacy). At the right side, click the Review tab and click the New Group button. Then click the Rename button and name it e.g. Shared workbook.
Now click the Add button in the middle to bring that Share Workbook (Legacy) command to this newly created Shared Workbook group.
Click OK to close and look at the Review tab again. A new button is added to bring back the shared workbook functionality as before.
The second option goes via the Quick Access Toolbar or QAT. This is the area with some small icons at the top left of the Excel application.
To bring it to the Quick Access Toolbar
In Excel click File, Options, Quick Access Toolbar. Go to Choose commands from combobox, select All Commands. Then, from the list of commands, locate ans select the option Share Workbook (Legacy). Click Add button or add the command to the list of commands that are already available at the Quick Access Toolbar. Click OK to close and the new Quick Access Toolbar. This will now look like this.
You could even have the Share workbook command available in the Review tab or the Quick Access Toolbar.
How to use the Share Workbook (Legacy)
Click the Share Workbook (Legacy) button either from the Review tab or either from the Quick Access Toolbar. Go to form and check the option Use the old shared workbooks feature instead of the new co-authoring experience and click OK.
You can now open this same Excel document by multiple users from different locations. Note that DataPoint connections reading out the information for your information screens, is also a second connection to your Excel, even when you expect to be alone.
Yay!! After multiple You tubes and searching I came across this for how to share an excel 2016 workbook and allow multiple users at a time. Took me 3 minutes to set it up
Hi Linda, great to hear that this is working for you. Thanks for the feedback. You and me happy!
I tried this, and the link is visible on the toolbar but not enabled. Is there something else I need to do? My workbook has many sheets with pivot tables, slicers, charts… Could that be the issue? It is an xlsb on a network.
I have been struggling with getting this file accessible to multiple users without lockout issues. I was so excited to see this… I hope I can make it work!
Not sure about xlsb files Barb. I would suggest to start with an xlsx file first and see if that brings you further. OK?
Perfect! Thanks for the information. My only issue was that it wouldn’t let me rename it. But that’s not a big deal
Thanks Steve. Probably because it is in use somewhere that you can’t rename it. Maybe in order to rename, you have to make it single user again. Then share it again.
this small thing made me hero in my team 😉 thank you
So glad to read this!!!
this is a password protected document but when others without password use the document those of us with password authority can not get in. With the instructions above assist with this issue also?
Sorry Linda, but no experience with this situation. Anybody else?
How can you make it single user again? The “Allow changes by more than one user at the same time” is no longer visible to uncheck.
I need to make changes that can’t be made while it is shared.
Are you on the same computer? It ahould be visible too, to uncheck it again.
Help! There are four of us that have been sharing an Excel 2016 file with much difficulty. We each need to be able to sort/filter for different information on the same worksheet where we do not overlap each other. For example if Column A included east, west, south, north we each would filter for our direction to add information to the empty rows. This report ‘breaks’ often. Is there a better option(s) for 4 or more people to work in the same data set at the same time like this without ‘breaking’ the file? Thanks!
Make sure that you are using the same version of Office. Also, make sure that all 4 of you are using the latest service packs. Are you?
I normally never leave a comment but I have to say THANK YOU SO MUCH!!! I searched everywhere and even watched a couple of youtube videos but this was the only thing that worked! I’m so happy and relieved.
Great news! I’m so glad to receive your (rare) compliment! 🙂
Have a nice day!
My boss recently upgraded my computer to Microsoft Office Professional Plus 2016. My problem is that when I open a document, other people can open the same document and make changes. The file isn’t “locked for editing”. When I’m finished, I try to save and it asks if I want to save over the other copy. I don’t want to do that. Do you know how to change this?
Do I understand you correctly that when you start, your Excel document is not shared at all?
hello, thank you for this mega tip, it will help me so much, by the way, i have an excel file that will be consult for multiple users on read-only mode, but this excel file is practically a table, and when i try to make it shared, it appears the message “it coudn’t be shared since it has tables or xml map … ” , do you know a way to preserve the tables (they have the benefit of be auto-sized, and i need that feature) and share the document? thanks in advanced
Joel, I’ve seen that before that files with tables cannot be shared. Can you set it up this way: first of all you have your base file with raw data. Make that shared. Then start a new file with those cells linked to the first (raw) file and do your table formatting in there. Not sure if that is an option at all for your scenario, but at least you have a shared file to work with. (but I guess you need that table functionality everywhere…)
I tried it an still received file is locked for editing.
Do you clearly see the words “(shared)” next to your filename in the top window of the excel application?
Your post is very helpful and information is reliable. I am satisfied with your post. Thank you so much for sharing this wonderful post.
I finally found someone that knew how to do it. Thank you!!!
OK great!
hi, other users cannot see Excel file in share folder
How do you mean? You both go to the same network share, and one sees the Excel file and the other does not see the file? Sure? That sounds weird. What if the one that does not see, creates a new file in there. Does the other see it?
Your post is very helpful for me now i can do this very easily.
Very Nice Article…..thank you for sharing
may i know if the excel shall be saved in “cloud” so that it could be online edited? or i can only save it to common drive (our own server)?
i cannot share the excel file as it insist me to save it to cloud.
Angela, I would expect it to be saved locally or on a network drive. Why would you not be able to save locally? Your Excel might be pointing you by default to the cloud, but normally you should also be able to save locally! Unless restricted by your admin.
Hi, this has been a great help, but when I try to make it a shared document, it says i have to delete all threaded comments? Any suggestion on how to keep these? Cheers
Sorry but no known workaround for this. I think you really have to choose between both.
Thanks so much for this. I had spent awhile searching and they all suggested just putting this on OneDrive, which was not the answer I was looking for.
I appreciate this!
Glad that we could help! Thanks for the feedback.
So I shared my workbook and it appears to allow 2 users to open it. To test how it would handle editing the same cell, we tried it. The one who saved first went in and the other one was asked if they wanted to over-write.
1. Is there a way to see who is already editing the document when you are opening it?
2. Is there a way to see where the other people are changing the document real-time like in GoogleDocs??? This way one person is not editing a field someone else is editing.
I have added the new group and the Share Workbook (Legacy) but now how do I add others to this workbook?
1. No guess not that you can see who.
2. You can open the Advanced tab there, and you can choose to see changes by others automatically, but you can’t go below 5 minutes. So that probably not an option for you. I guess you have to look more for a database-type solution for this functionality.
You don’t add people by name or email. You just put this shared file on a file server or network share, and others use their file explorer to browse to that file and open it.
Hi,
I see the icon in the review tab (as in your picture) but it’s grey and I cannot use it. Have you encountered this before and do you know if the issue can be fixed?
HI , i followed the steps but still if one user opens this file , other is not able to open as editable version. Message appearing like “this file is locked for editing by user A”. What to do in that case?
Weird. Sounds like the sharing is not working, or not for everyone. Can anybody come in as second user? Maybe try with a new clean spreadsheet first. And maybe someone is using an (incompatible?) older version of Excel?
No experience with that. Will it work on a new & empty & clean file too? Send in your file and screenshots privately via https://www.presentationpoint.com/support/ and I can try to look into this for you.
Great stuff, you saved my day, Thank you =).
Glad to read 🙂
There is no share workbook (legacy) option on the options tab. We have excel 2016 and a coworker is constantly jumping in while I’m in the data base for a longer time. And if I do save first, she won’t update it as it’s not in her job description.
Thanks for any hints or tips.
Have you chosen ‘All commands’ at the top?
dears
May I have your whats app number to communicate as I am facing some problem with sharing under excel 2016
THANK YOU! This was great information its accurate and my Excel docs are being shared with ease.
Without internet connection mupltiple people work in same exel workbook
Sure, no internet needed. Just a network is fine so that they all can open from the same source/share.
I am able to share/unshare the document- but only when I use the computer I created it on. I work across multiple buildings and when I go to unshare briefly or access full editing options it is greyed out.
In addition to this, my admin team need to be able to have this access incase I’m away
Summary: need to be able to have full editing access on more than one computer and need to assign specific people also in case I’m away
Hi Claire, the sharing on file level and the permissions levels are 2 different things. Here we are only talking about the file level shared access.
I’m on Excel 2016 16.0.5071.1000
Looks like Microsoft has quietly took the unhide feature away as far as I can tell.
No, no, it is still there. Make sure to select ‘all commands’ at the top. Then search alphabetically.
Ahh.. never mind! I got it going. They just removed the legacy part!
OK great!
Great, thanks a lot! After the above settings were updated how do I share the file with someone else who is at a different location? Or it can only be shared within a home/work network?
Yes correct. You have to be on the same network for this. When you are separated, this does not work.
Is it possible to use this option while having the document protected?
I want to have a protected shared workbook, but don’t know if it is possible.
Hi Jeppe, I have not tried that yet. I would suggest to unprotect a copy of your shared excel, and see if that is working.
Hello. Thanks for all this information. It is very helpful. I did have a question. Once I shared the excel file, I tried to copy a sheet in the excel file, but it keeps saying “command not available in a shared workbook”.
Then I suggest to unshare the document, make your changes, and share it again. You probably need exclusive rights at this time for this ‘special’ operation.
How would I go about getting exclusive rights?
You get exclusive rights when you are the only user that has the file open.
Ok. It didn’t seem to work. I guess the only thing I will be able to do just unshare, copy the sheet, then share it again. Thank you for the help.
OK good luck. That will probably work.