12 Essential Google Sheets Formulas You Need to Know

12 essential Google Sheets formulas will cover most of what you need to track expenses, manage projects, and analyze data without doing math by hand.

Google Sheets gets much of its power from built-in formulas, and a few of them, like IMPORTRANGE, ARRAYFORMULA, and GOOGLEFINANCE, do things Excel users often envy. You do not need to learn hundreds. A dozen well-chosen formulas will make you faster and far more confident with your data.

Each formula below includes the syntax, a working example, and a practical tip. Short videos are linked where they help, and there is a free formula builder that writes any of the twelve for you.

The 12 Google Sheets Formulas at a Glance

The 12 Google Sheets Formulas to Know

1. SUM: Add Up Values

SUM is one of the most basic and most used formulas in Google Sheets. It adds up a range of numbers.

Syntax: =SUM(range)

Example: =SUM(A1:A5)

This adds the values in A1 through A5.

Tip: Select a cell below a column of numbers and use Insert > Function > SUM, or simply type the formula. Text in the range is ignored, so check for numbers stored as text if a total looks low.

2. AVERAGE: Calculate the Mean

AVERAGE calculates the mean of a range, useful for seeing the typical value in your data.

Syntax: =AVERAGE(range)

Example: =AVERAGE(B1:B10)

This returns the average of the numbers in B1 through B10.

Tip: Empty cells are skipped but zeros are counted. Use AVERAGEIF when you only want to average values that meet a condition, such as sales for one region.

3. IF: Conditional Logic

The IF formula applies logic to your data. It returns one value when a condition is true and another when it is false.

Syntax: =IF(logical_expression, value_if_true, value_if_false)

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

This checks whether C1 is greater than 100 and returns "Yes" or "No."

Tip: For several conditions, the IFS function is easier to read than nesting IF inside IF.

4. VLOOKUP: Search Vertically

VLOOKUP (vertical lookup) searches the first column of a range for a value and returns a value from another column in the same row.

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

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

This looks for D1 in the first column of A2:C10 and returns the matching value from the second column.

Tip: Always set the last argument to FALSE for an exact match unless your data is sorted and you want an approximate match. Google Sheets also supports XLOOKUP, which can look in any direction.

For a full walkthrough, see how to use VLOOKUP in Google Sheets.

5. CONCATENATE: Combine Text

CONCATENATE, or the simpler ampersand operator, combines text from several cells into one.

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

Example: =CONCATENATE(E1, " ", F1) or =E1&" "&F1

Both combine E1 and F1 with a space between them, such as a first and last name.

Tip: To join a whole range with a separator, use TEXTJOIN: =TEXTJOIN(", ", TRUE, E1:E10) creates a comma-separated list and skips blanks.

6. COUNTIF: Count With Conditions

COUNTIF counts the cells in a range that meet one condition.

Syntax: =COUNTIF(range, criterion)

Example: =COUNTIF(G1:G20, "Completed")

This counts how many cells in G1 through G20 say "Completed."

Tip: Use wildcards for partial matches, such as "*late*", and COUNTIFS when you need more than one condition.

7. SUMIF: Conditional Summation

SUMIF adds up values that meet a specific condition.

Syntax: =SUMIF(range, criterion, [sum_range])

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

This adds the values in H1 through H10 where the matching cell in G1 through G10 says "Yes."

Tip: Put comparisons in quotes, such as ">100". For multiple conditions, use SUMIFS.

8. INDEX and MATCH: A More Flexible Lookup

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

Syntax: =MATCH(search_key, range, [search_type]) and =INDEX(range, row, [column])

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

This finds D1 in A2:A10 and returns the value in the same position from B2:B10.

Tip: Unlike VLOOKUP, INDEX and MATCH can return a value from a column to the left of the one you search, and they keep working if columns are inserted.

9. IMPORTRANGE: Import Data From Another Sheet

