Google Sheets Cheat Sheet

Every formula — text, math, lookups, dates & QUERY — with examples.

A free, searchable Google Sheets formula guide on one long page. Covers text functions, math & statistics, logical functions (IF/IFS/AND/OR/IFERROR), lookups (VLOOKUP, XLOOKUP, INDEX/MATCH, QUERY), dates, conditional totals (SUMIF/COUNTIF/SUMIFS), modern array functions (FILTER, UNIQUE, SORT), conditional formatting with a custom formula, and Sheets-only features like IMPORTRANGE, GOOGLEFINANCE and ARRAYFORMULA. Every formula has a copy-ready syntax line and a real worked example showing the result. Includes a quick-reference table comparing VLOOKUP vs INDEX/MATCH vs XLOOKUP. Search “vlookup”, “sum if” or “remove spaces” to jump straight to it. Runs fully in your browser, no sign-up, no API.

About Google Sheets Cheat Sheet

Google Sheets Cheat Sheet features

Best practices

Speed tricks almost nobody uses

A few keyboard habits that save real time once they're muscle memory.

Named ranges (Data → Named ranges) let you write =SUM(Sales) instead of =SUM(A2:A100) — formulas stay readable even after you insert or delete rows.

Frequently asked questions

How do I write a formula in Google Sheets?

Click a cell, type an equals sign (=), then your calculation — for example =A1+A2 or =SUM(A1:A10) — and press Enter. Sheets shows the result in the cell and the formula itself whenever you click back on it.

What is the difference between VLOOKUP and XLOOKUP?

VLOOKUP can only search the first column of a range and return a value from a column to its RIGHT. XLOOKUP is newer and more flexible — it can return a result from any column, left or right, and handles missing matches more gracefully. Use XLOOKUP when you can; use VLOOKUP for older sheets or shared templates.

How do I lock a cell reference so it doesn't change when I copy a formula?

Add dollar signs before the column letter and row number to make it absolute — $A$1 instead of A1. Press F4 (Windows) or Fn+F4 (Mac) right after typing a reference to cycle through the different locking options automatically.

How do I sum values that meet a condition?

Use SUMIF for one condition — =SUMIF(criteria_range, criterion, sum_range) — or SUMIFS for multiple conditions at once. For example =SUMIF(B:B,"Food",C:C) totals column C only where column B equals "Food".

What does the QUERY function do?

QUERY runs a spreadsheet-flavoured SQL-style query directly on a range, letting you filter, sort, group and total data in a single formula — a Google Sheets-exclusive feature that can replace what would otherwise need a pivot table.

How do I remove extra spaces from text?

Wrap the cell in TRIM — for example =TRIM(A1). It removes leading, trailing and repeated spaces between words, which is essential when cleaning data pasted from a website or PDF.

How do I combine text from two cells?

Use the & operator or the CONCATENATE function — =A1&" "&B1 or =CONCATENATE(A1," ",B1) both join the text in A1 and B1 with a space between them.

How do I calculate the difference between two dates?

Use DATEDIF — =DATEDIF(start_date,end_date,"D") for days, "M" for months, or "Y" for complete years. It's the standard way to build an age or tenure calculator in Sheets.

How do I apply one formula to an entire column at once?

Wrap your calculation in ARRAYFORMULA — for example =ARRAYFORMULA(A2:A100*B2:B100) multiplies every row in that range in one go, instead of writing and copying the formula down 100 separate rows.

How do I pull data from a different Google Sheets file?

Use IMPORTRANGE with the source file's URL and the range you want — =IMPORTRANGE("spreadsheet_url","Sheet1!A1:D100"). The first time you use it, Sheets will ask you to click "Allow access" to connect the two files.

Why is my formula showing an error like #DIV/0! or #N/A?

#DIV/0! means you're dividing by zero or an empty cell; #N/A usually means a lookup (VLOOKUP/XLOOKUP) found no match. Wrap the formula in IFERROR — for example =IFERROR(A1/B1,"—") — to show a clean fallback value instead of the raw error.

What's the difference between COUNT and COUNTA?

COUNT only counts cells containing numbers. COUNTA counts every non-empty cell, including text and dates. Use COUNTA when you want to know how many rows have ANY data in them, not just numeric ones.

Is this Google Sheets guide free to use?

Yes — it's 100% free with no sign-up, no login and no limits. Every formula, example and the search box work fully in your browser, so you can look anything up without creating an account.

How do I get live stock prices or currency rates in Sheets?

Use GOOGLEFINANCE — for example =GOOGLEFINANCE("GOOGL","price") for a stock price or =GOOGLEFINANCE("CURRENCY:USDINR") for an exchange rate. It pulls near-real-time data directly into the cell.

How do I remove duplicate values with a formula?

Use UNIQUE — =UNIQUE(A2:A100) returns only the distinct rows from that range, spilling into the cells below automatically. Unlike Data → Remove duplicates, it stays live and updates itself as the source data changes.

How do I filter rows without helper columns?

Use FILTER — =FILTER(A2:C10,B2:B10="Delhi") returns only the rows from A2:C10 where column B says "Delhi", spilling the results automatically. Add more conditions separated by commas to require all of them.

How do I highlight cells automatically based on their value?

Use conditional formatting: Format → Conditional formatting, then choose "Custom formula is" and enter a formula like =B2<10 — every cell where that's true gets the chosen colour, and it updates live as values change.

Related tools — Guides & Tutorials