=, e.g., =A1+B1.=TODAY().'007.| Function | Purpose | Example |
|---|---|---|
=SUM(range) | Add all values | =SUM(A1:A10) |
=AVERAGE(range) | Mean | =AVERAGE(A1:A10) |
=PRODUCT(range) | Multiply all values | =PRODUCT(A1:A5) |
=QUOTIENT(num, denom) | Integer part of division | =QUOTIENT(17, 5) → 3 |
=MOD(num, divisor) | Remainder | =MOD(17, 5) → 2 |
=ROUND(num, digits) | Round to N decimals | =ROUND(3.14159, 2) → 3.14 |
=POWER(num, exp) | numexp | =POWER(2, 10) → 1024 |
=SQRT(num) | Square root | =SQRT(64) → 8 |
=ABS(num) | Absolute value | =ABS(-7) → 7 |
Suppose A1:A5 contains 10, 20, 30, 40, 50. Then =SUM(A1:A5) = 150, =AVERAGE(A1:A5) = 30, =PRODUCT(A1:A5) = 12 000 000.
Check whether 84 is divisible by 7: =MOD(84, 7) → 0 ✓. Number of complete dozens in 100 eggs: =QUOTIENT(100, 12) → 8.
| Function | Purpose | Example |
|---|---|---|
=IF(test, val_if_true, val_if_false) | Conditional return | =IF(A1>35,"Pass","Fail") |
=AND(cond1, cond2, …) | TRUE if all conditions are TRUE | =AND(A1>0, A1<100) |
=OR(cond1, cond2, …) | TRUE if any condition is TRUE | =OR(A1="M", A1="F") |
=NOT(cond) | Inverts TRUE/FALSE | =NOT(A1=0) |
=IFERROR(formula, alt) | Show alt when formula errors | =IFERROR(A1/B1,"div by 0") |
=IFS(c1,v1,c2,v2,…) | Multiple if-then chains | =IFS(A1>90,"A",A1>75,"B",TRUE,"C") |
A class mark in A2; formula in B2: =IF(A2>=35, "Pass", "Fail"). Drag down for all students.
Compute grade using nested IF:
=IF(A2>=90, "A+", IF(A2>=75, "A", IF(A2>=60, "B", IF(A2>=35, "C", "F"))))
Or with IFS: =IFS(A2>=90,"A+", A2>=75,"A", A2>=60,"B", A2>=35,"C", TRUE,"F").
=VLOOKUP(lookup_value, table_array, col_index, [range_lookup])
Same idea but searches the first row instead of the first column.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode])
Replaces VLOOKUP / HLOOKUP with cleaner syntax; can lookup left of the key column.
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
Student table: A2:C10 has ID, Name, Marks. To get marks of student with ID 105: =VLOOKUP(105, A2:C10, 3, FALSE).
Same task with XLOOKUP: =XLOOKUP(105, A2:A10, C2:C10, "Not found"). Cleaner and more flexible.
| Type | Syntax | Behaviour when copied |
|---|---|---|
| Relative | A1 | Adjusts both row & column |
| Absolute | $A$1 | Stays fixed |
| Mixed (row absolute) | A$1 | Column adjusts, row fixed |
| Mixed (column absolute) | $A1 | Column fixed, row adjusts |
Keyboard: press F4 in the formula bar to toggle through A1 → $A$1 → A$1 → $A1.
If B2 = =A2*$D$1 (with D1 = tax rate), copying down to B3, B4, … gives =A3*$D$1, =A4*$D$1, … — the tax rate stays fixed.
Build a 10×10 multiplication table. In B2 type =$A2 * B$1 and drag right and down. The column-fixed $A2 and row-fixed B$1 ensure each cell multiplies its row label by its column label.
Data tab → Sort & Filter → Sort. Choose column, order (A→Z or smallest to largest), optional secondary keys.
Data → Filter (turns on drop-down arrows in column headers). Click an arrow to show only rows that match selected values, or apply text/number/date filters.
Home → Conditional Formatting. Highlight cells that satisfy rules:
Data → Data Validation. Restrict what users can type:
For column "Gender", select cells → Data Validation → Allow: List → Source: Male, Female, Other. Now those cells will only accept those values.
For column "Marks", restrict to whole numbers between 0 and 100. Any out-of-range input will trigger an error pop-up.
The Analysis ToolPak is a built-in add-in providing one-click tools for descriptive statistics, regression, ANOVA, t-test, F-test, histogram, sampling and more.
To enable: File → Options → Add-Ins → Manage: Excel Add-Ins → Go → tick "Analysis ToolPak" → OK. The "Data Analysis" button now appears under the Data tab.
Solver is an add-in for optimisation problems — find the values of variable cells that maximise / minimise a target cell subject to constraints.
To enable: File → Options → Add-Ins → Manage: Excel Add-Ins → Go → tick "Solver Add-in" → OK. Appears under Data tab.
Data in A2:A50 (marks of 49 students). Data → Data Analysis → Descriptive Statistics → Input A2:A50 → tick "Summary statistics" → output to a new cell. Excel prints Mean, Median, Mode, SD, Variance, Skewness, Kurtosis, Range, Min, Max, Sum, Count.
Maximise profit \(Z = 4x + 5y\) subject to \(x + y \le 8,\; 2x + y \le 10,\; x, y \ge 0\).
Set up cells for \(x, y\); a cell for Z = =4*X + 5*Y; two constraint cells. Solver: Max Z, by changing X & Y, subject to the constraints, method = Simplex LP. Click Solve → optimum \(x = 0, y = 8, Z = 40\) (the corner \((0,8)\) satisfies both \(x+y\le 8\) and \(2x+y\le 10\) and beats \((2,6)\) where \(Z = 38\)).
| Shortcut | Action |
|---|---|
| Ctrl+C / Ctrl+V | Copy / Paste |
| Ctrl+Z / Ctrl+Y | Undo / Redo |
| Ctrl+S | Save |
| Ctrl+Arrow | Jump to edge of data range |
| Ctrl+Shift+L | Toggle Filter |
| F2 | Edit active cell |
| F4 | Cycle through reference types ($) |
| Alt+= | Insert SUM formula |
| Ctrl+Shift+Enter | Enter as array formula (legacy) |