12 Essential Google Sheets Formulas You Need to Know

Google Sheets is a versatile and powerful tool for managing and analyzing data, and much of its power comes from its wide range of built-in formulas. Whether you’re tracking expenses, managing a project, or analyzing data, understanding key Google Sheets formulas can help you work more efficiently and effectively. In this blog post, we’ll cover some of the most essential Google Sheets formulas you need to know.

The 12 Google Sheets Formulas to Know

1. SUM: Add Up Values

The SUM formula is one of the most basic but essential formulas in Google Sheets. It allows you to quickly add up a range of numbers.

Syntax:

=SUM(range)

Example:To sum the values in cells A1 through A5, you would use:

=SUM(A1:A5)

2. AVERAGE: Calculate the Mean

The AVERAGE formula calculates the mean (average) of a range of numbers. This is useful when you want to find out the central tendency of your data.

Syntax:

=AVERAGE(range)

Example:To calculate the average of the numbers in cells B1 through B10, you would use:

=AVERAGE(B1:B10)

3. IF: Conditional Logic

The IF formula is a powerful tool for applying logic to your data. It returns one value if a condition is true and another value if it is false.

Syntax:

=IF(logical_expression,value_if_true,value_if_false)

Example:To check if a value in cell C1 is greater than 100, and return "Yes" if true and "No" if false, you would use:

=IF(C1>100,"Yes","No")

4. VLOOKUP: Search Vertically

VLOOKUP (Vertical Lookup) is used to search for a value in the first column of a range and return a value in the same row from a specified column.

Syntax:

=VLOOKUP(search_key,range,index,[is_sorted])

Example:To search for a value in cell D1 within the range A2 and return the value from the second column, you would use:

=VLOOKUP(D1,A2:C10,2,FALSE)

5. CONCATENATE: Combine Text

The CONCATENATE formula (or the simpler & operator) allows you to combine text from multiple cells into one.

Syntax:

=CONCATENATE(text1, [text2,...])

Example:To combine the text in cells E1 and F1 with a space in between, you would use:

=CONCATENATE(E1," ",F1)

Or:

=E1&" "&F1

6. COUNTIF: Count with Conditions

The COUNTIF formula counts the number of cells that meet a specific condition.

Syntax:

=COUNTIF(range,criterion)

Example:To count how many cells in the range G1 contain the word "Completed," you would use:

=COUNTIF(G1:G20,"Completed")

7. SUMIF: Conditional Summation

The SUMIF formula sums up values in a range that meet a specific criterion.

Syntax:

=SUMIF(range,criterion,[sum_range])

Example:To sum the values in the range H1 only if the corresponding cells in the range G1 contain "Yes," you would use:

=SUMIF(G1:G10,"Yes",H1:H10)

8. INDEX and MATCH: A More Flexible Lookup

INDEX and MATCH are often used together as an alternative to VLOOKUP. MATCH finds the position of a value in a range, and INDEX returns the value at that position in another range.

Syntax:

=MATCH(search_key,range,[search_type])

=INDEX(range,row_number,[column_number])

Example:To find the value in the second column of a table where the first column matches a specific value:

=INDEX(B2:B10,MATCH(D1,A2:A10, 0))

9. IMPORTRANGE: Import Data from Another Sheet

IMPORTRANGE allows you to pull in data from another Google Sheets file.

Syntax:

=IMPORTRANGE(spreadsheet_url,range_string)

Example:To import data from the range A1

in another sheet, you would use:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123","Sheet1!A1:B10")

10. ARRAYFORMULA: Apply a Formula to a Range

ARRAYFORMULA allows you to apply a formula to an entire range of cells rather than just a single cell, making it easier to work with large datasets.

Syntax:

=ARRAYFORMULA(array_formula)

Example:To multiply each value in the range I1

by 2, you would use:

=ARRAYFORMULA(I1:I10*2)

11. GOOGLEFINANCE: Pull Financial Data

GOOGLEFINANCE allows you to fetch real-time or historical financial information, such as stock prices, directly into your Google Sheet.

Syntax:

=GOOGLEFINANCE(ticker,[attribute],[start_date],[end_date|num_days],[interval])

Example:To get the current price of Google stock, you would use:

=GOOGLEFINANCE("GOOG","price")

12. UNIQUE: Remove Duplicates

The UNIQUE formula returns a list of unique values from a range, removing any duplicates.

Syntax:

=UNIQUE(range)

Example:To get a list of unique items from the range J1

, you would use:

=UNIQUE(J1:J20)

Google Sheets formulas are powerful tools that can transform the way you handle data, from simple calculations to complex data analysis. The formulas listed above are just a starting point, but they can significantly enhance your productivity and help you get more out of your data.

Whether you’re new to Google Sheets or looking to deepen your knowledge, mastering these formulas will give you the skills you need to tackle any data-related task with confidence. Get the Google Sheets workbook here to learn 80+ functions and formulas in Google Sheets.

Try the Excel class here and become an Excel Expert!