The 9 Coolest Google Sheets Formulas & Hacks for 2021

/ no-code hacks / 7 min read
Vrinda Singh

Table of Contents

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.

Extracting Meaningful Insights With Google Sheets Formulas

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.

The COUNTIF Conditional Counting Formula

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").

The COUNTIF formula in action

It's a simple formula that does a fairly simple job, but it's still useful for when you need to count the frequency of a value from a large database in a matter of seconds.

Array Formula

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)
Here you can see that the array formula helps us calculate the total revenue made from orders 1 to 20, adjusted for the cost from offering discounts.

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.

Integrate it With Powerful Apps

While Google Sheets is a powerful tool on its 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 collect data.

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:

  1. Once you create a form with questions and fields to capture information using Paperform, click on After Submissions > Integrations & Web-hooks. Here’s you’ll find Google Sheets as one of the 1000+ in-app integration options.
  2. After selecting Google Sheets, you’ll be able to automate the addition of responses to any of your spreadsheets. This means you’ll never have to manually update or manage a Google Sheet again.

You can turn into an automated entry in your spreadsheet, like this:


IMPORTRANGE

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:

=IMPORTRANGE("1KZzWGj0ICWxd0cl4iVDHOTQdB5g5-2G7RnTT8a3ynXY","Sheet1!A1:B10")

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:


SEARCH Function

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.


Use Filters to Get a Targeted View of Your Spreadsheet

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:

3 Fun Things To Do With Google Sheets Formulas

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. 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.

Use Conditional Formatting to Make Heat Maps

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 heat map that highlights the quantity ordered for each through colours. The darker the colour, the more popular the product.


Ask It Questions

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.

Translate Languages

As you know, Google has plenty of different apps for doing various tasks, from notes to website analytics. 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 its 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.


About the author
Vrinda Singh
Vrinda is the Growth Manager at Paperform. In her spare time, she loves learning all things marketing, design & automation-related, and NOT watching reality TV. No, not at all... vrinda@paperform.co

Try Paperform Now!

14 Day Free Trial, No CC Required

The Top Causes of Shopping Cart Abandonment (And How to Combat Them)

Almost 70% of online shopping carts are abandoned. In this guide we look at the reasons behind this scary stat, and how your business can combat them....

Nicholas Shaw

How to Segment Your Audience and Generate Sales with a Quiz Funnel

In this guide we show how, a quiz funnel can help you capture more leads, make more sales and better understand your customers.

Allie Decker

10 Local Marketing Strategies for Your Small Business

In this guide, we take a look at cheap ways your small business can get the word out about your products and services in your local area.

Meaghan Lehrer

Introducing Our New Dashboard

Organise, manage and build forms in beautiful new ways with our updated dashboard.

Paperform