23 Must-Know Google Sheets Formulas

Google Sheets features a great variety of built-in functions of different types. These formulas cover a wide array of topics and functionality and range from basic to advanced. However, if you can’t find what you need, you can create your own formulas easily. Using the many functions and operators available, you can create formulas as simple or as complex as you like.

In this guide, you will learn how to create formulas and how to use them. You’ll learn about the different mathematical, logical, and comparison operators available and how to use built-in and custom formulas. Finally, you will learn about 23 of the most useful formulas in Google Sheets, with examples of how to use them.

Google Sheets Operators

Whether you’re creating your own formula or creating conditions for a built-in function, you need to know about Google Sheets’ operators. Below, you have frequently used mathematical, comparative, and logical operators in Google Sheets.

Mathematical Operators

• + addition
• - subtraction
• * multiplication
• / division
• ^ exponent

Comparative Operators

• = equal to
• <> not equal to
• < less than
• > greater than
• <= less than or equal to
• >= greater than or equal to

Logical Operators

• & and
• | or

How to Write Google Sheets Formulas?

Whether you use a built-in function or write your own formula, you always start with the equal sign. For example, say you simply want to subtract one value from another. You can type the values directly into the formula:

However, you can also select the cells containing the values, instead of typing them. This reduces the chances of errors and allows you to select the values very quickly.

Additionally, you can use parentheses to structure nested formulas. For example, if you want to multiply the result of the previous formula by three.

For built-in functions, type the name of the function after the equal sign. After selecting the function, follow the instructions to fill in the parameters. In the next section, you have 23 must-know formulas with instructions and examples.

23 Must-Know Google Sheets Formulas

The list below features 23 must-know formulas and functions in Google Sheets. These include mathematical, financial, date/time, logical, lookup, text manipulation, and web formulas.

1. SUM

This formula returns the sum of as many values as you wish to add. Simply type in the formula and select the range of cells with the values you want to add.

=SUM(value1, value2*, ...)

The function only requires one value, but you can add many more.

2. NOW

This very simple function has no parameters and a very specific purpose: it returns the exact date and time. Simply type the formula as shown below.

=NOW()

NOW is a volatile function, as it will update every time it’s recalculated.

3. DATEDIF

This function calculates the number of days, months, or years between two dates.

=DATEDIF(start_date, end_date, unit)

• start_date: the start date to consider.
• end_date: the end date to consider.
• unit: the unit of time to be used: years, months, days. Acceptable values include “Y”, “M”, “D”, “YM”, “YD”, “MD”

4. TEXT

The TEXT function allows you to convert numbers to text using your specified format.

=TEXT(number, format)

• number: the number, date, or time to format.
• format: the pattern by which to format the number in quotation marks.

5. EXACT

This simple function tests whether two strings are identical, including case, spacing, and hidden characters.

=EXACT(string1, string2)

6. TRIM

The TRIM function helps you clean your data by removing leading, trailing, and repeated spaces in a text.

=TRIM(text)

7. JOIN

Using JOIN, you can concatenate multiple elements using the delimiter of your choice,

=JOIN(delimiter, value_or_array1, [value_or_array2, ...])

• delimiter: the character or symbol you want to place between concatenated elements.
• value_or_array1: the first element or value you want to concatenate.
• value_or_array2*: only the first element is required, but you can add many more.

8. SPLIT

This formula allows you to split text into fragments, which are placed in separate cells. The text is divided around the specified delimiter, which can be a character or a string.

=SPLIT(text, delimiter, [split_by_each], [remove_empty_text])

• text: the text that you want to split.
• delimiter: the character or string you want to use to divide the text.
• split_by_each*: optional. Whether to split the text around each character in the delimiter.
• remove_empty_text*: optional. Whether to remove empty text messages from results.

9. SUBSTITUTE

The SUBSTITUTE function allows you to search within a text for a specific string of text and replace it with a new one. Essentially, it is the function equivalent to find and replace.

=SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])

• text_to_search: the text within to search for the string.
• search_for: the string you want to find in text_to_search.
• replace_with: the string that will replace the search_for string.
• occurrence_number*: optional. By default, all occurrences of search_for are placed.

10. IF

This simple but powerful formula evaluates a logical expression to determine whether it’s ‘TRUE’ or ‘FALSE’.

=IF(logical_expression, value_if_true, value_if_false)

• logical_expression: the logical expression or condition to be evaluated.
• value_if_true: the value that the function should return if logical_expression is ‘TRUE’.
• value_if_false: the value that the function should return if logical_expression is ‘FALSE’.

11. IFERROR

If you're using a formula that could result in an error, you can wrap the IFERROR function around the formula or the result. This function allows you to specify the value returned if the first argument results in an error.

=IFERROR(value, [value_if_error])

• value: the value that should be checked. This can be an expression or a reference to a cell.
• value_if_error*: the value that should be returned if an error is encountered.

12. TRANSPOSE

