What a formula does and when you need one

A formula in Google Sheets is an instruction that tells the spreadsheet to perform a calculation or look up information. Instead of typing "150" into a cell, you write a formula that says "add up all the numbers in column B" — and if those numbers change, the result updates automatically. Formulas are what turn a spreadsheet from a static list into a tool that works for you.

You need a formula whenever the answer depends on other numbers in your sheet. If you are tracking monthly expenses and want to know the total, a formula adds them up. If you are comparing two columns to find matches, a formula does the comparison. If you want to count how many cells contain a specific word, a formula counts them. Without formulas, you would recalculate by hand every time a number changed.

Google Sheets formulas start with an equals sign. That single character tells the spreadsheet: "This is not just text — this is an instruction." Everything after the equals sign is the instruction itself.

Key Takeaways

  • Every formula starts with an equals sign, followed by a function name and the cells or numbers it should work with.
  • The most common formulas are SUM (add numbers), AVERAGE (find the middle value), COUNT (count cells with numbers), and IF (make a decision based on a condition).
  • You reference cells by their column letter and row number — B3 means column B, row 3 — and you can select a range like B3:B10 to include multiple cells at once.
  • When you copy a formula to another cell, Google Sheets automatically adjusts the cell references so the formula still makes sense in its new location.
  • If a formula shows an error like #REF! or #VALUE!, it usually means you referenced a cell that was deleted, or you tried to do math on text.

The basic structure: function, parentheses, and cell references

A formula has three parts. First is the equals sign. Second is the function — the name of what you want to do, like SUM or AVERAGE. Third is what goes inside the parentheses: the cells or numbers the function should work with.

Here is the simplest example. If you want to add the numbers in cells B1, B2, and B3, you write:

=SUM(B1:B3)

The colon between B1 and B3 means "from B1 through B3" — a range. You could also write =SUM(B1,B2,B3) with commas, but the colon is faster when the cells are next to each other. When you press Enter, Google Sheets calculates the result and shows it in that cell.

Cell references use the column letter first, then the row number. Column A is the first column on the left. Row 1 is the top row. So A1 is the top-left cell. B5 is column B, row 5. This system works the same way in every spreadsheet program.

The most useful formulas for everyday work

SUM adds numbers. =SUM(B1:B10) adds all the numbers from B1 to B10. This is the formula you use most often.

AVERAGE finds the middle value. =AVERAGE(B1:B10) adds all the numbers and divides by how many there are. Use this when you want to know a typical value — like average monthly spending or average test score.

COUNT counts how many cells contain numbers. =COUNT(B1:B10) tells you how many of those cells have actual numbers in them (it ignores empty cells and text). Use this to track how many entries you have.

COUNTIF counts cells that match a condition. =COUNTIF(B1:B10,"apple") counts how many cells in that range contain the word "apple". The condition goes in quotes if it is text, without quotes if it is a number. Use this to count how many times something appears.

IF makes a decision. =IF(B1>100,"over budget","under budget") checks whether B1 is greater than 100. If it is, the cell shows "over budget". If it is not, the cell shows "under budget". Use this to flag problems or sort data into categories.

How to build a formula step by step

Click the cell where you want the answer to appear. Type the equals sign. This tells Google Sheets you are writing a formula, not just text.

Type the function name. For a straightforward sum, type SUM. Google Sheets will show you a suggestion menu — you can click the function name in the menu or just keep typing.

Type the opening parenthesis. Now you tell the function which cells to work with. You can type the cell references directly — like B1:B10 — or you can click and drag to select the cells. If you click and drag, Google Sheets fills in the references for you.

Type the closing parenthesis and press Enter. Google Sheets calculates the result and shows it in the cell. The formula itself stays hidden; you only see the answer. To see the formula again, click the cell and look at the formula bar at the top of the screen.

Copying formulas and how cell references change

Once you write a formula in one cell, you can copy it to other cells. Click the cell with the formula. Press Ctrl+C (or Cmd+C on Mac) to copy. Click the cell where you want the formula to go and press Ctrl+V to paste.

Here is the useful part: Google Sheets automatically adjusts the cell references. If you write =SUM(B1:B3) in cell D1 and copy it to cell D2, it becomes =SUM(B2:B4). The formula moved down one row, so the references moved down one row too. This is called a relative reference, and it is what you want most of the time.

Sometimes you want a reference to stay the same when you copy. You use a dollar sign. =SUM($B$1:$B$3) means "always use B1 through B3, no matter where this formula is copied." The dollar signs lock the reference in place. This is called an absolute reference. Use it when you want to divide every number in a column by a single value that sits in one cell.

Common errors and what they mean

#REF! means you referenced a cell that no longer exists. This usually happens when you delete a column or row that a formula was using. To fix it, either undo the deletion or rewrite the formula to reference the correct cells.

#VALUE! means you tried to do math on something that is not a number. For example, =SUM(A1:A5) will show #VALUE! if one of those cells contains text instead of a number. Check the cells in your range and make sure they all contain numbers.

#DIV/0! means you tried to divide by zero. This usually happens in a formula like =A1/B1 when B1 is empty or contains zero. You can prevent this with an IF statement: =IF(B1=0,"no data",A1/B1) checks whether B1 is zero before dividing.

#NAME? means Google Sheets does not recognize the function name. Check the spelling. Function names are case-insensitive — SUM and sum both work — but they must be spelled correctly.

Formulas that work with text and dates

Formulas are not just for numbers. CONCATENATE joins text together. =CONCATENATE(A1," ",B1) takes the text in A1, adds a space, and adds the text in B1. Use this to combine first and last names or build sentences from separate pieces.

LEN counts how many characters are in a cell. =LEN(A1) tells you how many letters, numbers, and spaces are in cell A1. Use this to check whether entries are the right length.

TODAY shows today's date. =TODAY() needs no cell references — it just shows the current date and updates automatically every day. Use this to track when something was entered or to calculate how many days have passed.

DATEDIF calculates the difference between two dates. =DATEDIF(A1,B1,"D") shows how many days are between the date in A1 and the date in B1. Change "D" to "M" for months or "Y" for years. Use this to track project timelines or age.

Frequently Asked Questions

Can I use a formula that references cells from a different sheet?

Yes. Type the sheet name, then an exclamation point, then the cell reference. =SUM(Sheet2!B1:B10) adds up cells B1 through B10 on Sheet2. If the sheet name has a space in it, put the name in single quotes: =SUM('My Data'!B1:B10).

What is the difference between = and ==?

In Google Sheets, use a single equals sign to start a formula. The double equals sign (==) is used inside IF statements to check whether two things are equal. =IF(A1==B1,"match","no match") checks whether A1 and B1 contain the same value.

Why does my formula show the formula text instead of the answer?

The cell is probably formatted as text. Right-click the cell, click Format, and change the format to Number or Automatic. Then edit the formula and press Enter again. Google Sheets will now calculate the result instead of displaying the text.

Can I edit a formula after I write it?

Yes. Click the cell with the formula and look at the formula bar at the top. Click in the formula bar and edit the formula the same way you would edit text. Press Enter when you are done. You can also double-click the cell itself to edit the formula directly in the cell.

What does it mean when a cell shows a very long number with E+ in it?

Google Sheets is showing the number in scientific notation because the column is too narrow. Widen the column by dragging the border between column headers, or right-click the column header and select Resize column. The number will display normally.