Microsoft Excel has been the preferred spreadsheet program among financial experts since it was released in 1985, and it is without a doubt still a potent tool. There are several explanations for why it has maintained its appeal for so long. But the emergence of cloud computing, particularly after the COVID-19 pandemic started, and the exponential increase in the quantity of data that companies gather have altered the way we work, perhaps for good.
The benefits of Google Sheets particularly shine out in today’s collaborative, cloud-based work environment. However, despite switching to Microsoft’s cloud-based Microsoft 365, many finance and business professionals continue to use Excel spreadsheets in the same manner that many of us learned to do in the pre-cloud era.
The industrial machinery manufacturer where I manage financial data analytics made the momentous decision to transition all of its 11,000 employees to Google’s G Suite, now known as Google Workspace, in 2016, joining the growing number of businesses that are doing so from Excel. Since then, I’ve been using Google Sheets virtually daily and have become a self-taught authority on both its features and cutting-edge working methods. I have built discounted cash-flow models for sizable acquisitions, managed intricate integration projects, conducted financial planning and analysis, and frequently used Google Sheets in place of a whiteboard when I need to doodle during an online meeting.
I discuss some of Google Sheets’ benefits in this article, emphasizing the functions that have significantly increased my productivity. I also give a brief introduction to Google Apps Script, a potent tool for automating processes and expanding the capability of Google Sheets.
However, I don’t offer a feature comparison of Excel and Google Sheets side by side because the functionality of these programs changes so frequently that such a comparison would be out of date in a matter of weeks. Instead, I emphasize the benefits of Google Sheets and the innovative ways of working made possible by the rise of online productivity tools. I also discuss some of the drawbacks of moving away from Microsoft.
Benefits of Google Sheets
The key advantages Google Sheets has over Excel are its seamless collaboration capabilities, its integration with Big Query’s capacity to handle very massive projects and data sets, and the automation possibilities provided by Google Apps Script. Although Excel and other programs also have these features, Google has made them so user-friendly and straightforward that even the most resistant to change finance experts can rapidly catch up and begin experiencing real advantages.
The ability to collaborate easily when utilizing Sheets is an initial benefit. The traditional way of functioning required the use of a master file that each user had to “own,” which was (in the best case scenario) saved in a network shared folder or laboriously sent back and forth between users.
Asynchronous collaboration, in contrast, occurs when team members work separately and primarily at various times in the same file. Real-time simultaneous collaborative editing is another mode that is included in Sheets. With asynchronous editing, you may give people tasks to accomplish and use the comment tool to let them know when you have questions or concerns.
The user can access rapid information without leaving the paper thanks to smart chips. People chips, for instance, indicate the person in charge of a certain task and link to their contact information, whereas file and calendar event chips provide context and other relevant information. Smart chips make Sheets a fantastic hub for activities and information, encompassing all facets of projects, in addition to being a wonderful hub for financial analysis when combined with the timeline view function, which helps users to visualize time-associated data.
Live spreadsheet editing with coworkers is a highly effective method of working. When compared to the previous method of working, the efficiency of two or three highly trained Google Sheets users swiftly developing a financial or operating model together in real time is amazing. The several colored cursors moving simultaneously across the screen to assemble a model like a time-lapse film of a painting being created.
There is also space for a variety of people. For instance, not everyone needs to be able to modify a spreadsheet. Options for viewing and commenting only preserve data integrity and security while allowing visibility to those who require it.
Integrated Version Control
If you’ve ever had the frustrating experience of having a spreadsheet crash beyond repair and lose hours of effort, you may have formed the habit of constantly storing files. When numerous versions of a file start to circulate among coworkers and someone forgets to update the file name, chaos can result. This is fine—if cumbersome—for one person working alone.
Because Google Workspace includes built-in version tracking, it is feasible to work with just one file for the duration of the project. This tool, which you can access by selecting “See version history” from the menu, lets you view all document edits in a convenient timeline. The individual who made each adjustment is also identified by name. Every change is preserved, and you can “rewind” to any earlier time in the file’s history, even back to its creation. Additionally, you can choose “Show edit history” for any specific cell and go back through each edit to see who made the modification, when, and to what value.
Every Google Workspace user has access to this functionality. Microsoft customers will need to use OneDrive or SharePoint to view Excel’s version history.
When my colleagues and I first started using Sheets, one of our preconceptions was that it would be excellent for little calculations—basically acting as a sophisticated calculator—but useless for larger models or data sets. However, it turns out that Google Sheets is equally as effective as a desktop program. Google Sheets can successfully handle complex financial models, as I show below, and it can link to Google Big Query to examine enormous data sets.
A financial model, for instance, is one of the numerous documents you may want when working on an M&A deal in order to gather all the pertinent historical financial data and several forward-looking scenarios based on drivers. If you work on M&A frequently, you probably have a tendency to begin by using a template that already has all the information you require, such as standardized financial statements, valuation estimates, etc. The present document then develops during the course of the project to include modifications for financial and tax due diligence into the historical financials, utilizing commercial/market due diligence to support the various financial forecast scenarios.
The file can develop into dozens of tabs with a complicated network of linkages between them over the course of several months. Because to Sheets’ version history function, you’ll never again cry, “Who changed the discount rate?!” when the result of your valuation model is unexpectedly off. Sheets not only manages the massive volume of interrelated data properly.
Very Large Data Sets Analysis
As I already indicated, Google Sheets integrates with Google BigQuery, the data warehouse and analytics engine of the Google Cloud, enabling you to explore big data sets in Sheets. BigQuery can store all of your organization’s data and make it accessible for analytics use, whether for advanced machine learning use cases lead by a data science team or for analysis by domain experts like finance professionals.
I once used the following example in a Sheets training session at work to show the effectiveness of this procedure:
1.I connected to the public data collection for Chicago cab journeys in BigQuery from inside Sheets. This has 195 million rows of data in it at the time.
2.Then, using the entire set of data, I made a pivot table with the number of trips as the values and the time of day and day of the week as the axes.
3.Finally, to make the patterns stand out more clearly, I used conditional formatting, such as using red to denote peak hours.
It took less than two minutes to complete the transaction.
This particular data study may not be very useful unless you work in the taxi industry, but it demonstrates how rapidly you can analyze massive data sets utilizing the combination of BigQuery and Sheets to usefully learn and express insights.
A financial expert could need to combine data from various systems into one study. For instance, one consolidation system might have higher-level data that needs to be compared to a number of sources of particular data. If your business makes the data available through BigQuery, you can quickly combine all of the data sources into one spreadsheet and arrange the pertinent views side by side. Following that, you can rapidly do common spreadsheet calculations adjacent to and utilizing data from your BigQuery-powered tables that pivot or extracts.
Using information from BigQuery data sets, charts and formulas can also be produced. You can easily create analysis and reports if all of your financial data is in BigQuery.
Getting in touch with Outside Data Sources
A vital skill for financial professionals is the ability to extract data into a spreadsheet so that they can deal with it. There are several built-in formulas in Google Sheets that can achieve this, such as the Googlefinance formula, which allows you to directly pull data from Google Finance. It’s a great tool for finding public stock and currency information, even though it’s not comparable to a professional service like Bloomberg. For example, these closing bell prices for Alphabet stock in January 2018:
There are more, more versatile ways to access data from the internet. For example, you might want to collect and work with financial, market, or other publicly available information, in which case the ability to extract an HTML table or list directly from a website can be helpful.
Accessing the Complete Google Workspace
Google Apps Script is a potent tool that offers virtually endless opportunities for both extending and automating activities in Sheets and connecting it to other Google Workspace components. Operations from Sheets, Drive, and BigQuery are effortlessly integrated into one workflow via this automation tool. At work, I can think of countless instances where we’ve utilized Apps Script to make a lot of copies of a template reporting spreadsheet, automatically imported various financial data from BigQuery into each of them, and then distributed each version to various individuals. This made sure that each team could only see the private information that belonged to them, such costs.
Google Sheets: Are There Any Drawbacks?
I’ve been a Google Sheets evangelist. But not everyone has yet come around to its advantages. And I believe that’s due to one of Sheets’ shortcomings: Many of its advantages depend on collaboration with others, and you’ll discover that unequal adoption in your company will put restrictions on how quickly you can utilize some of the features to their fullest potential.
Additionally, Google Sheets can have its minor annoyances, just like any other piece of software, such as a few keyboard shortcuts that aren’t what you’d expect or a feature that’s either missing or doesn’t function as you’d want. But in many cases, rather than being a Google Sheets flaw, these irritations are more of a problem for users who are still used to using Excel.
Another issue is that a lot of the plugins that make Excel successful are not (yet) accessible for Google Sheets. This implies that utilizing Sheets means you might need to switch between both applications if Google Apps Script can’t duplicate what you require.
Improved Cooperation Wins Out
For many finance and business experts, spreadsheets rank among the most crucial tools, if not the most crucial tool altogether. They serve as our blank canvases, notebooks, and instruments. We use them for thousands of hours over the course of our careers. We need to have effective and enjoyable interactions with them.
When using web-based productivity tools like Google Sheets instead of their conventional desktop versions, I have noticed significant productivity gains from the collaboration features and other advancements. Although Microsoft 365, a cloud-based platform, has improved in recent years, the collaboration feature still lags behind current demands.
As I’ve hopefully shown, there are also significant productivity-boosting advantages to learning to use these tools beyond the basics. Finding the functions that work for you is essential because of trends like growing remote cooperation and the need to analyze ever-larger data sets, and the business case for assessing Google Sheets is getting stronger.
To include the most recent and correct information, this article has recently undergone a thorough update. The comments that follow could be outdated.