To create an Excel spreadsheet with formulas, enter your data in labeled columns, select the cell where you want the result, type an equals sign followed by a formula, and press Enter. For example, =B2*C2 multiplies the value in cell B2 by the value in cell C2. Excel formulas begin with = and can use cell references, ranges, mathematical operators and functions.
Example Excel Spreadsheet with Formulas
Create a sales spreadsheet with these headings:
| Cell | Heading |
|---|---|
| A1 | Product |
| B1 | Quantity |
| C1 | Unit Price |
| D1 | Total |
Then enter the sample data:
| Product | Quantity | Unit Price | Total |
|---|---|---|---|
| Notebook | 3 | 4.50 | =B2*C2 |
| Pen | 10 | 1.25 | =B3*C3 |
| Folder | 5 | 2.00 | =B4*C4 |
In cell D2, enter:
=B2*C2
This multiplies the quantity in B2 by the unit price in C2. To use the same calculation for the other products, drag the small square in the bottom-right corner of D2 down to D4. Excel changes the relative references for each row, producing =B3*C3 and =B4*C4.
How to Enter a Formula in Excel
- Open Excel and create a blank workbook.
- Add headings such as
Product,Quantity,Unit PriceandTotal. - Enter the data below the headings.
- Select the cell where you want the result.
- Type
=. - Enter the cell references and the mathematical operator.
- Press Enter.
To calculate the first product total, select D2 and enter:
=B2*C2
You can also build the formula by clicking the cells. Type =, click B2, type *, click C2, and press Enter. Excel inserts the references for you.
Common Excel Formulas
| Task | Formula example | What it does |
|---|---|---|
| Add two cells | =B2+C2 |
Adds B2 and C2 |
| Subtract one cell from another | =B2-C2 |
Subtracts C2 from B2 |
| Multiply cells | =B2*C2 |
Multiplies B2 by C2 |
| Divide cells | =B2/C2 |
Divides B2 by C2 |
| Add a range | =SUM(B2:B10) |
Adds the values from B2 through B10 |
| Calculate an average | =AVERAGE(B2:B10) |
Returns the average |
| Find the highest value | =MAX(B2:B10) |
Returns the largest value |
| Find the lowest value | =MIN(B2:B10) |
Returns the smallest value |
| Count numbers | =COUNT(B2:B10) |
Counts cells containing numbers |
| Test a condition | =IF(B2>=70,"Pass","Fail") |
Returns one result when the condition is true and another when it is false |
Change 70 to the pass mark you need, or replace it with a cell reference. For example:
=IF(B2>=70,"Pass","Fail")
How to Total a Column with AutoSum
To total a column quickly:
- Select the empty cell directly below the numbers.
- Select AutoSum on the Home or Formulas tab.
- Check the highlighted range.
- Press Enter.
Excel creates a SUM formula such as:
=SUM(D2:D4)
AutoSum works with vertical columns and horizontal rows. Excel usually selects the nearby range automatically, but check the highlighted cells before pressing Enter.
How to Create a Complete Sales Calculation
In the sample spreadsheet, enter this formula in D2:
=B2*C2
Copy it down through D4. Then enter this formula below the Total column, such as in D5:
=SUM(D2:D4)
D5 will contain the combined total for all three products.
To calculate sales tax, place the tax rate in F1 and use:
=D5*$F$1
The dollar signs make $F$1 an absolute reference. It stays fixed when you copy the formula to another cell. A relative reference such as B2 changes when copied, while an absolute reference such as $F$1 does not.
How to Copy Formulas to Other Rows
After entering a formula:
- Select the cell containing the formula.
- Move the pointer to the small square in the cell's bottom-right corner.
- Drag the fill handle down or across.
- Release the mouse button.
For instance, copying this formula from D2:
=B2*C2
to D3 changes it to:
=B3*C3
You can also copy the formula with Ctrl+C and paste it with Ctrl+V. Check at least the first copied result to make sure the references changed as expected.
How to Use Formulas in an Excel Table
For a growing dataset, convert the range into an Excel Table. Tables can extend calculated-column formulas when you add new rows, and their column names can make formulas easier to read.
A table formula for the sales example is:
=[@Quantity]*[@[Unit Price]]
This multiplies the Quantity and Unit Price values in the current row. Excel uses structured references in tables and can apply the calculated-column formula throughout the column.
Common Excel Formula Errors
| Error | Common cause | Fix |
|---|---|---|
#DIV/0! |
The formula divides by zero or a blank cell | Check the divisor or use IFERROR |
#VALUE! |
The formula uses an incompatible value, such as text instead of a number | Check the cell contents and formatting |
#REF! |
The formula refers to a deleted or invalid cell | Replace the broken reference |
#NAME? |
A function or named range is misspelled | Check the function name and spelling |
##### |
The column is too narrow to display the result | Widen the column |
Excel returns #DIV/0! when a calculation divides by zero or a blank cell. If the error is expected, you can use:
=IFERROR(A2/B2,0)
This returns 0 when the division produces an error. Check the original formula before using IFERROR, since it can hide a problem that needs fixing.
Best Practices for Excel Formulas
- Use cell references instead of typing the same values into multiple formulas.
- Give every column a clear heading.
- Keep input values separate from calculated results.
- Use
SUM(B2:B10)instead of adding each cell manually. - Use parentheses when the calculation order matters, such as
=(B2+C2)*D2. - Use absolute references for fixed values such as tax rates, discounts or exchange rates.
- Format prices as currency and percentages as percentages.
- Check formulas after copying them to new rows.
- Convert larger datasets into Excel Tables.
- Avoid deleting cells or columns that existing formulas depend on.