Google Sheets Formulas: Examples, Functions & AI
Learn how formulas work, copy reliable examples, compare functions and array formulas, and watch the SheetsX Agent create a complete calculation column from one plain-English prompt.
Reviewed by the SheetsX team
=B2*C2 → 100=SUM(D2:D4) → 361.5What is a formula in Google Sheets?
A Google Sheets formula is an expression that starts with an equals sign and calculates a result from values, cell references, operators, or functions. For example, =B2*C2 multiplies two cells, while =SUM(D2:D4) uses the SUM function to add a range. Press Enter to calculate, then use the fill handle to copy the formula while relative references adjust automatically.
Watch SheetsX create four formulas in Google Sheets
The Agent calculates revenue in D2:D4, adds a SUM total in D5, and confirms that every other cell remains unchanged.
“On Sheet2, create formulas in D2:D4 to calculate Units Sold × Unit Price for each product, then add a SUM formula in D5 for the total revenue. Keep all other cells unchanged.”
How to create a formula in Google Sheets
Start with the result you need, then translate it into references and operators. Verify one row before copying the formula through the rest of the range.
- 1
Select the result cell
Click the cell where the calculated value should appear, then type an equals sign (=).
- 2
Enter references and operations
Build the expression with cell references, operators such as +, -, *, or /, and functions when needed.
- 3
Press Enter and verify
Check the displayed value and the formula bar before you copy the expression to more rows.
- 4
Copy with the right references
Drag or double-click the fill handle. Add $ only when a row or column must stay fixed.
Useful Google Sheets formula examples
Replace the example references with the columns and rows in your own spreadsheet. Function names and required arguments are documented in Google's official function list.
| Task | Formula | What it returns |
|---|---|---|
| Multiply quantity by price | =B2*C2 | 8 × 12.50 → 100 |
| Add a range | =SUM(D2:D4) | 100 + 101.5 + 160 → 361.5 |
| Return a label conditionally | =IF(D2>=100,"Target met","Below target") | Target met |
| Sum rows that match a condition | =SUMIF(A2:A10,"Notebook",D2:D10) | Revenue from Notebook rows |
| Look up a value by product | =XLOOKUP(F2,A2:A10,D2:D10,"Not found") | Matching revenue or Not found |
Formula vs. function vs. array formula
These terms overlap, but they describe different parts of a calculation. The distinction helps when you search documentation or ask an AI assistant for the right output.
=B2*C2Combines references, operators, values, and optional functions to calculate a result.
=SUM(D2:D4)A predefined operation used inside a formula, with inputs placed inside parentheses.
=ARRAYFORMULA(B2:B4*C2:C4)Evaluates a range and returns multiple results from one formula when spill cells are available.
Google notes that many array results expand automatically, so an explicit ARRAYFORMULA wrapper is not required for every array-producing function.
Why a Google Sheets formula does not work
Check whether Sheets is reading the entry as a formula, whether references move as intended, and whether the result has enough empty cells to expand.
The formula stays as text
Confirm it begins with =, remove any leading apostrophe, and change the cell format from Plain text if needed.
Copied formulas point to the wrong cells
Use $ to lock the row or column you want to keep fixed, such as $F$2, F$2, or $F2.
An array result cannot expand
Clear values in the destination cells or reduce the range so the array has room to spill.
The sheet recalculates slowly
Prefer closed ranges such as A2:A1000 over entire-column references when the data has a known limit.
Describe the calculation, range, and boundary
A reliable formula prompt names the target cells, explains the calculation in plain language, and states what the Agent must leave unchanged. Review both the formulas and their displayed results.
Install SheetsX- Open a new Agent chat for a clean task context.
- Name the target sheet and cell range explicitly.
- Describe the calculation and expected total.
- State which cells must remain unchanged.
- Review the final formulas and displayed values.
Google Sheets formula questions
How do I create a formula in Google Sheets?+
Select a cell, type an equals sign, enter cell references, values, operators, or functions, and press Enter. For example, =B2*C2 multiplies the values in B2 and C2.
What is the difference between a formula and a function in Google Sheets?+
A formula is the full expression entered in a cell. A function is a predefined operation, such as SUM or IF, that can be used inside a formula. =B2*C2 is a formula without a function; =SUM(B2:B10) is a formula that uses the SUM function.
How do I copy a formula down a column?+
Select the formula cell and drag or double-click its fill handle. Relative references change by row, while references containing $ stay fixed in the locked row or column.
What is an array formula in Google Sheets?+
An array formula evaluates one or more ranges and can return results into multiple cells. For example, =ARRAYFORMULA(B2:B4*C2:C4) calculates three row-level products from one formula.
Can AI write Google Sheets formulas for me?+
Yes. With SheetsX, describe the target range, calculation, and cells that must remain unchanged. The Agent can create the formulas in your sheet, then you can review the exact expressions and results.