I have been talking with companies who use Excel to manage their business. They face many challenges using Excel.
In today’s post we are going to address the limitations around editing a single Excel spreadsheet (collaboration). Excel is inherently not a collaborative tool, it was built for a single user. It is not easy for several people to work on a single Excel spreadsheet. How often are you emailed a file called something like
filename_final_final_version.xlsx and then an hour later your emailed
filename_final_final_version_really.xlsx. Or are there several copies of the file floating around:
filename_bob_versions.xlxs. Or do you have to call Bob and let him know you need to edit the spreadsheet so he can’t access it for 30 minutes. This leads to confusion, wasted cycles and ultimately errors.
There are two ways to avoid this: Convert your Excel spreadsheet to a Google Sheet or move to Excel Online (Office 365 (paid) or Office Online (free)). How you are using Excel will determine if any of these options work. I would guess that the 80/20 rule works here. 80% of the spreadsheets are perfectly fine to move to an Online version of Excel or Google Sheets and 20% are too complex and need features not supported by the other tools. Let’s assume that you not doing any “heavy lifting” with your Excel Spreadsheet and that moving to Google Sheets or Excel Online is a perfectly good solution for your business. This blog post will explore the two options.
Microsoft Excel Online is Not a Good Solution:
When comparing Google Sheets and Microsoft Excel Online from a collaboration standpoint, I think Google Sheets wins hands down. I’ll be honest here, I find the model that Microsoft uses for collaboration is clunky and confusing at best. The major issues are:
- Users are given the option to edit the document in your desktop version of Excel which caused problems when trying to collaborate:
- From the desktop version users can save a copy of the spreadsheet to their desktop, a network drive, Dropbox etc. This means we can have multiple copies of a spreadsheet, which brings us right back to one of the limitations of using Excel.
- If a user is editing the spreadsheet with their desktop version, it locks out other users from editing the spreadsheet. This defeats the purpose of real-time collaboration.
- No track changes with the free Online version, I am not sure about Office 365, since I don’t have a license. This can make it difficult to determine who made what changes and can make debugging a collaborative spreadsheet a nightmare.
- The online version allows users to save a copy to their desktop, once again causing an issue when it comes to version control.
I understand why Microsoft has set sharing in such a manner, they have to support all versions of their products, but I believe this adds too many layers of complexity and too much room for error to make Microsoft Excel Online a viable choice for realtime collaboration. I am not going to outline the pros/cons of using Microsoft Excel Online, because for me their collaboration model makes the online version of Excel a “no-go” from the start.
Advantages of Switching to Google Sheets:
Google Sheets (IMHO) is by far superior of a solution for collaboration. The advantages of using Google Sheets over Excel are:
- Ease of Collaboration – Multiple users can edit the spreadsheet in real-time. Google Sheets makes it seamless to do this. This also scales very well. Sheets are limited to 50 simultaneous users for editing and 200 users can share a document.
- Version control – There is a single version of the spreadsheet, stored in the cloud. You don’t have to worry about having multiple copies of sheet lying around.
- Audit Trail – Google Sheets has the ability to review the history of what changes were made by each user and you can roll back to an earlier version of the sheet if necessary.
- Access Control – As the owner of the spreadsheet you can control who has access to the spreadsheet as well as the type of access (read, edit, comment).
- Cloud storage – Users can access the sheet from work, home, the coffee shop, their mobile device, anywhere that has an internet connection.
Disadvantages of Switching to Google Sheets:
Switching over to Google Sheets isn’t all champagne and strawberries. There are some disadvantages.
- Feature Set – Google sheets is not as full featured as Excel. Excel is definitely better for “heavy lifting” such as complex financial models, complex data analysis, use of PivotTables, Goal Seek to name a few.
- Speed – Excel loads faster than Sheet for large data sets.
- Formatting– Excel has better formatting of data, charts etc.
- Printing – It’s easier to print from Excel than sheets.
- Short cuts – Some folks complain that Google Sheets does not support as many short cuts. I have to be honest, I am not a big short-cut user, so I don’t really know. (I know shame on me! We all have our deficiencies.) I have heard that sheets has made great strides in this and supports most of Excel’s short cuts.
Converting an Excel Spreadsheet to a Google Sheet:
Converting an Excel Spreadsheet to a Google spreadsheet is pretty straight forward. Here are the steps you need to take:
- If you don’t already have a google account, create one. If you do log into google drive.
- Click on the big red New button (or click “My Drive”).
- Choose “file upload”.
- Pick the file to upload and click “Select”.
- The Excel spreadsheet will now be saved on your Google Drive.
- Click on the file to open it. This will open the file in your browser.
- Click Open and select “Google Sheets” this will open the file as a google sheets and automatically save it as a Google Sheet on Google drive.
If you access file from https://docs.google.com/spreadsheets directly instead of the drive, when you click on the file you will get a pop asking if you want to open as “View Only” or “Edit as a Google Sheets”, pick edit as Google Sheets.
Sharing the Google Sheet:
Sharing the spreadsheet with others is simple. You have two choices you can send folks a link to spreadsheet (no login required) or you can require a login by sharing the document.
To link share the document (no login required):
- Click the blue share button.
- Click on “get shareable link”.
- Determine the access level of the link (can view, can edit, can comment or off).
- Copy the link and distribute it as you see fit.
To share the document (login required):
- Click the blue share button.
- Type in the email addresses of the folks to share the sheet with.
- Determine the level of sharing (view, edit, comment.)
- Write a note if you want.
- Click “Done.
Like anything, Google Sheets has both pros and cons to using it over Excel. As stated earlier, for probably 80+% of Excel spreadsheets, Google Sheets is perfectly fine solution. I think most users will find Google Sheets more than sufficient for their needs.
Interested in learning more about the limitations and risks of using Excel? Sign up for our free 4 part email course today!