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 besides provide an introduction to Google Apps Script, a knock-down tool for automating workflows and extending Google Sheets ’ functionality. In a late article, I will go deeper into Google Apps Script and demonstrate a tutorial for getting started .
I won ’ triiodothyronine, however, be giving a sport comparison between Google Sheets and Excel ; their features change so quickly that such analysis would become excess within weeks. rather, I will focus on Google Sheets ’ advantages and the new ways of working that the emergence of on-line 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 like way that many of us learned over 10 years ago in the pre-cloud era. I have worked with many people who, evening though they may have switched to Google Sheets, even use it in the old offline Excel mold, meaning that they lose out on a issue of productivity-enhancing features .
In addition to listing the advantages, I will besides touch on some of the negative aspects I have experienced in this transfer. There are very only two that stand out : One is that many of the benefits depend on collaboration with others, and hera you will find that the odd consumption 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 raw ways. The “ old expressive style ” of working would be using a dominate file that person has to “ own, ” which is then ( in the best case ) kept on a shared net booklet, or painstakingly emailed around .
here is where Sheets very shines. There are several different modes of collaborate, ranging from the asynchronous where you work independently and by and large at different times, in the same file, to real-time coincident collaborative edit. With asynchronous editing, you can use the gossip feature to alert people and assign them tasks that they can then can mark as completed .
Editing a spreadsheet know with other colleagues is a potent way of working. The productiveness of two or three people who are all highly skilled with Google Sheets quickly building out a fiscal or operating exemplar together in real time is actually a sight compared to the old way of working. The multiple cursors in different colors, moving across the screen at the same clock to build a exemplary, is about like a time-lapse video of a painting .
There is besides room for unlike types of participants. not everyone needs to be able to edit a spreadsheet. There are levels of access rights, which ensures that security and descendants is kept in check by granting appropriate people the right floor 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 boost calculator—but not useful for larger models or datasets. Turns out we were wrong. I ’ ve used it for a number of larger function and evaluation models over the past years and am identical impress with the performance .
I recently did some analysis work on data extracted from our ERP organization, 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 early 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 calculator, which is far from instantaneous, but not a hindrance, ascribable to :
- 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.
- 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 solve ( “ wrangling ” ) is an important region of the finance professional ’ randomness daily liveliness, but even the best psychoanalysis is of limited value if you are unable to communicate your findings in a cogent and compel way. This brings us to two early staples of the finance toolbox : charts and presentations .
Presentations in the G Suite universe 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 constantly up to date through an “ Update ” button that pulls up the latest datum of the underlying mold, calculations, and assumptions .
Update functionality allows charts ’ data to be refreshed while maintaining formatting consistency .
When starting any large project now, one of the inaugural things I do is create two documents : one in Google Slides and the other in Google Sheets. These go bridge player in handwriting cleanly throughout the project, providing that “ one generator of truth ” for all data in the project .
4. Version Control
If you have always had the painful feel of a spreadsheet crash beyond recovery, resulting in hours of lost work, you might have a break habit of saving modern files frequently. This can end up in numerous iterations of files with long-winded interpretation updates ( v3.4.0, v3.4.1, etc. )
Through G Suite products, it actually is potential to equitable have one file throughout the life sentence of the stick out, thanks to adaptation manipulate .
Version operate, accessible through “ See adaptation history ” in the menu, allows you to see all edits made to a document since the identical beginning through a handy timeline, along with the appoint of the person who made each switch. That ’ south right—everything is saved, immediately. No need to press F12 every 5-10 minutes, increment the version number, and save, just to have peace of thinker knowing that nothing is lost in sheath of a crash.
Read more: Google drive
5. Linking Between Sheets in Different Files
obscure productivity makes link functionality far more elegant and ensures that it actually works ! It is potential to reference early 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 person ’ s hard drive : C:\users\smithj\projects\omega\valuation\ .
The IMPORTRANGE function in Google Sheets allows you to link seamlessly to early Sheets files. Irrespective of whether the file gets moved or renamed, the data will constantly be linked and remain one less trouble for you to worry about .
6. Working with Plugins
One cause why a number of people in my company still use Excel ( and PowerPoint ) is that there are a number of plugins that aren ’ thyroxine so far available for Google Sheets and Slides. Examples include the Smart View plugin for Oracle ’ s Hyperion creature 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 superscript to what is potential in Slides .
however, Google Sheets and Slides have varied functionality named “ Add-ons, ” which fills a like role to the plugins of the Microsoft world. unfortunately, as of July 2019, there is no defend for the enterprise applications mentioned above and the home brew nature of the addition ecosystem means that some of the offerings have questionable timbre. This means that within a bodied context, when working with medium data, I am at times hesitant to use add-ons .
The Add-ons Store has an accessible interface for discovering the versatile plug-ins on crack .
7. Connecting to External Data Sources
Getting data into a spreadsheet to work with is a fundamental motivation, and without some of the corporate plugins being unavailable, what is then possible ? Well, fortunately, there are many options. away from having backward compatibility with Excel files, Sheets offers endless possibilities through XML, HTML and RSS queries .
There are a few more concern options worth mentioning specifically for harnessing data in Sheets. For finance professionals, the GOOGLEFINANCE rule can be useful to learn about, which allows you to pull data immediately from Google Finance. Although not comparable to a professional service such as Flickroomberg, it is however an excellent tool for public stock certificate and currency information .
Using the GOOGLEFINANCE convention to extract historical stock price information
There are other, more general ways to get data from the web. The functionality to extract an HTML mesa or tilt immediately from a web site can, for exercise, be utilitarian when it comes to fiscal, 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 means for the non-technical exploiter to connect immediately to a database, although the functionality is available for those with entree to developer skills and resources .
Google Apps Script
This does, however, brings us to Google Apps Script, a powerful tool that opens about outright possibilities to extend and connect not entirely Sheets but all parts of the G Suite. In a future article, I will go into this functionality in more detail through a virtual presentation 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 impression. In the case of background software, the more that people use a certain format and understand its truthful powers, the more benefit and efficiency is gained for everyone in the organization .
Google Sheets, like any software, has its minor annoyances : a keyboard shortcut that isn ’ t what you would expect, a sport that is missing or doesn ’ t behave the way you want, etc. In many cases though, this is besides an unfair comparison. In corporate and business settings, many people have been using the Microsoft productiveness suite throughout their knead lives, an sum of experience that can not be compared to the first months ( or even years ) of working with a new typeset of tools .
Collaboration Is Key
Spreadsheets are one of the most important tools, if not the most crucial cock, for many finance and commercial enterprise 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 make creature and environment, we need our experiences to, therefore, be pleasant and effective.
In drumhead, I have seen huge productivity benefits from the collaboration features and other innovations that come with using web-based productiveness applications like G Suite over their traditional desktop counterparts. There are besides huge 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 enormous gains, benefitting both the individual and the organization. As note, 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 speech and, as I will demonstrate, offer a apparently infinite number of ways to automate and streamline processes .
so, if you haven ’ deoxythymidine monophosphate worked seriously with Google Sheets yet, I highly recommend that you take it for a spin. You might find yourself aquiline .
