Excel Advanced - Performing Calculations
Performing Calculations
Build dependable worksheet calculations and understand how Excel evaluates formulas, references and functions.
Learning outcomes
What you will be able to do
By the end of this lesson, you will have transformed raw sales data into a complete invoice calculation worksheet.
Download the Lesson 1 practice dataset
The same embedded JSON dataset powers both downloads. The Excel version includes a Practice Data sheet and an Instructions sheet.
Dataset orientation
Understand the worksheet layout
Open the downloaded file. The raw data occupies columns A:H. You will create the calculated columns I:M and a formula check in N.
| A Order ID | B Order Date | C Region | D Product | E Quantity | F Unit Price | G Discount Rate | H Payment Status | I Gross Sales | J Discount Amount | K Net Sales | L Sales Tax | M Final Amount | N Formula Check |
|---|
Guided activity
Build the calculation worksheet
Calculate Gross Sales with a relative reference
Select I2. Multiply Quantity in E2 by Unit Price in F2.
=E2*F2Press Enter. The result for ORD-1001 should be RM2,499.50.
=E3*F3.Calculate the Discount Amount
Select J2 and multiply Gross Sales by Discount Rate.
=I2*G2Format J2 as Currency. The first result should be RM249.95.
Control evaluation with subtraction and parentheses
Select K2. Net Sales is Gross Sales minus Discount Amount.
=I2-J2An equivalent single formula is =E2*F2*(1-G2). Parentheses force Excel to calculate 1-G2 as one expression.
Lock the tax rate with an absolute reference
Enter Tax Rate in P1 and 8% in P2. Select L2 and multiply Net Sales by the locked tax cell.
=K2*$P$2Press F4 after selecting P2 inside the formula to cycle through reference types.
$P$2 remains unchanged when the formula is filled down. Without the dollar signs, Excel would shift to P3, P4 and empty cells.Calculate the Final Amount
Add Net Sales and Sales Tax in M2.
=K2+L2You can also use a function:
=SUM(K2:L2)Add a nested formula check
Use IF with SUM to verify that Final Amount equals Net Sales plus Sales Tax.
=IF(M2=SUM(K2:L2),"OK","Check")This nested formula uses SUM inside IF. N2 should display OK.
Fill all formulas down
Select I2:N2. Double-click the fill handle, or drag it down to row 21. Check the last-row formulas: I21 should refer to E21 and F21, while L21 should still refer to $P$2.
Create a named range for the tax rate
Select P2, click the Name Box to the left of the Formula Bar, type TaxRate, and press Enter. Replace the L2 formula with:
=K2*TaxRateFill it down again. Named ranges make business assumptions easier to recognise and audit.
Functions and summaries
Create a calculation summary
Enter these labels and formulas in a clear summary area, for example R1:S7.
Core summary functions
- Total Final Amount:
=SUM(M2:M21) - Average Final Amount:
=AVERAGE(M2:M21) - Highest Final Amount:
=MAX(M2:M21) - Lowest Final Amount:
=MIN(M2:M21)
Conditional summary functions
- Paid orders:
=COUNTIF(H2:H21,"Paid") - Central gross sales:
=SUMIF(C2:C21,"Central",I2:I21) - High-value invoices:
=COUNTIF(M2:M21,">=2000") - Rows with checks:
=COUNTIF(N2:N21,"Check")
| Control total | Expected result | Use |
|---|---|---|
| Total Gross Sales | — | Confirm columns I and the fill-down operation |
| Total Discount | — | Confirm discount percentages |
| Total Net Sales | — | Confirm subtraction |
| Total Sales Tax | — | Confirm the absolute tax reference |
| Total Final Amount | — | Final reconciliation |
| Paid Orders | — | Confirm COUNTIF |
Formula auditing
Inspect before you trust
Show formulas
Use Formulas → Show Formulas, or press Ctrl + `, to display formulas instead of results. Scan column L: every row must contain $P$2 or TaxRate.
Trace relationships
Select M2 and use Trace Precedents. Excel should point to K2 and L2. Use Trace Dependents on K2 to see which later calculations use it.
Evaluate a formula
Select K2 and choose Evaluate Formula. Step through the multiplication and subtraction to see Excel's order of operations.
Inspect copied references
Compare I2, I10 and I21 in the Formula Bar. Row references should change consistently, while the tax assumption remains locked.
Troubleshooting
Recognise common formula errors
#DIV/0!A formula divides by zero or an empty cell. Confirm the denominator before dividing, or use IFERROR when appropriate.
#VALUE!A formula receives the wrong data type, such as text where a number is expected. Inspect the source cell.
#NAME?Excel does not recognise a function or named range. Check spelling and quotation marks.
#REF!A formula contains an invalid reference, often because a referenced row, column or cell was deleted.
#N/AA lookup cannot find a matching value. Check spelling, data types and the match mode.
Wrong resultNo error appears, but the logic is wrong. Check parentheses, reference types and copied ranges.
Knowledge check
Can you explain the formula?
1. Why does the tax formula use $P$2?
Both the column and row are locked, so every copied formula continues to use the single tax-rate assumption.
2. What will =E2*F2 become when copied to row 10?
=E10*F10, because E2 and F2 are relative references.
3. What is the difference between $B2 and B$2?
$B2 locks column B but allows the row to change. B$2 locks row 2 but allows the column to change.
4. Why use =SUM(K2:L2) instead of =K2+L2?
Both are correct here. SUM becomes more convenient and maintainable when adding a larger continuous range.
5. Which tool shows cells used by a selected formula?
Trace Precedents displays arrows from the source cells to the selected formula.
View the embedded tutorial dataset in JSON format
Lesson 1 complete
You have created a formula-driven invoice worksheet, controlled copied references, used functions, named an assumption and verified the calculation chain.