Professional Excel Engineering: practice exercises
The exercises here are about workbooks that other people have to trust and maintain. They cover structuring a model into inputs, calculations and outputs, protecting sheets and cells, auditing formulas with the trace tools, working together on a shared file and speeding up slow workbooks. Each task is a small repair or review job on an existing file. Best for people who build reports and models that live longer than one afternoon.
24 exercises · level: Expert
Start with the first task Study the course: Professional Excel Engineering →
Showing 24 of 24
Architecture and Auditing
Name cell H1 "SalesTax" and cell H2 "Discount".
In D2:D7, calculate the discounted total using the name "Discount".
In E2:E7, calculate the sales tax on the discounted total using the name "SalesTax".
In H4, show the total with tax for all items.
Test the model: change the discount in H2 to 10%. All the totals should recalculate by themselves.
In C4, the price is stored as text. Enter it again as the number 15.
D2:D7 should hold one formula, Qty × Price, in every row. Find the row where a number was typed in and fix it.
Fix the quantity total in B8: it should cover all the months.
In F2, calculate the revenue another way, without column D.
In F3, build a check cell: "OK" if D8 and F2 differ by less than 0.01, otherwise "Error". Get it to say "OK".
Performance and Integration
In D2:D9, replace TODAY() with a reference to the report date in H1.
In H2, calculate the total of all invoices once.
In E2:E9, calculate each invoice's share by referring to the finished total in H2 rather than recomputing SUM in every row.
In H3, find the total of overdue invoices (days overdue greater than 0).
In E2:E11, do the JOIN: look up each product category by code from the lookup table K2:L4.
In G2:G4, calculate SUM(amount) by region with the condition WHERE year = the value in G7.
In H2:H4, calculate COUNT(*): the number of orders in each region for the same year.
In I2:I4, mark HAVING: "yes" if the region's total is greater than the threshold in G8, otherwise "no".
Project
Rewrite the cost of goods in E2:E7 so the share comes from J1 (and fix the row where a number was typed in).
Rewrite the fee in F2:F7 to use the parameter J2.
Fix the "Total" row D8:G8: each total is a SUM formula over rows 2-7.
Add tax and net profit: G9 is the tax at the rate in J3, and G10 is the profit after tax.
In J5, build a check: "OK" if the profit total G8 matches D8-E8-F8 (tolerance 0.01), otherwise "Error".
Stress test: the marketplace fee went up. Enter 10% in J2. The whole model should recalculate.
No exercises match these filters.