14 Essential Google Sheet Functions And How To Use Them
By Indeed Editorial Team
Updated 19 September 2022
Published 14 May 2022
The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.
Google Sheets is an excellent resource for keeping data organised, creating charts and graphs and delivering valuable results. The best aspect of a Google Sheet is that even with minimal experience, you can use various formulas and functions to manage your sheets. Knowing how to deploy various functions can enhance how you can manipulate data and save time when performing complex calculations. In this article, we discuss what Google Sheet functions are, list 14 essential functions and understand how each of them works.
What Is A Google Sheet Function?
A Google Sheet function is a pre-defined operation that you can apply to your dataset to perform automatic calculations. These functions work as a shortcut for performing different calculations. With the help of these functions, you can create a formula that performs the desired calculation on data. These functions or formulas are helpful for professionals who handle a large amount of data. Using these formulas, professionals can check accuracy for data entry, perform desired calculations and increase the overall productivity when working with the large data set.
14 Important Google Sheet Formulas
Some essential and widely used Google Sheet Functions are:
The AVERAGE function calculates the average of numbers in a range of cells. You can find the average of a continuous range of cells within a single column or row or individually input cells. When entering individual cells, use a comma to separate each cell reference. To use the AVERAGE function, first select an empty cell. Now, click on the ‘Insert' tab and from the drop-down menu, choose ‘Functions'.
Next, choose the ‘AVERAGE' option from the ‘Functions' drop-down menu. You can even type ‘=AVERAGE' into an empty cell. Then, select the cells you want to find the average or input them manually between parenthesis and press the 'Enter' key.
The SUM function is one of the most basic functions in Google Sheets. Using the SUM function, you can add a range of cells. You can either select the 'SUM' from the ‘Functions' drop-down list or manually type ‘=SUM' followed by the range of cells in the parenthesis. You can even enter the cells manually.
The SUMIF function lets you add cells that meet certain criteria or conditions. This function expands the use of the IF function. To use the SUMIF function, first select an empty cell. Now, click on the ‘Insert' tab and choose the ‘Functions' option. Select the ‘SUMIF' function from the ‘Math' menu of the ‘Functions' drop-down list. You can even type ‘=SUMIF into any empty cell followed by a range and condition such as greater than, lesser than or equal to. After inputting the desired condition between parenthesis, press the ‘Enter' button.
The CONCATENATE function combines two or more text strings to create a single text string. To use this function, choose an empty cell, click on the ‘Insert' tab from the drop-down menu and choose the ‘Functions' option. Choose the ‘ Text ‘ option from the ‘Functions' drop-down menu. Finally, select the 'CONCATENATE' function from the ‘Text' drop-down menu. You can even type ‘=CONCATENATE' in an empty cell and list the cell addresses that contain the text you want to combine.
Another essential function in Google Sheet is the COUNT function. This function identifies the number of cells within a range that contain a value. For instance, you may have cells from B1 to B5, B10 to B15 and B6 to B9 are empty. Using the COUNT function, you can calculate cells with a value. Select ‘COUNT' from the ‘Functions' drop-down list of the ‘Insert' tab. Alternatively, you can type ‘=COUNT', followed by the range.
An extension to the COUNT function is the COUNTIF function. It allows you to count the number of cells within a range if they meet a specific condition. Similar to the SUMIF function, you can use both numerical and non-numerical conditions for the COUNTIF function. Select ‘COUNTIF' from the ‘Math' menu of the ‘Functions' drop-down list. For manual entry, type ‘=COUNTIF' followed by the range and condition between the parentheses.
The SORT formula allows you to sort numerical data from the smallest to the highest value. Select the ‘SORT' option from the ‘Filter' menu of the ‘Functions' drop-down list. Then enter the range between the parenthesis and press the ‘Enter' key to reorder the data.
8. ISEMAIL or ISURL
To validate email addresses and URLs, you can use ISEMAIL or ISURL function. The ISEMAIL function verifies whether an email address has proper characters such as the ‘@' sign or ‘.com'. This function ensures that are no major typos in the email address. Similarly, the ISURL function validates whether the URL has proper characters, such as backlashes. Google Sheet returns either TRUE or FALSE on using these two functions. FALSE value means some error in the email address or URL, while TRUE value means that the email address or URL is error-free.
To use the ISEMAIL function, select the ‘ISEMAIL option from the ‘Info' menu of the ‘Functions' drop-down list. For using the ISURL function, select the ‘ISURL' option from the ‘Web' menu of the ‘Functions' drop-down list. You can even type '=ISEMAIL' or '=ISURL' followed by the cell reference in closed parenthesis.
The MAX and MIN function displays the highest and lowest value of a range of cells. You can use the MAX function by selecting the 'MAX' option from the 'Functions' menu of the 'Insert' drop-down list. Or you can type '=MAX' in an empty cell. Between parenthesis, enter the cell range or manually enter the cell separated by a comma. Press the 'enter' key and the highest value populates.
You can use the MIN function by selecting the 'MIN' option from the 'Functions' menu of the 'Insert' drop-down list. Or you can type '=MIN' in an empty cell. Between parenthesis, enter the cell range or manually enter the cell separated by a comma. Press the 'enter' key and the lowest value populates.
The EXACT function compares two strings for the same characters, including identical case, space or hidden character. You can use the EXACT function by selecting the 'EXACT' option from the 'Text' menu of the 'Functions' drop-down list. Or you can type '=EXACT in an empty cell. Enter the two cells that you want to compare between the parenthesis, separated by a comma. Press the 'Enter' key and the function returns a TRUE value if the cells have the same string. Otherwise, the function returns a FALSE.
These functions are helpful if you have improper capitalisation in the text you enter in Google Sheets. The purpose of these commands are:
PROPER: Capitalises each word's first character to uppercase and the rest to the lowercase characters.
UPPER: Capitalises all letters in the text string of a given cell.
LOWER: Removes the capitalisation from all words in a given text string.
You can use the PROPER function by selecting the 'PROPER' option from the 'Text' menu of the 'Functions' drop-down list. Or you can type '=PROPER' in an empty cell followed by the cell reference. Similarly, you can use the UPPER function by selecting the 'UPPER' option from the 'Text' menu of the 'Functions' drop-down list. Or you can type '=UPPER' in an empty cell followed by the cell reference. You can use the LOWER function by selecting the 'LOWER' option from the 'Text' menu of the 'Functions' drop-down list. Or you can type '=LOWER' followed by the cell reference.
The MULTIPLY functions allow you to multiply two numbers. You can use this function by selecting the 'MULTIPLY' option from the 'Operator' menu of the 'Functions' drop-down list. Or you can type '=MULTIPLY' in an empty cell followed by the two cell references separated by a comma in the parenthesis. Press the 'Enter' key to get the product value.
The DIVIDE functions allow you to divide two numbers. You can use this function by selecting the 'DIVIDE' option from the 'Operator' menu of the 'Functions' drop-down list. Or you can type '=DIVIDE' in an empty cell followed by the two cell references separated by a comma in the parenthesis. Press the 'Enter' key to get the division value.
The TODAY function displays the current date into a cell on your Google Sheet. Choose an empty cell and select the 'TODAY' option from the 'Date' menu of the 'Functions' drop-down list. Or you can type '=TODAY()' and press the 'Enter' key to display today's date in the cell. Interestingly, you can get a date in the future by adding the required number of days to the function and you can get a date in the past by subtracting the required number of days to the function. For instance, if you want to calculate a date 30 days from now, you can type '=TODAY() + 30' and press the 'Enter' key. This displays a date 30 days from today's date.
Please note that none of the companies, institutions or organisations mentioned in this article are associated with Indeed.
Explore more articles
- What Are Business Expenses? (With Types And Examples)
- What Is Web Scraping? Definition, Uses And Techniques
- What Is Comprehensive Income: Definition And Examples
- How To Stay Focused When You Work From Home
- What Is Accounts Clerk Training? Definition And Benefits
- What Is Record To Report? (With Benefits, Steps And Tips)
- What Is Video Marketing? (With Types, Steps And Examples)
- Understanding Cost Of Goods Sold (With Formula And Methods)
- What Is Price Skimming? A Definitive Guide With Examples
- 14 Examples Of Practical Skills For Professionals
- How To Take Online Classes (With Tips And Benefits)
- What is BATNA? (With Ways To Identify It And Examples)