Google Sheets Formulas: 25 Must-Know Functions

Lookup and Reference Functions

These functions let you find and retrieve data from other parts of your spreadsheet, saving you from manual searching and cross-referencing.

1. VLOOKUP

VLOOKUP searches for a value in the first column of a range and returns a value from another column in the same row. The syntax is VLOOKUP(search_key, range, index, is_sorted). For example, =VLOOKUP("Widget A", A2:D100, 3, FALSE) looks for "Widget A" in column A and returns the value from the third column of that range. Always use FALSE for the last argument unless your data is sorted and you want an approximate match. VLOOKUP is the most commonly used lookup function, but it has a limitation: it can only search the leftmost column and return values to the right.

2. INDEX and MATCH

INDEX and MATCH together form a more flexible alternative to VLOOKUP. MATCH finds the position of a value within a range, and INDEX returns the value at that position in another range. The syntax looks like =INDEX(return_range, MATCH(search_key, lookup_range, 0)). For example, =INDEX(D2:D100, MATCH("Widget A", B2:B100, 0)) finds "Widget A" in column B and returns the corresponding value from column D. Unlike VLOOKUP, INDEX MATCH can look left, works with columns in any order, and does not break when you insert or delete columns.

3. XLOOKUP

Google Sheets now supports XLOOKUP, which combines the simplicity of VLOOKUP with the flexibility of INDEX MATCH. The syntax is =XLOOKUP(search_key, lookup_range, result_range, if_not_found, match_mode, search_mode). For example, =XLOOKUP("Widget A", B2:B100, D2:D100, "Not found") searches column B and returns from column D, with a custom error message if nothing matches. XLOOKUP can search in any direction, return multiple columns, and handle errors gracefully with its built-in not-found parameter.

Google Sheets Exclusive Functions

These functions are unique to Google Sheets or work significantly differently than their Excel counterparts. They are some of the most powerful tools in the platform.

4. QUERY

The QUERY function is arguably the most powerful function in Google Sheets. It lets you use SQL-like syntax to filter, sort, group, pivot, and aggregate data. The syntax is =QUERY(data, query, headers). Here are some practical examples:

=QUERY(A1:E100, "SELECT A, C WHERE B = 'Sales' ORDER BY C DESC", 1) — selects columns A and C where column B equals "Sales," sorted by C in descending order.

=QUERY(A1:E100, "SELECT A, SUM(D) GROUP BY A LABEL SUM(D) 'Total Revenue'", 1) — groups data by column A and sums column D, with a custom header label.

=QUERY(A1:E100, "SELECT A, AVG(D) WHERE C > 100 GROUP BY A HAVING AVG(D) > 50", 1) — filters, groups, and applies a HAVING clause, just like SQL.

QUERY replaces the need for many helper columns, complex nested formulas, and even pivot tables in many cases. Once you learn the query language, you will use it constantly.

5. ARRAYFORMULA

ARRAYFORMULA applies a formula to an entire range at once, producing multiple results from a single cell. Instead of dragging a formula down hundreds of rows, write one ARRAYFORMULA and it expands automatically. The syntax is =ARRAYFORMULA(formula). For example, =ARRAYFORMULA(B2:B * C2:C) multiplies every value in column B by the corresponding value in column C and fills the results down the column. You can combine ARRAYFORMULA with IF, IFERROR, and other functions: =ARRAYFORMULA(IF(A2:A <> "", B2:B * C2:C, "")) only calculates for rows where column A is not empty.

6. IMPORTRANGE

IMPORTRANGE pulls data from another Google Sheets spreadsheet into your current one. The syntax is =IMPORTRANGE("spreadsheet_url", "SheetName!A1:D100"). The first time you use it to connect two files, you will see a prompt to allow access — click it once and the connection persists. IMPORTRANGE is invaluable for consolidating data from multiple team spreadsheets into a master dashboard, or for referencing a shared data source without duplicating it.

7. IMPORTDATA

IMPORTDATA fetches data from a URL that serves CSV or TSV formatted content. The syntax is simply =IMPORTDATA("https://example.com/data.csv"). The data populates into your sheet starting from the cell where you enter the formula. This is incredibly useful for pulling in public datasets, API endpoints that return CSV, or any regularly updated data source. The data refreshes automatically, typically every hour.

8. GOOGLEFINANCE

GOOGLEFINANCE retrieves real-time and historical financial data directly into your spreadsheet. For current stock prices, use =GOOGLEFINANCE("GOOG") to get the latest price of Alphabet stock. For more detail: =GOOGLEFINANCE("AAPL", "price") returns the price, =GOOGLEFINANCE("AAPL", "marketcap") returns market capitalization, and =GOOGLEFINANCE("AAPL", "close", "1/1/2025", "12/31/2025", "WEEKLY") returns historical weekly closing prices for all of 2025. You can also get currency exchange rates with =GOOGLEFINANCE("CURRENCY:USDEUR"). This function makes Google Sheets a surprisingly capable tool for portfolio tracking and financial analysis.

