How to Build a Budget vs Actual Report in Google Sheets
Budget and actual side by side for each category, with the variance in dollars and percent. Two SUMIFS that read from two tabs over the same dates.
The short answer: one SUMIFS totals the budget tab for the period, another totals the expenses tab for the same period, and the variance is =Actual - Budget. In the sample data, Q3 2026 spending was $127,653 against a $124,550 budget, $3,103 over, driven by payroll at $3,663 over its line.
The sample data
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | Vendor | Category | Amount |
| 2 | 2026-01-01 | Warehouse lease | Rent | 4800 |
| 3 | 2026-01-02 | Accounting software | Software | 90 |
| 4 | 2026-01-02 | Online store platform | Software | 299 |
| 5 | 2026-01-03 | Inventory app | Software | 129 |
| 6 | 2026-01-03 | Office suite | Software | 96 |
| … | 202 more rows | |||
Sample data: a tab named Expenses with 207 operating expenses from January to October 2026, one row per bill or payment. Download expenses.csv and import it (File → Import) to follow along. The business is fictional.
| A | B | C | |
|---|---|---|---|
| 1 | Category | Month | Budget |
| 2 | Payroll | 2026-01-01 | 20800 |
| 3 | Rent | 2026-01-01 | 4800 |
| 4 | Shipping & freight | 2026-01-01 | 9000 |
| 5 | Marketing | 2026-01-01 | 3000 |
| 6 | Software | 2026-01-01 | 650 |
| … | 91 more rows | ||
Sample data: a tab named Budget with 96 monthly budget lines for 2026, one row per category per month. Month is the first day of the month. Download budget.csv and import it (File → Import) to follow along. The business is fictional.
The classic way: SUMIFS on both tabs
The Budget tab has Category (A), Month (B, the first of the month) and Budget (C). The Expenses tab has Date (A), Category (C) and Amount (D). On a new tab, type the categories in A2:A9, then in row 2, copied down:
=SUMIFS(Budget!$C:$C, Budget!$A:$A, $A2, Budget!$B:$B, ">="&DATE(2026,7,1), Budget!$B:$B, "<"&DATE(2026,10,1))
=SUMIFS(Expenses!$D:$D, Expenses!$C:$C, $A2, Expenses!$A:$A, ">="&DATE(2026,7,1), Expenses!$A:$A, "<"&DATE(2026,10,1))
=C2-B2
=IFERROR(D2/B2, "")B is the quarter's budget, C the actual spend, D the variance in dollars (positive means over budget) and E the variance as a percent of budget. The Total row sums B, C and D and divides the totals for E. Put the start and end dates in cells and point the formulas at them, and the same sheet reports any month or quarter.
Example on sample data| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Category | Budget | Actual | Variance | Variance % |
| 2 | Payroll | $62,400 | $66,063 | $3,663 | 5.9% |
| 3 | Rent | $14,400 | $14,400 | $0 | 0.0% |
| 4 | Shipping & freight | $27,000 | $27,886 | $886 | 3.3% |
| 5 | Marketing | $11,600 | $11,947 | $347 | 3.0% |
| 6 | Software | $1,950 | $2,012 | $62 | 3.2% |
| 7 | Utilities | $3,000 | $2,920 | -$80 | -2.7% |
| 8 | Travel | $3,300 | $1,440 | -$1,860 | -56.4% |
| 9 | Office & supplies | $900 | $985 | $85 | 9.4% |
| 10 | Total | $124,550 | $127,653 | $3,103 | 2.5% |
Computed from the sample CSVs above. Cells formatted as currency and percent.
Make the overruns visible. Select D2:D9 and add a conditional format rule "Greater than" 0 with red fill. On the sample data, Payroll is the overrun that matters: a hire started in July that the budget did not include.
Gotchas
- Sign convention. For costs, actual minus budget positive is bad. For revenue lines it is good. State the convention in the heading, or flip the formula on revenue rows.
- Categories must match on both tabs. If the budget says "Shipping" and the expenses say "Shipping & freight", the actual comes back $0 and the variance looks like a saving.
- Timing, not overspending. A trade-show bill paid in September but budgeted in August is a variance in both months that nets to zero. Compare quarter to date before reacting to a single month.
- Percent on a tiny budget. In the sample, Office & supplies is $85 over a $900 budget, 9.4%. Sort by the dollar variance, not the percent, to see what matters.
The one-cell way: =QUIRIZ
With the Quiriz add-on for Google Sheets, you type the question instead of building the formula. =QUIRIZ does not read the cells around it: it answers from the datasets in your Quiriz account. For this example, upload expenses.csv as a dataset, or connect QuickBooks, and upload budget.csv as a second dataset. You can also add a Google Sheet itself as a dataset that refreshes on a schedule. Then, in any empty cell:
=QUIRIZ("budget vs actual by expense category for Q3 2026 (July to September), with the variance in dollars and as a percent of budget, plus a total row", "table")The "table" mode spills the answer down and to the right from that cell, header row first:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Category | Budget | Actual | Variance | Variance % |
| 2 | Payroll | $62,400 | $66,063 | $3,663 | 5.9% |
| 3 | Rent | $14,400 | $14,400 | $0 | 0.0% |
| 4 | Shipping & freight | $27,000 | $27,886 | $886 | 3.3% |
| 5 | Marketing | $11,600 | $11,947 | $347 | 3.0% |
| 6 | Software | $1,950 | $2,012 | $62 | 3.2% |
| 7 | Utilities | $3,000 | $2,920 | -$80 | -2.7% |
| 8 | Travel | $3,300 | $1,440 | -$1,860 | -56.4% |
| 9 | Office & supplies | $900 | $985 | $85 | 9.4% |
| 10 | Total | $124,550 | $127,653 | $3,103 | 2.5% |
Same figures as the formula result above, from the same sample data. The spilled cells are plain numbers, so you can format them, total them or chart them; here they are formatted as currency and percent. Column headings are written with each answer and may be worded differently.
The other modes: "verbose" (the default) answers in a sentence, "bullets" writes two to four points one per cell, and "notes" adds how the figure was calculated. Install Quiriz for Google Sheets and try the question on your own data.
Ask the question, get the table
Quiriz answers business questions in a Google Sheets cell, from your imported files, scheduled Google Sheets and connected tools. Free to start.
Install for Google Sheets →Free on the Google Workspace Marketplace. Or try it on sample data first, no signup.