IMPORTRANGE pulls data from a different Google Sheets file into the one you are working in.

Syntax: =IMPORTRANGE(spreadsheet_url, range_string)

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

This imports Sheet1!A1:B10 from the other spreadsheet.

Tip: The first time you connect two files, click the cell and choose Allow access. Google explains the details in its IMPORTRANGE help page.

10. ARRAYFORMULA: Apply a Formula to a Range

ARRAYFORMULA applies one formula to an entire range instead of a single cell, which is ideal for large data sets and forms.

Syntax: =ARRAYFORMULA(array_formula)

Example: =ARRAYFORMULA(I1:I10*2)

This multiplies every value in I1 through I10 by 2 with a single formula.

Tip: ARRAYFORMULA is perfect for sheets fed by Google Forms, because the formula fills in new rows automatically as responses arrive.

11. GOOGLEFINANCE: Pull Financial Data

GOOGLEFINANCE fetches current or historical financial information, such as stock prices and currency rates, directly into your sheet.

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

Example: =GOOGLEFINANCE("GOOG", "price")

This returns the current price of Alphabet stock.

Tip: It also converts currency, for example =GOOGLEFINANCE("CURRENCY:USDEUR"). Quotes may be delayed, so it is fine for tracking but not for trading. See the Google GOOGLEFINANCE help page.

12. UNIQUE: Remove Duplicates

UNIQUE returns a list of distinct values from a range, with duplicates removed.

Syntax: =UNIQUE(range)

Example: =UNIQUE(J1:J20)

This lists each value in J1 through J20 once.

Tip: Wrap it in SORT for an alphabetical list: =SORT(UNIQUE(J1:J20)). It is a quick way to build a customer or category list from messy data.

Free Google Sheets Formula Builder

Google Sheets vs. Excel Formulas

Most everyday formulas, such as SUM, IF, VLOOKUP, SUMIF, and INDEX and MATCH, work the same way in both. A few are unique to Google Sheets.

Common Formula Mistakes in Google Sheets

• Text instead of numbers. Imported numbers sometimes arrive as text, and SUM ignores them. Use Format > Number to fix them.
• Missing FALSE in VLOOKUP. Without it, you may get the wrong row.
• Criteria without quotes. Conditions like ">100" need quotes in COUNTIF and SUMIF.
• Ranges that shift. Add dollar signs, such as $A$2:$A$100, before copying a formula.
• Blocked results. ARRAYFORMULA and UNIQUE need empty cells to fill. If they show #REF!, clear the cells in the way.

Time-Saving Habits for Google Sheets Formulas

• Name your ranges with Data > Named ranges so formulas read like sentences, such as =SUM(Expenses).
• Use open-ended ranges like A2:A so formulas include new rows automatically.
• Freeze the header row so column names stay visible while you write formulas far down the sheet.
• Check the formula help box that appears as you type. It shows each argument and a quick example.
• Keep inputs and results apart by putting raw data on one tab and calculations on another.

Where to Learn More

Google keeps a complete Google Sheets function list, and our Google Sheets formula list cheat sheet keeps the most useful ones in one place. For guided lessons, watch the free Excel and Google Sheets course videos or browse our free Excel courses.

When Spreadsheets Start Running Your Business

Google Sheets is great for analysis, but when hours, schedules, projects, and invoices all live in shared sheets, errors creep in and nobody knows which copy is current. Updoot is business management software that keeps that work in one system, and every tool exports to Google Sheets or Excel when you want to run these formulas yourself. Plans start at $5 per user per month.

Final Thoughts

These twelve formulas are a strong starting point. SUM, AVERAGE, IF, and the lookups handle everyday work, COUNTIF and SUMIF answer questions with conditions, and IMPORTRANGE, ARRAYFORMULA, GOOGLEFINANCE, and UNIQUE show off what makes Google Sheets special. Practice them on your own data and you will handle almost any spreadsheet task with confidence.