Spreadsheet applications like Google Sheets have functions that play a significant role in every action you perform. So, if you are new to the application, it can feel complicated and overwhelming. Worry not, as we’ve compiled the most basic functions commonly used on Google Sheets.
- Summing up Numbers
Whenever you’re working with numbers, you’ll likely need to add them at some point. Therefore, you need to master the sum function to add multiple numbers in cells easily.
To initiate the sum function, enter “SUM (value1, value2, etc.).” While the first value is mandatory, the following values are optional.
So, the formula below would help you add 5, 10, and 15:
Similarly, the formula below would help you sum up five cells labeled A1 to A5:
“=SUM (A1: A5)”
- The Average Function
If you want to view the average of numbers, the “Average” function has you covered. You can establish the average of numbers across the cells through the operation. The syntax for the Google sheets function to calculate the average is similar to that in Excel; AVERAGE (value1, value2, etc.) Remember that value1 in this syntax is required while the subsequent values are optional.
If you want to find out the average of numbers 5, 10, 15, you may use the formula below:
“=AVERAGE (5, 10, 15)”
The same formula applies when you want to establish average in different cells, but this time, you replace the numbers with the cells labels as in the formula below:
- The Count Function
The count function in Google sheets helps you whenever you need to count cells. With this function, you can quickly establish the number of cells.
To initiate the count function, enter “COUNT (value1, value2, etc.).
Simply insert the label of the first cell to the label of the cell you want the count to end. Enter “=COUNT (A1:A5)” to determine the number of cells from A1 through A5.
- Date and Time Function
The “NOW” or “TODAY” function helps you see the current time and date on Google Sheet. The “NOW” function shows the time and date while the “TODAY” indicates the date.
The syntax for this function is NOW () and TODAY (), with no arguments required. You only need to enter these syntaxes in your sheet to show the time and date.
Also, you can set different date formats to change how the dates appear in Google sheets.
- The Clean Function
This function helps you remove any unwanted character from Google Sheet. The cleanup is necessary when you import data from a different location. In most cases, imported data contains American Standard Code for Information Interchange (ASCII) or non-printable characters like returns and backspaces. The “Clean” function will eliminate these characters whether they are visible or not.
The syntax for this function is “CLEAN (text you want to clear).” Note that the text is required. Therefore, if you want to remove characters in cell A1, use the formula below:
Note that you may not notice a difference in the final results as the function deletes even the invisible characters.
- The Trim Function
This function works the same as the “TRIM” in Microsoft Excel. Essentially, the function helps you tidy up your sheet by removing all the white spaces in the cells.
The function’s syntax is “TRIM (text),” where the text represents the actual text you want to clear or a call reference.
For example, if you want to clear unnecessary space in B1, enter:
Similarly, you can be more specific and input “=TRIM (“remove extra space”) if you want to remove white space.
- CONCATENATE and CONCAT Functions
The functions come in handy when you want to combine text or values. The main difference between “CONCAT” and “CONCATENATE” functions is that the latter offers more flexibility. For example, through the “CONCATENATE” function, you can insert spaces between words or combine them in Google Sheet.
To activate the “CONCAT” function, enter “CONCAT (value1, value2)” while that of CONCATENATE is “CONCATENATE (string1, string 2, etc.).” Note that all arguments are required except “string 2.”
The formula below will help you combine values in C1 and D1.
“=CONCATENATE (C1, D1)”
The syntax below is an example of combining “Geek” and “Insider” with spaces.
“=CONCATENATE (“Geek”, ” “, “Insider”)”
Similarly, the formula below will help you combine values 5 and 10.
- Insert Image Function
While there’s a feature for inserting images on Google Sheets, the “IMAGE” function provides extra options that help you determine a unique width and size in pixels.
The function’s formula is “IMAGE (URL, mode, height, etc.).” You must insert the URL, but the other arguments, including size, are optional.
Below is a formula for inserting an image with a URL.
The formula below helps you resize the photo, set custom dimensions, and insert.
The number “4” above represents the mode of 50 by 200 pixels image custom size.
Note that you can’t use URLs or Scalable Vector Graphics (SVG) for images in Google Drive.
- ISEMAIL and ISURL Function
The two functions help you validate an email address or link when importing or entering data in Google Sheets. In other words, you use the function to verify that the data provided is what it’s supposed to be; an email address or a valid URL.
The syntax for both functions is ISURL (value) and ISEMAIL (value). The value represents your cell reference or specific text that you target. The results should display a validation as “TRUE” or “FALSE.”
The formula below would help you determine the validity of cell B1’s email address.
Similarly, the formula below would help you check the validity of a URL in the cell.
If you want to include a text for URL or an email address, add it in quotes as in below:
“=ISURL (“www. geekinsider.com”).”