18 Google Sheets formulas to simplify data management

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.

What are Google Sheets formulas?

From formulas that perform basic addition, to more complex formulas that hold multiple conditions, Google Sheets comes with an extensive list of ways to manipulate your data.

You build formulas by using an equals sign and then defining the rule. A handy menu appears to guide you through what you need to do.

Formulas can be static and unchanging or can adapt to data. A static formula contains all the information within itself. To understand the difference, let’s look at one of the simplest formulas: =SUM.

We can use =SUM to calculate the sum of a series of numbers. Type in =SUM(1,2), press enter, and you’ll see the cell display its calculation. No need to get out the calculator.

But formulas are most useful when they’re dynamic. By pointing the values in the formula at other cells, the calculation adapts when the data within those cells changes.

This means that your formulas stay relevant as you work. Rather than finding the sum of 1 and 2, this way you can find the sum of whatever value is in cells B2 to B9, even if they change. Take the following budget breakdown for example.

This simple =SUM formula makes calculating a budget infinitely easier. When the cost of something changes or you need to add another item to the list, the calculated total will also change.

Many of the tips we’ve compiled in this post are formulas, while others make use of menus, shortcuts and other functionality.

1. Inputting data

Blank spreadsheets never stay blank for long. Inputting data is the first challenge you come up against when you open your document. The following formulas and tricks help fill out your sheets.

Importing from other spreadsheets

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 makes it easy to import data from one Google Sheets spreadsheet into another one.

=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 the range of data being imported. Here’s a sample formula:

=IMPORTRANGE("1-us8lMIEkn3I7YsHs1poPLS_gR8OjpS6VlBMYOZqVjc","Sheet9!A2:B9")

Using this simple formula, you can add the data that you want from another spreadsheet, without wasting time copying and pasting.

Because it’s a dynamic formula, =IMPORTRANGE allows you to keep your spreadsheets up to date. If you change something in the original spreadsheet, the changes are automatically reflected in the cells you’ve imported.

Dragging cell data

Google is pretty great at picking up patterns. You can use this to your advantage when you want to input predictable data.

Whenever you highlight a cell or range of cells, there is a blue square in the bottom right corner of your selection. By clicking this and dragging it to new rows, you copy the specific values of the cell you have highlighted.

Not only does this copy data, you can also use it to anticipate data. If you highlight three sequential values and drag them down, Google will automatically continue the pattern you have established.

This can also be used to copy Google Sheets functions. For example, imagine you want to calculate the number of characters in a series of cells using the =LEN function.

You might do this to make sure that your website URLs aren’t too long. You can apply the formula to your first cell, and then drag it down so that it responds to each cell of data.

Each formula references the cell in its corresponding row, copying the format of the original formula.

Adding timestamps

There are a lot of handy keyboard shortcuts in Google Sheets, and the timestamp ones are definitely among the most useful.

When using Google Sheets as a task management tool, it can be useful to record when you’ve completed something.

Sure, you can always manually input the date and time, but that’s pretty tedious, especially if you have to do it over and over. It’s much easier to hit a couple of keys.

• Date: Command/Ctrl + ;
• Time: Command/Ctrl + Shift + :

The format of your time and date is set automatically by your locale. You can change these settings by navigating to Format > Number and then selecting “Date” or “Time”.

These shortcuts not only save time, but also keep reporting precise. You can’t beat that.

Integrating with other apps

While Google Sheets is a powerful tool on its own, it reaches another level of sophistication and usefulness when integrated with other apps.

The Google Sheets integration with Paperform is a great example. If you’re looking to collect data of any kind—whether that’s online orders, client responses, survey responses, contact information or more, Paperform does it with style.

When you integrate Paperform with Google Sheets, your collected data is automatically sent to Google Sheets the second it is submitted. This will save you a ton of time when it comes to managing data.

It also means that when it comes time to analyse that data, all the information gathered in your forms has been automatically organised in a spreadsheet and is ready to go.

2. Data analysis

Once you have inputted your data, you have to work with it. Most of the formulas in Google Sheets are used to analyse blocks of data. Here are some of the most useful ones.

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 the formula 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 range of 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 means 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 one formula, you can create a calculation that spans multiple rows and columns. If you decide that you want to make any changes later, your changes will apply to all of your data.

