The 9 Coolest Google Sheets Formulas & Hacks for 2019

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 a useful formulas for when you literally 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 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:

Once you create a form with questions and fields to capture information using Paperform, click on After Submissions > Integrations & Webhooks. Here’s you’ll find Google Sheets as one of the 1000+ in-app integration options.

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.

So that’s how submissions into a Paperform form like this:

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:

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

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