9. SPARKLINE

SPARKLINE creates a miniature chart inside a single cell. The basic syntax is =SPARKLINE(data, options). For a simple line chart: =SPARKLINE(B2:M2) creates a tiny line graph of 12 months of data in one cell. For a bar chart: =SPARKLINE(B2:M2, {"charttype","bar"; "color","steelblue"}). You can also create win-loss charts and column charts. Sparklines are perfect for dashboards where you want to show trends at a glance without taking up the space of a full chart. Combine them with GOOGLEFINANCE to show stock price mini-charts right next to your portfolio data.

10. IMAGE

The IMAGE function displays an image from a URL inside a cell. The syntax is =IMAGE("https://example.com/photo.jpg", mode). Mode 1 resizes the image to fit the cell while maintaining aspect ratio. Mode 2 stretches to fill. Mode 3 shows the original size. Mode 4 lets you specify custom width and height. This is useful for product catalogs, inventory sheets, and team directories where you want visual references alongside your data.

Data Cleaning and Text Functions

Messy data is inevitable. These functions help you clean, format, and transform text so it is ready for analysis.

11. TRIM

TRIM removes all leading, trailing, and repeated internal spaces from text. =TRIM(" Hello World ") returns "Hello World." This is essential when working with imported data or copy-pasted text that often contains invisible extra spaces that can break VLOOKUP matches and other functions.

12. CLEAN

CLEAN removes non-printable characters from text. When you import data from websites or external systems, invisible characters can sneak in and cause formulas to fail. =CLEAN(A1) strips them out. Combine with TRIM for maximum cleaning power: =TRIM(CLEAN(A1)).

13. SPLIT

SPLIT divides text into separate cells based on a delimiter. =SPLIT("John, Doe, Manager", ", ") puts "John" in one cell, "Doe" in the next, and "Manager" in the third. This is invaluable for parsing names, addresses, CSV data, and any text that follows a consistent pattern.

14. REGEXMATCH, REGEXEXTRACT, REGEXREPLACE

Google Sheets has built-in regular expression support — something Excel lacks natively. REGEXMATCH returns TRUE or FALSE: =REGEXMATCH(A1, "[0-9]3-[0-9]4") checks if a cell contains a phone-number-like pattern. REGEXEXTRACT pulls out the matching portion: =REGEXEXTRACT(A1, "[A-Za-z]+@[A-Za-z]+\.[a-z]+") extracts an email address from a cell of text. REGEXREPLACE substitutes matches: =REGEXREPLACE(A1, "[^0-9]", "") strips everything except digits. These are power-user functions that can save hours of manual data cleaning.

Math and Statistical Functions

From basic sums to conditional averages, these are the workhorses of spreadsheet analysis.

15. SUMIFS

SUMIFS adds up values that meet multiple criteria. The syntax is =SUMIFS(sum_range, criteria_range1, criterion1, criteria_range2, criterion2, ...). For example, =SUMIFS(D2:D100, B2:B100, "Sales", C2:C100, ">=2026-01-01") sums the values in column D where column B is "Sales" and column C is on or after January 1, 2026. SUMIFS is the multi-criteria upgrade to SUMIF, and it is one of the most frequently used functions in business spreadsheets.

16. COUNTIFS

COUNTIFS counts cells that meet multiple criteria. The syntax mirrors SUMIFS: =COUNTIFS(range1, criterion1, range2, criterion2). For example, =COUNTIFS(B2:B100, "Sales", D2:D100, ">1000") counts how many rows have "Sales" in column B and a value greater than 1,000 in column D. This is essential for building summary tables and dashboards.

17. AVERAGEIFS

AVERAGEIFS calculates the average of cells that meet multiple criteria. =AVERAGEIFS(D2:D100, B2:B100, "Sales", C2:C100, "Q1") averages column D values only for rows where column B is "Sales" and column C is "Q1." Like SUMIFS and COUNTIFS, you can add as many criteria pairs as needed.

18. ROUND, ROUNDUP, ROUNDDOWN

These functions control decimal places. =ROUND(3.14159, 2) returns 3.14. =ROUNDUP(3.141, 2) returns 3.15. =ROUNDDOWN(3.149, 2) returns 3.14. Use them to ensure consistent formatting in financial reports and to avoid floating-point display issues.

Logical Functions

Logical functions let your spreadsheet make decisions based on conditions.

19. IF and IFS