=ARRAYFORMULA(array_formula)

For example, you might want to subtract the cells in one column from the cells in another, and then add the totals together. Instead of doing this using two different formulas, you can use an array formula.

=ARRAYFORMULA(SUM(C2:C9-D2:D9))

Here you can see that the array formula helps us calculate the total revenue made from these orders, adjusted for the cost of offering discounts.

The SUM part of the formula is a standard formula, but the ARRAYFORMULA allows you to calculate a range instead of a single cell.

Conditional counting

Sometimes you need to count how many cell values meet 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.

=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 A, your formula might look something like =COUNTIF(A2:A500, "executive").

Or, 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, but it's still useful when you need to count the frequency of a value from a large database in a matter of seconds.

Finding data

Spreadsheets can get big, and this can make finding information in them tricky. Sometimes it’s useful to simplify your data by grabbing bits and pieces.

The =VLOOKUP formula finds the data you specify, and then returns information from a cell in the same row, from a chosen column. You might use it to look up an item’s price by searching for the item’s name.

VLOOKUP(search_key, range, index, [is_sorted])

The search_key can either refer to a cell (“F3”) or a static piece of data (“Row counter”) that you’re looking for. The range is the area that the formula looks within, and the index refers to which column of information you want to see.

Is_sorted refers to whether your search key must be exact (“FALSE”), or if you want the closest match (“TRUE”).

Ask 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 by generating a pivot table. Here’s how this looks:

Pivot tables allow you to simplify and summarise your data. They work in Google Sheets in much the same way as they do in Microsoft Excel—basically, they group values together so that you can view your data through a chosen lens.

The Explore function lets you create them in a more conversational way, so you know exactly what questions the table is answering.

Google even comes up with some questions you might be interested in, which can guide your analysis. You can also quickly apply attractive theming to your tables from this menu.

Translate languages

The internet has opened up possibilities to work with people from all over the world. One of the ways Google has made this easier is through its Google Translate app, 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.

Google has built-in a formula that means you can make use of the Google Translate app without leaving your spreadsheet. =GOOGLETRANSLATE("text", "source_language", "target_language")

The text part of the code can either point to a cell, or you can type in the words you want to translate directly into the formula.

In this example, the text points to cell I9, the source_language is in German (initials “de”) and we’re translating it to the target_language of English (initials “en”).

If you’re unsure what language is being used, for example when a customer has filled out a feedback form without indicating their language, you can use =DETECTLANGUAGE(“text or range”) to get the two-letter initials that correspond to the language.

3. Formatting and appearance

The way you present your data helps it be more understandable. There’s nothing more satisfying than a well-organised spreadsheet. These tips make formatting your Sheets easy.

Clean up data

Let’s say you’ve made the perfect customer feedback form and hooked it up with Google Sheets so that you can analyse your data.

When the results come in, inevitably people will fill it out with strange capitalisation, or with weird spaces where there shouldn’t be. Don’t pull out your hair just yet, we can make everything look beautiful in no time.

Our first column is looking a little rough. First, we can get rid of the spaces by using the =TRIM function, then pointing it at cell A2. By clicking the blue square in the bottom right corner of the highlighted box, we can then drag the formula down to apply to each cell.

Next, we can use =PROPER to make each word capitalised. By pointing =PROPER at cell B2, you can make sure that the changes are made to the text that’s already been trimmed. =UPPER can be used if you’d like the words to be entirely capitalised, and =LOWER can be used to make the words lowercase.

If you’d like to have one column for first names and a second column for last names, we can use the =SPLIT function to divide the content of a cell. Here, the delimiter we’ve split the content around is a blank space.

Sort data

It can be handy to sort your data for all kinds of reasons. The =SORT function allows you to sort your data in either ascending or descending order. You can even sort by multiple columns.

=SORT(range, column_index, ascending_order). Here you can see we have a list of employees. The =SORT formula lets us organise them by department, then last name.

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 colour scale using conditional formatting so you can easily see higher and lower values.

When you select Format > Conditional Formatting, you can create a colour scale rule and apply it to the range that you want to use.

You can make it easier to sense the popularity of a product by creating a heat map that highlights the quantity ordered for each through colours. In this example, a product with plenty in stock is green, while one that has run out is red.

4. Collaboration

