Multiple People Editing Workbook

Learn Excel From MrExcel, Podcast Episode 2157: A Bunch Of People Editing The Same Workbook At The Same Time.

Well, we've all had this situation where one workbook has to be edited by multiple people, alright? In the old way to do this, the first person opens the workbook, gets write access, everyone else has read-only, and then there's a lot of in-fighting over who gets access to the file, and you all agree, hey, we're going to take turns, but then someone forgets they have the file open and they go to lunch locking their door, and everyone is just screwed, alright, or you could do the old share workbook thing which was just horrible because then you can't do anything. All of Excel’s features get turned off.

So, what we have now, if you're in Office 365, then you have this AUTOSAVE icon in the top left-hand corner. That's an indicator that you have what they are calling co-authoring, alright? Now, in order to use co-authoring, you have to be on Office 365 or using Excel for the iPad or using Excel online -- one of those three -- your workbook has to be saved at OneDrive or the SharePoint, can't be stored locally on your hard drive, and if…like in my case right now, I have three different Excel running on three different computers in my office and they all have access to the same OneDrive account so I have all three of them open, but if you're sharing with other people in your department, then you're going to have to send a sharing link.

So, let me switch over here. VIEW, SWITCH WINDOWS, REPORT FOR JANUARY, and -- check this out -- we have multiple cell pointers, right, the blue one and the green one, and I'll click on the 18 here, so that's mine. The green one is me but there's someone else out there who has a second cell pointer, and I'll switch around here to my laptop, and, on the laptop, I'll move a couple of cells away, like that, and you'll see that, as the laptop moves, I get to see what the laptop is working on, alright? So, it says Mary Ellen Jelen is over there in that cell and it's amazing how fast it is. So, when it moves, it updates almost instantaneously.

Now, here, on the other workbook, I'm going to take that 18 and type 99 and press ENTER now, and we'll watch. We'll watch to see what happens here. It took about 3 or 4 seconds to see the change happen but it’s happening with multiple people at the same time. Now, I just picked up my iPad where I have this file open on my iPad and I'm going to choose January 4th, APPLE, that 13, and, unfortunately, the iPad will not transmit where the iPad is working on, but I'll change that to 999, and I'll press ENTER now and, oh, the iPad is a little bit slower. I just changed that 13 to 999 and it's not updating, not updating, not updating. How horrible is that, but, on the iPad, I can see that this 99 changed. Alright, so, there's some sort of a lag there from the iPad. I wonder why. I don't know why. I don't know why. Alright. Let's say that we want to share this workbook - there. It finally, finally updated. How long did that take? We’ll have to go back and measure how long that took.

Alright, so, I want to share this workbook with someone else. So, we go to SHARE and I can invite people if they're in my address book, or I can GET A SHARING LINK, and I'll CREATE AN EDIT LINK, alright? So, there's a nice little edit link. I copy that, I send it to someone, and they can take it from their email, and let's just come out here to a browser and we'll CONTROL+V.

Alright. Now, so, here's someone who doesn't have Office 365 but needs to be able to work on this workbook. It's just outside of your view here. I'll try and make this a little bit smaller, and you see that we have this thing called EDIT IN BROWSER. When I choose EDIT IN BROWSER, alright, so now, I and the browser can go to that date cell, January 1st, and change the 999. Press ENTER. Alright, now, I have nothing here on the browser indicating who else is in that file, but when I come back to Excel, the 999 has switched again. See, the Excel online, we're not getting transmitted who's in which cell. Alright, so, definitely people who are in Office 365 working on a Windows computer, this is the best experience because everyone will get to see where everyone else is.

Alright. So now, let's do the evil thing here and try and break it, alright? So, I'm going to go back to the laptop which is running Office 365, I'm going to choose DATES for January 6th 2018, which currently has the cell 10, and I'm going to type 999 but I'm not going to press ENTER, alright, and see, it changed not just the indicator but it says that that other person is editing that cell like that. Now, when you see it turn dark blue, that means stay the heck away from it, but I'm going to violate that. I'm going to come here and type 888. Alright. I'm going to press Enter on both computers at the same time. Alright. Mine shows 888 and this one shows 999. We each think that it worked but then mine changed the 999 over here. Alright. So, once it syncs up, we have a potential problem which is kind of bad.

So, I would have thought that they would have warned me. They would have said, hey, there's something going wrong here that you crashed but I guess the warning is the fact that someone else was editing the cell. So, if I come to the 17 on the laptop, ELDERBERRY, and start to edit that, change IT to 666, it's my own problem if I decide to go into that cell, alright? We can be trained to do this. We can get used to stay the heck away if someone else is in that cell. Don't let them get in there.

