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.
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.
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)
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.
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.
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.
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.
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.
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.
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.
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.
20 Free Google Sheets Business Templates to Use in 2018
Google Sheets to Excel: How to Move Back & Forth Between Spreadsheets
How to Edit & Format Cells in Google Sheets Spreadsheets
How to Use Pivot Tables in Google Sheets (Ultimate Guide)
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.
