Budget vs Actual in Excel: Variance Formula Step by Step

Build a budget vs actual report in Excel, month and year to date, with variance formulas that are always the right way round. One SUMIFS pattern, one sign column, a zero-budget guard, two conditional formatting rules and a variance chart, in under four minutes. Chapters 0:00 The finished report 0:12 Layout: one data table, month and year to date 0:31 SUMIFS for actual and budget 0:52 Variance and the sign convention (favourable / unfavourable) 1:21 Variance % with a zero-budget guard 1:40 Year to date with SUMIFS 1:56 Conditional formatting 2:11 Variance chart 2:25 Change the month 2:36 How to read a budget vs actual report 3:07 Actual and budget live from Xero The formulas used: Month actual: =SUMIFS(Data!$D:$D, Data!$B:$B, $A8, Data!$A:$A, $D$2) Year to date: the same SUMIFS with two more date conditions, on or after the year start in D4 and on or before the month end in D2. Variance, favourable positive: =(D8-E8)*$C8 (sign column: 1 for income, -1 for costs) Variance %: =IF(E8=0,"-",F8/ABS(E8)) All figures are demo company data. Try XO Report free for 14 days: https://xo-report.com Step-by-step guide: Xero budget vs actual in Excel: https://xo-report.com/xero-to-excel/b... Music: "Wallpaper" by Kevin MacLeod (incompetech.com), licensed under CC BY 4.0