Formulas are the heart of Excel. Without them, a spreadsheet is just a table of static numbers. With them, it becomes a living calculation engine that instantly recalculates when data changes. This module covers what a formula is, how Excel processes it, every arithmetic operator, operator precedence (the order of calculation), and the four main methods used to enter and build formulas: manual entry, AutoSum, the Insert Function wizard, and the Formulas tab. Every module from here onwards builds on these foundations.
A formula is an instruction that tells Excel to calculate a result. Every formula in Excel follows the same rules:
=). If you forget the equals sign, Excel treats your entry as text.| Entry | Excel Sees It As | Cell Displays |
|---|---|---|
1500 | A numeric value (number) | 1500 (right-aligned) |
Total Revenue | Text | Total Revenue (left-aligned) |
=1500 | A formula (evaluates to 1500) | 1500 (right-aligned) |
=A1+B1 | A formula referencing cells A1 and B1 | The sum of A1 and B1 |
=SUM(A1:A10) | A formula using a function | The total of cells A1 through A10 |
Operators tell Excel what calculation to perform between values or cell references. Excel uses the following arithmetic operators:
| Operator | Operation | Example | Result |
|---|---|---|---|
| + | Addition | =A1+B1 or =500+300 | Sum of the two values |
| − | Subtraction | =A1-B1 or =1000-250 | Difference of the two values |
| * | Multiplication | =A1*B1 or =150*12 | Product of the two values |
| / | Division | =A1/B1 or =3600/12 | Quotient of the two values |
| ^ | Exponentiation (power) | =A1^2 or =2^10 | Value raised to a power (A1 squared; 2 to the power 10 = 1024) |
| % | Percentage | =A1*15% | 15% of the value in A1 (same as =A1*0.15) |
| & | Concatenation (join text) | =A1&" "&B1 | Joins the text in A1 and B1 with a space between (e.g., "John" & " " & "Smith" = "John Smith") |
Comparison operators return TRUE or FALSE and are used in IF functions and logical tests:
| Operator | Meaning | Example |
|---|---|---|
= | Equal to | =A1=100 → TRUE if A1 is exactly 100 |
<> | Not equal to | =A1<>0 → TRUE if A1 is anything except 0 |
> | Greater than | =A1>50000 → TRUE if A1 exceeds 50,000 |
< | Less than | =A1<0 → TRUE if A1 is negative |
>= | Greater than or equal to | =A1>=18 → TRUE if A1 is 18 or more |
<= | Less than or equal to | =A1<=100 → TRUE if A1 is 100 or less |
When a formula contains multiple operators, Excel does not calculate from left to right. It follows a strict order of precedence. Getting this wrong is one of the most common causes of incorrect formula results.
| Priority | Operator | Operation |
|---|---|---|
| 1 (highest) | ( ) | Parentheses (brackets) — calculated first, innermost first |
| 2 | ^ | Exponentiation (power) |
| 3 | − (negative sign) | Unary minus (negating a number, e.g., −5) |
| 4 | % | Percentage |
| 5 | * / | Multiplication and Division (equal priority, left to right) |
| 6 | + − | Addition and Subtraction (equal priority, left to right) |
| 7 | & | Text concatenation |
| 8 (lowest) | = < > <= >= <> | Comparison operators |
| Formula | Excel Calculates | Result | Common Mistake |
|---|---|---|---|
=2+3*4 |
3*4=12 first, then 2+12 | 14 | Expecting 20 (left to right). Multiplication has higher precedence than addition. |
=(2+3)*4 |
2+3=5 first (brackets), then 5*4 | 20 | Parentheses force addition first — use brackets to control the order |
=10-2*3 |
2*3=6 first, then 10-6 | 4 | Expecting 24 (10-2=8, then 8*3=24). Add brackets if that is the intent: =(10-2)*3 |
=A1+B1/C1 |
B1/C1 first, then A1+result | A1 + (B1/C1) | If you want (A1+B1)/C1, you must write: =(A1+B1)/C1 |
=-2^2 |
2^2=4 first, then −4 | −4 | Expecting 4. Exponentiation before negation. Write =(-2)^2 to get 4. |
=(A1+B1)/(C1+D1) is always clearer than relying on precedence rules.
Manual entry is the most direct way to write a formula — you type it yourself, character by character. It requires knowing the formula syntax but gives you full control.
= (the equals sign — this tells Excel what follows is a formula)+ - * /)Instead of typing cell addresses (e.g., A1, B5), you can click the cells after typing the equals sign or an operator. Excel enters the cell address automatically. This is called Point Mode and reduces typing errors:
=A1 in the formula and highlights A1 with a coloured border+B1=SUM(A1:A10) to close the function → press EnterAs you type a formula, Excel's IntelliSense displays a dropdown of matching function names and a tooltip explaining each one:
=SU → Excel shows a dropdown: SUM, SUMIF, SUMIFS, SUMPRODUCT, SUBSTITUTE…When editing a formula (in Edit Mode — F2 or double-click), Excel colour-codes each cell reference and its corresponding cell with the same colour. This makes it easy to visually verify which cells the formula is referencing:
=A1+B1 → A1 is highlighted in blue, B1 in green (colours vary per formula)| Task | Formula |
|---|---|
| Add two cells | =A1+B1 |
| Subtract | =A1-B1 |
| Multiply (e.g., units × price) | =B2*C2 |
| Divide (e.g., total ÷ months) | =D2/12 |
| Calculate VAT (15% of price) | =B2*15% or =B2*0.15 |
| Price including VAT | =B2*1.15 or =B2+(B2*15%) |
| Average of three cells | =(A1+B1+C1)/3 |
| Gross profit margin | =(B2-C2)/B2 (format result as %) |
| Annual salary from monthly | =B2*12 |
| Join first and last name | =A2&" "&B2 |
| Compound interest (simplified) | =P*(1+r)^n (where P, r, n are cell references) |
AutoSum is Excel's one-click formula tool. It automatically detects adjacent data and inserts the most appropriate formula — usually SUM — with the range already filled in. It is the fastest way to sum a column or row.
The AutoSum button has a dropdown arrow ▼ that reveals additional one-click functions:
| Option | Function Inserted | What It Calculates |
|---|---|---|
| Sum ★ | =SUM(A1:A10) | Total of the range |
| Average | =AVERAGE(A1:A10) | Mean value of the range |
| Count Numbers | =COUNT(A1:A10) | Number of cells containing numeric values |
| Max | =MAX(A1:A10) | The largest value in the range |
| Min | =MIN(A1:A10) | The smallest value in the range |
| More Functions… | Opens Insert Function dialog | Access to all Excel functions |
The Insert Function wizard (also called the Function Arguments dialog) guides you through entering any Excel function step by step, with descriptions of each argument and a live preview of the result. It is the best method when you know roughly what you want to do but are not sure of the exact syntax.
The Formulas tab is the dedicated formula control centre of Excel. It organises all built-in functions by category and provides auditing, naming, and calculation management tools.
| Group | Key Tools | Use For |
|---|---|---|
| Function Library | Insert Function, AutoSum, Recently Used, Financial, Logical, Text, Date & Time, Lookup & Reference, Math & Trig, More Functions (Statistical, Engineering, Cube, Information, Compatibility, Web) | Browsing and inserting functions by category — click any category button to see a list of functions in that group. Click any function name to open the Function Arguments dialog for that function. |
| Defined Names | Name Manager, Define Name, Use in Formula, Create from Selection | Creating and managing named ranges — e.g., naming B2:B50 "Salaries" so you can write =SUM(Salaries) instead of =SUM(B2:B50). (Covered in Module 14) |
| Formula Auditing | Trace Precedents, Trace Dependents, Remove Arrows, Show Formulas, Error Checking, Evaluate Formula, Watch Window | Debugging formulas — tracing which cells feed into a formula, finding errors, stepping through a formula's calculation sequence |
| Calculation | Calculation Options (Automatic / Manual), Calculate Now (F9), Calculate Sheet (Shift+F9) | Controlling when formulas recalculate; forcing a manual recalculation in large workbooks |
Every Excel function follows the same syntax structure. Understanding it means you can read and write any function correctly.
| Term | Meaning | Example |
|---|---|---|
| Function Name | The name of the built-in calculation to perform — always uppercase by convention but not case-sensitive | SUM, AVERAGE, IF, VLOOKUP |
| Opening bracket | Opens the argument list — must always follow the function name with no space | SUM( |
| Arguments (required) | The inputs the function needs to calculate its result. Shown in the syntax tooltip without brackets. | VLOOKUP(lookup_value, table_array, col_index_num, ...) |
| Arguments [optional] | Arguments shown in square brackets in the syntax tooltip are optional — the function works without them, using default values | VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) |
| Comma separator | Separates each argument from the next. In some regional settings (e.g., some SA Windows configurations), the separator may be a semicolon (;) instead of a comma. | =IF(A1>0 , "Positive" , "Zero or negative") |
| Closing bracket | Closes the argument list. Every opening bracket must have a matching closing bracket — mismatched brackets are a common error. | SUM(A1:A10) |
| Nested functions | A function used as an argument inside another function. Excel evaluates the innermost function first. | =ROUND(AVERAGE(A1:A10), 2) — AVERAGE is calculated first, then ROUND is applied to the result |
| Argument Type | What to Enter | Examples |
|---|---|---|
| Number | A numeric value or cell reference containing a number | 100, A1, B2*1.15 |
| Range | A colon-separated cell range | A1:A100, B2:F50 |
| Text (string) | Text enclosed in double quotation marks | "Cape Town", "Yes", ">0" |
| Logical (TRUE/FALSE) | The value TRUE or FALSE, or a comparison that produces TRUE/FALSE | TRUE, FALSE, A1>0 |
| Error | Meaning | Common Cause | Fix |
|---|---|---|---|
| #DIV/0! | Division by zero | The denominator in a division formula is 0 or blank | Wrap with IFERROR: =IFERROR(A1/B1, 0) or =IF(B1=0, 0, A1/B1) |
| #VALUE! | Wrong data type in formula | Trying to add text to a number; a cell that looks like a number but is stored as text | Check all referenced cells contain the correct data type. Use VALUE() to convert text-numbers. |
| #REF! | Invalid cell reference | A cell the formula referenced has been deleted; a formula was copied to a position that makes a relative reference point outside the sheet | Ctrl+Z to undo deletion if possible. Otherwise, update the formula reference manually. |
| #NAME? | Unrecognised name in formula | Misspelled function name (e.g., =SUUM); text not in quotation marks; named range that does not exist; function not available in this Excel version |
Check spelling; add quotation marks around text arguments; verify the named range exists in Name Manager |
| #N/A | Value not available | VLOOKUP or MATCH cannot find the lookup value in the search range; exact match required but value does not exist in the table | Verify the lookup value exists in the table. Wrap with IFERROR: =IFERROR(VLOOKUP(...), "Not found") |
| #NULL! | Empty intersection | Two ranges specified with a space (intersection operator) do not overlap: =SUM(A1:A10 C1:C10) |
Use a comma instead of a space: =SUM(A1:A10, C1:C10) |
| #NUM! | Invalid numeric value | A formula produces a number too large or too small for Excel; square root of a negative number; an iterative function that cannot converge | Check inputs are within valid ranges (e.g., SQRT requires a non-negative number) |
| ####### | Column too narrow | The column is too narrow to display the formatted value (common with dates and currency) | Double-click the column border to AutoFit, or drag the border to widen the column |
Q1: What result does =4+3*2 return, and why? Write the corrected formula if you wanted the result to be 14.
✓ =4+3*2 returns 10. Multiplication (*) has higher precedence than addition (+), so Excel calculates 3*2=6 first, then 4+6=10. It does NOT calculate left to right as 4+3=7, then 7*2=14. To get 14: write =(4+3)*2. Parentheses force the addition to happen first: 4+3=7, then 7*2=14.
Q2: You have a column of monthly salaries in B2:B51 (50 employees). You want the total in B52. Write the formula using two different methods and state which is faster.
✓ Method 1 (Manual): click B52 → type =SUM(B2:B51) → Enter. Method 2 (AutoSum): click B52 → press Alt+= → verify the proposed range (B2:B51) → press Enter. Both formulas are identical. AutoSum is faster because Excel detects the range automatically — you do not need to type the range address. For a single total column, Alt+= takes about 2 seconds.
Q3: A cell shows #NAME?. Looking at the formula, you see =SUIM(A1:A10). What is the cause and the fix?
✓ The function name is misspelled — "SUIM" does not exist in Excel. #NAME? always indicates Excel cannot recognise something in the formula, most commonly a misspelled function name. Fix: click the cell → press F2 to enter edit mode → correct "SUIM" to "SUM" → press Enter. The formula becomes =SUM(A1:A10) and returns the correct total.
Q4: You want to calculate a price including 15% VAT. Cell B2 contains the VAT-exclusive price. Write three different formulas that all produce the correct VAT-inclusive result.
✓ All three produce the same VAT-inclusive price: (1) =B2*1.15 — multiplies price by 1.15 (100% + 15% = 115%). (2) =B2+B2*15% — adds 15% of the price to the original price. (3) =B2+(B2*0.15) — explicitly calculates 15% as 0.15 and adds it. Method 1 is the most concise and professional. Method 2 and 3 are more readable for beginners. If B2 = R100, all three return R115.
Q5: Explain the difference between using the Insert Function wizard (fx button) and typing a function manually. In what situations would you choose each?
✓ The Insert Function wizard (fx) guides you through entering a function step by step: it lets you search for functions by description, shows you exactly what each argument means, lets you click cells for ranges, and shows a live preview of the result before you commit. It also shows which arguments are required vs optional. Choose the wizard when: you are learning a new function, the function has many arguments (VLOOKUP, SUMIFS, PMT), you want to verify the result before confirming, or you cannot remember the exact argument order. Manual typing is faster when you know the function syntax well — experienced users type =VLOOKUP(A2,$F$2:$G$100,2,0) directly without the wizard because it is faster than navigating dialog boxes. For frequently used functions (SUM, AVERAGE, IF), manual typing is almost always faster.
Q6: A cell shows #DIV/0! because the formula =A1/B1 divides by B1 which is currently 0. You want the cell to show 0 instead of the error. How do you modify the formula?
✓ Two approaches: (1) IFERROR wrapper: =IFERROR(A1/B1, 0). IFERROR checks if the formula produces any error — if it does, it returns the second argument (0 in this case); if no error, it returns the formula result normally. (2) IF check: =IF(B1=0, 0, A1/B1). This explicitly checks if B1 is zero before dividing — if zero, returns 0; otherwise performs the division. IFERROR is more concise. The IF approach is more explicit and clear. Both are valid; use IFERROR when you want to catch any possible error, use IF when you specifically want to handle only the zero-division case and see other errors if they occur.