Mini Exercises — Excel for Analytics

A consolidated practice set bringing together formulas, pivot tables, lookups, conditional formatting, and shortcuts. Build a small analysis from start to finish — the way you would on the job.

The Dataset

Create a sheet with this sample sales data (or use any orders dataset you have). Type the headers and a few rows, or paste a larger set.

order_id	customer_id	product	category	region	quantity	unit_price	status	order_date
O001	C001	Headphones	Electronics	West	2	149.00	completed	2024-01-05
O002	C003	Standing Desk	Furniture	East	1	389.00	completed	2024-01-07
O003	C002	Analytics Book	Books	West	3	49.99	pending	2024-01-09
O004	C001	Yoga Mat	Sports	South	1	35.99	completed	2024-01-10
O005	C004	Headphones	Electronics	East	1	149.00	cancelled	2024-01-11
O006	C003	Coffee Maker	Kitchen	West	2	89.99	completed	2024-01-14

Also create a small Products lookup sheet: product, cost_price.

---

Exercise Set 1 — Formulas

1. Add a total column: quantity × unit_price.
2. Add a net_total column applying a 10% discount only to completed orders (use IF).
3. In a summary area, calculate: total revenue, average order value, count of completed orders.
4. Use SUMIFS to total revenue for completed orders in the West region.
5. Use COUNTIFS to count cancelled Electronics orders.

Hints

' total
=F2*G2
' net_total
=IF(H2="completed", F2*G2*0.9, F2*G2)
' completed revenue, West
=SUMIFS(total_range, status_range, "completed", region_range, "West")
' cancelled Electronics
=COUNTIFS(status_range, "cancelled", category_range, "Electronics")

---

Exercise Set 2 — Lookups

1. Add a cost column by looking up each product's cost_price from the Products sheet using XLOOKUP (or VLOOKUP).
2. Add a profit column: total − (quantity × cost_price).
3. Add a "Not found" fallback for any product missing from the lookup table.

Hints

' XLOOKUP cost price
=XLOOKUP(C2, Products[product], Products[cost_price], 0)
' profit
=total - (quantity * cost_price)

---

Exercise Set 3 — Pivot Table

1. Create a pivot table: revenue by region (rows) and category (columns).
2. Add a Year/Month grouping on the date field.
3. Show values as % of grand total.
4. Add a slicer for status and connect it.

---

Exercise Set 4 — Conditional Formatting

1. Highlight all orders with total > 200 in green.
2. Highlight the entire row of any cancelled order in light red (formula rule).
3. Apply a colour scale to the pivot table to create a revenue heatmap.

Hints

' Whole-row rule for cancelled orders
=$H2 = "cancelled"

---

Exercise Set 5 — Speed Challenge

1. Rebuild the total and net_total columns using only the keyboard: Ctrl+Arrow to navigate, F2 to edit, Ctrl+D to fill down, F4 for references.
2. Convert your data to a Table with Ctrl+T and confirm the pivot auto-updates when you add a row.

---

Capstone Mini-Task

Put it all together. Build a one-page Sales Summary that includes:

• [ ] KPI cells: total revenue, total profit, completed order count, average order value
• [ ] A pivot table of revenue by region and category
• [ ] A lookup pulling in cost and calculating profit per order
• [ ] Conditional formatting highlighting cancelled orders and high-value sales
• [ ] A slicer to filter by region

This is a complete, if small, Excel analysis — exactly the kind of deliverable you'd produce in week one of an analyst job.

---

Previous: 05-excel-shortcuts | Next: 07-interview-questions