How to Use Formulas in Google Sheets

Start a formula by typing '=' in a cell, then use function names and cell references; press Enter to apply. Use relative references by default and add the $ symbol to lock cells for absolute references. Test formulas with sample data and read error messages; follow the troubleshooting checklist below to resolve common issues.

Basic formula syntax and a quick start

Google Sheets evaluates formulas that begin with an equals sign. A formula can be a simple calculation, a built-in function call, or a combination of both. The minimal, practical steps to create and apply a formula are:

Click a cell and type =.
Enter a function or expression (for example, =SUM(A2:A10) or =A2*B2).
Press Enter. The cell shows the result; the formula is visible in the formula bar.

Additional tips that save time:

Press Ctrl+Enter (Cmd+Enter on Mac) to keep the active cell after entering a formula.
Use the autocomplete menu that appears as you type function names to avoid spelling errors.
Double-click the cell or press F2 to edit an existing formula instead of replacing it.

Relative vs absolute references: when and why to lock cells

Cell references tell a formula which values to use. By default references are relative, meaning they change when you copy the formula to another cell. Use absolute references to keep a reference fixed.

Key reference types:

Relative (A2) - shifts when copied horizontally or vertically.
Absolute ($A) - column and row locked; the reference never changes.
Mixed ($A2 or A) - only the column or row is locked.

Use relative references for repeating calculations across rows or columns (for example, computing row totals). Use absolute references for constants like tax rates or lookup table anchors. For a deeper explanation and examples, see Relative vs absolute cell references.

Common functions and a worked example: SUM, AVERAGE, VLOOKUP and INDEX-MATCH

Google Sheets includes many functions. Below are practical uses and a worked example to compare VLOOKUP and INDEX-MATCH.

SUM(range) - adds numbers.
AVERAGE(range) - calculates the mean.
COUNT(range) - counts numeric cells.
IF(condition, value_if_true, value_if_false) - conditional logic.
VLOOKUP(search_key, range, index, [is_sorted]) - vertical lookup.
INDEX(range, row_num, [column_num]) and MATCH(search_key, range, [match_type]) - flexible lookup pair.

Worked example: customer lookup from a product table

Scenario: you have a table where column A is Product ID, column B is Product Name, column C is Price, and you want to retrieve Price by Product ID entered in cell F2.

VLOOKUP method: in G2 enter =VLOOKUP(F2, A2:C100, 3, FALSE). This searches the leftmost column of A2:C100 for the value in F2 and returns the third column (Price).
INDEX-MATCH method: in G2 enter =INDEX(C2:C100, MATCH(F2, A2:A100, 0)). MATCH finds the row where Product ID equals F2; INDEX returns the corresponding Price from C2:C100.

Why choose INDEX-MATCH? It works when the lookup column is not the leftmost, and it can be slightly more reliable across range changes. For more on array-style operations that sometimes replace repeated lookups, see Array formulas explained.

Array formulas: perform calculations across ranges at once

Array formulas return or operate on multiple values with a single formula. They are useful when one formula should populate several rows or columns or when you want to avoid copying formulas repeatedly.

Common patterns:

Wrap a formula with ARRAYFORMULA to apply it to entire ranges, e.g., =ARRAYFORMULA(A2:A10 * B2:B10).
Use functions that natively return arrays, like FILTER or UNIQUE.

Array formulas change how results are placed; a single cell can output a vertical or horizontal array of values. Because the behavior is different from single-cell formulas, test on a duplicate sheet before converting many formulas.

Error handling and troubleshooting

Errors are common when building formulas. Google Sheets displays error types such as #DIV/0!, #N/A, #REF!, #VALUE!, and #ERROR. Read the provided message in the error tooltip — it often points directly to the problem.

Short troubleshooting checklist:

Check parentheses and commas. A missing closing parenthesis causes parse errors.
Verify ranges contain the expected data types (numbers vs text).
Confirm lookup ranges include the lookup column and are not misaligned.
If you see #N/A, the lookup key was not found; consider using IFNA to provide a fallback, e.g., =IFNA(VLOOKUP(...), "Not found").
Use error-checking wrappers: IFERROR(expression, value_if_error) gives a clean fallback instead of an error.

When an error persists, consult a focused guide: Fix common formula errors for step-by-step diagnostics and examples.

Practical step-by-step: build, test, and deploy a formula

Plan the expected inputs and outputs on paper or in a draft sheet.
Write the simplest formula that returns a correct result for one row or case, then confirm output.
Expand the formula using ranges or ARRAYFORMULA when you need it to apply across rows.
Lock references with $ where required so copying the formula preserves constants.
Validate results with known values, and add IFERROR or IFNA to handle missing data gracefully.
Document nonobvious formulas with a nearby comment or a legend sheet explaining assumptions.

Common mistakes and quick fixes

Using text instead of numbers - fix by wrapping VALUE around the cell or ensuring source cells are numeric.
Copying formulas without adjusting references - lock cells with $ or use named ranges for clarity.
Assuming VLOOKUP can look left - use INDEX-MATCH if the lookup column is not leftmost.
Forgetting to press Enter after edits - always confirm the edit to update dependent formulas.

Closing: apply formulas with intent and test early

Formulas are tools to automate calculations and reduce repetitive work. Start simple, test with sample data, and expand gradually. Keep a short checklist handy when you encounter an error, and use absolute references or array formulas to control how a formula behaves when copied. For deeper reference on cell locking, error fixes, or arrays, follow the internal guides linked above.