When moving organisations to the cloud, we often find the biggest pushback against Google Workspace (formerly G Suite) is the change from Excel to Google Sheets. As a long time user of both Excel and Google Sheets, I can comfortably say that for most applications and user requirements, Sheets is just as capable, and in many occasions more capable than Excel. Ben Collins (LinkedIn) summarises this brilliantly in his “Excel2Sheets” blog:
Fun Fact: Sheets actually has more formulas than Excel - this is due to the fact that Sheets is an online tool, and thus it can tap into sources of information that would otherwise be impossible to get such as live stock tickers, or live currency exchange information. The full list of formulas can be found here.
Aside from this, Sheets has powerful collaboration tools and an ever-increasing number of data manipulation techniques available for your organisation to use.
Functions and Formulas
A lot of people that I’ve trained are shocked to find that Sheets has a VLOOKUP function; it not only has VLOOKUP functionality, but also complex statistical analysis formulas such as LINEST and FORECAST. Aside from this, there is a list of functions that Google provides such as GOOGLEFINANCE for tracking live stock data or GOOGLETRANSLATE for translating content within your spreadsheet. If you’re a power user, the QUERY function may be of interest to you - it’s great for finding information within huge datasets using an SQL-like query language.
Collaboration
Google Workspace (G Suite) inherently comes with collaboration tools built in, and Sheets is no exception. You may have up to 100 people collaborating on a single Sheet at a time, and giving different levels of access to your colleagues is extremely simple. The access levels are as follows:
- View Access: Give a colleague or client view access so they can see the data in the sheet, but cannot comment or make any edits.
- Comment Access: This allows a viewer to make comments within a cell. This is great for someone that may have an input towards the spreadsheet, but shouldn’t have complete edit access.
- Edit Access: As the name says, this allows the user to edit the sheet just as if they were the owner.
Hidden collaboration features
Protected ranges
Within sheets, you can lock down a specific range of cells for edit access using the user's Mail address. In this example sheet, I may only want a specific subset of users to be able to change the address of my clients, and so I can protect the range for that set of users.
- Select your range
- Right Click
- Protect Range
- Add a description
- Select the users that you want to be able to edit the range.
Sheet Notifications
We all have a Spreadsheet or two that we don’t like people tampering with, but sometimes someone has to edit a value within your precious sheet. We can give them edit access, but to keep a closer eye at what they do, you can enable notifications to let you know whenever someone has made an edit.
- Go to Tools
- Notification Rules
- Set the notifications that you wish to receive
You have the option to receive a daily ‘digest’ - a collection of events that happened to the sheet within the day, or you may ask to receive a notification instantly. Note that this may end up clogging up your inbox if too many people trigger the notification!
Explore - Using AI to parse your data
The explore tab within sheets is extremely useful. It allows you to ‘pluck’ out metrics from your dataset without thinking about what metrics you’d like to see in the first place. Here, a footballer dataset was used to ‘pluck’ out the metric regarding player height.
There were other metrics available from the explore page, but you’re also able to query the data using natural language, for example, you may ask “What’s the average wage based on preferred foot?”, here we find that footballers actually get paid the same on average - no matter what foot they use to kick the ball 🤔.
Google Apps Script (GAS)
Google apps script is an extension of Google Workspace (G Suite) that gives easy access to Workspace's API’s (Application Programming Interfaces). This, coupled with Google Sheets allows you to make powerful apps within your Workspace environment. These apps can extend the functionality of sheets, such as being able to list the current people working on a certain file, or generating a custom set of folders within Google Drive. GAS is great for filling in the gaps in your workflows and makes long, tedious tasks manageable.
If you wish to learn more about Google Apps Script, check out our page on it here.
Google Sheets Training
Cobry provide advanced Google Sheets training, along with our standard Google Workspace (G Suite) training sessions which you can find out more about here.
If you're interested in providing your team with some training on any area of Google Workspace, please don't hesitate to book a discovery call or get in touch via the details below.
