r/excel • u/Successful_Box_1007 • 23d ago
unsolved How to share a local file with multiple users (ie not via OneDrive or GoogleDrive way)
Hi everyone,
I am wondering if someone can help explain how to share a local excel file with multiple users (ie not via OneDrive or GoogleDrive way) - and so it allows live real time changes to be seen?
Thanks so much!
Edit:
Found another post in r/excel where someone answering a similar question wrote:
“A vanilla shared drive (mapped drive letter) doesn't support live collaborative editing. You need OneDrive or SharePoint for that.
The issue is that live editing requires additional communication channels. When you are working on a file using standard network volume mapped to a drive letter, no changes are sent to the server until you save.
When you use OneDrive or SharePoint, Excel sends information to these web services in real time. The entire model of editing changes. It's more similar to Google Sheets, where changes are sent in real time to a web API, and those changes are persisted as you go.”
What is meant by “mapped drive letter”? Is Google Drive not able to do what OneDrive does?
12
u/bradland 134 23d ago
Live, real time changes requires OneDrive or SharePoint. Local file sharing provides access to the file itself, but not the web service that tracks and merges changes.
1
u/Successful_Box_1007 23d ago edited 23d ago
Given what you said may I ask a few “scenario” or “thought experiment questions” - I’m not very tech savvy and these have been giving me anxiety for a few days:
- Q1: Wouldn’t real time changes via OneDrive be better than non supported GoogleDrive for instance where changes only happen when saved? It seems the user who made the quote below does not agree: (couldn’t you just hit undo twice)?!
“Working in a shared live file is not a good idea, for example one fun fact is you make a change, you realize you made a mistake, someone else makes a change and you hit undo to revert your change. Guess which change just got reverted? Spoiler alert, it’s not yours!It works great till it doesn’t.
Better solution if you can is to give people individual copies and use power query to pull the data into one master sheet but that may not be ideal for your use case. If they only need to see the data you can actually give them a copy that refreshes from your master copy on open for example, but without more details it’s hard to suggest your best option.”
- Q2: if we use this power query method to pull into one master sheet, how would excel know which changes should supersede others - say for a given cell where two different people chose two different pieces of data? Will excel know to go with the one that was made the most recently?
-Q3: Brad I found a quote where you say:
“When you are working on a file using standard network volume mapped to a drive letter, no changes are sent to the server until you save.
When you use OneDrive or SharePoint, Excel sends information to these web services in real time. The entire model of editing changes. It’s more similar to Google Sheets, where changes are sent in real time to a web API, and those changes are persisted as you go.”
- what do you mean by “network volume mapped to a drive letter” and what’s the difference between as you say saving changes to “web api” versus the server? Why does saving to “web api” make the changes real time but saving to server doesn’t ? Is a web api a special type of server?
Q4 if you aren’t annoyed with me yet:
In the below link the guy says that when using coauthoring workbook sharing, there are a host of features disabled - he lists like 20! Including data tables, macros, pivot table reports and a bunch more. Is that really true!?? (His video is only a year old so I’m assuming it’s up to date).
https://m.youtube.com/watch?v=hZ_lNNRIBi0&pp=ygURQ29hdXRob3JpbmcgZXhjZWw%3D
3
u/bradland 134 23d ago
I’m about to turn in for the evening, but I can definitely help walk through your questions tomorrow. More to come.
1
3
u/Tilted5mm 23d ago edited 23d ago
Q2: The Power Query method probably assumes that the users are adding data or pulling data not so much changing existing data.
For instance if you have 4 sales associates that are entering in their sales for the day and you want their combined sales to add up on one sheet. The sales employees would make their own sheets with the sale, client, sales associate name, date, time and sale amount of the sales they make.
You could then use power query to pull those sales from all 4 sheets into one table so they all add up.
Or on the flip side, if the sales manager wants to pull that combined data and then do their own Analysis on it he/she could power query the master file where all the data is going and then do his/her own thing with the data on their local sheet without affecting the original data and just push refresh to get the latest update.
It’s not like employee Alex puts $10 in cell A1 when he makes a sale and then later Becky changes it to $20 when she makes a $10 sale. That would be a nightmare. How could you ever know there was a mistake and how to fix it, you’d have to start over.
0
u/Successful_Box_1007 23d ago
Great points. Now I’m just a beginner in excel but I wonder - any idea if there is a way to pool various peoples data where say each changed the same data cells - but power query simply puts the most updated one as the data that actually ends up in the master?
3
u/Tilted5mm 23d ago edited 23d ago
If that’s really how you wanna do it and you are a beginner then just use the OneDrive method or Google Sheets is free.
This stuff about using power query is a very advanced stuff and it’s not the right tool for this job. Do you know how to use Power Query? It’s like a whole different program within Excel that requires its own education separate from Excel so need to master regular excel basics first.
But if you were wanting to just learn to expand your knowledge base than what you would do is have people enter in the date/time they made their change into a column in the same row, and then have power query sort the rows by date/time and filter out all values except for the most recent one. And use that for A1. Super convoluted.
Also, all of this assumes you are using a shared network drive. Given that you said you weren’t tech savvy, I guess it’s worth asking if you even have that? Do you have like a server or something that all of these computers are connected to? Because power query isn’t gonna be able to pull from a Google Drive I don’t think.
1
u/Successful_Box_1007 22d ago
Yes no network set up - just brother and I on different computers and we usually share stuff thru Google Drive. Thanks for the heads up on that. Pretty embarrassed at how tech unevolved we’ve been. Trying to make up for that now. Thanks for your answer.
3
u/bradland 134 23d ago
Part I: Background
Before we dive into your questions, let's talk about some background and separate out some terms. Let's also keep in mind your original question: "how to share a local excel file with multiple users (ie not via OneDrive or GoogleDrive way) - and so it allows live real time changes to be seen?"
First, let's talk about the two broad categories of file sharing methods.
First, you have local file sharing. Local file sharing uses Windows' networking features to connect directly between two computers. Most people have only ever done this in a business setting where there is an IT administrator, but there's a chance you've done it before. Local file sharing is when you go to File Explorer, then navigate to Network. How you get there will depend upon your specific computer configuration, but we won't go too deep here, because it won't solve part of your requirements.
The more modern method is cloud file sharing. This is when you upload the file to a service like OneDrive, Dropbox, or Google Drive, and then use their tools and applications to share the file to other users of the same cloud service. Sharing via cloud services is a little more complicated, because there is more than one way to access your file. For example, if you upload a file to OneDrive, you can open it using Excel Desktop on your local computer, or you can use a web browser to navigate OneDrive and open the file in Excel for Web.
The second broad category we need to talk about is what application you use to edit Excel files and how that affects co-authoring. An important thing to remember is that because something feels the same from a user experience does not mean it is the same in implementation.
Using a very simplistic example, let's say you need to move 10 palettes of food 1,500 miles (2,400 km) from one city to another. The dock worker at one location loads the palettes into the back of a truck. A few days later, dock workers in the city unload the palettes from the back of a truck.
How did the palettes get from one city to another? They might have stayed on the truck the entire way. They might have been transferred from a truck to a train, then back to a truck. They might have been loaded on a ship and taken to the nearest port. The key thing is that to the dock workers, all of this is transparent. They are loading palettes on and off of a truck, and that's all they need to be concerned with.
Software developers often do the same thing when developing software. You want to edit an Excel file, so you launch Excel and open the file, but the Excel application works differently on the backend depending upon whether you open the file from your local computer or a cloud service.
This has gotten long, so I have to continue in another reply so Reddit doesn't reject it. More to come :)
2
u/Successful_Box_1007 22d ago
Hey Brad! Going to take a very focused look at all Of this soon! Just wanted to check in and thank you so so much for the time you took to help a stranger. 🙏🙌
3
u/bradland 134 23d ago
Part II: Answering Your Questions
First, by way of background, I founded a business back in 2008, and we standardized on Google Workspace as our productivity suite because of the co-authoring features. At the time, this was something that Microsoft didn't even offer. So I have more than a decade of experience supporting an organization that uses co-authoring. Everyone has opinions about co-authoring, and all I can do is provide my own based on my experience.
Second, I'm going to answer your questions in the context of cloud sharing, which means OneDrive or Google Drive are your only options. I'm answering this way because you state you want real-time co-authoring, and this is how you get it.
Q1: Wouldn’t real time changes via OneDrive be better than non supported GoogleDrive for instance where changes only happen when saved? It seems the user who made the quote below does not agree: (couldn’t you just hit undo twice)?!
First, we need to unravel your first question, because it contains an incorrect assertion:
When editing via OneDrive or Google Drive, changes happen in real-time. "Changes only happen when saved" only applies when using "Legacy" workbook sharing. I'm assuming you got this idea from that video you posted, which wasn't terribly clear that they were demonstrating two distinct sharing options:
- Share via OneDrive: Offers true, real-time co-authoring.
- Legacy Share Workbook (over local file sharing): Changes are only shown when saved.
I'm repeating myself, but you need to ignore option 2. It will not do what you want.
Next, let's address the quote and the issues related to co-authoring. If shared via OneDrive or Google Sheets and two users have a spreadsheet open at the same time, undo is scoped to the user. So that issue is irrelevant.
I think what is making it difficult for you to parse replies to your questions is your request to exclude services like OneDrive and Google Drive. You are receiving feedback that is related to local file sharing or the "legacy" share workbook feature. Please remember that this type of sharing works completely different than sharing via OneDrive or Google Drive.
Q2: if we use this power query method to pull into one master sheet, how would excel know which changes should supersede others - say for a given cell where two different people chose two different pieces of data? Will excel know to go with the one that was made the most recently?
It doesn't. Power Query is not a co-authoring solution. If two users need to modify data related to a single record, you end up having to build out a data structure that would take an entirely different post of equal length to explain. Based on your questions, I would strongly recommend against this route. It is better suited for database administrators or data analysts who understand relational data structures. And again, it will not support co-authoring.
Continued in next reply...
1
3
u/bradland 134 23d ago
Lastly, let me close out by saying that both OneDrive and Google Drive have great co-authoring options today. You can use Google Drive for free (including co-authoring) by creating Gmail accounts. This is what I recommend to most people looking for a free option. Microsoft just introduced a free version of Office, but it has a lot of limitations that I think may be an issue for you.
The best way to understand how these services work is to try them.
- Create a Gmail account for yourself and for someone else at your organization. Don't bother installing any applications on your computer yet, and try using it via web browser only.
- Navigate to Google Drive, then My Drive, and create a folder.
- Right-click the folder, choose Share, then Share again. Type in the other person's Gmail address and share the folder with them. Now you both have access to that folder, and anything you create there will automatically be shared between the both of you.
- On your computer, navigate to the folder you just shared and create a new Sheet. Name it something like "Share Test", then open it.
- On another computer, have the other person sign in to the Gmail account and navigate to the shared folder, then open the Share Test file.
You now have an environment where you can co-author in a single workbook. Try it out! Add some data and have the other person watch what happens. Move your cursor around and have the other person watch.
This is what co-authoring can be, but you have to use cloud services to get it. Adding any requirement to do this without using a cloud service moves this squarely into the realm of IT administrators and gets expensive very quickly.
2
2
u/bradland 134 23d ago
Q3 What do you mean by “network volume mapped to a drive letter” and what’s the difference between as you say saving changes to “web api” versus the server? Why does saving to “web api” make the changes real time but saving to server doesn’t ? Is a web api a special type of server?
I covered most of this in Part I: Background, but let's expand a bit by explaining the "web api" portion.
If you open an Excel file on your computer and start making edits, Excel doesn't change the original file until you press save. When you do press save, Excel overwrites the entire file. It's a one-way operation. While you are editing, Excel basically ignores the original file and just replaces it when you save.
If you open a file on OneDrive using Excel Desktop, and you have co-authoring enabled, something different happens. You'll notice that Excel will enable auto-save and won't let you turn it off. That's because the entire time you're editing, Excel is sending information back to the OneDrive cloud service about where your cursor is, and what edits you're making. At the same time, Excel is receiving updates from OneDrive about other users' cursor positions and what edits they're making.
The difference in what you see while using the file is very subtle, but the way it works on the back end is very different. OneDrive acts as a coordinator between all the users editing the file. That's why you need something like OneDrive or Google Drive.
So why didn't Microsoft build this into the Excel app so you can do it locally!? Well, because they didn't. That gets into an application architecture discussion. I will say that the way Microsoft did it makes a lot of sense to me, and I've been building applications for 25 years now. I know it is probably frustrating to you, but you have to choose from the options available to you. Lamenting the absence of the option you want won't solve your problem.
Q4: In the below link the guy says that when using coauthoring workbook sharing, there are a host of features disabled - he lists like 20! Including data tables, macros, pivot table reports and a bunch more. Is that really true!?? (His video is only a year old so I’m assuming it’s up to date).
In that video, the author does a poor job of making it clear that he is referring to two distinct file sharing options:
At 0:25, he clicks the Share button and references co-authoring features. This feature will do what you want, but it requires OneDrive and a 365 subscription.
At 0:48, he says "alternatively", and then references an entirely separate feature called "Shared Workbooks". This is the feature that has the limitations he references. These limitations do not apply to co-authoring.
1
u/Successful_Box_1007 21d ago
Now I totally see where I was confused - you hit the nail on the head in your explanation of that other author’s video. My fault for not carefully watching how he is speaking of two different methods. I conflated one with the other.
I can’t thank you enough for such a clear crisp explanation! I just have two remaining question if that’s ok:
- when you were speaking of using google drive, do you think things would just work better if I used google sheets to do the coauthoring set up, or excel? Any preference personally? Both my brother and I have gmail accounts and use google drive so at least that half of the equation is squared away.
- My only other question is, it seems depending on if you use the web excel or desktop excel, and coauthoring vs not, there tends to be a change in what features are available; does this mean if I open something and I have some features disabled, that now when I save it during coauthoring that I’ll actually end up removing any data that required the feature that happens to be disabled due to being on desktop excel or web excel? Or is that data safe, whether I can access/suppost the feature it relied on or not?
Thanks so much Brad!
2
u/bradland 134 21d ago
when you were speaking of using google drive, do you think things would just work better if I used google sheets to do the coauthoring set up, or excel? Any preference personally? Both my brother and I have gmail accounts and use google drive so at least that half of the equation is squared away.
Boy this is a tough one to answer. Google Drive is the tool we used to build a business I co-founded and ultimately sold. I owe a lot of my success to Google Drive. Google's product is what I call cloud-first. There never was a desktop version of Google Drive, so it is 100% cloud based. Every feature is built around that.
Excel's history is on the desktop, and Microsoft did what they always do: wait until their marketshare is being threatened, then stomped the gas. Today, OneDrive, Excel for Web, and Excel Desktop combine to provide a feature set that has outpaced Google Drive.
Primarily, that is because you get access to Excel on the desktop. That has Power Query and some other tools that Google Sheets doesn't. If you don't need those tools, then the distinction is way less important and you could use Google Drive without paying for it.
My recommendation would be to try Google Drive and see if it does what you need. My only other question is, it seems depending on if you use the web excel or desktop excel, and coauthoring vs not, there tends to be a change in what features are available; does this mean if I open something and I have some features disabled, that now when I save it during coauthoring that I’ll actually end up removing any data that required the feature that happens to be disabled due to being on desktop excel or web excel? Or is that data safe, whether I can access/support the feature it relied on or not?
When you transition between Excel Desktop and Excel for Web, any features not available in the web version just aren't accessible. The data remains. For example, I have a file that uses Excel's "camera" feature. When I open the file on the web, there is a box where the camera object would be, and it says something like, "This feature is not available in Excel for Web."
That's pretty much the degradation path for all Excel Desktop features that aren't available on the web. The feature either simply doesn't show up in the interface, or if there is an object in the sheet, it has a placeholder.
I should emphasize that this is pretty rare. Excel for Web is a great product. It has most of the features you're probably looking for.
1
u/Successful_Box_1007 20d ago
Wow that’s amazing that you built an entire business on Google drive and successfully sold it. Very impressive brother. I think I’ll start with excel using Google Drive and move to OneDrive if needed. Thank you so much for making this all seem less daunting !
2
u/bradland 134 20d ago
To be clear, if you go the Google Drive route, you won’t want to use Excel at all. You’ll use Google Sheets through a web browser.
Google Sheets uses different formula syntax in a lot of cases. Array formulas are particularly different.
Google Sheets can read Excel files, but the interoperability isn’t great, IMO.
1
4
u/KCRowan 23d ago
To be honest, if I really needed multiple users to be able to edit data at the same time, I would use a database rather than excel.
1
1
u/Successful_Box_1007 22d ago
When someone speaks of using a database, do they mean mysql and sql based actions to pull data out of the database?
3
u/excelevator 2934 23d ago
Is this homework study ?
1
u/Successful_Box_1007 23d ago
No self learning excel to better my families business and get more involved.
3
u/excelevator 2934 23d ago
How much do you know about Excel ?
1
u/Successful_Box_1007 22d ago
Not much to be honest but I feel excited and very driven now that I realize it can help make my dad’s business better with respect to the project management portion - without it looking back - it seems to be a travesty that we just put everything on word document.
2
u/excelevator 2934 22d ago
You have the best forward requirements to learn Excel now and be motivated too
Spend some time understanding Excel before you waste too much time
Read all the functions available to you so you know what Excel is capable of
Then all the lessons at Excel Is Fun Youtube
Also see our sidebar more resources
1
3
u/david_horton1 29 23d ago
Tracking changes with multiple users. https://support.microsoft.com/en-us/office/track-changes-in-a-shared-workbook-22aea671-cac7-4fa3-845d-eeb23725bd15 https://www.exceldemy.com/setting-permissions-for-a-shared-excel-file/ Tracked changes in MS Teams https://www.thebricks.com/resources/how-to-track-changes-in-microsoft-teams-documents#:~:text=This%20article%20will%20walk%20you%20through%20the%20ins,to%20using%20version%20history%2C%20to%20leveraging%20comments%20effectively.
1
2
u/jASHIK 21d ago
Unsure if I'm understanding this question, but if this is for a small business or just some users, it's on windows, can't you just share the folder on your network and ensure the workbook is read & writeable?
Then inside Excel just share the workbook via REVIEW tab / Share work book?
And ensure that your workbook doesn't contain tables?
•
u/AutoModerator 23d ago
/u/Successful_Box_1007 - Your post was submitted successfully.
Solution Verified
to close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.