{"id":9441,"date":"2022-02-03T17:36:50","date_gmt":"2022-02-03T17:36:50","guid":{"rendered":"https:\/\/mailinvest.blog\/index.php\/2022\/02\/03\/9-basic-google-sheets-functions-you-should-know\/"},"modified":"2022-02-03T17:36:50","modified_gmt":"2022-02-03T17:36:50","slug":"9-basic-google-sheets-functions-you-should-know","status":"publish","type":"post","link":"https:\/\/mailinvest.blog\/index.php\/2022\/02\/03\/9-basic-google-sheets-functions-you-should-know\/","title":{"rendered":"9 Basic Google Sheets Functions You Should Know"},"content":{"rendered":"

<\/a>\n<\/p>\n

\n

\"\"<\/p>\n

Functions are key components of spreadsheet applications like Google Sheets. But if you seldom use them, or you\u2019re just beginning, they can feel overwhelming. For the most basic actions you\u2019d perform, here are several simple Google Sheets functions.<\/p>\n

1. Add Numbers: SUM<\/h2>\n

It doesn\u2019t get more basic when working with numbers than adding them. Using the SUM<\/code> function, you can add multiple numbers, add numbers in cells, or use a combination.<\/p>\n

The syntax for the function is SUM(value1, value2,...)<\/code> with value1<\/code> required and value2<\/code> optional.<\/p>\n

To add the numbers 10, 20, and 30, you would use the following formula:<\/p>\n

=SUM(10,20,30)<\/pre>\n
To add the numbers in the cells A1 through A5, you would use this formula:<\/p>\n
=SUM(A1:A5)<\/pre>\n
\"SUM<\/p>\n
2. Average Numbers: AVERAGE<\/h2>\n
Maybe you need to view the average of numbers or numbers in a range of cells. The AVERAGE<\/code> function has you covered.<\/p>\n
Similar to calculating average in Excel<\/a>, the syntax for the Google Sheets function is AVERAGE(value1, value2,...)<\/code> with value1<\/code> required and value2<\/code> optional.<\/p>\n
To find the average of numbers 10, 20, and 30, you would use the following formula:<\/p>\n
=AVERAGE(10,20,30)<\/pre>\n
To find the average of the numbers in cells A1 through A5, use this formula:<\/p>\n
=AVERAGE(A1:A5)<\/pre>\n
\"AVERAGE<\/p>\n
Tip:<\/strong> You can also see basic calculations without formulas<\/a> in Google Sheets.<\/p><\/blockquote>\n
3. Count Cells With Numbers: COUNT<\/h2>\n
If you\u2019ve ever had to count cells, you\u2019ll appreciate the COUNT<\/code> function. With it, you can count how many cells in a range contain numbers.<\/p>\n
The syntax for the function is COUNT(value1, value2,...)<\/code> with value1<\/code> required and value2<\/code> optional.<\/p>\n
To count the cells A1 through A5, you would use the following formula:<\/p>\n
=COUNT(A1:A5)<\/pre>\n
To count the cells A1 through A5 and D1 through D5, use the following formula:<\/p>\n
=COUNT(A1:A5,D1:D5)<\/pre>\n
\"COUNT<\/p>\n
You can also count data that matches criteria<\/a> using COUNTIF<\/code> in Google Sheets.<\/p>\n
4. Enter the Current Date and Time: NOW and TODAY<\/h2>\n
If you want to see the current date and time each time you open your Google Sheet, you can use the NOW<\/code> or TODAY<\/code> function. NOW<\/code> displays the date and time whereas TODAY<\/code> displays only the current date.<\/p>\n
The syntax for each is NOW()<\/code> and TODAY()<\/code> with no required arguments. Simply enter one or the other of the following in your sheet to display the date and time or just the date.<\/p>\n
NOW()<\/pre>\n
TODAY()<\/pre>\n
\"NOW<\/p>\n
If you want the dates to appear in a certain format, you can set the default date format in Google sheets<\/a>.<\/p>\n
5. Remove Non-Printable Characters: CLEAN<\/h2>\n
When you import data from another location into your sheet, that data can include non-printable or ASCII characters<\/a> like backspaces and returns. The CLEAN<\/code> function removes both visible and invisible characters.<\/p>\n
The syntax is CLEAN(text)<\/code> with the text required.<\/p>\n
To remove the non-printable characters from the text in cell A1, you would use this formula:<\/p>\n
=CLEAN(A1)<\/pre>\n
\"CLEAN<\/p>\n
Note:<\/strong> Because the function removes the characters you can\u2019t see as well as those you can, you may not notice a difference in the resulting cell.<\/p><\/blockquote>\n
6. Remove White Space: TRIM<\/h2>\n
Another helpful function for tidying up your sheet is the TRIM<\/code> function<\/a>. Just like in Microsoft Excel, this function removes the white spaces in a cell.<\/p>\n
The syntax is TRIM(text)<\/code> where text can represent a cell reference or the actual text.<\/p>\n
To remove the white space in cell A1, you would use the following formula:<\/p>\n
=TRIM(A1)<\/pre>\n
To remove the white space from \u201d\u00a0\u00a0 remove\u00a0\u00a0 extra space\u00a0 \u201d use this formula:<\/p>\n
=TRIM(\"\u00a0\u00a0 remove\u00a0\u00a0 extra space\u00a0 \")<\/pre>\n
\"TRIM<\/p>\n
RELATED:<\/strong> How to Use the TRIM Function in Microsoft Excel<\/em><\/strong><\/a><\/p>\n
7. Combine Text or Values: CONCATENATE and CONCAT<\/h2>\n
To combine strings, text, or values, you can use the CONCATENATE<\/code> and CONCAT<\/code> functions. The main difference between the two is that CONCATENATE<\/code> offers more flexibility. For instance, you can combine words and insert spaces between them.<\/p>\n
The syntax for each is CONCATENATE(string1, string2,...)<\/code> and CONCAT(value1, value2)<\/code> where all arguments except string2<\/code> are required.<\/p>\n
To combine the values in cells A1 and B1, you can use the following formula:<\/p>\n
=CONCATENATE(A1,B1)<\/pre>\n
To combine the words \u201cHow,\u201d \u201cTo,\u201d and \u201cGeek\u201d with spaces, you would use this formula:<\/p>\n
=CONCATENATE(\"How\", \" \", \"To\", \" \", \"Geek\")<\/pre>\n
To combine the values 3 and 5, you can use the following formula:<\/p>\n
=CONCAT(3,5)<\/pre>\n
\"CONCATENATE<\/p>\n
For more details on these two functions, take a look at our how-to for concatenating data in Google Sheets<\/a>.<\/p>\n
8. Insert an Image in a Cell: IMAGE<\/h2>\n
While Google Sheets provides a feature for inserting an image into a cell<\/a>, the IMAGE<\/code> function gives you extra options to resize it or set a custom height and width in pixels.<\/p>\n
The syntax for the function is IMAGE(url, mode, height, width)<\/code> with the URL<\/a> required and the other arguments optional.<\/p>\n
To insert an image with a URL as it is, you would use the following formula:<\/p>\n
=IMAGE(\"https:\/\/logos-download.com\/wp-content\/uploads\/2019\/11\/How-To_Geek_Logo.png\")<\/pre>\n
To insert the same image resized with a custom height and width, use this formula:<\/p>\n
=IMAGE(\"https:\/\/logos-download.com\/wp-content\/uploads\/2019\/11\/How-To_Geek_Logo.png\",4,50,200)<\/pre>\n
The 4<\/code> in this formula is the mode that allows the custom size of the image at 50 by 200 pixels.<\/p>\n
\"IMAGE<\/p>\n
Note:<\/strong> You cannot use SVG<\/a> graphics or URLs for images in Google Drive.<\/p><\/blockquote>\n
For more help with how to resize images using the function, visit the Docs Editor Help page for the IMAGE function<\/a>.<\/p>\n
9. Validate an Email Address or Link: ISEMAIL and ISURL<\/h2>\n
Whether importing or entering data in Google Sheets, you may want to verify it is what it\u2019s supposed to be. With ISEMAIL<\/code> and ISURL<\/code>, you can make sure the data is an email address<\/a> or valid URL.<\/p>\n
RELATED:<\/strong> How to Restrict Data to Email Addresses in Google Sheets<\/em><\/strong><\/a><\/p>\n
The syntax for each is ISEMAIL(value)<\/code> and ISURL(value)<\/code> where you can use a cell reference or text. The results of the validation display as TRUE or FALSE.<\/p>\n
To check an email address in cell A1, you would use the following formula:<\/p>\n
=ISEMAIL(A1)<\/pre>\n
To check a URL in cell A1, use this formula:<\/p>\n
=ISURL(A1)<\/pre>\n
To use text in the formula for either an email address or URL, simply enter it in quotes like this:<\/p>\n
=ISURL(\"www.howtogeek.com\")<\/pre>\n
\"ISEMAIL<\/p>\n
To go even further, take a look at how to use the AND and OR functions<\/a>, take advantage of the QUERY function<\/a>, or start using the IF function<\/a> in Google Sheets.<\/p>\n<\/div>\n