What a formula does and when you need one

A formula in Excel is an instruction that tells a cell to calculate something instead of just holding a number or word. You type it into a cell, and Excel does the math or comparison for you. The simplest formulas add, subtract, multiply, or divide. More complex ones can look up information, count cells that match a condition, or combine text from different places.

You need a formula whenever you want a cell to change automatically when the numbers around it change. If you have a list of monthly expenses and you want a total at the bottom, a formula adds them up. If you add a new expense next month, the total updates without you retyping it. Without a formula, you would have to recalculate by hand every time.

The alternative is typing the answer yourself — which works fine for a one-time calculation, but becomes tedious and error-prone when the numbers change regularly or when you have dozens of rows to total.

Key Takeaways

  • Every formula starts with an equals sign, followed by the instruction — for example, =A1+A2 adds the numbers in cells A1 and A2.
  • Cell references like A1, B3, or C10 tell Excel which cells to use in the calculation, and they update automatically if you move or copy the formula.
  • The most common formulas are SUM (to add a range of cells), AVERAGE (to find the middle value), and COUNT (to count how many cells have numbers).
  • You can copy a formula down a column or across a row, and Excel adjusts the cell references for each new position automatically.
  • If a formula shows an error like #REF! or #VALUE!, it usually means you referenced a cell that was deleted or used the wrong type of data.

The anatomy of a formula: equals sign, function, and cell references

Every formula begins with an equals sign. This tells Excel that what comes next is an instruction, not just text or a number. Without the equals sign, Excel treats your entry as plain text and does nothing with it.

After the equals sign comes the function — the name of the operation you want Excel to perform. Common functions include SUM (add cells), AVERAGE (find the middle value), COUNT (count cells with numbers), and IF (make a decision based on a condition). Each function has its own rules about what information it needs inside the parentheses.

Inside the parentheses, you put cell references — the addresses of the cells you want to use. A cell reference is a letter (the column) followed by a number (the row). Cell A1 is in column A, row 1. Cell B5 is in column B, row 5. You can reference a single cell, a range like A1:A10 (all cells from A1 to A10), or multiple separate cells like A1,C1,E1.

Here is a complete example: =SUM(A1:A10) means "add all the numbers in cells A1 through A10 and show me the total." The equals sign starts the formula, SUM is the function, and A1:A10 is the range of cells to add.

Writing your first formula: addition and subtraction

The simplest formulas use basic math. To add two cells, type =A1+A2 into an empty cell. To subtract, use =A1-A2. To multiply, use =A1*A2. To divide, use =A1/A2. After you type the formula and press Enter, Excel calculates the result and shows it in that cell.

If you want to add more than two cells, you can chain them together: =A1+A2+A3+A4. But if you have many cells to add, the SUM function is faster and clearer: =SUM(A1:A4) does the same thing with less typing.

When you press Enter, the cell shows the answer, but the formula bar at the top still displays the formula itself. This is how you know a calculation is happening behind the scenes. If you click on that cell again, you can see and edit the formula in the formula bar.

Using SUM, AVERAGE, and COUNT for common tasks

SUM adds all the numbers in a range. If you have sales figures in cells B2 through B12, type =SUM(B2:B12) to get the total. This is much faster than typing =B2+B3+B4 and so on.

AVERAGE finds the middle value. If you have test scores in C2:C10, type =AVERAGE(C2:C10) to find the average score. Excel adds them all up and divides by how many scores there are.

COUNT tells you how many cells in a range contain numbers. If you have a column where some cells are empty and some have values, =COUNT(D2:D20) counts only the cells with numbers, ignoring blanks and text. This is useful when you want to know how many entries you actually have.

All three functions follow the same pattern: the function name, then parentheses, then the range of cells. You can use them on any range — a single column, a single row, or even a rectangular block of cells.

Copying formulas down columns and across rows

Once you write a formula in one cell, you can copy it to other cells, and Excel automatically adjusts the cell references. This saves enormous amounts of time when you have many rows or columns to calculate.

To copy a formula down a column, click on the cell with the formula, then drag the small square at the bottom-right corner of the cell downward. As you drag, Excel copies the formula to each new cell and adjusts the references. If your original formula was =A1+B1 in row 1, the formula in row 2 becomes =A2+B2, row 3 becomes =A3+B3, and so on.

You can also copy the cell (Ctrl+C or Cmd+C), select the range where you want to paste, and press Ctrl+V or Cmd+V. Excel adjusts the references automatically. This method works for copying across rows as well as down columns.

This automatic adjustment is why cell references are so powerful. You write the formula once, and it adapts to every row or column you copy it to. If you later change a number in one of those cells, every formula that references it recalculates when ready.

Understanding absolute references when you need them to stay fixed

Most of the time, you want cell references to adjust when you copy a formula. But sometimes you want a reference to stay the same. This is where absolute references come in.

An absolute reference uses dollar signs: $A$1 instead of A1. When you copy a formula with an absolute reference, that reference does not change. If your formula is =B1*$A$1 and you copy it down, the B1 part adjusts to B2, B3, and so on, but $A$1 always stays as $A$1.

A common use is a tax rate or discount that applies to many rows. If the tax rate is in cell A1, and you have prices in column B, you can write =B2*$A$1 in cell C2 to calculate the tax on the first price. Copy this formula down, and each row multiplies its price by the fixed tax rate in A1.

You can also make just the column absolute ($A1) or just the row absolute (A$1), depending on what you need. Most of the time, though, you will use either a regular reference (A1) or a fully absolute reference ($A$1).

Common formula errors and what they mean

If a cell shows #REF!, it usually means the formula refers to a cell that no longer exists — perhaps you deleted a column or row. Check the formula in the formula bar and update the cell reference to point to the correct cell.

#VALUE! appears when you try to do math on text or mix incompatible data types. For example, if you write =A1+A2 but A1 contains the word "Total" instead of a number, Excel cannot add them. Fix this by making sure all the cells in your formula contain the right type of data.

#DIV/0! means you tried to divide by zero, which is impossible. Check your formula to see if you are dividing by a cell that is empty or contains zero. You can use an IF statement to prevent this: =IF(A2=0,0,B2/A2) checks whether A2 is zero before dividing.

#NAME? appears when you misspell a function name. Excel does not recognize it. Double-check the spelling of your function — SUM, not SUMM; AVERAGE, not AVERGE.

Frequently Asked Questions

Can I use a formula to combine text from different cells?

Yes, using the CONCATENATE function or the ampersand (&) symbol. =A1&" "&B1 combines the text in A1 and B1 with a space between them. If A1 is "John" and B1 is "Smith", the result is "John Smith". This works for any text, not just names.

What does the colon mean in a formula like A1:A10?

The colon means "through" or "to". A1:A10 refers to all cells from A1 to A10 in one continuous range. If you want to reference only certain cells that are not next to each other, use commas instead: A1,A3,A5 refers to just those three cells.

If I change a number in a cell, do all the formulas that use it update automatically?

Yes. Excel recalculates every formula that references that cell when ready. This is one of the main reasons to use formulas instead of typing answers by hand — change one number, and everything that depends on it updates.

Can I write a formula that does different things based on a condition?

Yes, using the IF function. =IF(A1>100,"High","Low") checks whether A1 is greater than 100. If it is, the cell shows "High"; if not, it shows "Low". You can nest multiple IF statements for more complex decisions, though they become harder to read.

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

The cell is probably formatted as text. Right-click the cell, choose Format Cells, and change the format to Number or General. Then press Enter again. Alternatively, make sure you started the formula with an equals sign — without it, Excel treats the whole thing as text.