How to Use Google Spread Sheets Functions

Functions put Google Spreadsheets to work for you

 Tricia Goss

Although the idea might sound intimidating, once you learn more about functions in Google Sheets and how to use them you will discover that they can save you a ton of time and help you achieve more with your worksheets. In short, they can make your tasks — and dare we say your life — just a little easier. 

What Does a Google Sheets Function Look Like?

There are several ways to calculate data in a spreadsheet. For instance, if you have a row of numbers that you want to add, you could add the contents by hand to come up with the sum or even whip out a calculator to get the total.

However, this can quickly become tedious when you have many figures to calculate. Besides, Google Sheets is a powerful calculator in its own right. Using formulas simplify the process.

A formula is an expression that tells Google Sheets how you want to calculate the value of a cell. Remember that row of numbers we mentioned earlier? You could use the following formula to add the figures in Row 1, Columns A through F:

  • Click on the cell where you want the total to appear, type =A1+B1+C1+D1+E1+F1 and press Enter. The total will appear in the cell you selected.
  • Alternatively, you could type in the formula =SUM(A1+B1+C1+D1+E1+F1) or easier still =SUM(A1:F1) and Google Sheets sums up the cells.

A function is simply a predefined formula that helps make more complex calculations without a great deal of effort. Each function has a syntax, which is the specific order in which you must enter it for the function to perform the desired calculation. 

Every function begins with an equal sign (=), followed by the function name and then the argument, which is entered into parentheses. Arguments are the inputs or information required to calculate the data correctly.

The basic construction of a function is (below the function is what it looks like in use):

Function_Name(argument1, argument2)
=SUM(A1:F1)

How to Use Google Sheets Functions

The fastest and easiest way to use a function is via the Function menu

  1. Click inside the cell where you want to display the result of the calculation. 

  2. Click the Function button on the toolbar and select one of the five functions listed. 

    • SUM returns the sum of a series of numbers and/or cells.
    • AVERAGE gives you the average number in a range of cells (ignoring any text that is included).
    • COUNT gives you a count of the number of values in the selected range.
    • MAX returns the highest number in a set.
    • MIN returns the lowest number in a set. 
  3. Then follow the steps below to complete writing the function.

Creating Custom Functions in Google Spreadsheets

When you use a function, Google Sheets will provide the outline for the required data. To find out what you need to enter, click inside of the parentheses on the formula bar. You will see a popup explaining what you should enter along with an example and a summary of the function’s description.

For example, if you want to add up all the numbers in a column, you can do the following.

  1. Click inside the empty cell at the bottom of the list you want to sum up. 

  2. Click the Function button on the toolbar and choose SUM.

  3. Click inside the parentheses on the formula bar to activate it.

  4. Press and hold the Shift key while clicking on the first number and the last number in your list. Google will select the entire list.

  5. Press Enter to apply the function. Your result will appear in the cell at the bottom of the list. 

Alternatively, let’s say you have a spreadsheet tracking daily mileage for work and you would like to find out the average number of miles you drive each day. These are the steps you would take to write a function that determines that average:

  1. Click on the cell where you want the average to appear.

  2. Click the Function button on the toolbar and choose AVERAGE.

  3. Click inside the parentheses on the formula bar to activate it.

  4. Press and hold the Shift key while clicking on the first number and the last number in your list. Google will select the entire list.

  5. Press Enter to apply the function. The average of all the numbers in your list will appear in the cell where you entered the function.

If you want to find out the sum, average or other calculation of numbers not in a consecutive list, simply select each cell you want to include with a comma between each one, such as =SUM(A1,B3,B5,D8).

Finding the Right Function in Google Spreadsheets

What if you want to do something other than adding or averaging a list of numbers? 

You can find the complete list of all Google spreadsheet functions and read their descriptions at any time. Click the Functions button on the toolbar and select More Functions at the bottom of the list. There are dozens of functions available, but don’t let the options overwhelm you. When you have an idea of what result you want, you can type one or two relevant words into the Filter box and press Enter to see some possible choices.

You can also add the current date and time in Google Sheets using the NOW function.

For instance, if you want to calculate the number of days between two dates, you could search the word 'days.' The first result is the DAYS function, which returns the number of days between two dates. Another function listed is NETWORKDAYS, which returns the number of net working days between two provided dates, such as the start and end date of a project. 

Here is one way you might use the NETWORKDAYS function:

  1. Click inside the cell where you want to show the number of net working days between two specific dates.

  2. Type =NETWORKDAYS() into the Formula bar and click inside the parentheses. You will see the required argument, which is start_date, end_date, [holidays]. The brackets let you know that holidays are optional.

  3. Click on the cell containing the start date.

  4. Type a comma.

  5. Click on the end date and press Enter.

  6. The number of workdays (Monday through Friday) will appear in the cell containing the function. 

Using Functions with Text in Google Spreadsheets

Google Sheets functions can be helpful with text, as well. 

For example, you can use the GOOGLETRANSLATE function to translate selected text from a source language you specify to another specified language.

The TRIM function can be helpful when you import CSV files or other text into a Google worksheet. This function gets rid of spaces before and after the text as well as duplicate spaces between the text. To use this function: 

  1. Click inside of a cell where you want the trimmed text to appear. 

  2. Type =TRIM() into the formula bar and enter the address (location) of the cell containing the text inside the parentheses. For example =TRIM(A4).

  3. Press Enter.

Apply a Function to an Entire Column in Google Sheets

If you have an entire column that needs trimming or you want to apply any function to more cells in a column, click on the cell containing the function. Click on the fill handle in the bottom-right corner of the cell. While holding the mouse button down, drag it down the rest of the column. Google Sheets will apply the function to these cells, as well.