The Complete Excel Course with AI Integration

Budget vs actual in Excel: build a variance report

Where did the money go, and which lines ran over budget? FinanceGuard, the budget vs actual report you build in The Complete Excel Course with AI Integration, answers both: a budget table and an actuals table become one variance engine, giving every line its variance, its variance % and an over or under budget note, then a dashboard of KPI cards, charts and slicers. Designed after talking with people from many different industries, it is an industry-standard project that runs in Excel alone, every formula yours.

Taught by Sagnik Bhattacharya and Virginia Thorn

The Complete Excel Course with AI Integration: Sagnik Bhattacharya and Virginia Thorn

What you will be able to do

You will be able to:

  • Turn a budget range and an actuals range into named Excel Tables, so the model's formulas read their columns by name instead of by cell address.
  • List every key from both tables once, sorted, with SORT, UNIQUE and VSTACK in one spilling formula, then read the month, department and category out of each key.
  • Fetch each key's budget and actual with SUMIFS, then calculate the variance and the variance %, with IFERROR guarding any line whose budget is zero.
  • Label each line over budget, over budget high, under budget or under budget high on a 5% threshold, and show negative variances in red brackets with a custom number format.
  • Read the same figures five ways in PivotTables (totals, month by month, by department, by category and a department-by-month matrix), with variance % and budget utilisation beneath.
  • Build the dashboard: KPI cards for budget, actuals, variance, variance % and utilisation, a budget against actual combo chart, and month, department and category slicers connected to every PivotTable.

Projects

What you will build.

The course has 14 industry-standard projects. These three matter most here.

  • Project 09

    FinanceGuard

    The budget vs actual report finance teams send every month.

    The report on this page, from a starter workbook of budget and actual figures by month, department and category to a finished dashboard: a Model sheet that does the comparing, five PivotTable views, KPI cards, charts and slicers.

  • Project 06

    PeopleInsight HR workbook

    Employee data joined across sheets with one formula.

    An HR workbook of employee records where codes are split with LEFT, RIGHT and MID, and UNIQUE, FILTER and SORT spill clean lists: the moves FinanceGuard's key column and month labels are made of.

Tap or pinch to zoom in, drag to move around. Click or scroll to zoom in, drag to move around.

  • Project 03

    Sales Goal Tracker

    A tracker of sales against quota where nested IF labels every result in tiers and SUMIFS adds only the rows that meet two conditions: the logic of FinanceGuard's notes and of its totals.

Lessons

The lessons that matter, in course order.

The Complete Excel Course with AI Integration has 186 lessons. This page picks out 23 of them, 1 h 52 min of video. Each lesson keeps its number in the course.

Where to start

Lesson 115 FinanceGuard budget vs actual dashboard

Start at Lesson 115 if SUMIFS and IF already feel familiar: the FinanceGuard starter workbook holds the budget and actuals tables, and the variance engine is built from Lesson 117. New to formulas? Begin at Lesson 10, or at Lesson 1 if Excel itself is new.

23 of 186 lessons · 1 h 52 min

01 Microsoft Excel fundamentals: building a foundation from scratch 3 of 13 lessons · 10 min Absolute and mixed references with F4, the kind that keeps FinanceGuard's SUMIFS pointing at its key column, a value as a percentage of a total, and IFERROR, which turns #DIV/0! into something a reader can use.
  1. 10 Relative vs absolute references in Excel Dollar signs and mixed references 3 min
  2. 11 Calculating percentages in Excel Each expense as a percentage of income 2 min
  3. 12 Common Excel errors and how to fix them DIV0, NAME, VALUE errors and IFERROR 4 min
03 Managing data in Excel: sort, filter and tables (Dunder Michael Paper employee database) 1 of 9 lessons · 8 min Excel Tables on an employee list: create, style and total a table and watch new rows join it, the base of FinanceGuard's budget and actual tables.
  1. 19 Excel tables Create and format a table, structured references and total row 8 min
04 Essential formulas and functions (IF, SUMIF and more) 1 of 13 lessons · 6 min Nested IF on sales data: ratings turned into customer tiers, and sales against quota labelled achieved, near target or underperformed, the pattern of FinanceGuard's notes.
  1. 31 Nested IF and IFS in Excel Customer value tiers with nested IF 6 min
