What a formula does and when to use one

A formula in Excel is an instruction that tells the spreadsheet to perform a calculation or comparison using the numbers or text you give it. Instead of typing "15 + 8 = 23" into a cell, you type =15+8, and Excel shows you 23. The real power comes when you point the formula at cells instead of numbers — so =A1+A2 adds whatever is in cell A1 to whatever is in cell A2, and if you change those numbers later, the answer updates automatically.

You use a formula when you need the same calculation done many times, or when the numbers change and you do not want to recalculate by hand. A budget spreadsheet with formulas will update your total spending the moment you add a new expense. A timesheet will calculate your weekly hours the moment you enter your daily totals. Without formulas, you would be typing the same math over and over.

Key Takeaways

  • Every formula starts with an equals sign, followed by the instruction — =A1+B1 or =SUM(A1:A10) — and Excel shows the result in the cell.
  • Cell references like A1, B2, or C5 point to the box that holds the data, so when that data changes, the formula result updates without you retyping anything.
  • Built-in functions like SUM, AVERAGE, COUNT, and IF do common calculations for you instead of making you write out the math yourself.
  • You can copy a formula down a column or across a row, and Excel adjusts the cell references automatically — so =A1+B1 becomes =A2+B2 in the next row.
  • The formula bar at the top of the screen shows you what the formula actually says, so you can check it or edit it without clicking inside the cell.

The anatomy of a straightforward formula

A formula has three parts: the equals sign, the operation, and the cell references or numbers. Start by clicking the cell where you want the answer to appear. Type = first — this tells Excel you are giving it an instruction, not just typing text. Then type what you want it to do.

If you want to add the number in cell A1 to the number in cell B1, type =A1+B1 and press Enter. Excel calculates the sum and shows the result. The cell now contains the formula, not the number you see. If you click on that cell again, the formula bar (the white box at the top of the screen) shows you =A1+B1, while the cell itself displays the answer.

The basic operations are: + for addition, - for subtraction, * for multiplication, and / for division. You can also use parentheses to control the order — =(A1+B1)*C1 adds A1 and B1 first, then multiplies the result by C1.

Using built-in functions instead of writing out the math

Excel comes with hundreds of pre-written formulas called functions that do common tasks. Instead of typing =A1+A2+A3+A4+A5, you can type =SUM(A1:A5). The colon between A1 and A5 means "from A1 through A5" — a range. SUM adds all the numbers in that range.

Other common functions are AVERAGE (which adds up the numbers and divides by how many there are), COUNT (which counts how many cells have numbers in them), and MIN or MAX (which find the smallest or largest number). To use any of them, type the function name, then put the range or cell references in parentheses: =AVERAGE(B2:B10) or =COUNT(C1:C20).

The IF function lets you make a decision. =IF(A1>100,"Over budget","OK") checks whether the number in A1 is greater than 100. If it is, the cell shows "Over budget". If it is not, the cell shows "OK". This is useful for flagging problems in a budget or marking whether a task is complete.

Copying formulas down columns and across rows

Once you have written a formula in one cell, you can copy it to many cells at once, and Excel will adjust the cell references for you automatically. Click the cell with the formula. Look at the bottom right corner of the cell — there is a small square called the fill handle. Click and drag that square down the column (or across the row) to copy the formula to as many cells as you need.

When you drag down, Excel changes the row numbers automatically. If your first formula is =A1+B1 in row 1, dragging down to row 5 creates =A2+B2, =A3+B3, =A4+B4, and =A5+B5 in the rows below. The column letters stay the same because you are moving down, not across. This is called a relative reference — the formula adjusts based on where it is.

If you want a cell reference to stay the same when you copy the formula, put a dollar sign in front of the column letter or row number. =A$1+B1 keeps row 1 locked but lets the B change to C, D, and so on if you copy across. =$A1+B1 keeps column A locked but lets the row number change. This is called an absolute reference.

Checking and fixing formulas that do not work

If a formula shows an error like #DIV/0!, #REF!, or #NAME?, something is wrong with the instruction. Click the cell with the error and look at the formula bar to see what you typed. #DIV/0! means you tried to divide by zero or by an empty cell. #REF! means the formula points to a cell that no longer exists — usually because you deleted a column or row. #NAME? means you misspelled a function name or used a cell reference that does not exist.

If the formula runs but gives you the wrong answer, check that you are pointing at the right cells. Click the cell with the formula and look at the formula bar. The cells that the formula uses will be outlined in colored boxes on the spreadsheet — this helps you see whether you are adding the right numbers. If the colored boxes are in the wrong place, click inside the formula bar and change the cell references.

A common mistake is forgetting the equals sign at the start. If you type A1+B1 without the =, Excel treats it as text, not a calculation. Another common mistake is using a comma instead of a colon in a range — =SUM(A1,A5) adds only cells A1 and A5, while =SUM(A1:A5) adds all cells from A1 through A5.

Formulas that compare and make decisions

Beyond straightforward math, formulas can compare numbers and text to help you organize information. The comparison operators are > (greater than), < (less than), = (equal to), >= (greater than or equal to), <= (less than or equal to), and <> (not equal to). These are used inside IF functions to make the spreadsheet decide what to do.

=IF(A1>50,A1*0.1,0) calculates a 10 percent bonus if the number in A1 is greater than 50, otherwise it shows 0. =IF(B1="Complete","Done","Pending") checks whether B1 says "Complete" and shows "Done" if it does, or "Pending" if it does not. You can nest IF functions inside each other to handle more complex decisions, though this gets hard to read quickly.

Frequently Asked Questions

What is the difference between a cell reference and a number in a formula?

A cell reference like A1 points to a box on the spreadsheet, so if the number in that box changes, the formula result updates automatically. A number like 15 stays the same no matter what. Use cell references when the data might change, and numbers when you are describing a fixed rule — like a tax rate or a discount percentage.

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

Yes, using the ampersand & or the CONCATENATE function. =A1&" "&B1 joins the text in A1, a space, and the text in B1. If A1 says "John" and B1 says "Smith", the result is "John Smith". This is useful for combining first and last names or building descriptions from separate pieces of information.

Why does my formula show the formula itself 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. If the formula still shows as text, delete it and retype it — sometimes Excel needs you to re-enter it after changing the format.

Can I use a formula to count how many cells have a certain word in them?

Yes, using COUNTIF. =COUNTIF(A1:A20,"apple") counts how many cells in the range A1 through A20 contain the word "apple". You can also use wildcards — =COUNTIF(A1:A20,"app*") counts cells that start with "app", like "apple", "process", or "appraisal".

What happens if I copy a formula to a cell that already has data?

The old data is replaced by the formula. If you are not sure, copy to an empty area first to check that the formula works the way you expect. You can always undo with Ctrl+Z (or Command+Z on a Mac) if you make a mistake.