Microsoft Excel for Data Analysis: Useful Formulas and Practical Examples

If you are a student, there is a good chance your next assignment needs Excel, and a handful of formulas will cover most of it.

This post uses one small sales table and walks through the formulas I find most useful. I have checked every result below by recalculating it separately in Python, so the numbers match what Excel should show.

I have also done assignments where the sheet is formula-driven: you change an input and everything else updates by itself. That is near the end.

The sample data

Paste this into a blank sheet starting at cell A1.

	A	B	C	D	E	F
1	Order ID	Product	Region	Units	Unit Price	Revenue
2	101	Pen	North	10	5	50
3	102	Pen	South	20	5	100
4	103	Book	North	5	40	200
5	104	Book	South	8	40	320
6	105	Bag	North	3	250	750
7	106	Bag	East	4	250	1000
8	107	Pen	East	15	5	75
9	108	Book	East	6	40	240

The Revenue column is not typed in. It is a formula. In F2, enter:

=D2*E2

Then drag the fill handle (the small square at the bottom-right of the cell) down to F9. Each row calculates its own revenue.

The basic five

SUM

Adds up numbers.

=SUM(F2:F9)

Result: 2735. That is total revenue.

AVERAGE

=AVERAGE(F2:F9)

Result: 341.875. Average revenue per order.

COUNT and COUNTA

These look alike but count different things.

=COUNT(D2:D9)
=COUNTA(B2:B9)

COUNT counts cells that contain numbers. COUNTA counts cells that are not empty, including text. Both return 8 here. Try =COUNT(B2:B9) and you get 0, because the Product column is all text. That mistake is easy to make.

IF: make the sheet decide

IF checks a condition and returns one thing if true, another if false.

In G2, add a header "Tier" in G1, then:

=IF(F2>=300,"High","Low")

Fill down. Orders 104, 105, and 106 (revenue 320, 750, 1000) show High. The other five show Low.

The pattern is IF(test, value_if_true, value_if_false).

SUMIF and COUNTIF: totals with a condition

This is where Excel starts to feel like analysis.

=SUMIF(C2:C9,"North",F2:F9)

Result: 1000. It looks at the Region column, picks rows equal to "North", and adds their Revenue. The North orders are 50 + 200 + 750.

The order of arguments is: range to check, criteria, range to add.

=SUMIF(B2:B9,"Pen",F2:F9)

Result: 225 (50 + 100 + 75).

COUNTIF counts rows that meet a condition:

=COUNTIF(C2:C9,"North")
=COUNTIF(F2:F9,">=300")

The first returns 3 orders from North. The second returns 3 orders with revenue of at least 300.

If you need more than one condition, look at SUMIFS and COUNTIFS. For example, =SUMIFS(F2:F9,B2:B9,"Book",C2:C9,"East") returns 240.

Looking things up

Say you want the revenue of order 104 without scanning the table by eye. Put 104 in a cell, for example K2, and use a lookup.

VLOOKUP

=VLOOKUP(K2,A2:F9,6,FALSE)

Result: 320. Breaking it down:

K2 is what you are looking for.
A2:F9 is the table. The lookup value must be in the first column of this range.
6 means "return the value from the 6th column of the range".
FALSE means exact match. Use this almost every time. Leaving it out can give wrong answers on unsorted data.

The weak point: that 6 is hard-coded. If someone inserts a column in the middle, the formula may return the wrong thing without any warning.

XLOOKUP

=XLOOKUP(K2,A2:A9,F2:F9)

Result: 320. You give it the column to search and the column to return. No column numbers, and exact match is the default.

One catch: XLOOKUP is available in Excel 2021 and Microsoft 365 (plus Excel for the web). It will not work in Excel 2019 or older. If your college lab machines run an older version, use the next option.

INDEX + MATCH

=INDEX(F2:F9,MATCH(K2,A2:A9,0))

Result: 320. MATCH finds the position of 104 in the Order ID column (position 4). INDEX then returns the 4th item from the Revenue column. The 0 means exact match.

This works in every Excel version, and it can look to the left, which VLOOKUP cannot. For instance, to find the first order ID for "Bag":

=INDEX(A2:A9,MATCH("Bag",B2:B9,0))

Result: 105. It returns the first match only, so if a product appears many times you get the earliest row.

Function	Works in older Excel	Looks left	Column numbers needed
VLOOKUP	Yes	No	Yes
XLOOKUP	No (2021 / 365 only)	Yes	No
INDEX + MATCH	Yes	Yes	No

Relative vs absolute references

This is the topic that causes the most confusion, and it matters as soon as you fill formulas down.

A relative reference like F2 changes when you copy the formula. Copy it down one row and it becomes F3. That is what you want for =D2*E2.

An absolute reference uses dollar signs and stays fixed: $F$11.

Say you put the grand total in F11:

=SUM(F2:F9)

Now you want each order's share of the total. In H2:

=F2/$F$11

Fill it down. F2 shifts to F3, F4 and so on, but $F$11 stays pinned to the total. Without the dollar signs, the second row would divide by F12, an empty cell, and you would see a #DIV/0! error.

For order 101 the share is 50 / 2735, about 1.8%. For order 106 it is 1000 / 2735, about 36.6%. Format column H as a percentage.

A quick way to add the dollar signs: click inside the reference in the formula bar and press F4. It cycles through $F$11, F$11, $F11, and F11.

Formula-driven sheets: change an input, everything updates

The real value of Excel is that you do not retype results. You set inputs once, and formulas react.

Extend the same sheet with a bonus rate. In J4 type the label "Bonus rate", and in K4 type 5%. Then add a "Bonus" header in I1 and, in I2:

=F2*$K$4

Fill down to I9. The $K$4 is absolute, so every row points at the same rate cell. Put a total at the bottom of the column:

=SUM(I2:I9)

At 5%, order 106 gets a bonus of 50 and the total bonus is 136.75. Now change K4 to 10%. Every bonus doubles without you touching a formula: order 106 becomes 100 and the total becomes 273.5.

Try changing a price too. Set E2 (the Pen price on order 101) from 5 to 6. Revenue for that row goes from 50 to 60, the grand total in F11 moves from 2735 to 2745, and =SUMIF(B2:B9,"Pen",F2:F9) moves from 225 to 235.

The habit to build: keep inputs in clearly labelled cells, never bury numbers inside formulas, and let everything else be calculated.

Mistakes to avoid

Numbers stored as text. If a number is left-aligned and SUM ignores it, it is probably text. Convert it or retype it.
Forgetting FALSE or 0 in lookups. You may get a wrong match instead of an error, which is worse.
Missing dollar signs. If a filled-down formula gives odd results from row three onward, check your references first.
Typing results by hand. If a number can be a formula, make it one.
Extra spaces in text. "North " is not equal to "North", and your SUMIF will quietly skip it.

Where to go next

Once these feel normal, look at SUMIFS, COUNTIFS, IFERROR, and PivotTables.

Excel is also a good place to rehearse ideas before moving to Python. A SUMIF is the same thinking as a groupby in Pandas. If you want to see that side, I will link the Pandas post here: Pandas for Beginners: Cleaning and Analysing Real-World Data.

If this helped, follow the blog for more practical student-level tutorials. You can also find my work on GitHub: https://github.com/Pranaav717