04 Essential formulas and functions (IF, SUMIF and more) 1 of 13 lessons · 7 min SUMIFS and COUNTIFS with several conditions, sum range first and then each criteria range with its condition, as every line of the model sums its budget and its actual by key.
  1. 37 SUMIFS and COUNTIFS in Excel Sum and count with multiple criteria 7 min
06 Data visualisation: charts and graphs 1 of 9 lessons · 4 min Two measures on two scales in one chart: a combo chart puts its second series on a secondary axis, groundwork for FinanceGuard's budget against actual lines.
  1. 52 Combo charts in Excel Column and line chart with a secondary axis 4 min
07 PivotTables and PivotCharts: data analysis made easy 2 of 10 lessons · 3 min A first PivotTable from a sales table, then rows, values and Value Field Settings: how the PivotTables that summarise the model are built and arranged.
  1. 59 Create your first PivotTable in Excel Insert a PivotTable from a sales table 1 min
  2. 60 PivotTable fields in Excel Rows, values, columns and Value Field Settings 2 min
08 Advanced Excel functions: lookups, text and arrays 1 of 12 lessons · 7 min LEFT, RIGHT and MID on a code such as DAL-SAL-0001: how a compact key gives up its parts, as FinanceGuard's keys give up their year and month.
  1. 73 LEFT, RIGHT and MID in Excel Extract parts of a text code like DAL-SAL-0001 7 min
08 Advanced Excel functions: lookups, text and arrays 3 of 12 lessons · 15 min Dynamic arrays on employee records: UNIQUE spills a list without repeats, FILTER returns only the rows that pass a test, and SORT orders the result, with SORT and UNIQUE joined in one formula.
  1. 75 UNIQUE function in Excel Extract a distinct list with dynamic arrays 6 min
  2. 76 FILTER function in Excel Dynamic array filtering with AND and OR criteria 5 min
  3. 77 SORT and SORTBY in Excel Sort data dynamically with formulas 4 min
12 Project: FinanceGuard budget variance analyser (budget vs actual Excel dashboard) 10 lessons · 52 min FinanceGuard from two tables to a finished dashboard: named tables, the variance engine, notes and red-bracket formats, five PivotTable views with variance % and budget utilisation, KPI cards, a combo chart and two more, slicers, and a dark theme with a custom slicer style.
  1. 115 Start here FinanceGuard budget vs actual dashboard Excel project overview 3 min
  2. 116 Excel tables for budget and actual data Organise the FinanceGuard datasets 4 min
  3. 117 Budget vs actual model in Excel SORT, UNIQUE, VSTACK, LET, TEXTSPLIT, SUMIFS 8 min
  4. 118 Budget variance in Excel Calculate variance and percent variance 4 min
  5. 119 Custom number formats in Excel Show budget variances with colours and signs 2 min
  6. 120 PivotTables for budget vs actual The FinanceGuard pivot workspace 8 min
  7. 121 KPI cards in Excel Summary cards for a budget variance dashboard 4 min
  8. 122 Budget vs actual charts in Excel Combo chart with secondary axis for variance 5 min
  9. 123 Slicers and PivotCharts in Excel Interactive FinanceGuard budget dashboard 4 min
  10. 124 Finish the FinanceGuard dashboard in Excel Final polish and variance insights 10 min

Instructors

Who teaches it.

Virginia Thorn smiling at the camera, dark hair tied back, wearing a navy top.

Instructor, Excel with AI

Virginia Thorn

Emmy Award winner. Teaches Excel with AI at Coding Liquids.

Questions

Asked before starting.

Which Excel course teaches you to build a budget vs actual report?

The Complete Excel Course with AI Integration from Coding Liquids teaches it as FinanceGuard, an industry-standard budget variance analyser you build from a starter workbook. A budget table and an actuals table become one model that gives every line its variance, its variance % and a plain-English note, then a dashboard with KPI cards, budget against actual by month in a combo chart, variance by department and by category, and slicers. The 23 lessons that build it are laid out on this page, 1 hour 52 minutes of video, with the finished FinanceGuard to check yours against and a Certificate of Completion when you finish the course.

