A couple of years ago, the company I work for took the significant step of moving all of its 11,000 employees over to Google’s G Suite. Consequently, since then I’ve been using Sheets (spreadsheets) and Slides (presentations) products almost daily, learning their functionality and experiencing new ways of working.

In this article, I will share some of the most useful Google Sheets features that I learned early on, which dramatically improved my productivity. Toward the end, I will also provide an introduction to Google Apps Script, a powerful tool for automating workflows and extending Google Sheets’ functionality. In a later article, I will go deeper into Google Apps Script and demonstrate a tutorial for getting started.

I won’t, however, be giving a feature comparison between Google Sheets and Excel; their features change so quickly that such analysis would become redundant within weeks. Instead, I will focus on Google Sheets’ advantages and the new ways of working that the emergence of online productivity tools have enabled. In my experience, there are many finance and business professionals out there who are still working with Excel spreadsheets in the same way that many of us learned over 10 years ago in the pre-cloud era. I have worked with many people who, even though they may have switched to Google Sheets, still use it in the old offline Excel mold, meaning that they lose out on a number of productivity-enhancing features.

In addition to listing the advantages, I will also touch on some of the negative aspects I have experienced in this shift. There are really only two that stand out: One is that many of the benefits depend on collaboration with others, and here you will find that the uneven uptake in the organization will place limits on how fast you can take some of the features into use. The other is that Excel is not a standalone tool: There is an ecosystem of plugins out there, many of which are not (yet) available for Google Sheets, meaning that you, in some cases, may end up juggling both applications.

Google Sheets’ Advantages

1. Collaboration

The most immediate benefit from using Sheets is in the ability to collaborate in completely new ways. The “old style” of working would be using a master file that someone has to “own,” which is then (in the best case) kept on a shared network folder, or painstakingly emailed around.

Here is where Sheets really shines. There are several different modes of collaborating, ranging from the asynchronous where you 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 and assign them tasks that they can then can mark as completed.

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 really a 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, is almost like a time-lapse video of a painting.

There is also room for different types of participants. Not everyone needs to be able to edit a spreadsheet. There are levels of access rights, which ensures that security and posterity is kept in check by granting appropriate people the right level of access.

2. Working at Scale

One of the misconceptions my colleagues and I had was the notion that Sheets is fine for small calculations—more or less like an advanced calculator—but not useful for larger models or datasets. Turns out we were wrong. I’ve used it for a number of larger operating and valuation models over the past years and am very impressed with the performance.

I recently did some analysis work on data extracted from our ERP system, where I was working with a dataset that had about 10 sheets, each ranging in size from approximately 25,000 to 100,000 rows and up to 30 columns (Google Sheets has a limit of 5 million cells per workbook). These sheets each build on each other through a series of the traditional VLOOKUP, INDEX, MATCH and SUMIF formulas. Copying a new formula across a column of around 25,000 rows took around 10 seconds on my computer, which is far from instantaneous, but not a hindrance, due to:

1. Sheets shows a small, helpful progress bar in the corner of the screen, which is more reassuring than Excel’s tendency to freeze and leave you guessing when it will complete.
2. You can also continue working elsewhere in the active workbook as cells are updating.

CONNECTING TO GOOGLE BIGQUERY

For analysis of (even) larger datasets, Google Sheets now connects to Google BigQuery, allowing you to write and run SQL queries using data in Google BigQuery and get the results straight into Sheets.

3. Creating Charts and Linking to Google Slides

Raw spreadsheet work (“wrangling”) is an important part of the finance professional’s daily life, but even the best analysis is of limited value if you are unable to communicate your findings in a cogent and compelling way. This brings us to two other staples of the finance toolbox: charts and presentations.

Presentations in the G Suite world are made in Google Slides which, unsurprisingly, feels like Microsoft PowerPoint. The link between Sheets and Slides works very well and makes it easy to ensure that charts and tables in your presentation are always up to date through an “Update” button that pulls up the latest data of the underlying modeling, calculations, and assumptions.

When starting any large project now, one of the first things I do is create two documents: one in Google Slides and the other in Google Sheets. These go hand in hand cleanly throughout the project, providing that “single source of truth” for all data in the project.

4. Version Control

If you have ever had the painful experience of a spreadsheet crash beyond recovery, resulting in hours of lost work, you might have a developed habit of saving new files frequently. This can end up in numerous iterations of files with tedious version updates (v3.4.0, v3.4.1, etc.)

Through G Suite products, it really is possible to just have one file throughout the life of the project, thanks to version control.

Version control, accessible through “See version history” in the menu, allows you to see all edits made to a document since the very beginning through a handy timeline, along with the name of the person who made each change. That’s right—everything is saved, instantly. No need to press F12 every 5-10 minutes, increment the version number, and save, just to have peace of mind knowing that nothing is lost in case of a crash.

