Don’t forget to share this post

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 post, 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.

Layer Google Sheets Add On
Get Started With Layer Today!

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

GET STARTED FOR FREE

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:

23 Must Know Google Sheets Formulas Type Values
23 Must-Know Google Sheets Formulas - Type Values

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.

23 Must Know Google Sheets Formulas Use Cell References
23 Must-Know Google Sheets Formulas - Use Cell References

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

23 Must Know Google Sheets Formulas Use Parentheses
23 Must-Know Google Sheets Formulas - Use Parentheses
23 Must Know Google Sheets Formulas Formula Result
23 Must-Know Google Sheets Formulas - Formula Result

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 Built In Function
23 Must-Know Google Sheets Formulas - Built-In Function

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.

23 Must Know Google Sheets Formulas SUM
23 Must-Know Google Sheets Formulas - SUM

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.

23 Must Know Google Sheets Formulas NOW
23 Must-Know Google Sheets Formulas - NOW

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”
23 Must Know Google Sheets Formulas DATEDIF
23 Must-Know Google Sheets Formulas - DATEDIF

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.
23 Must Know Google Sheets Formulas TEXT
23 Must-Know Google Sheets Formulas - TEXT

5. EXACT

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

=EXACT(string1, string2)
23 Must Know Google Sheets Formulas EXACT
23 Must-Know Google Sheets Formulas - EXACT

6. TRIM

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

=TRIM(text)
23 Must Know Google Sheets Formulas TRIM
23 Must-Know Google Sheets Formulas - TRIM
Top Free Google Sheets Templates and Financial Models
Top Free Google Sheets Templates for 2023

Top Free Google Sheets Templates and Financial Statements to help you manage your business financials, monitor performance, and make informed decisions.

READ MORE

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.
23 Must Know Google Sheets Formulas JOIN
23 Must-Know Google Sheets Formulas - JOIN

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.
23 Must Know Google Sheets Formulas SPLIT
23 Must-Know Google Sheets Formulas - SPLIT

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.
23 Must Know Google Sheets Formulas SUBSTITUTE
23 Must-Know Google Sheets Formulas - SUBSTITUTE

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’.
23 Must Know Google Sheets Formulas IF
23 Must-Know Google Sheets Formulas - IF

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.
23 Must Know Google Sheets Formulas IFERROR
23 Must-Know Google Sheets Formulas - IFERROR

12. TRANSPOSE

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

=TRANSPOSE(array_or_range)
23 Must Know Google Sheets Formulas TRANSPOSE
23 Must-Know Google Sheets Formulas - TRANSPOSE
How To Share Only One Tab in Google Sheets
How To Share Only One Tab in Google Sheets

When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.

READ MORE

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.
23 Must Know Google Sheets Formulas FILTER
23 Must-Know Google Sheets Formulas - FILTER

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.
23 Must Know Google Sheets Formulas SORT
23 Must-Know Google Sheets Formulas - SORT

15. UNIQUE

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

=UNIQUE(range)
23 Must Know Google Sheets Formulas UNIQUE
23 Must-Know Google Sheets Formulas - UNIQUE

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.
23 Must Know Google Sheets Formulas QUERY
23 Must-Know Google Sheets Formulas - QUERY

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.
23 Must Know Google Sheets Formulas COUNTIF
23 Must-Know Google Sheets Formulas - COUNTIF

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.
23 Must Know Google Sheets Formulas SUMIF
23 Must-Know Google Sheets Formulas - SUMIF
Top Must Have Google Sheets Add ons
10 Must-Have Google Sheets Add-ons

Discover the best free Google Sheets add-ons to have to save time, improve your productivity, and take your spreadsheets to the next level.

READ MORE

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)
23 Must Know Google Sheets Formulas ARRAYFORMULA
23 Must-Know Google Sheets Formulas - ARRAYFORMULA

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
23 Must Know Google Sheets Formulas FV
23 Must-Know Google Sheets Formulas - FV

21. IMPORTDATA

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

23 Must Know Google Sheets Formulas IMPORTDATA
23 Must-Know Google Sheets Formulas - IMPORTDATA

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 Must Know Google Sheets Formulas IMPORTRANGE
23 Must-Know Google Sheets Formulas - IMPORTRANGE

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.
23 Must Know Google Sheets Formulas IMPORTXML
23 Must-Know Google Sheets Formulas - IMPORTXML

How to Automate Your Data on Top of Google Sheets?

Layer is an add-on that equips you with the tools to increase efficiency and data quality in your processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds. See how it works.

Using Layer, you can:

  • Share & Collaborate: Automate your data collection and validation through user controls.
  • Automate & Schedule: Schedule recurring data collection and distribution tasks.
  • Integrate & Sync: Connect to your tech stack and sync all your data in one place.
  • Visualize & Report: Generate and share reports with real-time data and actionable decisions.

Limited Time Offer: Install the Layer Google Sheets Add-On today and Get Free Access to all the paid features, so you can start managing, automating, and scaling your processes on top of Google Sheets!

Conclusion

As you have seen, 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.

Layer Google Sheets Add On
Get Started With Layer Today!

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

GET STARTED FOR FREE
Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Jan 9 2023, Updated Jan 8 2023