MS Excel

high importance⚡ 10 shortcuts4 subtopics

Overview Learn (4)Formulas (0)Examples (5)Practice (83)

Functions you must know

⏱ 4 min read🧩 6 question types🎯 27 practice Q

The idea in one minute

A function is a ready-made formula. It has a name and takes inputs in brackets. Excel groups the common ones into maths, text, logical, lookup and date functions. Learn the exact result of each function, because the exam asks for the output of a given formula.

How a function looks

A function has a name and arguments in brackets. For example, =SUM(A1:A5). The arguments are separated by commas. The formula still starts with =.

Maths functions

Function	Job	Example	Result
SUM	Adds	=SUM(2,3,5)	10
AVERAGE	Mean	=AVERAGE(2,4,6)	4
MAX / MIN	Largest / smallest	=MAX(2,9,4)	9
ROUND	Rounds to given digits	=ROUND(3.14159,2)	3.14
INT	Rounds down to whole number	=INT(7.9)	7
MOD	Remainder	=MOD(17,5)	2
ABS	Removes the minus sign	=ABS(-7)	7
SQRT	Square root	=SQRT(9)	3
POWER	Raises to a power	=POWER(2,5)	32

Tip: MOD gives the remainder after division. 17 divided by 5 leaves 2.

Text functions

Function	Job	Example	Result
LEFT	First characters	=LEFT("EXCEL",2)	EX
RIGHT	Last characters	=RIGHT("EXCEL",2)	EL
MID	From the middle	=MID("COMPUTER",3,3)	MPU
LEN	Counts characters	=LEN("EXCEL")	5
UPPER / LOWER	Change case	=UPPER("abc")	ABC
PROPER	Capital first letter of each word	=PROPER("ram kumar")	Ram Kumar
CONCATENATE	Joins text	=CONCATENATE("A","B")	AB

MID takes three inputs: the text, the start position and the number of characters. Text inside a formula is written in double quotes.

Logical function IF

=IF(condition, value if true, value if false)

For example, =IF(A1>=40,"Pass","Fail") shows Pass when A1 is 40 or more. Otherwise it shows Fail. You can put one IF inside another. That is called a nested IF.

Rule: Use AND when all conditions must hold. Use OR when any one is enough.

Lookup function VLOOKUP

=VLOOKUP(lookup value, table, column number, FALSE)

VLOOKUP searches the first column of the table. It then returns a value from the column you name. The last input FALSE asks for an exact match. TRUE (or leaving it out) allows an approximate match. HLOOKUP does the same across rows.

Date functions

TODAY() gives today's date. It has no arguments.
NOW() gives the date and the time.

Watch: Both update every time the sheet recalculates. They are not fixed stamps. Use Ctrl+; for a fixed date.

Conditional counting and adding

COUNTIF(range, condition) counts cells that meet the test. For example, =COUNTIF(A1:A10,">40").
SUMIF(range, condition, sum range) adds only the cells that meet the test.
AVERAGEIF averages only the matching cells.
RANK gives the position of a number in a list.

Question types you will see

Each type: how to recognise it, the method step by step, and one question to try.

Type 1very common4 practice Q

Output of a maths function

How to spot it:

A formula with ROUND, INT, MOD, ABS, SQRT or POWER is given, and a result is asked.

Method

Name the function and read its job.
Work with the numbers inside the brackets.
INT cuts down, ROUND goes to the nearest, MOD is the remainder.

Why it works:

Each function has one clear rule, so the result can be worked out by hand.

Try this

What is the result of =POWER(2,5)?

Show solution

POWER raises the first number to the second.
2 to the power 5 is 2 x 2 x 2 x 2 x 2.
That equals 32.

Answer

Type 2very common4 practice Q

Output of a text function

How to spot it:

A LEFT, RIGHT, MID, LEN or PROPER formula on a word is given.

Method

Number each letter from 1.
Apply the start position and the count.
Count characters for LEN, spaces included.

Why it works:

Text functions work on positions, so counting letters gives the answer.

Try this

What does =MID("COMPUTER",3,3) return?