5. Linking Between Sheets in Different Files

Cloud productivity makes link functionality far more elegant and ensures that it actually works! It is possible to reference other workbooks in the offline Excel world, of course, but that tends to be brittle and breaks once files are moved out of their original folder location. You will often see cells containing obscure references to a folder structure on someone’s hard drive: C:\users\smithj\projects\omega\valuation\.

The IMPORTRANGE function in Google Sheets allows you to link seamlessly to other Sheets files. Irrespective of whether the file gets moved or renamed, the data will always be linked and remain one less problem for you to worry about.

6. Working with Plugins

One reason why a number of people in my company still use Excel (and PowerPoint) is that there are a number of plugins that aren’t yet available for Google Sheets and Slides. Examples include the Smart View plugin for Oracle’s Hyperion tool and the @RISK plugin that I demonstrated in a guide to Monte Carlo simulations. Similarly, PowerPoint’s think-cell plugin offers chart functionality that can be superior to what is possible in Slides.

Nevertheless, Google Sheets and Slides have wide-ranging functionality named “Add-ons,” which fills a similar role to the plugins of the Microsoft world. Unfortunately, as of July 2019, there is no support for the enterprise applications mentioned above and the homebrew nature of the add-on ecosystem means that some of the offerings have questionable quality. This means that within a corporate context, when working with sensitive data, I am at times hesitant to use add-ons.

7. Connecting to External Data Sources

Getting data into a spreadsheet to work with is a fundamental need, and without some of the corporate plugins being unavailable, what is then possible? Well, fortunately, there are many options. Aside from having backward compatibility with Excel files, Sheets offers endless possibilities through XML, HTML and RSS queries.

There are a few more interesting options worth mentioning specifically for harnessing data in Sheets. For finance professionals, the GOOGLEFINANCE formula can be useful to learn about, which allows you to pull data directly from Google Finance. Although not comparable to a professional service such as Bloomberg, it is nevertheless an excellent tool for public stock and currency information.

There are other, more general ways to get data from the web. The functionality to extract an HTML table or list directly from a website can, for example, be useful when it comes to financial, market, or other information publicly available that you want to retrieve and continue working with.

Aside from Google BigQuery (discussed previously), there is—as of writing—no simple way for the non-technical user to connect directly to a database, although the functionality is available for those with access to developer skills and resources.

GOOGLE APPS SCRIPT

This does, however, brings us to Google Apps Script, a powerful tool that opens almost unlimited possibilities to extend and connect not only Sheets but all parts of the G Suite. In a future article, I will go into this functionality in more detail through a practical introduction tutorial.

So, Is There a Catch?

I have been evangelizing Google Sheets and Slides in this article. However, not everyone in my organization feels the same! And I think that is one of its drawbacks: The lack of consensus about a format, be it in software or even everyday life (think 8-track vs. cassette tapes), ultimately dilutes the overall effect. In the case of desktop software, the more that people use a certain format and understand its true powers, the more benefit and efficiency is gained for everyone in the organization.

Google Sheets, like any software, has its small annoyances: a keyboard shortcut that isn’t what you would expect, a feature that is missing or doesn’t behave the way you want, etc. In many cases though, this is also an unfair comparison. In corporate and business settings, many people have been using the Microsoft productivity suite throughout their working lives, an amount of experience that cannot be compared to the first months (or even years) of working with a new set of tools.

Collaboration Is Key

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! We spend hundreds, if not thousands, of hours using them over the course of a career. Thus, as with any work tool and environment, we need our experiences to, therefore, be pleasant and efficient.

In summary, I have seen huge productivity benefits from the collaboration features and other innovations that come with using web-based productivity applications like G Suite over their traditional desktop counterparts. There are also immense benefits from learning to use these tools past the elementary level, as I have demonstrated in this article with productivity-enhancing aspects.

Google Sheets’ advantages, multiplied across the huge number of hours spent with these tools, can add up to tremendous gains, benefitting both the individual and the organization. As mentioned, I will be writing another article that will go into detail about macros and scripts in G Suite. These are performed through the Google Apps Script language and, as I will demonstrate, offer a seemingly infinite number of ways to automate and streamline processes.

So, if you haven’t worked seriously with Google Sheets yet, I highly recommend that you take it for a spin. You might find yourself hooked.

UNDERSTANDING THE BASICS

• Is Google sheets the same as Excel ?

As a spreadsheet software application, Google Sheets has many similar functionalities to Excel. Its main differentiator though, is that it is web-browser based and it uses Google Apps Script language for macros (instead of Visual Basic).

• What are the advantages of Google Sheets ?

Google Sheets’ cloud-based interface makes it very accessible for collaborative exercises and tracking version histories, without the need for separate files.