Excel test for freshers: a 45-minute practice task with answers

Set up the workbook (5 minutes)

Create two sheets. The data is synthetic.

Sheet `Orders`, headers in row 1, data in rows 2 to 13:

Columns A to F in that order. Dates are day-month-year. Type them so Excel stores real dates, not text: after entering, the cells should right-align, and =ISNUMBER(B2) should return TRUE. If they left-align, your system date format differs; enter them as 2026-08-03 instead.

Sheet `Products`, headers in row 1, data in rows 2 to 4:

The eight questions (start the 40-minute timer)

Add new columns to Orders starting at G.

1. 1In G, bring in each order's category from Products.
2. 2In H, bring in the unit price.
3. 3In I, compute revenue (qty × unit price).
4. 4In J, write "High" if revenue is ₹20,000 or more, otherwise "Normal".
5. 5In a summary area, find total revenue for North in August 2026.
6. 6Count Asha's orders with a quantity of 10 or more.
7. 7Find the average revenue per Furniture order.
8. 8Build a pivot table of revenue by region (rows) and category (columns), and state North's share of total revenue as a percentage with one decimal place.

Stop at 40 minutes even if unfinished. The habit of working to a clock is part of what you're practising.

Answer key

Check each answer before reading the formula, so you can see whether your method or your result was wrong.

The pivot should show:

To build it: select A1:J13, Insert, PivotTable, new sheet; drag region to Rows, category to Columns, revenue to Values, and confirm the value field says "Sum of revenue", not "Count of revenue". If it shows counts, a revenue cell is probably text.

Check the key yourself

If you don't trust the key, recompute it. This script runs as written with plain Python and reproduces every number above.

orders = [
    ("O-101", "2026-08-03", "North", "Asha", "P1", 12), ("O-102", "2026-08-05", "South", "Ravi", "P2", 4),
    ("O-103", "2026-08-09", "North", "Asha", "P3", 7), ("O-104", "2026-08-14", "West", "Imran", "P1", 15),
    ("O-105", "2026-08-21", "South", "Ravi", "P3", 10), ("O-106", "2026-08-28", "North", "Neha", "P2", 3),
    ("O-107", "2026-09-02", "West", "Imran", "P2", 6), ("O-108", "2026-09-06", "North", "Neha", "P1", 9),
    ("O-109", "2026-09-11", "South", "Asha", "P3", 14), ("O-110", "2026-09-17", "West", "Imran", "P3", 5),
    ("O-111", "2026-09-22", "North", "Asha", "P2", 11), ("O-112", "2026-09-27", "South", "Ravi", "P1", 2),
]
products = {"P1": ("Accessories", 850), "P2": ("Furniture", 6200), "P3": ("Accessories", 1400)}

rows = [dict(id=o, month=d[:7], region=r, rep=p, cat=products[c][0], qty=q, rev=q * products[c][1])
        for o, d, r, p, c, q in orders]
total = sum(x["rev"] for x in rows)

print("Q4 High:", [x["id"] for x in rows if x["rev"] >= 20000])
print("Q5:", sum(x["rev"] for x in rows if x["region"] == "North" and x["month"] == "2026-08"))
print("Q6:", sum(1 for x in rows if x["rep"] == "Asha" and x["qty"] >= 10))
furn = [x["rev"] for x in rows if x["cat"] == "Furniture"]
print("Q7:", sum(furn) / len(furn))
north = sum(x["rev"] for x in rows if x["region"] == "North")
print(f"Q8: {north / total:.1%} of {total}")

Where freshers usually lose marks

• Relative references in lookups. Without the $ signs, the lookup range shifts as you fill down and rows near the bottom return "Not found".
• Dates stored as text. SUMIFS with date conditions returns 0, silently. This is the most common reason Q5 comes out wrong; check with ISNUMBER.
• Hard-coding the month. "August" typed as a condition matches nothing in a date column. Use DATE() bounds as in the key.
• Wrapping everything in IFERROR. It hides the mistake instead of fixing it. XLOOKUP's fourth argument handles a genuinely missing code more honestly.
• Pivot value field set to Count. Always read the field name.
• Forgetting to answer the question asked. Q8 wants a percentage with one decimal; 0.494 or 49.43% are both less than full marks in a strict test.

Make it harder next time

Once you can finish in 30 minutes, change the drill: add an order with product code P4 that isn't in the product list and see which formulas break; add a second sheet for October and combine them; or answer Q5 for every region-month combination with a single pivot and a date grouping. Our existing guides on reconciling pivot totals and on SUMIFS with dates cover the next step, and the SQL in 30 days plan moves the same questions into queries.

Timed tool work like this, followed by a written summary, runs through the Data Analyst programme: 3–4 months of Excel, SQL and Power BI, live online with optional labs at our Noida centre. The free skill check shows which of these skills your resume already demonstrates.