Unlimited WordPress themes, graphics, videos & courses! Unlimited asset downloads! From $16.50/m
Advertisement
  1. Business
  2. Spreadsheets
Business

How to Add Formulas & Functions in Google Spreadsheets

by
Difficulty:BeginnerLength:MediumLanguages:
This post is part of a series called How to Use Google Sheets (Essential Tutorial Guide).
How to Use (Timesaving) Google Sheets Keyboard Shortcuts
How to Sort & Filter Spreadsheet Data in Google Sheets

Spreadsheets can handle everything from simple calculations to complex functions that transform your data. Google Sheets is no slouch when it comes to spreadsheet apps that can work magic on your data and help you find meaning in your data thanks to formulas and functions. Fortunately, formulas in Google Sheets aren't difficult to learn.

The terms formula and function are often used interchangeably when talking about spreadsheets. Simply put, I think of formulas as simple operators, like cell A2 + cell B2, for example.

Functions, on the other hand, will transform the data that's in a spreadsheet. Feed data to a function, specify your options, and see a different output than what you started with. Basically, a function is an operator that's going to take data and return a transformed version of that data.

In this tutorial, we're going to take a look at Google Sheets formulas. I'll show you the power of formulas and functions in Sheets and how you can use them to accomplish tasks at hand.

Functions in Google Sheets art sized graphic

What is a Google Sheets Function?

Before we dive in, let's talk about what functions can do for your data, and why they're so important in Sheets.

Think of a function as a factory that transforms your data. You put data into the function, it transforms it, and you receive an output that differs from the input. Instead of re-typing results over and over, let the function do the transformation for you.

Average Function
The average function takes inputs and creates a finished product based on how the function works.

Each time that you run data through this factory (function), you'll receive a predictable output with the data modified. 

Spreadsheet apps can capture plain data, but they're far more useful when you apply functions to the data to change it in some way. Let's learn more about Google Sheets functions.

How to Write Google Sheets Formulas and Functions

In a spreadsheet, most formulas and functions start with the equals sign ("="). This signals to the spreadsheet app that you're going to enter a formula that will calculate on or transform the input in some way. 

After you type the equals sign, you can start to enter simple formulas like =D3+E3 for simple calculations on two cells.

Another option is to use functions, which are those repeatable steps that are built into a spreadsheet app. For example, the =AVERAGE function will average out all of the cell values. An example usage to average all values in a list would be:

=AVERAGE(A2:A5)

average example
Using average on a list of data automates the process.

This illustrates the power of Google Sheets functions. Instead of manually adding up all of the values and then counting the elements and dividing by the number of items, the AVERAGE function automates that for us. 

Tip: Follow the Guides

One of my favorite Sheets features is that it's easy to use. There's no better example of that than the fact that you can view instructions for how to use Google Sheets functions while you're typing them.

To see these guides, simply start typing one and pause while writing the function. You'll see a guide pop up on how to use the syntax for the function right inside of Sheets. This is sometimes all that's needed to understand how to use a new function.

Google Sheets Function Guide
When you start typing a function in Google Sheets you'll see help options pop up right inside of your web browser.

This ability to see the syntax and guides on how to use the formula is one of the strongest things that's going in Sheets' favor. Now, let's dive into looking at some of the Google spreadsheet functions that you might want to try out.

Five Google Sheets Formulas Worth Trying

Spreadsheets are incredibly powerful, but you don't always know the Google Sheets formula that you need to solve the challenge at hand.

Google spreadsheet functions run the gamut of functionality. Let's take a look at some of my favorites.

1. =TRIM

When you open up data inside a spreadsheet, it's not always going to be clean and neat. It might have spaces

When this is the case, the TRIM function is a lifesaver. It automates the work of cleaning up those leading and trailing spaces. Instead of clicking inside each and every cell to do the cleanup work, let TRIM automate that for you.

To use TRIM, simply type =TRIM, and then point it to the cell that you want to tidy up. Press enter, and you'll clean up the data quickly as you can see in the example below.

Trim Example screenshot
The TRIM function will quickly clean up leading and trailing spaces in a cell.

Scale TRIM up by dragging it down to automate cleaning up all of the adjacent cells. Keep this Google Sheets function in mind any time that you've got data that has those spaces that should be removed.

2. =PROPER

Keeping along those same lines of cleaning up data, you might have data that comes out of a system in a messy format, like all lowercase or uppercase format. When that's the case, the PROPER function can save you hours versus re-typing data from scratch, and it can transform text into title format.

