Google Sheets Formulas Cheat Sheet for Savvy Business Owners *

AD | As a business owner, did you know that using Google Sheets means you can access various formulas to make your life easier? And that many of these spreadsheet functions will help you arrange, edit, look up and analyse all kinds of information that’s invaluable to running your business successfully. Here are the top 6 useful spreadsheet formulas you need to know, collated into one handy Google Sheets formulas cheat sheet.

The best Google Sheets formulas cheat sheet for business owners

Many business owners don’t keep close track of their sales performance, so it’s nearly impossible to forecast their sales for the week or month ahead. As the old adage goes, “If you can’t measure it, you can’t control it,” and that can lead to feelings of overwhelm and stress. You need to keep track of your sales metrics, which is key to improving your sales performance. Try these top tips to help you make sense of your data and achieve your overall business goals.

Separate data into multiple cells using the Split() function

Sometimes, you may want to extract data from one cell and divide it into several cells. This can be accomplished through this split formula.

SPLIT(text, delimiter, [split_by_each], [remove_empty_text])

The text is the copy you want to split and the delimiter character(s) will be used to divide the text.

You could make use of this feature if you have an alphabetical list of names; however, the first and last names aren’t separated. The split formula simply puts every first name and last name in appropriate columns.

Import information using IMPORTRANGE()

Sometimes, you may need to transfer data from one document to another. The IMPORTRANGE formula lets users transfer a certain set of cells from different worksheets into a new one.

IMPORTRANGE(spreadsheet_url, range_string)

The formula could appear complex, particularly when you see the lengthy spreadsheet URL, but it’s really quite simple. All you need is the spreadsheet’s URL and cells of information you wish to import.

Here’s an example:

IMPORTRANGE(“https://docs.google.com/spreadsheets/x/ 1ewe0OPLHUAlo-RIOLS829sa/edit#gid=1023456789”, “Sheet3!B1:D15”)

Note that quotes contain both the spreadsheet URL and range. This is because Google Sheets treats both arguments as strings. There is also an exclamation mark in between the sheet’s name (Sheet3) and the cell range (B1:D15), which you need to include, so as to distinguish the two separate names.

Add numbers using SUMIF() in a range of cells

For a single criterion, use this: SUMIF(range, criterion, [sum_range])

For multiple criteria, use this: SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, …])

This formula can be used to calculate the total of cells based on the conditions in the range.

The best way to use the formula would be to multiply the costs in certain areas while excluding other categories.

For example, you could use this formula to examine your budget. There may be an inventory of items under categories such as Electronics, Movies, Books and if you just need to know how much you’ve paid for Movies, you could use this formula:

=SUMIF(D1:D10, movies, E1:E100)

The D1:D10 spectrum represents all different categories (Electronics, Movies, Books), and the E1:E100 range covers all prices. When you include the term movies in the formula, Google Sheets only pulls the movie prices from both columns.

Use advanced functionality to find and highlight duplicates in Google Sheets

Here’s my favourite tip on this useful Google Sheets formulas cheat sheet, it’s a formula I use ALL the time. When you make a long list or spreadsheet, it’s always possible to accidentally create duplicate data in a few cells. To locate any text that has been repeated, you can apply a formula to highlight duplicates. If happens regularly to you too, check out this article about highlighting duplicates in Google Sheets to tidy up your spreadsheet.

Search for data in columns using VLOOKUP

VLOOKUP is a term used to describe vertical lookup, in other words, a feature that searches for information in columns (vertical field).

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

In the simplest terms, this feature allows users to find data in an Excel spreadsheet that meets the criteria they’re seeking. A few examples of how this function could be beneficial in the business world include:

• Finding items based on the item number
• Finding receipts and identifying them using a docket or spending numbers
• The process of determining sales figures for specific items or groups of products
• Importing information from other sheets

Using data from other sheets and incorporating it into an entirely new document is one of the greatest features that come with this VLOOKUP formula. Think of how much time you could save by not having to move the entire set of data into a new document, you only need to connect the relevant data using VLOOKUP.

Create and use Pivot Tables

Do you have enormous sets of data that make it difficult to view all at once? That’s where a pivot table can help.

Do you have a lot of data that is difficult to see in a single go? That’s why the pivot table could come in handy.

Imagine the pivot table as an overview of all your information.

Basically, spreadsheets store data arranged on an x-axis and y-axis, which are rows and columns.

A pivot table is the third dimension (pivot) to spreadsheets, which aggregates information.

For example, imagine you own four stores, and the one spreadsheet that contains sales from every store throughout the year is all in one sheet. This makes it hard to track sales by date or store, so you might use a pivot table to take the data from each of these variables and then put them into a smaller table.

Why savvy business owners need to learn spreadsheet and Google Sheets formulas

A spreadsheet is a powerful tool with numerous options and formulas that can help improve workflows for business. This handy Google Sheets formulas cheat sheet lists only the bare minimum of the simplest formulas you ought to know, of course there are lots more to discover.

Once you’re confident about using these formulas though, you’ll be amazed at the improvements you can make to the overall efficiency of your business. Learning how to use Google Sheets or Excel properly can really help your business, as well as your peace of mind when you gain a better understanding of your sales. Even the simplest and smallest formulas such as IF can make a real difference once you start using it.

Have you ever used any of these handy Google Sheets formulas? Do you use spreadsheets to help you track your business income and expenses? Which of these formulas would you find most useful? Do you know any other top functions hacks that aren’t on this list?

* This is a sponsored post  – please see my Disclaimer.