Excel Formulas for Beginners: Build and Check Your First Calculations
On this page
Your first useful Excel formula does not need to be complicated. It needs to take the right inputs, calculate a result and update when those inputs change. In this lesson, you will build a small sales sheet and check each result yourself.
Before you start: Use a new practice workbook. The names and figures below are fictional. Enter the headers in row 1 and the first record in row 2 unless a step says otherwise. Formulas use English function names and commas; your regional settings may require semicolons instead. Save a copy before experimenting.
Enter a small dataset
Type this data into A1:C5. Each row represents one product line. Quantity is a count of units, while Unit price is the price of one unit in fictional currency units.
| Product | Quantity | Unit price |
|---|---|---|
| Notebook | 3 | 120 |
| Pen | 10 | 25 |
| Folder | 4 | 80 |
| Marker | 2 | 150 |
Put Line total in D1. Keep numbers such as 120 as numeric entries rather than typing a currency word into the same cell. You can apply a currency number format later. A format changes how a value appears without turning it into a sentence.
Begin a formula with an equals sign
Select D2, type =B2*C2 and press Enter. Excel multiplies the quantity in B2 by the unit price in C2. The result is 360. The asterisk means multiplication; Excel does not use the letter x for this operation.
A cell reference such as B2 is an address. The letter identifies the column and the number identifies the row. Referring to the input cells is better than writing =3*120 because the result can update when the inputs change.
Select D2 again and look at the formula bar. The cell shows the calculated value, while the formula bar shows the formula. Knowing how to inspect both helps you distinguish an entered number from a calculated one.
Fill the formula down
Copy D2 into D3:D5 using copy and paste or the fill handle at the corner of the selection. Relative references move with the formula. D3 should contain =B3*C3, D4 should contain =B4*C4, and D5 should contain =B5*C5.
The line totals should be 360, 250, 320 and 300. Inspect at least one copied formula instead of assuming the fill worked. If you paste 360 as a value into every row, the numbers will look tidy but will not calculate each product separately.
Type Grand total in C7 and =SUM(D2:D5) in D7. The colon means the inclusive range from D2 through D5. The total is 1230. SUM is easier to maintain than a long addition formula, especially as the list grows.
Understand calculation order
Excel performs multiplication and division before addition and subtraction. =2+3*4 gives 14 because 3 times 4 is calculated first. =(2+3)*4 gives 20 because the parentheses make the addition happen first.
Parentheses are particularly useful in pricing or percentage calculations. Write the business calculation in words before translating it into a formula. For example, “quantity times unit price” gives a line total, while “line total minus discount amount” gives a different result.
Test whether the sheet responds correctly
Change the Notebook quantity in B2 from 3 to 4. D2 should become 480 and D7 should become 1350. Change B2 back to 3 after checking. This small input-change test helps reveal a hard-coded value or a total that points to the wrong range.
If Excel displays the formula as text, check whether the cell was formatted as Text or whether you typed an apostrophe before the equals sign. Change the format to General if needed, then re-enter the formula. If every formula is displayed, check the Show Formulas setting.
Division by zero produces #DIV/0!. A deleted reference can produce #REF!. Do not replace every error with zero before finding its cause. A visible error is often a clue that an input or reference needs attention.
Try it yourself
Add Price after 10% discount in E1. Calculate the discounted unit price in E2, then copy it down. Add Discounted line total in F1 and calculate quantity times discounted price. What should F2 and the sum of F2:F5 be?
Check your answer
In E2 use =C2*(1-10%), then fill through E5. In F2 use =B2*E2, then fill through F5. F2 should be 324. The other discounted totals are 225, 288 and 270, and =SUM(F2:F5) returns 1107.
The 10% is a rate. Multiplying by 1 minus that rate keeps 90% of the original price. If you subtract 10 directly from each price, you are applying a fixed amount discount instead of a percentage discount.
Keep learning: Return to the Excel learning path to choose your next lesson.