📎 This article includes 1 downloadable practice file ↓
In this article
| Column | Formula |
|---|---|
| Variance ₹ | =[@Actual] - [@Budget] (positive = overspend for costs) |
| Variance % | =[@Actual] / [@Budget] - 1 |
| Status | =IFS(var% > 10%, "Over", var% < -10%, "Under", TRUE, "OK") |
In the practice data: budget ₹31,10,000, actual ₹34,16,600 (₹3,06,600 over). Three heads are more than 10% over; Travel is 25.9% over; the largest overspend in rupees is Salaries, because a small percentage on a big head beats a big percentage on a small one.
Make it readable
- Conditional formatting icon set on Variance %: red above +10%, amber 0 to 10%, green below 0.
- Show sub-totals by department with SUMIFS; add YTD columns using the FY functions from the Excel Intermediate course.
- For revenue lines, flip the sign logic: under budget is bad.
Practice
Download the workbook below. Type your formula in the yellow column; the Check column turns green when it matches Excel’s own answer, and the Answers sheet has a working formula for every task. Tax rates and limits in the examples are for practice; verify current rules before real use.
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
📎 Practice files for this article
- 📗Practice workbookEight expense heads, budget and actual, 8 tasks.⬇ XLSX · 14 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.