Core Formulas & Functions — Excel masterclass

🟢

The Foundation

Level 1 · the hook & basics

Think about your kitchen. A blender has just one job: drop in the fruit, press the button, and out pours juice. A toaster takes bread and hands back toast. You never watch the machinery — you give it something, and you collect a result. An Excel function is exactly that: a little appliance. You put ingredients in — we call them arguments — it does its one job, and it hands back an answer.

The most famous appliance is =SUM. It is a calculator that adds a whole tray of numbers at once, so instead of typing =B2+B3+B4+B5+B6 by hand you simply say =SUM(B2:B6). Same answer, far less typing — and it keeps working when the numbers change.

The shape never changes. Every function is written the same way, and once you can read one, you can read them all:

Read it left to right: the equals sign wakes Excel up (it means stop reading, start calculating); the NAME says which appliance to use; the brackets hold the ingredients; and the arguments inside are the cells or numbers it works on. When a function needs more than one ingredient you separate them with commas — for example =ROUND(77.75, 0) hands ROUND two things: the number first, then how many decimals to keep. A colon is a different tool: it means through, so B2:B6 is one unbroken range from B2 down to B6.

That really is the whole idea. Everything else in this module is just a tour of the handiest appliances — adding, averaging, counting, rounding and ranking — and how to click them together.

A function is a machine: =NAME(arguments). Ingredients go in, one answer comes out. Commas separate the arguments; a colon : means through for a whole range.

🗺️

Visual Architecture

the whole system on one screen

Two quick pictures: a mind-map of the whole toolkit, and a flowchart that points you to the right function for the job in front of you.

🧠 The core toolkitfive jobs, one appliance each

View Mermaid.js source

mindmap
  root((Core Formulas))
    Adding
      SUM
      PRODUCT
      AutoSum Alt equals
    Averaging
      AVERAGE
      MEDIAN
    Counting
      COUNT numbers
      COUNTA non empty
      COUNTBLANK empties
    Rounding
      ROUND
      ROUNDUP and ROUNDDOWN
      INT and MOD
    Ranking
      MAX and MIN
      LARGE and SMALL
      RANK

🧭 Which function do I need?name the job, get the function

View Mermaid.js source

flowchart TD
  A[What answer do you need] --> B{Name the job}
  B -->|Add it all up| C[SUM]
  B -->|A typical middle value| D[AVERAGE or MEDIAN]
  B -->|How many entries| E{Numbers only or every cell}
  E -->|Only the numbers| F[COUNT]
  E -->|Every filled cell| G[COUNTA]
  B -->|Biggest or smallest| H[MAX MIN LARGE SMALL]
  B -->|Neaten the decimals| I[ROUND ROUNDUP ROUNDDOWN]

🟡

The Deep Dive

Level 2–3 · core mechanics → expert edge

1 · AutoSum — the one-tap total. Click the empty cell just below a column of numbers and press Alt + =. That shortcut is AutoSum: Excel guesses the range, writes =SUM(B2:B6) for you, and you press Enter to confirm. The little arrow beside the AutoSum button (on the Home tab) hides the same one-tap trick for Average, Count, Max and Min. It is the fastest total in Excel.

2 · The adding family. =SUM(range) adds every number it can see and quietly ignores blank or text cells. Its cousin =PRODUCT(range) multiplies them together — handy when you need quantity times rate across many rows. You can also hand SUM more than one block at once: =SUM(B2:B6, D2:D6) totals two separate ranges in a single go, separating them with a comma.

3 · The averaging family. =AVERAGE(range) is the arithmetic mean — add everything up, then divide by how many numbers there were. It skips blanks and text, but mind this: a genuine 0 is a number, so it pulls the average down. =MEDIAN(range) hands back the middle value once the numbers are lined up in order, which is far fairer when one giant or tiny value would otherwise skew the mean.

4 · The counting family. This one trips up almost everyone, so hold it firmly. =COUNT(range) counts only numbers. =COUNTA(range) counts every non-empty cell — numbers, words, even a stray full stop. =COUNTBLANK(range) counts the empties. So on a class list where one pupil is marked AB for absent, COUNT sees the marks, COUNTA sees the pupils, and COUNTBLANK sees the gaps.

COUNT counts numbers only. COUNTA counts anything that is not empty. If the two ever disagree, you have text hiding among your numbers.

5 · Biggest, smallest and k-th place. =MAX(range) returns the largest number and =MIN(range) the smallest. When you want the second or third biggest, reach for =LARGE(range, 2) — that 2 is the rank you are after, so =LARGE(range, 1) is simply MAX. Its mirror, =SMALL(range, 3), gives the third-smallest. To find where one value sits in the pack, =RANK(B2, B2:B6, 0) reports its position, with 0 (or a blank) ranking highest-first; modern Excel also spells it RANK.EQ.