How do I calculate budget variance and variance percentage in Excel?

Subtract, then divide by the budget, as The Complete Excel Course with AI Integration does on FinanceGuard's Model sheet (Lesson 118). Variance is actual minus budget, so a positive figure means overspent and a negative one means money saved. Variance % is the variance divided by the budget, inside IFERROR with zero as the fallback, so a line with no budget reads 0% instead of #DIV/0!. Format it as a percentage, fill both columns down, and every line shows its gap twice: in money, and as a share of what was planned, which is what tells a trivial miss from a serious one.

How do I combine a budget sheet and an actuals sheet in Excel?

Give both tables a shared key and let formulas do the joining. In The Complete Excel Course with AI Integration, FinanceGuard's Model sheet stacks the keys of both tables with VSTACK, keeps one of each with UNIQUE and orders them with SORT, in a single formula that spills down the column (Lesson 117), so a line that sits in only one table still appears. SUMIFS then brings in each key's budget and actual from the named tables, and two more formulas read each key back into plain columns: LET turns it into a readable month, and TEXTSPLIT pulls out the department and the category. Change a figure in either table and the model follows at once.

How do I show over-budget and under-budget lines in Excel?

In words and in colour. The Complete Excel Course with AI Integration gives FinanceGuard a Notes column built with nested IF on the variance %: above 5% it reads Over budget, high; above zero, Over budget; below minus 5%, Under budget, high; anything else, Under budget (Lesson 118). A custom number format then shows every negative variance in red inside brackets, in the money column and the percentage column alike (Lesson 119). Change the 5% to your own tolerance and every label follows; on income lines, where beating the plan is good news, swap the wording of the labels.

Which chart shows budget against actual in Excel?

A combo chart of both across the months, the one The Complete Excel Course with AI Integration builds for FinanceGuard (Lesson 122). From the monthly PivotTable of budget and actual, Recommended Charts offers a combo; put actual on the secondary axis and draw both as lines with markers, so the months where spending pulls away from the plan stand out. Beside it, a stacked column chart shows variance % by department and a stacked bar shows variance by category. The groundwork comes earlier, with revenue and sign-ups sharing one combo chart on two axes (Lesson 52).

Should I use a budget vs actual template or build my own?

A variance report is questioned line by line, so build the one whose every figure you can explain. A downloaded template hides its logic in formulas you did not write, and it breaks the day your figures stop matching its maker's layout. In The Complete Excel Course with AI Integration you write each piece of FinanceGuard yourself, from the key column to the slicers' connections, starting from the starter workbook with the finished one to compare, so the report fits your departments, your categories and your tolerance, not a stranger's. You can say why every figure is what it is, the first thing a finance reviewer asks, and lifetime access keeps the lessons open for every rebuild.

How do I build a budget vs actual dashboard in Excel?

In Excel alone, with no Power BI or Tableau. The Complete Excel Course with AI Integration copies the model's values to a sheet of their own, builds five PivotTables from them (totals, a monthly trend, departments, top categories and a department-by-month matrix) and works out variance % and budget utilisation beneath (Lesson 120). Five KPI cards show total budget, total actuals, variance, variance % and utilisation (Lesson 121); month, department and category slicers, wired through Report Connections to all five PivotTables, let a director click Marketing and see Marketing (Lesson 123). When new actuals arrive, paste the model's values again and Refresh All.

Can I use the same model for sales or cost variance analysis?

Yes: the engine compares any plan with any actual that shares a key. The Complete Excel Course with AI Integration builds FinanceGuard on spending by month, department and category, and the same Model sheet takes sales targets against sales, or planned against actual cost, once each row carries a key of that shape. SUMIFS sums each side by key, the variance and variance % work unchanged, and the PivotTables slice the result any way you need. On sales, read the sign the other way round: a positive variance is sales above target, and the nested IF of Lesson 31, which labels sales against quota as achieved, near target or underperformed, shows how to word it.

Excel with AI

See the whole course.

The Complete Excel Course with AI Integration: 16 hours of video in 186 lessons, built around 14 industry-standard projects, with lifetime access, a Certificate of Completion and a 14-day money-back guarantee.

The same course

For another job, goal, level, topic or comparison.