Microsoft Excel, introduced in 1985, has been the spreadsheet of choice for financial professionals ever since. It is a very powerful program. It’s popularity has lasted for many decades. The exponential increase in data collected by businesses and cloud computing has changed our working methods forever.
Google Sheets’ advantages are most evident in today’s cloud-based, collaborative business environment. Many finance and business professionals still use Excel spreadsheets the way they did in pre-cloud days, including those that have moved to Microsoft’s cloud based Microsoft 365.
The industrial machinery manufacturer where I manage financial data analytics made the important decision in 2016 to move all its 11,000 employees over to Google G Suite (now Google Workspace), joining the growing number of companies making the switch away from Excel. Since then, I have been using Google Sheets daily, and am now a self taught expert on its innovative features. Google Sheets has been used to create discounted cash flow models, manage integration projects, perform financial analysis and planning, and to replace a whiteboard in online meetings.
This article highlights the Google Sheets features that I have found to be very useful in increasing my productivity. Google Apps Script is also introduced, a powerful way to automate workflows and extend Google Sheets functionality.
However, I do not provide a feature comparison of Excel and Google Sheets. Functionalities are changing so rapidly that such an evaluation would be outdated within a few weeks. In this article, I will focus instead on the advantages of Google Sheets and new working methods that online productivity tools enable. In addition, I touch upon some of the downsides of switching from Microsoft.
Google Sheets: The advantages
Google Sheets is superior to Excel because of its seamless collaboration, ability to manage large data sets and projects through integration with BigQuery and automation options available via Google Apps Script. Excel, and other programs offer similar capabilities. However, Google’s Sheets is so user-friendly and intuitive that even finance professionals who are averse to change can easily get on board and see tangible results.
Easy Collaboration
It is easy to work together with Sheets. The old way of working required you to use a “master file” that was owned by someone. This master file would be kept (in the best-case scenario) in a network shared folder, or sent painstakingly via email between users.
Sheets, on the other hand, offers several collaboration modes, from asynchronous (when the collaborators are working independently, and usually at different times), to real-time, simultaneous editing. You can also use the comments feature in asynchronous editing to notify people of questions and concerns, or assign tasks they can complete once they are completed.
The smart chips provide quick access to information without the need for the user to leave the document. People chips, for example, identify a person who is responsible for completing a task and link them to their contact details, while calendar events and file chips provide context and other materials. Smart chips, when combined with timeline views, allowing users to see time-related data, make Sheets a great hub, not just for financial analyses, but for all types of information and activities, such as projects.
Working together on a spreadsheet is an effective way to work. It is amazing to see how quickly two or more people with high-level Google Sheets skills can build a financial model or an operating model in real-time. Multiple cursors of different colors moving simultaneously across the screen to create a model look like time-lapse videos.
You can also have different kinds of participants. For example, not everyone must be able edit a spread sheet. The view-only and comment options ensure data security while providing visibility for those who require it.
Built-in Version Control
You may have become accustomed to saving your files often if you ever experienced the pain of losing hours of work due to a spreadsheet that crashed beyond repair. It’s fine for a single person to do this, even if it is cumbersome. But when that file becomes a part of an office file and multiple copies are shared, things can get messy.
Google Workspace’s version tracking allows you to keep only one document throughout the entire project. This feature is accessible through the “See version histories” menu. It allows you to view all changes made to an existing document in a timeline. The name of each person responsible for the change is also included. You can “rewind”, and go back in time to the original creation of the file, or any other point. You can also select the “Show Edit History” option for each cell and then click through to view who made changes, when and what value.
Google Workspace users can access this feature. Microsoft users need SharePoint or OneDrive in order to view the version history of Excel.
Work at Scale
When we first adopted Sheets, my colleagues and myself had the impression that it was only good for simple calculations – more or less like a calculator advanced – but not for large models or datasets. Google Sheets, it turned out, is as powerful as desktop applications. Google Sheets is capable of handling large financial models. It can also connect with Google BigQuery for analysis.
When you work on an M&A, you may need a financial modeling tool to gather all relevant financial data from the past and to create various scenarios for the future based on the drivers. You may start with a template containing all the information you require, including standardized financials, valuation calculations and so on. Over the duration of the project, the current document will be updated to include financial due diligence and tax adjustments to the historical financials. Commercial/market due diligence is used to support various financial scenarios.
The file may grow over several months to include dozens of tabs and a web of complex links. The version history function in Sheets allows you to keep track of all the data that is interconnected. You will never have to ask “Who changed discount rate?” again. When your valuation model’s output turns out to be unexpectedly wrong, you won’t have to shout “Who changed the discount rate?”
Analysis of very large data sets
Google Sheets is connected to Google BigQuery. This data warehouse/analytics engine of Google Cloud allows users to access and analyze large data sets in Sheets. BigQuery is a powerful analytics tool that can store all the data in your company and provide it to domain experts, such as financial professionals. It also allows advanced machine-learning use cases by data scientists.
In a Sheets session I used this example to demonstrate the effectiveness of the process:
- In Sheets I was able to connect with one of BigQuery’s public data sets: Chicago taxi rides. This data set contained at the time 195 millions rows.
- From the data I collected, I made a pivot chart with time and weekday on two axes and number of trips in values.
- To make patterns more visible, I used conditional formatting. For example, using red for peak hours.
It took only two minutes to complete the entire procedure.
This data analysis may not be very helpful unless you are in the taxi industry, but it does show how you can quickly analyze and share insights using BigQuery with Sheets.
As an example, to use Sheets pivot tables in another way, perhaps a finance expert needs to combine data from several different sources into one analysis. For instance, one system may contain higher level data which must be compared to other data sources. You can link the different data sources into one spreadsheet with a few simple clicks if your company provides the BigQuery data. Then, you can quickly do spreadsheet calculations using the data in your pivot tables and extracts powered by BigQuery.
Using data from BigQuery, you can create formulas and charts. You can create reports and analyses very quickly if you store all your financial data within BigQuery.
Connection to external data sources
Financial professionals need to be able pull data from a spreadsheet in order to work with them. Google Sheets has a few built-in formulas to do this. One of them is the Googlefinance Formula, which pulls data from Google Finance. It’s not as professional as Bloomberg but it is still a great tool to get public information on stocks and currencies. For example, these Alphabet closing bells for January 2018.
Other, more generic ways of getting data are available. It is possible to use the functionality of extracting an HTML table directly from a site. This can be helpful for retrieving and working with publicly accessible information, such as financial or market data.
You can connect to your entire Google Workspace
Google Apps Script, a powerful and versatile tool for automating Sheets workflows as well as connecting it to other Google Workspace features is a great way to enhance your productivity. The automation tool connects Sheets, Drive, BigQuery, all in one seamless workflow. Apps Script has been used in many instances at my workplace to generate a number of versions of a reporting template spreadsheet. We then downloaded financial data from BigQuery and shared them with various people. It was possible to limit the data that each team could view, such as costs.
Google Sheets: Are There Any Drawbacks?
I’ve been evangelizing Google Sheets. Not everyone has yet embraced its advantages. Sheets has a few drawbacks, one being that many of its features require collaboration.
Google Sheets can also be a little annoying, just like other software. For example, a few keyboard shortcuts that don’t work as you expect, or features that are missing or behave differently than you’d hoped. These annoyances, however, are often more a result of users who have become accustomed to Excel than they are Google Sheets’ functional weaknesses.
A second problem is the fact that Excel relies heavily on an ecosystem of plug-ins. Many of these are (yet to be) unavailable for Google Sheets. If you cannot replicate your needs with Google Apps Script then you will need to switch between Sheets and Google Apps.
Collaboration improves the chances of success
Spreadsheets have become a vital tool for finance and business professionals. Our canvases, notepads and instruments are spreadsheets. We spend thousands of hours with them over the course of our career. Our experiences should be enjoyable and effective.
Google Sheets, a web-based application with collaboration and innovation features, has been able to provide me with a much higher level of productivity than their desktop equivalents. Microsoft 365, a cloud-based service, has improved in the last few years. However its collaboration features have not yet caught up with today’s needs.
There are also immense productivity-enhancing benefits from learning to use these tools beyond the elementary level, as I hope I have demonstrated. Finding the right functionalities for your needs is essential, especially with the increasing trend of remote collaboration, as well as the requirement to analyse ever larger data sets.
The article was recently updated to include the most recent and accurate information. The comments below could predate the changes.