Creating spreadsheets just barely scratches the surface when it comes to all the cool things you can do with Google Sheets.
Whether you're new to Google Sheets or you're looking for new ways to make the most of it, there’s a lot more you can get out of the seemingly simple app than you think.
The best part is that you don’t need to be a tech guru to make Google Sheets one of the most powerful analysis tools in your deck. With the right formulas, you can extract genuinely meaningful insights for your business and manipulate data to make better decisions.
Let’s dive into the formulas and hacks that’ll make your life easier, help you find out exactly what you need and shave hours off your workload.
You can use Google Sheets formulas to do a wide variety of things, from simple counting to more complicated equations. Whether you're managing your inventory or keeping track of a list of contacts, here are some of the most valuable formulas that you can use.
Sometimes you need to count how many cell values meet a certain criteria. Of course, you could count them manually, but this is time-consuming, and you could easily miss some cells (especially if you’re dealing with large data sets).
Using the COUNTIF formula, you can specify the range that you want to search and the criteria that you want to search by.
Formula: =COUNTIF(range, criterion)
For example, you might be looking through a list of contacts for people whose job title includes the word executive. If job titles are listed in column B, your formula might look something like =COUNTIF(B2:B500, "executive").
If you were looking for items in your inventory that had sold fewer than 10 units, and unit numbers were listed in column E, you might write =COUNTIF(E2:E500, "<10").
It's a simple formula that does a fairly simple job, but it's still a useful formulas for when you literally need to count the frequency of a value from a large database in a matter of seconds.
When you use a formula for a large database, you often don't want it to apply to only a single cell. When you need it to apply to more than one cell, one option is to drag it to copy it into other cells, and the formula will adjust to match the new cells.
However, there can be issues with this approach, as it essentially creates a new formula in every cell. Firstly, this can cause the sheet to load and process slower if you're using a lot of data, and secondly, it can mean that you need to change each formula individually if you want to make adjustments.
An array formula can fix both of these problems. With just the one formula, you can have your results in multiple rows and columns. If you decide that you want to make any changes later, your changes will apply to all of your data.
For example, you might want to subtract the cells in one column from the cells in another, then add the totals together. Instead of doing this using two different formulas, you can use an array formula.
Your formula could look like this: =ARRAYFORMULA(SUM(A2:A5-C2:C5)
The SUM part of the formula is like a standard formula, but the ARRAYFORMULA allows you to calculate a range instead of a single cell.
While Google Sheets is a powerful tool on it’s own, it reaches another level of sophistication and usefulness when coupled with some of the amazing integrations that it supports.
The Google Sheets integration with Paperform is a great example of this. If you’re looking to collect data of any kind - whether that’s information, online orders, client responses, survey responses, contact information or more, Paperform is an easy way to create a quick form and capture this information.
The fun part of integrating Paperform with Google Sheets is that the collected data will then automatically be sent to Google Sheets the second it is submitted. This will not only save you a tonne of time when managing data, it will also help you create a repository of important information that’s ready to analyze.
Here’s how this works:
So that’s how submissions into a Paperform form like this:
Can turn into an automated entry in your spreadsheet, like this:
Anyone running a small business is all too familiar with the plague of having too many different spreadsheets for different things.
If you’re looking to quickly move data from one spreadsheet to another, the IMPORTRANGE function is your friend. This formula makes it easy to import data from one Google Sheets spreadsheet into another one.
Formula: =IMPORTRANGE(“spreadsheet_url”, “range_string”)
The “spreadsheet_URL” in this formula is the spreadsheet’s key, highlighted here:
Meanwhile, the “range_string” should first define the name of the sheet that is being imported from (eg. “Sheet 1”), and then declare the range of data being imported. Here’s a sample formula:
Using this simple formula, you can simply add the data that you want from another spreadsheet, without wasting time copying and pasting.
Using the formula above, I was able to quickly import a spreadsheet of customer names to the customer orders spreadsheet:
The SEARCH formula lets you find a value in a string if it exists. You might want to search for a particular phrase in the data that you have. Perhaps you're looking for indications that you have the right information or that you're getting the results that you want.
You would use the formula: =IF(SEARCH("search phrase",B2), "YES") if you want to search column B from cell B2 onwards. Put the formula into the top of a different column (such as C2) and drag it down to copy it into the rest of the column. When the phrase you're looking for is found, you should see a YES in the column where you have typed the formula.
This is a particularly useful feature for large spreadsheets with lots of data. Instead of wading through the spreadsheet to find patterns, you can use a filter to quickly group together and segment content based on the frequency of a cell value.
Just click the ‘Filter’ button on the top right hand side menu and create a filter view for each of your columns in seconds. Here’s how I created a filter to quickly divide my spreadsheet based on the region of orders:
If spreadsheets aren't your idea of a fun Friday night, then hold your breath because these Google Sheets features are bound to make you reconsider your stance.
There are some incredibly cool things that you can do with the platform, which are both useful and can just make your sheets look better.
Being able to view your data more visually doesn't just make your sheets look good. It also makes it easier to find the information you need and take it in. Heat maps are a great way of highlighting certain values and seeing important data with a glance. You can add a color scale using conditional formatting so you can easily see higher and lower values.
When you select Format>Conditional Formatting, you can create a color scale rule and apply it to the range that you want to use.
Here’s how I made it easier to sense the popularity of a product by creating a heatmap that highlights the quantity ordered for each through colours. The darker the colour, the more popular the product.
It wouldn’t be a Google tool if you couldn’t ask it questions, right? Google Sheets has an incredibly powerful feature that allows you to ask it questions and extract data based on your spreadsheet.
By using the ‘Explore’ button on the bottom right hand corner of your spreadsheet, you can quickly get answers to questions like “Which rep sold the most aggregate units?” in a matter of seconds. The Sheet uses AI to quickly calculate a sum of each rep’s sold units and feed through an answer. Here’s how this looks:
You can even drag and drop the pivot table and chart it produces to any point in your spreadsheet.
As you know, Google has plenty of different apps for doing various tasks. This includes Google Translate, which helps you to translate languages. It might not be perfect, but it can often provide a fairly accurate translation, especially if you're translating something back into English.
When you're using Google Sheets, you can translate something in your sheet using the GOOGLETRANSLATE function. It might look something like this:
=GOOGLETRANSLATE("je ne sais quoi", "fr", "en")
You can point the formula toward a cell that contains text so that you don't have to separately copy and paste text into Google Translate. Here’s the formula in action:
We’re sure you’re now inspired by the power of Google Sheets and it’s integrations to start doing the coolest things with data.
Not sure how to get started? The first step is collecting useful data to power better decisions. Start collecting the data you need with Paperform in as little as 3 minutes.
Business process automation is the use of technology and tools to automate repetitive tasks to save both time and money. Here are some automation proc...
Completely removing response bias from your surveys is next to impossible, but taking the right steps to reduce it can be the point of difference betw...
We asked the Senior UX Designer at Fox Sports Australia some big questions on getting your first break as a rookie, standing out in a saturated space ...