Many spreadsheets exist — Mac users will be familiar with numbers, Google Sheets is becoming popular, there is the trusted (or hated) Microsoft Excel which now comes in many versions and formats. With all these choices, it’s hard to know which spreadsheet should you use.
As always, some preferences — I only use Microsoft Excel and Google Sheets (I lean towards using Google Sheets more due to ease, but more on that later). I have a Mac, so Numbers pops up from time to time. I curse it every time, unless I just want to take a quick look at the file I am opening, I quit it as soon as I can. I also used OpenOffice when I was an undergraduate student and while it’s good and does the job, the formatting issues I had switching between OpenOffice and Microsoft still haunt me (this was mostly Word documents rather than Spreadsheets, and I had not learnt of the magic of PDFs). There are others out there, I’m sure they are great. I’m also sure Numbers and OpenOffice do a great job for those who bother to engage with them properly. As the two most popular (both to me and in terms of number of users), I am going to focus on Microsoft Excel and Google Sheets.
MASSIVE CAVAET — if your business (or university or school) use Google or Microsoft, it is nearly always best just to use their version of spreadsheets. It makes life substantially easier than have to save files in particular ways and risk the formatting (or formulas) not being transferred properly.
Why I prefer to use Google Sheets when possible:
In the browser, anywhere.
Being able to update a Sheet using my phone or tablet has been useful. Microsoft Excel Online is also in browser, but it doesn’t feel as easy to use or offer full functionality. As Sheets was designed for browser, it is slick. This also means versions of the software and compatibility issues are less of a problem.
Collaborative working and version control.
Working in browser also makes collaborative working significantly less stressful — all changes are live and saved (internet permitting). Google Sheets saves multiple versions of each spreadsheet. These can be reverted and compared easily. Users can now check cell level changes to see previous values and who changed them. In an increasingly remote and online world, these features make working together less prone to errors and stress.
IMPORTRANGE and QUERY functions.
I am an SQL person at heart, and these functions in Google Sheets makes moving and transforming data very SQL-like. The ability to import data from other Sheets is beyond useful for making real-time dashboards and reports.
Macros are written in JavaScript.
Macros (the coding bit in the background of spreadsheets) allows advanced functionality in your spreadsheets. They allow buttons to send emails or other useful automation to happen. In Microsoft, macros are written in Visual Basic Applications (VBA). Unless you have a Computer Science degree, you are unlikely to just know VBA. Google uses Apps Scripts which is written in JavaScript (and have excellent document). For me, this is a big plus as I find JavaScript a lot easier than VBA.
(Apparently) Pivot tables are easier to understand.
I learnt Pivot tables in Excel and I don’t find them complicated. However, I know a lot of people do. I have been told, pivot tables are significantly more user friendly and easy to understand in Sheets.
Ease of connecting to the whole Google product range (Slides, Docs as well as Google Data Studio).
The entire GSuite offering is easy to use, and connects together smoothly. I find Gmail, Slides and Docs much nicer to use than their Microsoft counterparts. Apps Scripts (the macro language) also allows for some great pre-programmed automation between the product range. We’re so into this product range at Taught by Humans we are planning a series focusing on all the useful things GSuite has to offer.
Ease of connection to other software.
I’m not sure if this is because Google works on this, start-ups use Google or Google was free before Microsoft offered a free version, but a lot of software based startups integrate to Google Sheets. Some also integrate to Excel, but the integration is never as easy or as clean. Take Typeform for example, the survey output can be added to a tab of a Google Sheet on any Google Drive. The Microsoft equivalent has to be a new spreadsheet. Not the biggest of deals, but it does add some additional work and room for human error with the extra processing needed to automate the workflow.
Charting abilities.
Making the charts is easier in Google Sheets than Microsoft Excel. Google Sheets is good at predicting which chart you want and the initial chart made looks so much better in Google Sheets.
Why Google Sheets isn’t always the best:
Charting abilities.
Yes, this appeared in the pros section as well. While making simple charts is better in Google Sheets, making advanced charts is complex (nearly impossible to get exactly what you want sometimes). The chart setup section is very restricted which means a lot of data manipulating is needed to get desired results (more so than in Excel). Also, editing the chart style is very limited in Google Sheets.
Macros are limited.
While easy to write, well documented and great pre-built examples, the macro ability in Google Sheets is limited. In Microsoft Excel, I can write VBA to do whatever I want. I cannot write Apps Scripts in the same way as it is a limited language.
When you might not have internet.
You can use an offline version of Google Sheets when you do not have internet. If you are working on a solo Sheet, great. If the Sheet is collaborative, there is a risk of either losing yours or someones work.
All in all, Google Sheets gets my vote. As a newer product, it is also constantly improving and the documentation and forums (both official and unofficial) make it easy to problem solve.