22 Essential Google Sheets Formulas and How To Use Them

Updated February 3, 2023

A person sits at a table while working on a laptop.

Employees in many industries use Google Sheets to keep their data organized. Even if you have minimal experience creating spreadsheets, there are formulas you can use to simplify the process for you and help you use this program effectively. Learning about some key formulas can help you improve your organizational skills and become more comfortable using technology at work.

In this article, we explain Google Sheets formulas and why they're important, list 22 essential formulas and describe how you can use each for success.

What is a Google Sheets formula?

A Google Sheets formula is a calculation tool that you can use in the spreadsheet application. As you enter data, you can use a formula to help you sort data and understand its relationship to other data points. These formulas can range in complexity, with some completing simple math equations and others helping you understand more complex information.

Why are Google Sheets formulas important?

Google Sheets formulas are important because they make data entry, modification and maintenance more efficient. Especially for employees who have large amounts of information to store, creating spreadsheets without using automatic functions can lead to slow processes and data errors. By using formulas, experts can check their entries for accuracy, make changes quickly and increase overall productivity.

22 essential Google Sheets formulas

Here are 22 Google Sheets formulas you can use to make managing data easier:

1. SUM

The SUM formula adds the values within a range of cells. You can select "SUM" from the "Function" drop-down list, or you can enter the formula manually into a cell by typing =SUM. Then, hold the Shift key, select the cells you want to add together or input them manually between two parentheses and hit "Enter."

2. SUMIF

The SUMIF formula allows you to find the sum of cells that meet certain conditions. You can select "SUMIF" from the "Math" menu in the "Formula" drop-down list or enter =SUMIF in an empty cell and input a condition, such as less than, greater than or equal to. You can also input text criteria if the cells you want to add to correspond with a non-numerical value. Once you've input your conditions and the cells you want to add between parentheses, hit "Enter."

3. AVERAGE

The AVERAGE formula finds the average of the data in a range of cells. You can find the average of cells within a single column or row, or you can input random cells to add together. To use this formula, select an empty cell, select "AVERAGE" from the "Function" drop-down list or enter =AVERAGE into the cell. Then, select the cells you want to average or input them manually between parentheses and hit "Enter."

4. COUNT

You can use the COUNT formula to determine how many cells within a range contain a value. For example, you may have values in cells A1 through A3 and A7 through A10, while cells A4 through A6 and A11 through A15 are empty. You can find the number of cells that have a value quickly by clicking an empty cell, selecting "COUNT" from the "Function" drop-down list or entering =COUNT into the cell and selecting the cells you want to include or inputting them manually between parentheses. Hit "Enter" to complete the calculation.

5. COUNTIF

The COUNTIF formula allows you to count how many cells within a certain range contain a value if they meet certain conditions. Similar to the SUMIF function, you can choose a numerical or non-numerical condition for this formula. Select an empty cell and either enter =COUNTIF manually or select "COUNTIF" from the "Math" menu of the "Formulas" drop-down list. Then, enter your ranges and conditions between parentheses and hit "Enter" to calculate.

6. TODAY

The TODAY formula allows you to enter the current date into a cell on your spreadsheet. Click an empty cell and select the "TODAY" option from the "Date" menu in the "Formula" drop-down list or enter =TODAY() manually. Hit "Enter" to add the date to the cell. You can also enter a date in the future by adding a number of days to the TODAY formula or enter a date in the past by subtracting a number of days from the TODAY formula.

7. SPLIT

The SPLIT formula allows you to split text in a single cell that is separated by commas into multiple cells. To use the split formula, enter =SPLIT into an empty cell or click the cell and select "SPLIT" from the "Text" menu in the "Formula" drop-down list. Then, between parentheses, input the cell you want to split, followed by two quotation marks with each element separated by a comma. Hit "Enter" and the data from the cell populates separately into new cells.

8. SORT

The SORT formula allows you to sort cells with numerical data from the lowest value to the highest. To use the SORT formula, enter =SORT into an empty cell or click the sell and select "SORT" from the "Filter" menu in the "Formula" drop-down list. Then, enter the cell range you want to sort between parentheses and hit "Enter" to reorder the data.

9. IFERROR

The IFERROR formula allows you to determine a value for the cell if a function returns an error in a particular cell. You can use the IFERROR formula by entering =IFERROR into an empty cell or clicking a cell and selecting "IFERROR" from the "Logical" menu in the "Formula" drop-down list. Between parentheses, enter the value you want to populate, follow it with a comma and include the value you want to populate if the function returns an error in brackets. Hit "Enter" to apply the formula to the cell.

10. TEXT

The TEXT formula converts numbers within cells into formats such as currency, decimals or dates. To use the TEXT formula, click an empty cell and enter =TEXT or click the cell and select "TEXT" from the "Text" menu in the "Formula" drop-down list. Then, enter the command for the format you want to change the current value to, such as "$0.00" for currency between parentheses. Hit "Enter" to populate the change in the cell.

