The Advantages of Google Sheets
The main advantages Google Sheets has over Excel are seamless collaborative functionality, the ability to handle extremely large projects and data sets through its integration with BigQuery, and the automation opportunities available through Google Apps Script. While Excel and others also offer these capabilities, Google has made them so intuitive and end-user-friendly that even the most change-averse finance professionals can quickly get up to speed and start seeing tangible benefits.
Easy Collaboration
The most immediate advantage of using Sheets is the ability to collaborate seamlessly. In the old style of working, you’d use a master file that someone had to “own,” which was (in a best case scenario) kept in a shared network folder or painstakingly emailed back and forth among users.
In contrast, Sheets incorporates several modes of collaborating, ranging from the asynchronous—when collaborators work independently and mostly at different times, in the same file—to real-time simultaneous collaborative editing. With asynchronous editing, you can use the comment feature to alert people to questions or concerns and assign them tasks that they can mark complete when they finish them.
Smart chips put quick information at the user’s fingertips without their having to click out of the document. For example, people chips identify the person responsible for specific tasks and provide a link to their contact information, while file and calendar event chips put context and additional materials at hand. Combined with the timeline view feature, which enables users to visualize time-associated data, smart chips make Sheets an excellent hub not only for financial analysis but also as a hub for activities and information, including all aspects of projects.
Editing a spreadsheet live with other colleagues is a powerful way of working. The productivity of two or three people who are all highly skilled with Google Sheets quickly building out a financial or operating model together in real time is a remarkable sight compared to the old way of working. The multiple cursors in different colors, moving across the screen at the same time to build a model, look almost like a time-lapse video of a painting being created.
There’s also room for different types of participants. Not everyone needs to be able to edit a spreadsheet, for example. View- and comment-only options maintain security and data integrity while granting visibility to those who need it.
Built-in Version Control
If you’ve ever had the painful experience of a spreadsheet crash beyond recovery that results in hours of lost work, you might have developed the habit of saving files frequently. This is fine—if cumbersome—for one person working alone, but when multiple versions of a file begin to circulate among colleagues and someone forgets to update the file name, chaos can ensue.
Google Workspace makes it possible to have just one file throughout the life of the project, thanks to its built-in version tracking. Accessible through “See version history” in the menu, this feature allows you to see all edits made to a document through a handy timeline. It also includes the name of the person who made each change. Every alteration is saved and you can “rewind” to any previous point in the file’s life, back to when it was first created. Not only that, but for any individual cell, you can select “Show edit history” and click back through each edit to see who changed it, when, and to which value.
This feature is available to every Google Workspace user. Microsoft users will need either SharePoint or OneDrive to access version history for Excel.
Working at Scale
One of the misconceptions my colleagues and I had when we adopted Sheets was that it would be fine for small calculations—more or less like an advanced calculator—but not useful for larger models or data sets. But as it turns out, Google Sheets is just as powerful as a desktop application. As I demonstrate below, Google Sheets can capably handle large financial models, and it can connect to Google BigQuery to analyze huge data sets.
For example, when you’re working on an M&A project, one of the many documents you’re likely to need is a financial model to collect all the relevant historical financial data and various forward-looking scenarios based on drivers. If you often work on M&A, then you most likely tend to start from a template that contains everything you need, such as standardized financial statements, valuation calculations, etc. The current document then grows over the course of the project to incorporate financial and tax due diligence adjustments into the historical financials, using commercial/market due diligence to support the various scenarios for financial projections.
Over the course of several months, the file can grow to dozens of tabs with a complex web of links between them. Sheets not only handles the large amount of interconnected data well but, thanks to its version history feature, you’ll never again yell “Who changed the discount rate?!” when your valuation model output is unexpectedly off.
Analyzing Very Large Data Sets
As I mentioned, Google Sheets connects to Google BigQuery, Google Cloud’s data warehouse and analytics engine, which allows you to analyze huge data sets directly in Sheets. BigQuery can house all of your organization’s data and make it available for analytics use, whether for analysis by domain experts such as finance professionals or for advanced machine learning use cases led by a data science team.
To demonstrate the power of this process, I once used the following example in a Sheets training session at work:
From within Sheets, I connected to one of the public data sets in BigQuery: Chicago taxi trips. At the time, this contained 195 million rows of data.
Then I created a pivot table from the full data, with the time of day and day of week on the two axes, and the number of trips as values.
Finally, I applied conditional formatting, such as using red to represent peak hours, to make the patterns stand out more clearly.
Unless you’re in the taxi business, this particular data analysis is perhaps not very useful, but it shows how quickly you can analyze large data sets using the combination of BigQuery and Sheets to effectively gain and communicate insights.
To offer another example of Sheets pivot table usage, a finance professional might need to pull data from different systems into one analysis—perhaps one consolidation system holds higher-level data that needs to be reconciled against several detailed data sources. If your company makes the data available through BigQuery, you can, with just a few clicks, link each data source into one spreadsheet and put the relevant views next to each other. You can then quickly perform typical spreadsheet calculations right next to—and using data from—your BigQuery-powered pivot tables or extracts.
You can also create charts and formulas using data from BigQuery data sets. If you have all of your financial data in BigQuery, you can develop analyses and reports very quickly.
