What a formula does and where to type it

A formula in Excel is an instruction that tells a cell to perform a calculation or operation on other cells. Every formula starts with an equals sign (=). When you type =2+2 into a cell and press Enter, Excel shows you 4. When you type =A1+B1, Excel adds whatever numbers are in cells A1 and B1 and displays the result.

You type formulas directly into the cell where you want the answer to appear. Click on any cell, type your formula starting with =, and press Enter. The cell then shows the result, but the formula bar at the top (the long white box above your spreadsheet) displays the actual formula you wrote. This matters because if a number in one of your source cells changes, the formula automatically recalculates.

Formulas are what make spreadsheets useful beyond just storing numbers in rows. Without formulas, you would have to manually recalculate and re-enter numbers every time something changed. With formulas, the spreadsheet does the work for you.

Key Takeaways

  • Every formula starts with = and can reference other cells by their letter and number (A1, B2, C3) or use straightforward math operators like +, −, *, and /.
  • The most common formulas are SUM (adds a range of cells), AVERAGE (finds the middle value), and COUNT (counts how many cells have numbers).
  • When you change a number in a cell that a formula references, the formula result updates automatically without you having to rewrite anything.
  • You can copy a formula to other cells and Excel adjusts the cell references automatically, so =A1+B1 becomes =A2+B2 when you copy it down one row.

Basic math formulas and how they work

The simplest formulas use the four basic math operators: plus (+), minus (−), multiply (*), and divide (/). If you have a price in cell A1 and a quantity in cell B1, you can type =A1*B1 to get the total cost. If you have an original amount in A1 and a discount in B1, you can type =A1−B1 to find what remains.

The order matters when you combine operations. Excel follows the standard math rule called order of operations: multiplication and division happen before addition and subtraction. So =2+3*4 gives you 14 (not 20), because Excel multiplies 3*4 first, then adds 2. If you want to add first, use parentheses: =(2+3)*4 gives you 20.

You can reference as many cells as you need in one formula. =A1+B1+C1+D1 adds four cells together. You can also mix cell references with plain numbers: =A1*1.1 multiplies the value in A1 by 1.1 (useful for calculating a 10% increase).

SUM, AVERAGE, and COUNT — the three formulas you will use most

SUM adds up all the numbers in a range of cells. Instead of typing =A1+A2+A3+A4+A5, you type =SUM(A1:A5). The colon (:) means "through", so A1:A5 means all cells from A1 to A5. This saves time and reduces mistakes when you have many cells to add.

AVERAGE finds the middle value. =AVERAGE(A1:A5) adds all five numbers and divides by 5. This is useful for calculating class grades, monthly spending, or any time you need a typical value from a group.

COUNT tells you how many cells in a range contain numbers (not text or blank cells). =COUNT(A1:A5) returns 5 if all five cells have numbers, or 3 if only three of them do. This helps you verify that data was entered correctly or find out how many responses you received.

All three of these follow the same pattern: the function name, then parentheses, then the range of cells. You can use them on any range, whether it is five cells or five hundred.

Copying formulas and letting Excel adjust the references

Once you write a formula in one cell, you can copy it to other cells and Excel automatically adjusts the cell references. If you type =A1+B1 in cell C1, then copy that cell and paste it into C2, the formula becomes =A2+B2. Copy it to C3 and it becomes =A3+B3. This is called a relative reference — the formula adjusts based on where you paste it.

To copy a formula, click the cell with the formula, then use Ctrl+C (or Cmd+C on Mac) to copy. Click the cell where you want it to go and use Ctrl+V (or Cmd+V) to paste. You can also click and drag the small square in the bottom-right corner of a cell downward to copy the formula to multiple cells at once.

Sometimes you want a cell reference to stay the same when you copy. If you want to always multiply by the value in B1, no matter where you copy the formula, use =A1*$B$1. The dollar signs lock that reference so it never changes. This is called an absolute reference. You can lock just the column ($B1), just the row (B$1), or both ($B$1).

Common formula mistakes and how to spot them

If you see #NAME? in a cell, you likely misspelled a function name. Excel does not recognize =SUMM(A1:A5) because the correct spelling is SUM. Check the function name against what you intended.

If you see #DIV/0!, you tried to divide by zero or by an empty cell. The formula =A1/B1 will show this error if B1 is blank or contains 0. Fix it by checking that your divisor (the number you are dividing by) is not zero or empty.

If a formula shows a number but it looks wrong, check that you are referencing the correct cells. Click on the cell with the formula and look at the formula bar. The cells it references will be highlighted with colored boxes so you can see exactly which cells are being used.

If you copy a formula and the results do not look right, you may have meant to use an absolute reference (with $) but used a relative reference instead. Click one of the copied cells and check the formula bar to see what references it contains.

Using formulas across sheets and with text

You can write a formula that references cells in a different sheet. If you have a sheet called "January" and another called "February", you can type =January!A1+February!A1 to add the same cell from both sheets. The exclamation mark (!) tells Excel to look in a different sheet. On Mac, use a period instead: =January.A1+February.A1.

Most formulas work with numbers, but some work with text. The CONCATENATE function (or the & symbol) joins text together. =A1&" "&B1 combines the text in A1, a space, and the text in B1. If A1 contains "John" and B1 contains "Smith", the result is "John Smith".

The LEN function counts how many characters are in a cell. =LEN(A1) returns the number of letters, numbers, and spaces in cell A1. The UPPER and LOWER functions convert text to all capitals or all lowercase: =UPPER(A1) and =LOWER(A1).

When to use formulas instead of just typing numbers

Use a formula whenever the answer depends on other cells that might change. If you are tracking a budget and you have income in one cell and expenses in another, use =A1−B2 to calculate what remains. When the income or expenses change, the result updates automatically. If you typed the number 500 directly, you would have to remember to change it manually.

Use a formula when you need the same calculation in many rows. A spreadsheet with 100 products and their prices is much easier to manage with a formula in the total column that you copy down, rather than calculating each total by hand.

Use a formula when accuracy matters. Typing numbers by hand introduces the risk of typos. A formula that references other cells in your spreadsheet is only as accurate as your source data, but it will not introduce new mistakes.

Frequently Asked Questions

What is the difference between a formula and a function?

A formula is any instruction that starts with =, including =2+2 or =A1*B1. A function is a named operation like SUM or AVERAGE. So every function is a formula, but not every formula uses a function. =A1+B1 is a formula that does not use a function; =SUM(A1:B1) is a formula that uses the SUM function.

Can I use a formula to check if a cell is empty?

Yes, using the IF function. =IF(A1="","empty","not empty") checks whether A1 is blank and displays the word "empty" or "not empty" accordingly. You can also use =ISBLANK(A1) to return TRUE if the cell is empty or FALSE if it contains anything.

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

The cell is probably formatted as text. Right-click the cell, select Format Cells, and change the format to Number or General. Then edit the formula (click in the formula bar and press Enter) and it should calculate. This sometimes happens when you copy a formula from a website or email.

Can I use a formula to find the largest or smallest number in a range?

Yes. =MAX(A1:A10) returns the largest number in that range, and =MIN(A1:A10) returns the smallest. Both work the same way as SUM and AVERAGE — type the function name, then the range in parentheses.

What does it mean when a formula shows #REF!?

The formula references a cell that no longer exists, usually because you deleted a column or row that the formula was using. Check the formula bar to see which cell it is trying to reference, then either restore the deleted column or row, or rewrite the formula to reference the correct cells.