Excel Formulas Every Data Analyst Needs First

The sample sheet we will use

Type this small shop sales sheet into Excel or Google Sheets, exactly as shown — every result below is worked out on these numbers. Row 1 is the header; column D starts empty.

Before any analysis, fill column D — the total for each product is units × price. Click D2 and type:

=B2*C2

Then copy D2 down to D6 (or drag the fill handle at D2's bottom-right corner). The sheet now shows:

Two things to notice. First, B2*C2 uses *references* (B2, C2), not the numbers 40 and 150 — change the units for Tea to 50 and D2 updates itself to 7500. That is the whole point of formulas: the sheet stays correct when the data changes. Second, * means multiply in a formula, and every formula begins with =.

How do I total and average a column? SUM and AVERAGE

Below the table, in row 8, type the label Total sales in A8 and Average units in A9 — then the formulas.

In D8:

=SUM(D2:D6)

D2:D6 is a *range*: every cell from D2 to D6. SUM adds them: 6000 + 7500 + 7200 + 7000 + 9000 = 36700.

In B9:

=AVERAGE(B2:B6)

AVERAGE adds the units and divides by how many there are: (40 + 25 + 60 + 35 + 50) ÷ 5 = 210 ÷ 5 = 42. The average product sold 42 units.

Expected result: D8 shows 36700, B9 shows 42. If D8 shows 0, check the range and that the cells contain numbers, not text.

How do I count things? COUNT and COUNTIF

Counting asks: how many rows are there, and how many meet a condition?

In B10, next to a label Products listed:

=COUNT(B2:B6)

COUNT counts cells that contain numbers: five rows have units, so the answer is 5. Note: COUNT ignores text — it counts numbers only.

In B11, next to Products selling over 40 units:

=COUNTIF(B2:B6,">40")

COUNTIF counts only the cells that pass a test. The test ">40" is in quotes because it is text describing a condition, not a number. Units over 40 are Sugar (60) and Milk (50) — two products — so the answer is 2.

How do I label a result? IF

Numbers are hard to scan; labels are easy. IF tests a condition and returns one value when it is true and another when it is false. In E2, next to Tea:

=IF(D2>7000,"Strong","OK")

D2 is 6000, so E2 shows OK. Copy E2 down to E6:

Biscuits is exactly 7000, and >7000 is strictly greater than — so it is "OK". That boundary is the classic IF trap: if you meant "7000 or more", the test must be >=7000.

How do I total only some rows? SUMIF

SUM totals everything; SUMIF totals only the rows that match a condition. In D9, next to the label Tea sales only:

=SUMIF(A2:A6,"Tea",D2:D6)

Read it as: in A2:A6, find rows equal to "Tea"; add the matching cells from D2:D6. Only row 2 matches, so the answer is 6000.

With one product this looks trivial, but the same formula answers real questions on a bigger sheet: =SUMIF(A2:A100,"Coffee",D2:D100) totals every Coffee row in a hundred-row sales list without you finding them by hand. SUMIF is the first step from "a spreadsheet that adds" to "a spreadsheet that answers questions".

How do I fetch a value from another table? VLOOKUP and XLOOKUP

The last first-week formula is the lookup: given a label, find its row and return a value from another column. A second small table, in G1:H6, lists each product and its supplier:

In F2, to find Coffee's total from the main table:

=VLOOKUP("Coffee",A2:D6,4,FALSE)

What to find ("Coffee"), where to look (A2:D6), which column to return (4 — the Total column), and FALSE for exact match only. The answer is 7500.

FALSE matters: without it, VLOOKUP returns the nearest match below your value, which is almost never what a beginner wants. Always write FALSE (or 0, which means the same thing).

If your version of Excel offers XLOOKUP, it is the simpler modern replacement:

=XLOOKUP("Coffee",A2:A6,D2:D6)

No column number, no FALSE — same answer, 7500. Use XLOOKUP where it is available; use VLOOKUP for older Excel versions or existing files.

What does the finished sheet look like, and how do you check it?

Your summary block (rows 8–11) should read:

Check it two ways. First, change one input: set Tea's units (B2) to 50. D2 becomes 7500, D8 becomes 38200, B9 becomes 44, and Tea's label in E2 flips to "Strong". Undo (Ctrl+Z) and everything returns — proof your cells are formulas, not typed-in numbers. Second, click any result cell and read the formula bar: it should show the formula while the cell shows the answer. If the cell shows the formula text itself, you forgot the leading = or the cell is formatted as Text.

Common mistakes

• Forgetting the = at the start. Without it, SUM(D2:D6) is just text in a cell — Excel will not calculate anything, and there is no error message to warn you.
• Typing numbers instead of references. =40*150 gives the right answer once and the wrong answer forever after — change the units and the total stays frozen. Always point at cells (=B2*C2), never type the numbers in.
• Numbers stored as text. If a formula returns 0 or ignores a cell, the "number" is probably text — often after pasting from a website. Select the cells, use Data → Text to Columns (or retype the values), and the formulas start working.
• The >7000 boundary. IF(D2>7000,...) excludes exactly 7000. Decide whether the boundary belongs in or out, and use >= when it belongs in.
• Leaving out FALSE in VLOOKUP. Without it, VLOOKUP returns an approximate match and silently gives you the wrong row's value. Exact match (FALSE) is the safe default for beginners.
• Ranges that miss the new row. Add a product at the bottom and =SUM(D2:D6) still stops at row 6 — extend the range, or format the data as an Excel Table (Insert → Table), which grows automatically.

Frequently asked questions