Formulas and functions

01The problem

You'll write hundreds of formulas as an analyst. Most mistakes come from a few causes: a reference that shifts when copied, a function applied to the wrong range, or an error value spreading silently through a workbook. Get the basics right once and those mistakes mostly disappear.

02The concept

A formula is an instruction that tells Excel to calculate something. A function is a ready-made formula with a name, such as SUM or AVERAGE, that does a common job for you.

This lesson works on the Orders table you built in lesson 2. Its columns are:

The data runs from row 2 to row 4267: 4,266 order lines.

Writing a formula

Every formula starts with an equals sign. Without it, Excel treats what you type as text.

=E2*F2

1. Click an empty cell.
2. Type =.
3. Click cell E2 (or type it). Excel colours the reference and outlines the cell in the same colour.
4. Type *, then click F2.
5. Press Enter. The cell shows the result, 260,400. The formula bar above the grid still shows =E2*F2.

That is the rule to remember: the cell shows the result, the formula bar shows the formula.

Operators and the order of calculation

Excel calculates in maths order: brackets first, then powers, then multiply and divide, then add and subtract.

=1+2*3        → 7, because 2*3 happens first
=(1+2)*3      → 9, because the brackets happen first

This matters for the revenue formula from lesson 2:

=E2*F2*(1-G2/100)

Without the brackets, =E2*F2*1-G2/100 would multiply first and then subtract a tiny number, which is wrong. For row 5 (28 packs at ₦6,000 with a 5% discount), the bracketed version gives ₦159,600; the unbracketed version gives ₦167,999.95.

Cell references

A reference such as E2 means "the value in column E, row 2". If that value changes, every formula that uses it updates on its own. That's why analysts write =E2*F2 instead of typing =14*18600: the formula keeps working when the data changes.

A range is a block of cells, written as first cell, colon, last cell:

Inside a table, prefer the table names. Orders[quantity] is easier to read than E2:E4267, and it stays correct when new rows are added.

Relative and absolute references

When you copy a formula to another cell, Excel adjusts its references by the same distance you moved. Copy =E2*F2 one row down and it becomes =E3*F3. That's a relative reference, and it's usually what you want: each row calculates its own revenue.

Sometimes you want a reference not to move: a total, a tax rate or an exchange rate that sits in one cell. Put a dollar sign in front of the part you want to lock. That's an absolute reference.