To use the PROPER function, simply type =PROPER, and then point it to the cell with text that needs cleanup. Just press enter, and the capitalization will be adjusted to match the proper style.

Proper Example
Use PROPER on uncapitalized or incorrectly capitalized text to capitalize the first word in each string.

As always, you can simply pull the Google Sheets formula down to apply it to all of the adjacent cells.

3. =GOOGLEFINANCE

For those interested in tracking their stock portfolio, the =GOOGLEFINANCE function is a lifesaver. This is a function that taps directly into Google's financial data store to let you pull information about stocks, ETF's, mutual funds, and more.

The GOOGLEFINANCE function can pull many different data points about a security, but here's a basic usage to pull the latest price of Apple's stock:

=GOOGLEFINANCE("AAPL","price")

This will pull (and keep up to date) Apple's stock price right inside of a spreadsheet. You could use this to track your portfolio values, for example.

Google Finance example
Using Google Finance, you can grab data on stocks, bonds, and funds directly from the service.

For a complete guide to using the GOOGLEFINANCE function, check out the tutorial below:

4. =GOOGLETRANSLATE

Google's Translate service is extremely handy for making quick translations of your data. But it's even better when you can use it directly inside of a spreadsheet thanks to the =GOOGLETRANSLATE function.

Let's say that you frequently download data from systems that outputs text in a foreign language. You need to translate it repeatedly, each time that you grab new data and drop it into your spreadsheet.

Instead of jumping back and forth to the Google Translate website and constantly copying and pasting data between the two, simply use the =GOOGLETRANSLATE function inside of your spreadsheet.

The simplest use of this function is to simply type =GOOGLETRANSLATE(A2), for example, to point it to cell A2 with text to be translated. Google will attempt to guess at the language and provide a translation in your default language for your account.

Google Translate
With Google Translate, simply point the function at a cell with text from a foreign language and it will automatically attempt to translate.

Much like the GOOGLEFINANCE function, the GOOGLETRANSLATE function is a great example of the power of Google's web-based spreadsheet tool. It hooks into the power of Google's other services and is easy to apply to your spreadsheet data.

5. =RANDBETWEEN

When I'm building out spreadsheet exercises, one of my favorite tools is to use the RANDBETWEEN function that will randomly generate values for use in exercises.

To use =RANDBETWEEN, simply open up the function and then specify a lower limit for the value and an upper limit for the value. For example, showing a random value between 1 and 100 could be =RANDBETWEEN(1,100). Check out the screenshot below for an example.

RANDBETWEEN
Use RANDBETWEEN with upper and lower limits to randomly generate values for testing.

You could use a Google spreadsheet function like this for a bit of fun, too. Simply pop open a new Sheets tab if you're doing a random drawing or trying to find out who is closest to a number in a guessing game.

A Complete List of Google Sheets Functions

Showing off five of my favorite Google Sheets functions is far from even scratching the surface of what you can do in a spreadsheet. There are many more functions that you can use to transform and work with data in Google Sheets.

One option to see all of those functions is to jump over to Google's guide to all Sheets functions. I count over 400 Google spreadsheet functions that you can use in your Sheets to work with and understand your datasets.

When you're working in a spreadsheet, it might help to occasionally view all of the formulas that you're already using, just in case you want to use them in another cell. To do that, go to View > Show Formulas, or hit the keyboard shortcut Ctrl + ` to show the formulas instead of the results in the cells.

View all Google Sheets formulas
Occasionally view all Google Sheets formulas in the spreadsheet to ensure the data behind the scenes is correct.

Don't forget to occasionally switch to this view to audit your Google spreadsheet formulas. A single bad formula can throw off calculations, so make sure that you're checking in periodically to ensure your formulas are using the correct references.

Again, Sheets really shines for its ability to help you learn Google Sheets functions and formulas right inside of the app. This complete list of functions highlights just how powerful Google spreadsheet functions can be.

Recap & Keep Learning

Spreadsheets are an amazing blank template for everything from making calculations to organizing your life. If you haven't thought of using spreadsheets for a task, check out some of the tutorials below to learn more about how you might apply them in your next project.

If you had to recommend one Google Sheets formula or function for a spreadsheet beginner, what would it be? Let me know in the comments section below by sharing your favorite Google Sheets function or formula.

Advertisement
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.