A formula tells Google Sheets to do math or look up data instead of you typing the answer
A formula is an instruction you write in a cell that tells Google Sheets to calculate something, find something, or combine information from other cells. Every formula starts with an equals sign. When you press Enter, Google Sheets runs the formula and shows you the result instead of the text you typed.
The simplest formulas do basic math. If you type =5+3 in a cell, Google Sheets shows 8. But formulas become useful when they reference other cells. If cell A1 contains 5 and cell B1 contains 3, you can type =A1+B1 in cell C1, and Google Sheets will show 8. Now if you change the 5 to 10, cell C1 automatically updates to 13. That is the real power: you write the formula once, and it updates whenever the data changes.
Key Takeaways
- Every formula starts with an equals sign, and Google Sheets shows the result, not the formula text, once you press Enter.
- Formulas reference cells by their column letter and row number (A1, B2, C3), so changing the data in those cells automatically updates the formula result.
- Common formulas include SUM to add cells, AVERAGE to find the middle value, and IF to make decisions based on whether something is true or false.
- You can copy a formula down to many rows at once, and Google Sheets will adjust the cell references automatically for each row.
The anatomy of a formula: equals sign, function name, and parentheses
A formula has three parts. First is the equals sign, which tells Google Sheets you are writing an instruction, not just text. Second is the function — the name of what you want to do, like SUM or AVERAGE or IF. Third are the parentheses with the information the function needs inside them, called arguments.
Here is a real example. If you have numbers in cells A1 through A10 and you want to add them all up, you write =SUM(A1:A10). The colon between A1 and A10 means "from A1 to A10, including everything in between." When you press Enter, Google Sheets adds all ten numbers and shows the total.
If you want the average of those same cells, you write =AVERAGE(A1:A10). If you want to count how many cells have numbers in them, you write =COUNT(A1:A10). The function name changes, but the structure stays the same: equals sign, function name, parentheses with the range inside.
How to build a formula step by step
Click on the cell where you want the result to appear. Type the equals sign. Google Sheets will show a small menu with function suggestions as you type, but you can ignore it or click on a suggestion if you recognize it.
Type the function name. For a sum, type SUM. For an average, type AVERAGE. Then type an opening parenthesis. Now select the cells you want the formula to use. You can click and drag to highlight a range, or type the range directly. If you are adding cells A1 through A10, you can either drag from A1 to A10 with your mouse, or type A1:A10 inside the parentheses.
Type the closing parenthesis and press Enter. Google Sheets runs the formula and shows the result. If you made a mistake, the cell will show an error message starting with #, like #VALUE! or #REF!. Click the cell again and check that the cell references are correct and that you closed all parentheses.
Common formulas for household budgets and tracking
SUM adds a range of cells. If you have monthly expenses in cells B2 through B13, write =SUM(B2:B13) to see the total for the year.
AVERAGE finds the middle value. If you track your monthly electric bill in B2 through B13, write =AVERAGE(B2:B13) to see what you typically pay.
IF makes a decision based on whether something is true or false. If you want to flag any month where you spent more than $500, write =IF(B2>500,"Over budget","OK"). Google Sheets will show "Over budget" if the number in B2 is greater than 500, and "OK" if it is not. You can copy this formula down to all your months, and each row will check its own cell.
COUNTIF counts cells that match a condition. If you have a list of yes or no answers in column C and you want to count how many say yes, write =COUNTIF(C2:C100,"yes").
Copying a formula down to many rows at once
Once you write a formula in one cell, you can copy it down to many rows without retyping it. Google Sheets will automatically adjust the cell references for each row.
Click on the cell with your formula. Look at the small square in the bottom right corner of the cell — this is the fill handle. Click and drag it down to as many rows as you need. Google Sheets will copy the formula to each row and adjust the references. If your formula in row 2 was =A2+B2, row 3 will automatically become =A3+B3, row 4 will become =A4+B4, and so on.
You can also select the cell with the formula, copy it (Ctrl+C on Windows, Cmd+C on Mac), then select the range where you want it to go and paste (Ctrl+V or Cmd+V). Google Sheets will adjust the references the same way.
Why a formula breaks and how to fix it
The most common error is a typo in the function name or a missing parenthesis. If you type =SUM(A1:A10 without the closing parenthesis, Google Sheets will show an error. Add the closing parenthesis and press Enter again.
Another common mistake is referencing a cell that does not exist or that you deleted. If you write =A1+B1 and then delete column B, the formula will show #REF! (reference error). You will need to rewrite the formula to use a different cell, or undo the deletion.
If you see #VALUE!, it usually means you are trying to do math with text. For example, if cell A1 contains the word "hello" and you write =A1+5, Google Sheets cannot add a number to text, so it shows #VALUE!. Check that the cells you are using actually contain numbers, not labels or text.
When to use a formula instead of typing the answer yourself
Use a formula whenever the same calculation will happen more than once, or whenever the numbers might change. If you are adding up a monthly budget once and never touching it again, typing the total is fine. But if you add new expenses every week and want the total to update automatically, a SUM formula saves you from recalculating by hand.
Use a formula when you want to spot-check your work. If you manually add up ten numbers and get 487, you can write =SUM(A1:A10) in another cell to verify. If Google Sheets shows a different number, you know you made an arithmetic mistake.
Use a formula when you are comparing many items. If you have 50 rental listings and you want to flag which ones are under $1,500 a month, an IF formula in each row is much faster than reading through the list by hand.
Frequently Asked Questions
What is the difference between a range and a single cell in a formula?
A single cell is one box, like A1 or B5. A range is multiple cells in a row or column, written with a colon, like A1:A10 (cells A1 through A10) or B2:B20. Most functions like SUM and AVERAGE work on ranges because they need multiple numbers to add or average.
Can I use a formula to combine text from two cells?
Yes, using the CONCATENATE function or the ampersand symbol. If A1 contains "John" and B1 contains "Smith", you can write =A1&" "&B1 to get "John Smith". The quotation marks around the space tell Google Sheets to add a space between the two names.
What does it mean when a formula shows a number with many decimal places?
Google Sheets is showing you the exact result. You can change how many decimal places appear by right-clicking the cell, choosing "Format cells", and setting the number of decimal places. This does not change the actual number, only how it displays.
Can I use a formula to look up information from a different sheet?
Yes. If you have two sheets in the same spreadsheet, you can reference the other sheet by typing its name followed by an exclamation point, like =SUM(Sheet2!A1:A10). This adds up cells A1 through A10 from Sheet2 instead of the current sheet.
What happens if I accidentally delete a formula?
Press Ctrl+Z (or Cmd+Z on Mac) when ready to undo. Google Sheets keeps a history of your recent changes, so you can undo several steps back if needed. If you close the spreadsheet and reopen it, the undo history is gone, so undo right away.