Excel and Google Sheets are completely different tools. Here's why you want to learn how to use both...
Watch the video here
Today we're going to take a look at Excel vs Google Sheets.
What a lot of people don't realise is that Excel and Google sheets are actually completely different products.
In this video we'll look at where these two products are the same but more importantly how they are different and how those differences can make huge difference to the job that you are working on.
So first up Excel and Google sheets are both spreadsheet applications.
This allows users to freely enter data and formulas into cells, pivot to create quick summaries and prepare some basic charts.
Google sheets also allows you to work with standard Excel files so if you are a basic spreadsheet user, you're really not going to find too much difference with the products.
This is where the similarities pretty much end though.
Common data tasks such as data preparation, data entry and collaboration are handled massively differently with very clear winners for these different types of work.
First up lets start with Excel
Excel has 750 Million users and has become the defacto standard for spreadsheets.
Excel has been around for a very long time and contains the most built out and refined feature set.
If you're a more advanced Excel user, it will probably bother you all of the features that are missing from Google sheets.
There are still some real benefits to google sheets though, even for long time Excel users, which we'll go over in a sec.
Some of the power features of Excel actually do not use the spreadsheet model at all and are actually tools from some of Microsoft's more enterprise products which have been bundled directly into Excel without that many people realising this.
For example creating a report in Excel often means creating a manual process that needs to be refreshed and re-run every month.
Using the traditional spreadsheet methods for this type of work creates more work and risk of things not being copied properly.
This is where tools such as R and Python are incredibly effective. Yep R and Python are much easier and more effective for data preparation than VBA but that's another video.
R and Python are awesome tools for data professionals and despite being programming tools, maybe easier to learn than you think.
For those not inclinded to learn code there is another solution.
Microsoft Power Query for Excel is taken from Microsoft Power BI (Microsoft's data visualization tool, equivilent to Tableau).
Power Query allows you to, merge, clean and transform your data.
It records all of your steps for automation through a no-code interface which is faster and more robust than VBA.
Power Query has many similarities to tools such as Alteryx which charges thousands of dollars for some similiar functionality.
If you perform any regular reporting, Power Query could save you hours every month with just a few clicks of the mouse. If you want to find out any more about Power Query you can take a look at some the links in the description section of this video.
Power Query is built in to the Windows version of Excel 2016. A free addin for Excel 2010 and 2013. Unfortunately it's not available for Mac or Online versions of Excel.
This brings me to one of the downsides of Excel. There are many different versions of Excel and they are definitely not the same.
Although the latest version of Excel for windows is a highly developed product, the mac and especially the online version of Excel is far less developed than Google sheets.
Implementing advanced Excel features on Windows and then trying to share you work across other platforms can be highly frustrating.
This leads me to my next point.
Google sheets is natively built for collaboration and sharing, where Excel is not.
There is only one version Google sheets in the cloud, and it's the same version that everyone uses, regardless of what platform they are on.
Even though Excel has shared workbook functionality, if you've tried this before, you'll know how poorly this works and how many file locking and syncing issues occur when trying to collaborate on a file.
It's also worth noting that enabling shared workbooks in Excel also severely limits it's functionality even when working 100% on windows.
So Google sheets is far superior for collaborating with multiple users for a single file.
The implications of this is that it is also far better for data collection.
See if you're collecting data from only one user than Excel is probably fine but often data needs to be collected from a number of different people.
With Excel you would normally chop up the file, email it to people, hope they use the right version, enter the data properly and not alter the columns.
If all goes perfectly you still need to glue all of the files back together.
The problem is this process rarely goes perfectly. Even with data validation enabled, it's far too easy to corrupt the file which means, manual collection and clean up of the data before you can see any of the results.
What you really want is a form.
A form that makes it easy for users to enter the correct information,
Automatically records who provided the information and when they provided it.
The form should then automatically submit all the results back to a central data source so that you can report off it in real time. No file cleaning or gluing required.
This is exactly what google forms do for you and they are already perfectly integrated into google sheets.
Free, instant forms in minutes, no coding required.
For data collection this is far better than anything Excel has to offer.
So in a nutshell
Excel is great as a no code data preparation and automation tool
Google sheets is far better for data collection
There are other differences for sure and also other tools too. If you have a use case that you are wondering about, send me a message.