The TRANSPOSE function allows you to switch the rows and columns in an array or range of cells.

=TRANSPOSE(array_or_range)

13. FILTER

This function returns a filtered version of your selected range by including only rows or columns that meet the conditions you specify.

=FILTER(range, condition1, [condition2])

• range: the range of data you want to filter.
• condition1: the condition that values in a given row or column must meet.
• condition2*: optional.

14. SORT

The SORT function allows you to sort the rows in a range of cells according to the values in one or more columns.

=SORT(range, sort_column, is_ascending, [sort_column2], [is_ascending2])

• range: the range of data you want to sort.
• sort_column: the index number of the column containing the values by which you want to sort.
• is_ascending: ‘TRUE’ for ascending order and ‘FALSE’ for descending order.
• sort_column2*, is_ascending2*: optional.

15. UNIQUE

This function returns unique rows in the selected range by discarding duplicates.

=UNIQUE(range)

16. QUERY

QUERY is a powerful lookup function that allows you to find, filter, and manipulate data in a given dataset based on multiple criteria. You can easily combine QUERY with IMPORTRANGE to access datasets in other Google Sheets.

=QUERY(data, query, [headers])

• data: the range of cells to be queried.
• query: the query written in the Google Visualization API Query language.
• headers*: optional. Applies when the headers are split over more than one row.

17. COUNTIF

This function allows you to count cells based on whether they meet the condition or criterion that you specify. If you want to use multiple criteria, use COUNTIFS. Check out this post to learn more about COUNTIF and COUNTIFS.

=COUNTIF(range, criterion)

• range: the range of cells that you want to test using the criterion.
• criterion: the test or condition that you want to apply to the range.

18. SUMIF

This function allows you to sum cells based on whether the values in the column you select meet the condition or criterion that you specify. If you want to use multiple criteria, use SUMIFS. Take a look at this post to learn more about SUMIF and SUMIFS.

=SUMIF(range, criterion, [sum_range])

• range: the range of cells that you want to test using the criterion.
• criterion: the test or condition that you want to apply to the range.
• sum_range*: the range you want to sum if different from the range used as the first parameter.

19. ARRAYFORMULA

The ARRAYFORMULA function is very useful as it allows you to use non-array formulas with arrays. You can also use them with array formulas; however, most array formulas will automatically expand to neighboring cells, making this function redundant.

=ARRAYFORMULA(array_formula)

20. FV

Google Sheets also features many financial formulas. The FV function calculates the future value of an annuity investment, given a constant interest rate and periodic payments of a constant amount.

=FV(rate, number_of_periods, payment_amount, [present_value], [end_or_beginning])

• rate: defines the annual interest rate.
• number_of_periods: defines the number of payments to be made.
• payment_amount: the amount to be paid per period.
• present_value*: optional. The default value is 0.
• end_or_beginning*: optional. Indicates whether payments are due at the end (0) or at the beginning (1) of the period

21. IMPORTDATA

The IMPORTDATA function allows you to import data from CSV and TSV files stored online using the URL.

22. IMPORTRANGE

Using the IMPORTRANGE function, you can import data stored in a different Google Sheets file. You can easily combine it with other functions, like QUERY or VLOOKUP.

=IMPORTRANGE(spreadsheet_url, range_string)

• spreadsheet_url: the link to the spreadsheet containing the data you want to import.
• range_string: the format is “[sheet_name!]range", where sheet name is optional.

23. IMPORTXML

The IMPORTXML function allows you to import data from websites using structured data types like XML, HTML, or CSV/TSV.

=IMPORTXML(url, xpath_query)

• url: the URL of the website.
• xpath_query: this parameter specifies the data that should be imported.

Want to Boost Your Team's Productivity and Efficiency?

Transform the way your team collaborates with Confluence, a remote-friendly workspace designed to bring knowledge and collaboration together. Say goodbye to scattered information and disjointed communication, and embrace a platform that empowers your team to accomplish more, together.

Key Features and Benefits:

• Centralized Knowledge: Access your team’s collective wisdom with ease.
• Collaborative Workspace: Foster engagement with flexible project tools.
• Seamless Communication: Connect your entire organization effortlessly.
• Preserve Ideas: Capture insights without losing them in chats or notifications.
• Comprehensive Platform: Manage all content in one organized location.
• Open Teamwork: Empower employees to contribute, share, and grow.
• Superior Integrations: Sync with tools like Slack, Jira, Trello, and more.

Limited-Time Offer: Sign up for Confluence today and claim your forever-free plan, revolutionizing your team’s collaboration experience.

Conclusion

Using formulas in Google Sheets is easy. In addition to the many built-in functions available, you can create formulas as simple or as complex as you want. You now know how to use built-in formulas, as well as how to create your own. You have a list of frequently used mathematical, comparative, and logical operators, so you can build your own formulas and conditions. Finally, you have 23 must-know built-in functions, with examples of how you can use them.