Alright. Now, the guest, the Excel online experience, is actually showing what cell they're in. Let's see how fast that changes. So, I'll come back here to Excel online, choose HONEYDEW for January, and then come to Excel and see how long it takes. Alright. So, now, the Excel online is actually updating. Still no love for the Excel on the iPad there. We can choose other cells and it's not doing anything.

Okay. Now, one interesting thing about this: I was out at Excelapalooza in Dallas, and Steve Kraynak from the Excel team was there doing a demo about co-authoring and showing how cool co-authoring is, and, of course, Excel for the iPad has some features that, you know, we can't normally do or can't easily do on your laptop or something like that. So, on the iPad right now, I'm going to the DRAW tab, I'm choosing the HIGHLIGHT, and I'm drawing a little highlight over some cells, 17 and 13 in Excel 2010, like that, and then turning off the DRAW feature. So, I'm actually using the touch screen on the iPad to highlight some cells, and then I'm also going to the INSERT tab and I'll come out just to the CHERRY for January 3rd. I'm going to insert a photo using the camera tool, the true camera tool, the camera on the iPad, alright. So, there. Here, let’s just do the office wall here. I’ll take a picture, say use photo. Alright. So, I just took a picture on the iPad.

Now, if you're a regular viewer of the podcast and you watched yesterday's episode -- episode 2156 -- that was an angry video talking about how horrible the AUTOSAVE is.  AUTOSAVE is a by-product of co-authoring. So, in order to give us co-authoring, they gave us AUTOSAVE. In the 1/10 of the 1% of the time when we need to co-author, co-author is an awesome feature, and if you found this video, you're loving co-authoring because now multiple people can operate on the same workbook at the same time, and life is really, really great. It's the other 99.999% of the time when we don't need co-authoring that AUTOSAVE is completely evil and it's going to screw up your spreadsheets. So, feel free to go back and watch that video to see about the dangers of AUTOSAVE.

Well, it took some time but the photo from the iPad actually showed up. Okay. So, in general, things are working well. Things are syncing between all the 4 end points. Even the pictures are showing up that I took on the iPad. Even in Excel online, the pictures are showing up that I took in the iPad which is great, alright? So, now, I'm going to come here and go to the drawing tools, choose one of these new pens, and just draw a little scribble here, alright, and then STOP INKING, and see what happens, and it says that it's saving. Saved to OneDrive. Alright, great.

Now, let's go see what happens on the other machines. Oh. Unsupported Feature. This feature cannot be edited because it contains the following features: Objects like toolbar controls, toolbox controls, or ActiveX controls. Well, I didn't insert any of those. I inserted one of the new pen items. So, now, I'm stuck with save a copy from the file tab and edit the new copy. Well, that's horrible because now I've created a forked version of the file.

Alright. Boy, it would have been nice if back in Windows 7 there, in the real Excel, it would have told me, hey, you're about to completely screw this up for everybody. That would have been really, really good. Alright, so, you know, essentially now, well, I could save a copy but I don't have a clue what it is that I recently typed here and what I'm going to lose, so it'd just be…well, I don't know. Alright. So, FILE and then…well, we'll just lose our changes.

Alright. So, back here, we now have the red line. It's not showing up on the iPad. The iPad says, refresh recommended, a newer version of this file is available on the server. So, I say more, and refresh. We're waiting for it to download, and, yes, now, the red scribble shows up on this version. Now, going back to check on the Windows 10 laptop where it also made a red scribble, different red scribble. Upload failed. Your file wasn't uploaded because your changes can't be merged with changes made by someone else, and, again, my choices are save a copy or discard changes, and, again, save a copy, useless because now I’ve create a forked version, and [what am I going to use, spreadsheet inquirer, - 10:49] to go figure out what changed. So, are you sure you want to discard all your changes that have not been uploaded to the server? Yes, and then, on the laptop, if I reopen, the scribble that I created in Windows 7 now shows up, but I had to [close, potential – 11:06], lose changes and reopen, alright?

So, things are going great between the four endpoints -- Excel online, Windows 7, Windows 10, and the iPad -- until I came here and did one of these new pen objects in either of the Excel for Windows versions, and then, well, now, I can't deal with the online version anymore and the syncing became a real hassle.

So, hey, just a note for Microsoft that there are certain operations you can do -- rare operations -- like going back here and using one of the new pen things that can force all the endpoints to have to reload in order to get past that, alright? Known issue. It's a thing.

Alright. So, wrap up. Co-authoring, hey, it avoids this whole hassle if everyone has to be read-only except for one person, a lot of in-fighting. Great news: if you're using Office 365 or even Excel online, Excel on the iPad, in general, it seems to work pretty well until you do something stupid like insert one of the new drawing objects that it's not expecting, you know, and then Excel online doesn't work, and then things just kind of go to heck, but, in general life, just entering numbers and formulas, I can see where this is going to work really, really well.

Alright, well, hey. I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.