The 15 Google Sheets Functions You NEED to Know
Google Sheets is a strong spreadsheet app, giving users plenty of options for performing calculations on data, creating useful results. But if you’re only using Google Sheets for the most basic calculations, you’re not unleashing its full power and capabilities.
The Google Sheets functions are an important aspect of making the most of this spreadsheet’s capabilities. Deploying functions greatly enhances the way you can manipulate your data, saving you time in performing calculations, while delivering useful data.
After we discuss exactly how Google Sheets functions work, we’ll explain the 15 Google Sheets functions that you need to know how to use to make the most of this spreadsheet app.
What Is a Function in Google Sheets?
Google Sheets is a cloud based spreadsheet that’s completely free. But that doesn’t mean that it’s a scaled down version of spreadsheet software that only can perform basic calculations. Thanks to functions, Google Sheets has plenty of power for handling your data.
A function is an operation that the Google Sheets spreadsheet can apply to your data to perform automatic calculations. Using functions, you can create a formula that will make calculations on data entered in certain cells of the spreadsheet.
Functions deliver a shortcut for performing your calculations. Rather than having to spell out exact formulas for performing calculations, you can apply functions to individual cells or a range of cells, even if the data in those cells changes. Using functions will save time when setting up your calculations for the spreadsheet.
With that in mind, here are the 15 Google Sheets functions you should think about using in your spreadsheet. (To use the majority of these functions, you’ll type the function you want to use in a cell, followed by the cells on which you want to apply the function in parentheses.)
AVERAGE/MEDIAN: Determine the Average or Median Value in a Range of Cells
To display the average of a range of cells with numeric values, use the AVERAGE function with the range of cells listed inside the parentheses. By using a similar formatting with the MEDIAN function, you can find the median number in the range of cells.
CLEAN: Removing Extraneous Characters
When you’re copying data into your Google Sheets spreadsheet, you may end up copying unwanted characters that are non-printable. Sometimes, these ASCII characters are visible, although some are invisible.
Use the CLEAN function to remove these miscellaneous characters.
Understand that the CLEAN function will create a text result, rather than a number. So if you use CLEAN on a cell where you have a number you want to use in a formula for calculations later, it will not work, as Google Sheets treats the result as a text string.
CONCATENATE: Combine Text From Multiple Cells Into One Cell
Say you have entered first, middle, and last names for your business’ clients in separate cells in a Google Sheet. This type of entry makes sense, so you can sort the names alphabetically easily.
However, there may be times where you’d like a person’s first, middle, and last name combined in a single cell. The CONCATENATE function combines each of the text strings, creating a single text string.
To use the CONCATENATE function, list the cell addresses that contain the text you want to combine in parentheses. (You may want to use the CLEAN function to remove extra spaces that could affect the way the final text string will look before running CONCATENATE.)
IF: Test a Condition
The IF function is a highly useful aspect of Google Sheets, allowing you to use data entered into the spreadsheet to determine whether a condition is true or false.
For example, you could use the IF function to test whether one value you’ve calculated in the spreadsheet is greater than another value in a cell. The IF function returns a TRUE or a FALSE answer, which you then can use to run additional calculations.
Once you have learned to use all aspects of the IF function, you can really unleash some cool features, such as automatically applying a certain multiplier to values that are below a certain threshold and applying another multiplier to values above the threshold.
The formulas you will have to enter with the IF function can be quite detailed, so it will take some practice to enter the information correctly for the function to run.
IMAGE: Add an Image to a Cell
Spreadsheets are handy pieces of software, giving you a lot of ways to make use of the data entered into them. But they also can be pretty boring. Staring at a wall of cells with numbers and text strings in them is not going to grab anyone’s attention.
Fortunately, you can use the IMAGE function to add some images to your Google Sheet to break up the wall of numbers, helping you emphasize certain segments of the spreadsheet.
Inside the parentheses for the IMAGE function, add a URL address for the image you want to add, as well as the height and width in pixels that Google Sheets should use to display the image in the cell. You also can tell Google Sheets to resize the image to fit inside the cell. (You cannot use URLs from your Google Drive account for the IMAGE function.)
IMPORTRANGE: Pull Data From Other Sheets
When you have source data in one copy of your Google Sheets app that you want to use in multiple other spreadsheets, you can import this data using the IMPORTRANGE function.
You’ll have to enter the URL address for the Google Sheet from which you want to pull the data inside the parentheses, as well as the range of cells you want to use. This can be a complex set of instructions to put together properly.
The first time your current Google Sheet tries to access the original Google Sheet from which it will be pulling the data, the person who created the original Google Sheet will have to give permission.
ISEMAIL/ISURL: Validating Email and URL Addresses
If you’ve entered a series of email addresses in your Google Sheets spreadsheet, you may want to be certain that all of the email addresses have the proper characters in them using the ISEMAIL function, ensuring there are no major typos that would leave the address unusable. Along the same lines, you can validate that any URL addresses you’ve entered into the cells have the proper characters.
This function will not ensure that the email addresses or the URL addresses are valid and active, but it will ensure that the text entered in the spreadsheet cell has the proper characters in it to function as an email address, such as an @ sign, or as a URL address, such as backslashes.
Google Sheets will return either a TRUE or FALSE answer when you use the ISEMAIL and ISURL functions. If the function returns a false, that particular email or URL address has incorrect characters.
MAX/MIN: Determine the Highest or Lowest Value in a Range of Cells
When you want to display the maximum or minimum value in a range of cells in a Google Sheet, you can use the MAX or MIN function.
Just enter the range of cells to use inside the parentheses after the MAX or MIN function, and Google Sheets will give you the appropriate value.
PROPER/UPPER/LOWER: Adjusting Capitalization
If you have improper capitalization in some of the text strings in your Google Sheets spreadsheet, you can fix all of them at once using Google Sheets functions. A few different commands are available to help with capitalization, including:
- PROPER: which will capitalize each word in the cells where you apply it.
- UPPER: which will capitalize all letters in the cells.
- LOWER: which will remove any capitalization of all words in the cell.
These functions are especially helpful when you have entered a number of people’s names into your spreadsheet that have inconsistent capitalizations.
SPARKLINE: Add a Chart to a Cell
When you want to add a simple chart that reflects the data in a series of cells in the Google Sheet, you can use the SPARKLINE function.
Enter the SPARKLINE function in a single cell in the Google Sheet along with the range of cells inside parentheses, and, in its most basic function, SPARKLINE will create a line chart that shows the data.
If you’d rather create other kinds of charts, you can do that by adding the CHARTTYPE subfunction, using brackets inside the parentheses, followed by the type of chart you want to use, including:
- Bar
- Column
- Win/loss
SPARKLINE also provides an option for controlling the color of the chart by adding the COLOR subfunction inside brackets.
SPLIT: Break Up a Text String Into Multiple Cells
The SPLIT function takes a text string contained in one Google Sheet cell consisting of two or more words (such as a person’s name or a city and state) and enters each word into its own cell. This simplifies sorting the data in alphabetical order.
Type the SPLIT function in a cell, followed by the cell address of the text string you want to split inside the parentheses. Additionally, you’ll have to enter the character that designates where the text string should split (which is usually a blank space).
The first split-up word will appear in the cell where you enter the SPLIT function. Additional split-up words will continue in cells along the same row to the right until all of the words appear.
SUM: Only Values in Cells
The SUM function is one of the most basic functions in Google Sheets. Use SUM on a range of cells to add all of the values together.
SUMIF: Only Add Up Values in Cells That Meet a Condition
The SUMIF function will expand the usage of the IF function, while adding the usefulness of the SUM function.
For example, if you have a Google Sheet that lists all items that your salespeople have sold for the month, you may want to add the total amount of sales for each salesperson individually. The SUMIF is the best way to do this.
Inside the parentheses for the SUMIF function, enter the name of the salesperson in quotation marks for whom you want to calculate the total amount of sales, as well as the range of cells that contain the values to use in the calculations. SUMIF will only add the values linked to the name of the salesperson you designated, rather than all salespeople.
This sounds like a complex formula to enter, but once you have used it a couple of times, you’ll find SUMIF very easy to use.
TODAY/NOW: Update Dates and Times in Cells Automatically
If you want a cell in the Google Sheet to list the current date and time every time you open the file, you can use the TODAY or NOW functions.
With the TODAY function, Google Sheets will only list the current date in the cell. With the NOW function, Google Sheets will list both the current date and time in the cell.
Understand that using TODAY or NOW in a spreadsheet may cause the Google Sheet to run slowly, as the spreadsheet must take time to pull the date and time each time you open it. Some users report glitches in spreadsheets that use these functions in several cells.
TRIM: Removing Extra Spaces
If the information you have entered into cells has extra blank spaces at the start or end of the text string, or if you have multiple spaces between words, the TRIM function will remove these spaces.
The TRIM function can give your text strings a cleaner, professional look, which is important when you plan to print the results at some point.