Show solution

Letters are C(1) O(2) M(3) P(4) U(5).
Start at position 3.
Take three letters: M, P and U.

Answer

MPU

Type 3very common5 practice Q

IF and nested IF

How to spot it:

A formula with IF is given and a result is asked for some cell value.

Method

Test the condition with the given cell value.
If it is true, return the second argument.
If it is false, return the third argument.

Why it works:

IF picks one of two answers depending on a test.

Try this

A1 holds 35. What does =IF(A1>=40,"Pass","Fail") show?

Show solution

Test 35 >= 40.
The test is false.
The third argument is returned.

Answer

Fail

Type 4common5 practice Q

VLOOKUP inputs

How to spot it:

The question asks what the fourth input of VLOOKUP does, or which column is searched.

Method

The lookup value is searched in the first column of the table.
The third input is the column number to return.
FALSE gives an exact match. TRUE gives an approximate match.

Why it works:

VLOOKUP always searches the leftmost column, which is why it cannot look left.

Try this

In VLOOKUP, which last argument finds an exact match?

Show solution

The last argument sets match type.
Exact means no nearest guess.
That is FALSE.

Answer

FALSE

Type 5common4 practice Q

TODAY vs NOW

How to spot it:

The question asks which function returns only the date, or the date and time.

Method

TODAY() returns the date only.
NOW() returns the date and time.
Neither takes arguments.

Why it works:

The two names hint at the output, and both refresh on recalculation.

Try this

Which Excel function returns the current date and time?

Show solution

TODAY gives only the date.
The function with time also is NOW.

Answer

NOW()

Type 6occasional

Text case and joining

How to spot it:

The question asks which function changes case or joins two pieces of text.

Method

UPPER makes capitals, LOWER makes small letters.
PROPER capitalises the first letter of each word.
CONCATENATE joins the pieces.

Why it works:

Case functions are easy to confuse by name, so match the result.

Try this

Which function converts "ram kumar" to "Ram Kumar"?

Show solution

The result has a capital at the start of each word.
That is the job of PROPER.

Answer

PROPER

Shortcuts that save time

⚡ MOD is the leftover

MOD(a, b) is what is left after a is divided by b. MOD(17,5) is 2, and MOD(10,5) is 0.

Example

What does =MOD(17,5) return?

Show solution

17 divided by 5 gives 3 whole times.
3 times 5 is 15.
17 minus 15 leaves 2.

Answer

⚡ MID has three inputs

MID(text, start, count). MID("COMPUTER",3,3) starts at the third letter and takes three letters. That gives MPU.

⚡ FALSE means exact

The last input of VLOOKUP decides how it matches. FALSE means exact match. TRUE means nearest smaller match.

Mistakes to avoid

Where most students lose marks on this subtopic.

Mistake 01

Thinking INT(7.9) gives 8.

INT rounds down. INT(7.9) is 7.

Mistake 02

Reading MOD(17,5) as 3.

MOD gives the remainder. That is 2. The 3 is the quotient.

Mistake 03

Leaving out the quotes around text in a formula.

Write text in double quotes, such as "EXCEL".

Mistake 04

Using VLOOKUP with TRUE when an exact match is needed.

Use FALSE for an exact match.

Mistake 05

Treating TODAY() as a fixed date.

TODAY() changes every day. Ctrl+; enters a fixed date.

Quick revision

Read this the night before the exam.

A function has a name and arguments in brackets. Formulas begin with =.
ROUND(3.14159,2) is 3.14. INT(7.9) is 7. MOD(17,5) is 2. ABS(-7) is 7. SQRT(9) is 3. POWER(2,5) is 32.
LEFT and RIGHT take from the ends. MID(text,start,count) takes from the middle. LEN counts characters.
UPPER, LOWER, PROPER change case. CONCATENATE joins text.
IF(condition, true value, false value). Nested IF puts one IF inside another.
VLOOKUP searches the first column. FALSE means exact match. HLOOKUP works across rows.
TODAY() gives the date. NOW() gives date and time.

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.

Continue learning