IF evaluates a condition and returns one value if true and another if false: =IF(A1 > 100, "Over Budget", "Within Budget"). For multiple conditions, nest IF statements or use the cleaner IFS function: =IFS(A1 >= 90, "A", A1 >= 80, "B", A1 >= 70, "C", A1 >= 60, "D", TRUE, "F"). IFS evaluates conditions in order and returns the result for the first TRUE condition.

20. IFERROR

IFERROR catches errors and returns a custom value instead. =IFERROR(VLOOKUP(A1, Data!A:D, 3, FALSE), "Not Found") performs the VLOOKUP and shows "Not Found" instead of an ugly error if no match exists. Wrap any formula that might produce an error in IFERROR to keep your spreadsheet clean and professional.

21. AND, OR

AND returns TRUE only if all conditions are true: =AND(A1 > 0, B1 < 100, C1 = "Active"). OR returns TRUE if any condition is true: =OR(A1 = "Red", A1 = "Blue", A1 = "Green"). These are most useful inside IF statements for complex decision-making: =IF(AND(B1 >= 90, C1 = "Submitted"), "Pass", "Review").

Date and Time Functions

Working with dates in spreadsheets can be tricky. These functions handle the complexity for you.

22. TODAY and NOW

TODAY returns the current date: =TODAY() might return 2026-03-17. NOW returns the current date and time. These update automatically every time the sheet recalculates. Use them for deadline tracking: =IF(A1 < TODAY(), "Overdue", "On Track") or to calculate age: =DATEDIF(A1, TODAY(), "Y") returns the number of full years between a birthdate in A1 and today.

23. DATEDIF

DATEDIF calculates the difference between two dates in years, months, or days. The syntax is =DATEDIF(start_date, end_date, unit). Units include "Y" for complete years, "M" for complete months, and "D" for days. =DATEDIF("1990-05-15", TODAY(), "Y") returns the person's age in years. This undocumented function (it does not appear in autocomplete) is one of the most useful date functions available.

Array and Filter Functions

These functions let you manipulate, filter, and sort data dynamically without modifying your source data.

24. FILTER

FILTER returns a filtered version of a range based on conditions. =FILTER(A2:D100, C2:C100 > 1000, B2:B100 = "Sales") returns all columns for rows where column C is greater than 1,000 and column B is "Sales." Unlike the built-in filter feature, FILTER produces a dynamic result in a new location without affecting the source data. It updates automatically when the source data changes, making it perfect for dashboards and reports.

25. SORT and UNIQUE

SORT returns a sorted version of a range: =SORT(A2:D100, 3, FALSE) sorts by the third column in descending order. UNIQUE returns only the distinct values from a range: =UNIQUE(B2:B100) gives you a deduplicated list of values in column B. Combine them: =SORT(UNIQUE(B2:B100)) produces a sorted list of unique values. These functions are building blocks for dynamic dropdown menus and summary tables.

Putting It All Together: Practical Combinations

The real power of Google Sheets emerges when you combine these functions. Here are several real-world combinations that solve common problems.

Dynamic dashboard summary: Use QUERY to aggregate your raw data, SPARKLINE to visualize trends, and GOOGLEFINANCE to pull in market benchmarks — all updating automatically.

Multi-sheet consolidation: Use IMPORTRANGE to pull data from team spreadsheets into a master sheet, then QUERY or FILTER to extract the specific data you need for reporting.

Automated data cleaning pipeline: Chain TRIM, CLEAN, REGEXREPLACE, and SPLIT to transform messy imported data into analysis-ready columns, wrapped in ARRAYFORMULA so one formula handles the entire column.

Live portfolio tracker: Use GOOGLEFINANCE for prices, ARRAYFORMULA to calculate gains and losses across all holdings, SPARKLINE for price charts, and conditional formatting to highlight winners and losers.

For more spreadsheet tools and data utilities, explore our web tools collection which includes converters and calculators that complement your spreadsheet workflows.

Tips for Writing Better Formulas

Use named ranges. Instead of referencing A2:A100, give your range a name like "ProductNames" through Data, Named ranges. This makes formulas self-documenting: =VLOOKUP("Widget", ProductData, 3, FALSE) is much clearer than =VLOOKUP("Widget", A2:D100, 3, FALSE).

Use absolute references when needed. Dollar signs lock a reference: $A$1 always refers to cell A1 regardless of where you copy the formula. A$1 locks the row, and $A1 locks the column. This is critical when building lookup tables and when copying formulas across rows and columns.

Comment complex formulas. Google Sheets lets you add notes to cells. For any formula longer than a single function, add a note explaining what it does and why. Your future self will thank you.

Break complex formulas into steps. Instead of one massive nested formula, use helper columns to compute intermediate results. This makes debugging easier and your spreadsheet more maintainable. Once everything works, you can consolidate into a single formula if space is a concern.

Frequently Asked Questions