Excel Formulas Every Clerk and Computer Operator Candidate Should Know

Excel questions look intimidating on paper, but the same small set of ideas comes up again and again. If you understand the eight things below, and try each one in a real spreadsheet once, you will be ahead of most candidates.

1. Every formula starts with an equals sign

Type =5+3 into a cell and Excel shows 8. Type 5+3 without the equals sign and Excel treats it as plain text. Exam questions often test this directly: “A formula in Excel must begin with…”

2. Cells, ranges and references

• A cell is where a column and a row meet, such as B3 (column B, row 3).
• A range is a block of cells, written with a colon: A1:A10 means A1 to A10.
• Columns are letters (A, B … XFD) and rows are numbers (1 to 1,048,576 in Excel 2007 and later).

3. Relative, absolute and mixed references

This is the single most important concept for exam questions.

Reference	What happens when you copy the formula
A1 (relative)	Both column and row change
$A$1 (absolute)	Nothing changes; it always points to A1
A$1 (mixed)	Column changes, row stays fixed
$A1 (mixed)	Row changes, column stays fixed

Example: you have prices in column B and a single tax rate in cell E1. In C2 you write =B2*$E$1. When you copy it down to C3, it becomes =B3*$E$1. The price reference moves, but the tax rate stays locked.

While editing a formula, pressing F4 cycles a reference through these four forms.

4. SUM, AVERAGE, MAX and MIN

• =SUM(B2:B11) adds the ten values.
• =AVERAGE(B2:B11) gives their mean.
• =MAX(B2:B11) and =MIN(B2:B11) give the highest and lowest values.

Questions often ask which function to use, so be precise: there is no ADD or TOTAL function for adding a range.

5. COUNT versus COUNTA versus COUNTBLANK

A favourite trick question:

• COUNT counts only cells containing numbers.
• COUNTA counts all non-empty cells (numbers, text, anything).
• COUNTBLANK counts empty cells.

If a range contains 5, "Ali", (empty), 7, then COUNT gives 2, COUNTA gives 3 and COUNTBLANK gives 1.

6. The IF function

=IF(condition, value_if_true, value_if_false)

Example: =IF(C2>=33,"Pass","Fail") shows Pass if the marks in C2 are 33 or more, otherwise Fail. Text results must be inside double quotes.

7. Handy keyboard shortcuts

Shortcut	What it does
F2	Edit the active cell
F4	Toggle reference type while editing a formula
Ctrl + ;	Insert today’s date
Ctrl + Shift + ;	Insert the current time
Ctrl + Z / Ctrl + Y	Undo / redo

8. File formats

• .xlsx: normal workbook (Excel 2007 and later)
• .xlsm: workbook containing macros
• .xls: older format (Excel 2003 and earlier)
• .csv: plain text with comma-separated values, no formatting or formulas saved

Practise for ten minutes

Open a blank spreadsheet and type ten numbers in column A. Then try SUM, AVERAGE, COUNT, an IF formula, and copy a formula with an absolute reference down the column. Ten minutes of doing this will fix the concepts in your memory far better than an hour of reading.

When you are ready, test yourself with our MS Office MCQs.