Function	What it hands back	Quick example
`SUM`	The total of the numbers	=SUM(2, 4, 6) → 12
`PRODUCT`	The numbers multiplied	=PRODUCT(2, 4, 6) → 48
`AVERAGE`	The mean average	=AVERAGE(2, 4, 6) → 4
`MEDIAN`	The middle value	=MEDIAN(1, 2, 9) → 2
`COUNT`	How many are numbers	=COUNT(7, "x", 9) → 2
`COUNTA`	How many cells are filled	=COUNTA(7, "x", 9) → 3
`COUNTBLANK`	How many cells are empty	tallies the blanks in a range
`MAX / MIN`	Largest / smallest	=MAX(2, 9, 5) → 9
`LARGE / SMALL`	The k-th largest / smallest	=LARGE(range, 2) → 2nd biggest
`RANK`	A value's position in the list	=RANK(9, list) → 1

6 · Rounding — ROUND, ROUNDUP, ROUNDDOWN. =ROUND(number, digits) takes two arguments: the value, and how many decimal places to keep. That second number is the clever part. A positive digit rounds to the right of the decimal point; 0 gives a whole number; and a negative digit rounds to the left — to the nearest ten, hundred and beyond. ROUNDUP always pushes away from zero and ROUNDDOWN always pulls towards it, whatever the next digit says.

Formula	Result	In plain words
=ROUND(1234.567, 2)	1234.57	keep 2 decimals (paise)
=ROUND(1234.567, 1)	1234.6	keep 1 decimal
=ROUND(1234.567, 0)	1235	nearest whole rupee
=ROUND(1234.567, -1)	1230	nearest ten
=ROUND(1234.567, -2)	1200	nearest hundred
=ROUNDUP(12.1, 0)	13	always up
=ROUNDDOWN(12.9, 0)	12	always down

Looking rounded is not the same as being rounded. Number formatting only changes what you see — the full 77.75 is still hiding underneath, and it is what the next sum quietly uses. Only ROUND() changes the value for real.

7 · The whole-number toolkit. =INT(number) chops off the decimals and drops to the whole number below, so =INT(7.9) is 7. =MOD(number, divisor) hands back the remainder after dividing — =MOD(10, 3) is 1 — which is perfect for spotting odd or even, or every third row. =ABS(number) strips the minus sign, so =ABS(-8) is 8. =SQRT(number) is the square root, =SQRT(81) being 9. And =POWER(4, 2) raises a number to a power — the caret ^ does the very same job, so =4^2 is also 16.

8 · Clicking functions together (nesting). The real power comes when one appliance feeds another. Excel always runs the innermost brackets first, then works outward — just like the brackets in school maths. The classic pairing is rounding an average: =ROUND(AVERAGE(B2:B6), 0). AVERAGE runs inside, produces a single number, and ROUND tidies it. Build it from the inside out:

Work out the inside job first — =AVERAGE(B2:B6) turns the marks into one mean, say 77.75.

Choose the outer job. You want a whole number, so ROUND will wrap around it, keeping 0 decimals.

Nest them by dropping the whole AVERAGE where ROUND expects its number: =ROUND(AVERAGE(B2:B6), 0).

Count the brackets — two open, two closed — and read back the tidy 78. A #NAME? usually means a misspelt function; a stray bracket gives an error too.

🔴

Expert Traps & Hacks

Level 4 · where 90% slip — and the exam edge

Almost every mistake with the core functions comes from a handful of look-alikes. Learn to spot these five and your totals will stop misbehaving.

COUNT when you meant COUNTA. COUNT only sees numbers, so on a list where some cells hold text such as AB or Pending, it quietly under-counts. If the question is how many entries, use COUNTA; keep COUNT for how many numbers.

Formatting is not rounding. Trimming a cell to two decimals with the toolbar only hides the extra digits — it does not remove them. Total a column of prices that merely look rounded and the answer can be a rupee or two out, because Excel is still adding the hidden paise. When the value must truly be tidy, wrap it in ROUND().

The wrong number of ROUND digits. ROUND(x, 2) keeps paise, ROUND(x, 0) gives a whole rupee, and ROUND(x, -1) snaps to the nearest ten. A negative digit rounds to the left of the decimal point, not the right — mixing these up is the classic slip.

Blank is not zero. AVERAGE skips an empty cell but happily counts a real 0, which drags the mean down. If an absentee should not count, leave the cell blank; if a genuine zero belongs there, type 0. The two are not the same to Excel.

Rounding too early. Round at every step and the little errors pile up. Keep the full-precision numbers while you calculate and round once, right at the end, so the final figure is both tidy and correct.

Pro speed. Press Alt + = to AutoSum a column in a single tap. Select any block of numbers and glance at the status bar along the bottom — Excel shows the Sum, Average and Count instantly, with no formula at all. For a quick podium use =LARGE(range, 1), then 2, then 3 to pull the top three; and wrap money in =ROUND(…, 2) so your totals never drift.

💻

Interactive Sandbox — Formula Sandbox

type a real formula - the cell fills and turns green

Type a formula into the bar and press Run. Your answer fills the cell and it turns green the instant it matches. We start gentle with SUM, meet COUNT beside COUNTA so you can watch them disagree, and finish by nesting ROUND around AVERAGE.

🧮 Formula Sandboxtype it · run it · green when right

0Correct

0Streak

🇬🇧 The complete Excel pathThis module is one of ten. Work through them in order — Grid Basics to Dynamic Arrays — then keep the Function Atlas open as your desk reference. All 10 Excel modules →