Since its launch in 1985, Microsoft Excel has become the go-to spreadsheet program for finance professionals, and it remains undoubtedly a powerful tool. There are many reasons why it has maintained its popularity for decades. However, the rise of cloud computing, especially since the beginning of the COVID-19 pandemic, and the exponential growth of data collected by businesses have changed the way we work, perhaps permanently.
It is in today’s collaborative cloud-based professional environment that the advantages of Google Sheets truly shine. Yet, many finance and business professionals continue to work with Excel spreadsheets in the same way that many of us learned to do back in the pre-cloud era, even those who have transitioned to cloud-based Microsoft 365.
In 2016, the industrial machinery manufacturing company where I oversee the analysis of financial data took a significant step by transitioning its 11,000 employees to Google’s G Suite, now known as Google Workspace. It joined the growing number of companies that are moving away from Excel. Since then, I have been using Google Sheets almost daily and have become a self-taught expert in its functionality and innovative ways of working. I have used Google Sheets to build discounted cash flow models for large acquisitions, manage complex integration projects, financial planning and analysis, and very often as a replacement for a whiteboard when I want to sketch something during an online meeting.
In this article, I will share some of the advantages of Google Sheets, highlighting the features that have significantly improved my productivity. I will also introduce Google Apps Script, a powerful tool for automating workflows and extending the capabilities of Google Sheets.
I will not, however, make a direct comparison of features between Excel and Google Sheets, as features evolve so rapidly that such an analysis would become obsolete within weeks. Instead, I will focus on the advantages of Google Sheets and the new ways of working made possible by the emergence of online productivity tools. I will also address some of the negative aspects of transitioning from Microsoft.
The Advantages of Google Sheets
The main advantages of Google Sheets over Excel are its seamless collaboration features, the ability to handle large projects and datasets through integration with BigQuery, and the automation possibilities offered by Google Apps Script. While Excel and others also offer these features, Google has made them so intuitive and user-friendly that even the most change-resistant finance professionals can quickly upgrade their skills and start reaping tangible benefits.
Easy Collaboration
The most immediate advantage of using Sheets is the ability to collaborate seamlessly. In the old way of working, you would use a master file that someone had to “own” and that was (at best) kept in a shared network folder or emailed between users.
In contrast, Sheets incorporates multiple modes of collaboration, ranging from asynchronous – when collaborators work independently and primarily at different times, in the same file – to real-time collaborative editing. With asynchronous editing, you can use the comments feature to alert people to issues or concerns and assign them tasks that they can mark as completed when finished.
Smart chips quickly bring information to the user’s fingertips without the need to click outside of the document. For example, people chips identify the person responsible for specific tasks and provide a link to their contact details, while file and calendar event chips provide context and additional documents. Combined with the timeline view feature, which allows users to visualize time-related data, smart chips make Sheets an excellent hub not only for financial analysis but also for activities and information, including all aspects of projects.
Live editing a spreadsheet with colleagues is a powerful way to work. The productivity of two or three highly skilled individuals with Google Sheets quickly building a financial or operational model together in real-time is a remarkable sight compared to the old way of working. Multiple cursors in different colors moving simultaneously on the screen to build a model almost resemble a fast-forward video of a painting being created.
There is also room for different types of participants. Not everyone needs to have the ability to edit a spreadsheet, for example. Read-only and comment-only options preserve data security and integrity while granting visibility to those who need it.
Integrated Version Control
If you have ever experienced the painful process of an irreparable spreadsheet crash that results in hours of lost work, you may have gotten into the habit of frequently saving files. This is fine but cumbersome for a single user. But when multiple versions of a file start circulating among colleagues, and someone forgets to update the file name, chaos can ensue.
Google Workspace makes it so you only have one file throughout the life of the project, thanks to its built-in version tracking. Accessible through the “See version history” option in the menu, this feature allows you to see all the changes made to a document through a convenient timeline. It also indicates the name of the person who made each change. Each change is logged, and you can “revert” to any previous point in the file’s life, since its creation. Furthermore, for each individual cell, you can select “Show edit history” and trace back each change to see who made it, when, and what value was modified.
This feature is available to every Google Workspace user. Microsoft users will need SharePoint or OneDrive to access version history for Excel.
Working at Scale
One misconception that my colleagues and I had when we adopted Sheets was that it would be suitable for small calculations, almost like an advanced calculator, but not useful for larger models or datasets. But it turns out that Google Sheets is as powerful as a desktop application. As I will demonstrate below, Google Sheets can effectively handle large financial models and connect to Google BigQuery to analyze massive datasets.
For example, when working on a merger and acquisition project, one of the many documents you will likely need is a financial model to gather all relevant historical financial data and various prospective scenarios based on factors. If you frequently work on mergers and acquisitions projects, you are probably used to starting from a template that contains everything you need, such as standardized financial statements, valuation calculations, etc. The current document then grows throughout the project to incorporate financial and tax due diligence adjustments into the historical financial data, using business/market due diligence to support different financial projection scenarios.
Over the course of several months, the file may have dozens of tabs with a complex network of interlinks. Sheets not only handles the large amount of interconnected data but, thanks to its version history feature, you will never wonder “Who changed the discount rate?!” when the results of your valuation model are unexpected.
Analyzing Very Large Datasets
As I mentioned, Google Sheets connects to Google BigQuery, Google Cloud’s data warehouse and data analytics engine, allowing you to analyze massive datasets directly within Sheets. BigQuery can host all your organization’s data and make it available for analytical use, whether it’s for analysis by domain experts such as finance professionals or for advanced machine learning use cases driven by a data science team.
To demonstrate the power of this process, I used the following example in a Sheets training session at work:
- From Sheets, I connected to one of BigQuery’s public datasets: Chicago taxi trips. At the time, it contained 195 million rows of data.
- I then created a pivot table from the full dataset, with time of day and day of the week on both axes, and the number of trips as values.
- Finally, I applied conditional formatting, such as using red to represent peak hours, to highlight patterns more clearly.
The entire process took less than two minutes.
Unless you work in the taxi industry, this particular data analysis may not be very useful, but it showcases how quickly you can analyze large datasets using the combination of BigQuery and Sheets to efficiently gather and communicate insights.
Another use case for Sheets’ pivot tables is extracting data from different systems into a single analysis. For example, a finance professional may need to pull data from various sources into one analysis – such as a consolidation system containing higher-level data that needs to be reconciled with multiple detailed data sources. If your company makes data available via BigQuery, you can, with just a few clicks, link each data source into one spreadsheet and place relevant views side by side. You can then quickly perform common spreadsheet calculations right next to – and using data from – your pivot tables or query-powered extracts.
You can also create charts and formulas using data from BigQuery datasets. If you have all your financial data in BigQuery, you can quickly develop analyses and reports.
Connecting to External Data Sources
The ability to pull data into a spreadsheet for working with it is a fundamental need for finance professionals. Google Sheets offers several built-in formulas that allow for it, such as the Googlefinance formula, which allows you to directly retrieve data from Google Finance. While it may not be comparable to a professional service like Bloomberg, it is still a great tool for getting public information on stocks and currencies, such as the closing price of Alphabet stock in January 2018:
There are also other, more general ways to get data from the web. The functionality to directly extract an HTML table or list from a website, for example, can be useful for retrieving and working with financial, market, or other publicly accessible information.
Integration with the Entire Google Workspace
Google Apps Script is a powerful tool that opens up virtually limitless possibilities not only for extending and automating workflows in Sheets but also for connecting it with other parts of Google Workspace. This automation tool seamlessly connects Sheets, Drive, and BigQuery operations into a single workflow. I have many examples at work where we have used Apps Script to create a large number of copies of a master report spreadsheet, automatically pull different financial information from BigQuery into each one, and then share each version with different people. This ensured that different teams could only see their own sensitive data, such as costs.
Are there any Downsides to Google Sheets?
I have praised Google Sheets. However, not everyone is convinced of its advantages at the moment. And I think it’s because of one of Sheets’ downsides: many of its benefits depend on collaboration with others, and you will find that uneven adoption within your organization will limit the speed at which you can fully leverage certain features.
Additionally, Google Sheets, like any software, can have its minor irritations, such as a keyboard shortcut or two that don’t match your expectations, or a feature that is missing or doesn’t behave as you would like. In many cases, however, these irritations are primarily a problem for users still accustomed to Excel rather than a functional weakness of Google Sheets.
Another challenge is that Excel’s success relies on its ecosystem of plugins, many of which are not (yet) available for Google Sheets. This means that if you can’t replicate what you need with Google Apps Script, using Sheets will require you to switch between the two applications.
Enhanced Collaboration Takes the Lead
Spreadsheets are one of the most important tools, if not the most important tool, for many finance and business professionals. They are our canvases, our notepads, and our instruments. Throughout our careers, we spend thousands of hours using them. We need our experience with them to be enjoyable and efficient.
I have found tremendous productivity benefits from the collaboration features and other innovations offered by online productivity apps like Google Sheets, compared to their traditional desktop counterparts. Cloud-based Microsoft 365 has become more robust in recent years, but its collaboration functionality has not fully met today’s requirements yet.
There are also immense productivity improvement benefits to learning how to use these tools beyond the basic level, as I hope I have demonstrated. With trends such as increased remote collaboration and the need to analyze increasingly large datasets, it is crucial to find the features that work for you, and the business case for considering Google Sheets continues to strengthen.
This article has recently been thoroughly updated to include the most up-to-date and accurate information. The comments below may precede these changes.
Consult Crawlan.com to stay up to date with the latest tricks and tips for online marketing professionals.