The major advantage of the Google Suite is how easy it is to work as a team. The following tips are useful when you want to collaborate with your coworkers.

Share Sheets

Sharing Google Sheets with your team is as simple as clicking the 'Share' button at the top-right corner of the screen.

It works in much the same way as it does in Google Docs. You can share with individuals by typing in their email addresses or create a link that anyone can use.

There are three levels of sharing: Viewer, Commenter and Editor. Viewers and Commenters can’t change the data in a spreadsheet at all, which is handy for when you want to show off your stuff without the risk of someone else swooping in and changing it.

Protect your data

It can be risky to give people Editor access to your Sheets. There are a few ways you can make sure that your data is protected from other users’ clumsy keyboards.

One method is to single out sheets or ranges and restrict who can edit them, or set it up so that a warning pops up whenever an edit is made.

You do this by navigating to 'Data > Protect sheets and ranges'. From there, a side menu allows you to view all the protected sheets and ranges.

You can also set up notification rules via the Tools menu so that you receive an email when changes are made.

Another way to prevent accidental edits is to set data validation, which you can find in the Data menu. This means that only certain kinds of data can be inputted into the cell, so people can’t accidentally add dates into a row dedicated to names.

Comments and notes

Sometimes you want to be able to add extra information to a specific cell. There are two ways to do this, depending on whether or not you want to attribute the information to a user or not.

If you right-click on a cell and add a “Note”, that information will show as a black triangle in the upper-right corner of the cell and will appear when you hover over the cell.

You might do this so that the information is associated with the sheet itself, rather than a specific user.

The other way you can add information is through comments. These are attached to your Google account and have the added functionality of replies.

Comments are indicated with a yellow arrow in the top-right corner of the cell, as well as in the Sheet tab. You can view all comments across a sheet from the Comments menu in the top-right corner of Google Sheets.

Comments allow for communication between multiple users of a sheet. You can reply to comments, and even use the @ symbol to notify other users of your comment. Comments are compiled in a side menu, so you never miss out on the conversation.

5. Bonus hacks

If spreadsheets aren't your idea of a fun Friday night, these Google Sheets features are bound to make you reconsider. There are some incredibly cool things that you can do with the platform, which take it beyond the workplace.

Create works of art

Pixel art is a form of digital art where images are created by colouring individual pixels. It’s how early computer games were forced to render their art, and many modern ones continue to use the style. All you need is a grid and the ability to block colours in it, which Google Sheets easily delivers.

Fair Isle knitting works under the same principles as pixel art, where the crafter creates pictures by changing the individual stitches in a project. Because of this, Google Sheets is also the program of choice for some knitting, cross-stitch and other textile enthusiasts to plan their creations.

Celebrate pride

Google Sheets gained an Easter egg in 2019 to celebrate Pride Month. When you type out the word “PRIDE” across five columns, the Sheet transforms to have the colours of the rainbow flag. In 2022, it updated to the colours of the Progress Pride flag, which explicitly supports transgender people, people of colour, and AIDS victims.

Play tic tac toe

Because you can share documents, you can actually use Sheets to play grid-based games like chess, connect 4, and tic tac toe.

As anyone who has ever sat next to a friend in a boring class knows, all you need to play tic tac toe is a simple grid. Highlight a 3 x 3 lot of cells, and from within the Borders menu, choose Inner borders. Then resize the rows and columns of the grid so that it’s an even size.

To make it a little more visually interesting, let’s assign colours to our players. By using conditional formatting, we can set the grid up so that when an X is placed in the grid, the cell turns red, and when an O is placed, the cell turns blue.

We can also merge some cells below the grid, so that the players have a space to talk to each other.

Now that the grid is set up, all that’s left to do is share the Sheet with a friend. You’re ready to play.

Over to you

Google Sheets is one of the most useful and versatile apps in the Google Suite. By now, you’ve learned a boatload of new tricks and tips to make a lot more than just your standard spreadsheet.

And these are only the beginning—armed with your newfound appreciation for the versatility of Google Sheets, you can let your imagination run wild finding even more ways to use it.

Not sure how to get started? The first step is collecting useful data to power better decisions. With Paperform you can create stunning forms that integrate directly with Google Sheets, so you’ll always have something to play with. You can give it a try for free for 14 days, with no credit card required.