MS Excel
🔒 Log in to trackFunctions you must know
🔒 Log in to track| Category | Functions |
|---|---|
| Maths | SUM, SUMIF, PRODUCT, ROUND, INT, MOD, ABS, POWER, SQRT |
| Statistics | AVERAGE, COUNT, COUNTA, COUNTBLANK, COUNTIF, MAX, MIN, MEDIAN, MODE, RANK, LARGE, SMALL, STDEV |
| Logical | IF, AND, OR, NOT, IFS, IFERROR |
| Text | LEN, LEFT, RIGHT, MID, CONCATENATE/CONCAT, TRIM, UPPER, LOWER, PROPER |
| Date-time | TODAY, NOW, DAY, MONTH, YEAR, DATEDIF |
| Lookup | VLOOKUP, HLOOKUP, INDEX, MATCH, XLOOKUP (2021+) |
| Financial | PMT (loan instalment), FV, PV, NPV |
Count-family (classic trap): COUNT counts numeric cells only; COUNTA counts all non-empty cells; COUNTBLANK counts empty cells; COUNTIF counts cells meeting a condition (e.g. COUNTIF(A1:A10,">80")).
Lookup: VLOOKUP(value, table, col_index, FALSE) searches the first column of the table vertically and returns the matching row's col_index-th column; FALSE = exact match. HLOOKUP works horizontally on the first row.
Text/date: LEN counts characters (LEN("SSCCGL") = 6); TRIM strips extra spaces; PROPER capitalises Each Word; UPPER/LOWER change case. TODAY() gives today's date (updates); NOW() gives date and time.
Formulas start with =
Every Excel calculation begins with the = sign: =A1+B1. Without it, Excel treats "A1+B1" as text. Calculations follow BODMAS order; functions are written as =NAME(arguments) with commas between arguments.
The counting family — the classic exam trap
Range for all examples below: A1=50, A2=70, A3="exam", A4 empty.
| Function | Job | Result here |
|---|---|---|
| =SUM(A1:A4) | adds numbers | 120 |
| =AVERAGE(A1:A4) | mean of the numbers | 60 |
| =MAX / =MIN | largest / smallest number | 70 / 50 |
| =COUNT | counts cells holding numbers | 2 |
| =COUNTA | counts all non-empty cells | 3 |
| =COUNTBLANK | counts empty cells | 1 |
COUNT counts Numbers; COUNTA counts Anything. Text and blank cells are silently skipped by SUM, AVERAGE and COUNT — that is why COUNTA exists.
Maths and logic functions
- =ROUND(1234.567, 2) gives 1234.57 (round to 2 decimals); =INT(8.9) gives 8 (chops the fraction); =MOD(10, 3) gives the remainder 1; =ABS(-9) gives 9; =SQRT(144) gives 12; =POWER(2, 10) gives 1024.
- =IF(test, yes-value, no-value): =IF(A1>=40, "Pass", "Fail") prints Pass when A1 holds 50.
- =COUNTIF(range, criteria): =COUNTIF(A1:A10, ">50") counts how many cells exceed 50.
Text functions
=LEFT("EXAM", 2) gives EX; =RIGHT("EXAM", 3) gives XAM; =MID("computer", 4, 3) gives put (start at 4th letter, take 3); =LEN("computer") gives 8; =UPPER/=LOWER/=PROPER change case (PROPER("india gate") gives India Gate); =CONCATENATE("SS","C") or "SS"&"C" joins text.
Lookup and date
- =VLOOKUP(value, table, column-number, FALSE) searches for the value in the first (leftmost) column of the table and returns the matching entry from the chosen column. The FALSE asks for an exact match. The V means the search runs vertically down the first column.
- =TODAY() shows today's date, =NOW() shows date and time — both recalculate every time the sheet opens. For a fixed stamp, press Ctrl+; (date) or Ctrl+Shift+; (time).
Reading a function question in the exam
- Write down the cell values from the stem in a small table.
- Apply the function's rule, remembering what it IGNORES (text, blanks) or where it LOOKS (first column for VLOOKUP).
- Compute with the ignored cells removed — then match. Distractors are always built from the mistakes: dividing by the full range length, counting text in COUNT, or taking MID from position 0.
Question types you will see
Each type: how to recognise it, the method step by step, and one question to try.
SUM / AVERAGE output computation
A small cell list is given and the output of =SUM or =AVERAGE is asked; options include realistic wrong totals.
Add the numeric cells for SUM; divide the total by the count of NUMERIC cells for AVERAGE.
Text and blank cells are skipped by both — AVERAGE never counts them in the denominator.
Verify by hand once, then match the option.
A1=10, A2=20, A3=30. What does =AVERAGE(A1:A3) return?
Show solutionHide solution
(10+20+30)/3 = 60.
COUNT family trap
A range mixing numbers, text and blanks is described and COUNT / COUNTA / COUNTBLANK outputs are asked — the options differ by exactly the text or blank cells.
COUNT counts only numeric cells; COUNTA counts every non-empty cell; COUNTBLANK counts empties.
Count the mixture carefully: numbers first, then everything filled, then the gaps.
COUNTA + COUNTBLANK = total cells in the range.
A1=5, A2='word', A3 blank, A4=9. =COUNT(A1:A4) and =COUNTA(A1:A4) give —
Show solutionHide solution
COUNT = 2 (only 5 and 9 are numbers); COUNTA = 3 (all filled cells, text included).
Maths function output
Outputs of ROUND, INT, MOD, ABS, SQRT, POWER on given numbers, or the function that returns a remainder / square root / rounded value.
ROUND(number, digits) rounds to that many decimals; INT chops the fraction for positives.
MOD(a, b) = remainder of a divided by b; ABS removes the minus; SQRT is the square root; POWER(a, b) raises a to the power b.
Beware INT of a negative number rounds DOWN (towards minus infinity).
What does =MOD(23, 5) return?
Show solutionHide solution
3 — 23 divided by 5 is 4 with remainder 3; MOD returns the remainder.
Text and date functions
Outputs of LEFT, RIGHT, MID, LEN, PROPER, CONCATENATE, or the function that stamps today's date / joins text.
LEFT takes letters from the start, RIGHT from the end, MID(text, start, how-many) from the middle; LEN counts characters (spaces included).
UPPER/LOWER/PROPER set the case; CONCATENATE or the & sign joins text.
TODAY() and NOW() recalculate on every open; Ctrl+; and Ctrl+Shift+; stamp fixed values.
What does =LEN('keyboard') return?
Show solutionHide solution
8 — LEN counts the characters of the text; keyboard has 8 letters.
IF and VLOOKUP logic
The output of =IF(condition, x, y) for a given cell value, or which column VLOOKUP searches / what the FALSE argument means.
IF tests the condition and returns the second argument when true, the third when false.
VLOOKUP searches the FIRST (leftmost) column of the table vertically and returns from the chosen column number; FALSE asks for an exact match.
Write the condition with the given value substituted, then pick the branch.
=IF(B2>=40, 'Pass', 'Fail') with B2 = 38 returns —
Show solutionHide solution
Fail — 38 is not greater than or equal to 40, so the third argument is chosen.
Shortcuts that save time
COUNT = digits only; COUNTA = everything non-empty (text too); COUNTBLANK = the gaps. Test any MCQ by tagging each cell in the range N (number), T (text), E (empty).
Range has 3 numbers, 1 text cell, 1 blank. COUNT? COUNTA?
Show solutionHide solution
COUNT = 3; COUNTA = 4.
VLOOKUP hunts down the first column; HLOOKUP along the first row. The 4th argument FALSE = exact match ('F for Full match').
VLOOKUP searches the table for the lookup value in which direction?
Show solutionHide solution
Vertically, in the first column.
TODAY() = date only; NOW() = date + time. Both refresh on recalculation (unlike Ctrl+; which stamps a fixed date).
Function returning current date and time together?
Show solutionHide solution
NOW().
Mistakes to avoid
Where most students lose marks on this subtopic.
Letting COUNT count text cells - it skips them silently.
Looking up VLOOKUP values in any column - the lookup value must be in the table's first column.
Believing TODAY() freezes the date - it recalculates; Ctrl+; stamps a static date.
Quick revision
Read this the night before the exam.
Every formula starts with =; arguments separated by commas.
COUNT = numbers only, COUNTA = anything non-empty, COUNTBLANK = empties.
ROUND rounds, INT chops, MOD gives remainder, SQRT, POWER.
IF(test, yes, no); COUNTIF counts cells meeting a condition.
LEFT/RIGHT/MID/LEN/PROPER for text; & joins text.
VLOOKUP searches the first column vertically; TODAY/NOW recalculate.
Practice: 27 questions
Sets of 10, mixed across the question types above. Every answer has a step-by-step explanation.
Topic test · 10 questions
Suggested time 6 min · wrong answers go to your mistake notebook automatically.