You don't need to type the dollars. While you're editing a formula, put the cursor on a reference and press F4. Each press cycles K1 → $K$1 → K$1 → $K1 → K1. (On many laptops it's Fn + F4.)

Functions

A function has a name, then brackets holding its arguments: the values it works on, separated by commas.

=FUNCTION_NAME(argument1, argument2, ...)
=ROUND(194688.52, 0)

As you type =RO, Excel lists matching functions. Press Tab to accept one. Once the bracket is open, a tooltip shows the arguments it expects; optional ones are in square brackets.

The rest of this section takes the everyday functions one at a time. Each result is from Kolanut's real Orders table, so you can check your own answers against it.

SUM

Adds up numbers.

=SUM(number1, [number2], ...)

SUM ignores text and empty cells. That's helpful, but it can hide a problem: a number stored as text (often from a bad import) is quietly left out. If a total looks too small, check for numbers that sit on the left of their cell; Excel puts text on the left and numbers on the right.

Shortcut: select the cell under a column of numbers and press Alt + = (AutoSum). Excel writes the SUM for you.

AVERAGE, MEDIAN, MIN and MAX

=AVERAGE(number1, [number2], ...)
=MEDIAN(number1, [number2], ...)
=MIN(number1, [number2], ...)
=MAX(number1, [number2], ...)

Why are the average and the median different? A few very large lines pull the average up. The median is "the typical line", the average is "total divided by count". When a manager asks "what does a normal order look like?", the median is often the better answer. You'll meet this again in the Statistics course.

COUNT, COUNTA and COUNTBLANK

These three are easy to mix up.

So how many order lines are there? =COUNTA(Orders[order_id]) gives 4,266, or =ROWS(Orders) counts the table's rows directly.

Use COUNT and COUNTA together as a quick data check: if =COUNT(Orders[quantity]) is lower than =COUNTA(Orders[quantity]), some quantities are stored as text.

ROUND

Rounds a number to a set number of decimal places.

=ROUND(number, num_digits)

The last one is called nesting: Excel works from the inside out. First AVERAGE gives 194,688.52, then ROUND turns it into 194,689.

YEAR, MONTH, DAY and TEXT for dates

Excel stores a date as a number: the count of days since 1 January 1900. That's why you can subtract dates to get a number of days, and why COUNT counts dates. These functions pull the parts out.

YEAR and MONTH return numbers. TEXT returns text. For a column you'll group by month, TEXT(date, "yyyy-mm") is a good choice because 2025-01, 2025-02 ... sort in the right order as text. "mmm yyyy" reads better, but "Apr 2025" sorts before "Jan 2025" alphabetically.

UNIQUE

Returns the distinct values in a range, each once.

=UNIQUE(array)

=UNIQUE(Orders[product_id]) fills a column with the 16 product IDs. It spills: you type it in one cell, and the results flow down into the cells below. Count them with =COUNTA(UNIQUE(Orders[product_id])) (16), or count order days with =COUNTA(UNIQUE(Orders[order_date])) (546).

UNIQUE is in Excel 365 and Excel 2021 onwards. In Google Sheets it's also UNIQUE, and COUNTUNIQUE counts in one step.

IFERROR

Shows something else when a formula gives an error.

=IFERROR(value, value_if_error)

=IFERROR(H2/K2, 0) gives 0 instead of #DIV/0! when K2 is empty.

Use it carefully. An error usually means something is wrong, and IFERROR hides it. Fix the cause first; use IFERROR only where the error is expected, such as a lookup for a customer who genuinely isn't in the list (lesson 6).

Error values and what they mean

When a formula can't work, Excel shows an error value instead of a result. Each one tells you something specific.

Errors spread. If H2 shows #VALUE!, then =SUM(H2:H4267) shows #VALUE! too. Always fix the first error in the chain, not the total at the end.

03Example

What share of total revenue does each order line make up?

1. In K1, put the grand total: =SUM(Orders[revenue]). It shows 830,541,245.
2. In I2, the first line's share: =H2/$K$1. Format column I as a percentage with two decimals.
3. Press Enter. Because column I is right next to the table, Excel adds it as a new table column and fills the formula down every row for you, written as =[@revenue]/$K$1: the same formula in table language. (Outside a table, double-click the fill handle, the small square at the bottom right of the cell, to copy a formula down.)

Row 2 shows 0.03%: ₦260,400 out of ₦830,541,245. Every row divides by the same total because $K$1 is locked. If you'd written =H2/K1, row 3 would divide by K2, which is empty, and show #DIV/0!, exactly as in the diagram above.

To check the result, =SUM(I2:I4267) should be exactly 100%. Testing a result like this is a habit worth building.

Revenue by year. Add a column year with =YEAR([@order_date]). You'll learn to total by year with SUMIFS in the next lesson, but you can already check one year by filtering: 2025 brings in ₦539,810,790 from 2,832 lines; January to June 2026 brings in ₦290,730,455 from 1,434 lines.

04Walkthrough

Build a small summary block to the right of your Orders table, one figure per row. This is the first step of most analyses: before anything clever, know the basic size and shape of the data.

1. In K3, type the label Total revenue. In L3, type =SUM(Orders[revenue]). It should show 830,541,245.

2. Below it, add these rows:

   Label (K)	Formula (L)	Expect
   Packs sold	=SUM(Orders[quantity])	58,757
   Order lines	=COUNTA(Orders[order_id])	4,266
   Average per line	=ROUND(AVERAGE(Orders[revenue]), 0)	194,689
   Median per line	=MEDIAN(Orders[revenue])	166,680
   Largest line	=MAX(Orders[revenue])	713,400
   Days with orders	=COUNTA(UNIQUE(Orders[order_date]))	546

3. Format the money cells: select them, press Ctrl + 1, choose Number, tick Use 1000 Separator, set decimals to 0.

4. Press Ctrl + ` (the key left of 1) to show every formula instead of its result. Check each one points at the right column. Press it again to switch back.

5. Check by a second route. Click the revenue column header to select it. The status bar at the bottom of the window shows Average, Count and Sum. Sum should match L3. If your formula and the status bar disagree, find out why before moving on: usually a filter is on, or a number is stored as text.

Shortcuts for formulas

Summary

05Practice

06Challenge

07More practice

Optional drills. They don't count towards the certificate, but they're the fastest way to make this lesson stick. Several use a different dataset from the lesson on purpose: if you can do the same thing on unfamiliar data, you've really learned it.

08Check your understanding