# 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:

```excel
=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.

```excel
=SUM(F2:F9)
```

Result: **2735**. That is total revenue. 

### AVERAGE

```excel
=AVERAGE(F2:F9)
```

Result: **341.875**. Average revenue per order.

### COUNT and COUNTA

These look alike but count different things.

```excel
=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:

```excel
=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.

```excel
=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.

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

Result: **225** (50 + 100 + 75).

`COUNTIF` counts rows that meet a condition:

```excel
=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

```excel
=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

```excel
=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

```excel
=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":

```excel
=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`:

```excel
=SUM(F2:F9)
```

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

```excel
=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`:

```excel
=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:

```excel
=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](https://pranaavkarthik.hashnode.dev/pandas-for-beginners-cleaning-analysing-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