11. VLOOKUP

The VLOOKUP, or vertical lookup, function allows you to look up specific data within the spreadsheet. To use this formula, enter =VLOOKUP in an empty cell or click the cell and select "VLOOKUP" from the "Lookup" menu in the "Formula" drop-down list. Then, enter commands for the value you want to search for, the columns and rows you want to include in the search, the column index of the return value and whether you want the data to be sorted between parentheses. Hit "Enter" to look up the data.

12. ARRAYFORMULA

The ARRAYFORMULA formula allows you to perform an action on multiple ranges of data, such as adding the values of data in two separate lists. To use this formula, enter =ARRAYFORMULA into an empty cell or click the cell and choose "ARRAYFORMULA" from the "Google" menu in the "Formula" drop-down list. Then, enter a range followed by a command, such as plus or minus, and enter another range between parentheses. Hit "Enter" to complete the action.

13. NOW

The NOW formula allows you to input the current time and date into a cell. To use the formula, enter =NOW() into an empty cell or select "NOW" from the "Date" menu in the "Formula" drop-down list. Hit "Enter," and the system date and time appear in the selected cell. The NOW value changes from the original entry value as you recalculate the spreadsheet.

14. ADD

The ADD formula is a simple function that allows you to add the sum of two values to enter into a cell. To use the ADD formula, enter =ADD into an empty cell or click "ADD" in the "Operator" menu in the "Formula" drop-down list. Between parentheses, enter two values separated by a comma. Hit "Enter," and the sum of the two values appears in the cell.

15. MINUS

The MINUS formula is a simple formula you can use to subtract one value from another and input the sum into a cell. To use the MINUS formula, enter =MINUS into a cell or click an empty cell and select "MINUS" from the "Operator" menu in the "Formula" drop-down list. Between parentheses, enter two values separated by a comma, and hit "Enter" to input the sum of the two values into the cell.

16. DIVIDE

The DIVIDE formula allows you to divide one value by another and enter the quotient in the cell. To use the DIVIDE formula, select "DIVIDE" from the "Operator" menu in the "Formula" drop-down list or enter =DIVIDE into a blank cell. Between parentheses, enter the dividend and divisor separated by a comma. Hit "Enter" to populate the quotient in the cell.

17. MULTIPLY

The MULTIPLY formula allows you to multiply one number by another and input the product into the cell. You can use the MULTIPLY formula by entering =MULTIPLY into an empty cell or by clicking the cell, selecting "MULTIPLY" from the "Operator" menu in the "Formula" drop-down list. Then enter the factors between two parentheses, separated by a comma. Hit "Enter" to input the product into the cell.

18. TO_DOLLARS

The TO_DOLLARS formula allows you to turn a value from any cell into a dollar amount in a new cell. Use the TO_DOLLARS formula by selecting "TO_DOLLARS" from the "Parser" menu in the "Formula" drop-down list or entering =TO_DOLLARS into an empty cell. Between parentheses, enter the cell with the value you want to convert to dollars. Hit "Enter" to input the dollar amount into the cell.

19. TO_PERCENT

The TO_PERCENT formula allows you to turn a value from any cell into a percentage in a new cell. The formula multiplies the value by 100, so this formula is most useful for converting decimals to percentages. To use the "TO_PERCENT" formula, enter =TO_PERCENT into an empty cell or click the empty cell and select the "TO_PERCENT" option from the "Parser" menu in the "Formula" drop-down list. Then, enter the cell with the value you want to convert to a percentage between parentheses. Hit "Enter" to input the percentage into the cell.

20. EXACT

The EXACT formula allows you to determine whether two values within different cells on your spreadsheet are the same. You can use the EXACT formula by clicking an empty cell and selecting "EXACT" from the "Text" menu in the "Formula" drop-down list or entering =EXACT in the cell. Then, enter the two cells you want to compare in parentheses, separated by a comma. Hit "Enter" to evaluate the values. If they're exact, TRUE appears in the cell. If they differ, FALSE appears in the cell.

21. MAX

The MAX function allows you to find the highest value within a selected range of cells. To use the MAX formula, enter =MAX into an empty cell or clicking the cell and selecting "MAX" from the "Statistical" menu in the "Formula" drop-down list. Between parentheses, enter separate cells separated by commas or a range of cells separated by a colon. Hit "Enter", and the highest value should populate in the new cell.

22. MIN

The MIN function allows you to find the lowest value within a selected range of cells. You can use the MIN formula by clicking an empty cell and selecting "MIN" from the "Statistical" menu in the "Formula" drop-down list and or entering =MIN in an empty cell. "Enter" each cell separated by commas or a range of cells separated by a colon between parentheses. Hit "Enter" to input the lowest value of the range in the new cell.

Please note that the company mentioned in this article is not affiliated with Indeed.