Excel Budget Report Template: How to Build One

How-to basics · Updated November 22, 2026

The fastest way to get an Excel budget report is to use a built-in template: open Excel, go to File > New, type “budget report” in the search box, and pick one of the free options like “Monthly Budget Report” or “Annual Budget Report.” These templates already have formulas for totals, variances, and charts, so you can replace the sample numbers with your own and the report updates automatically. If you need a custom layout, you can build one from scratch in about 15 minutes using SUM, IF, and a few formatting tricks — this article covers both routes.

What a Budget Report Actually Contains

A budget report compares what you planned to spend (budget) against what you actually spent (actuals), usually for a month, quarter, or year. The core output is a variance — the difference between budget and actual — shown both in dollars and as a percentage. Most reports also include:

  • Income categories (salary, freelance, interest)
  • Expense categories (rent, utilities, marketing, payroll)
  • Budgeted amounts per category
  • Actual amounts per category
  • Variance (actual minus budget) and variance %
  • Totals for income, expenses, and net surplus/deficit

For a business, the report might also include a “forecast” column or a year-to-date (YTD) section. For personal use, you might add savings goals or debt payments. The template’s job is to make these numbers readable at a glance, not to do your bookkeeping for you.

Using a Pre-Built Template (Easiest Route)

Excel’s built-in templates are the fastest start because they include conditional formatting, charts, and pre-written formulas. Here’s how to use one:

  1. Open Excel and click File > New.
  2. In the search bar, type “budget report” or “budget” and press Enter.
  3. Choose a template (e.g., “Monthly Budget Report” or “Personal Budget”).
  4. Click Create — the template downloads and opens as a new workbook.
  5. Replace the sample data in the input cells (usually yellow or blue) with your own numbers.
  6. Adjust categories as needed by adding or deleting rows — but be careful: if you delete a row that has a formula, the formula may break. Instead, insert a new row and copy the formula down.

Most templates use a “summary” sheet and a “data” sheet. The summary sheet shows charts and totals; the data sheet is where you enter monthly numbers. If you don’t see a template you like, search online for “free Excel budget report template” — many reputable sites offer downloadable files, but always scan them for macros before enabling content.

Building a Custom Budget Report from Scratch

If you need a specific layout or want full control, create your own. Here’s a simple structure that works for both personal and small business use:

Assume you have a worksheet with:

  • Column A: Category name (A2:A10)
  • Column B: Budget amount (B2:B10)
  • Column C: Actual amount (C2:C10)
  • Row 11: Totals

Enter the following formulas:

  • In B11: =SUM(B2:B10) — total budget
  • In C11: =SUM(C2:C10) — total actual
  • In D2 (variance $): =C2-B2 — copy down to D10
  • In E2 (variance %): =IF(B2=0,0,(C2-B2)/B2) — copy down to E10. The IF prevents division by zero.
  • In D11: =C11-B11 — total variance
  • In E11: =IF(B11=0,0,(C11-B11)/B11) — total variance %

For a worked example, if B2=1000 and C2=1150, then D2 returns 150 and E2 returns 0.15 (formatted as 15%). If B2=0 and C2=50, E2 returns 0, not an error.

To make it look like a report, use Home > Format as Table to add banded rows, and Home > Conditional Formatting > Highlight Cells Rules > Greater Than to flag variances over a threshold (e.g., >10%). For a chart, select the category and variance columns, then go to Insert > Bar Chart.

Key Formulas and Functions for Budget Reports

Beyond SUM and IF, these functions are commonly used in budget reports:

  • SUMIF – sum actuals for a specific category group. Example: with categories in A2:A20 and actuals in C2:C20, =SUMIF(A2:A20,"Marketing",C2:C20) returns the total actual for “Marketing”.
  • AVERAGE – average monthly spending. =AVERAGE(C2:C13) gives the mean of 12 monthly actuals.
  • MIN/MAX – find the lowest and highest spending month. =MIN(C2:C13) and =MAX(C2:C13).
  • YEARFRAC – for annualizing partial-year data. =YEARFRAC(start_date,end_date) returns the fraction of a year.
  • XLOOKUP – pull a budget amount from a lookup table. With budget table in A1:B10 (category, amount), =XLOOKUP("Rent",A2:A10,B2:B10) returns the budget for Rent. XLOOKUP requires Excel 2021 or Microsoft 365.

For a monthly report that pulls from a data sheet, use SUMIFS to sum by month and category. Example: with dates in A2:A100, categories in B2:B100, and amounts in C2:C100, =SUMIFS(C2:C100, A2:A100,">="&DATE(2025,1,1), A2:A100,"<="&DATE(2025,1,31), B2:B100,"Food") returns January’s food spending.

How to Choose the Right Template for Your Needs

Not all budget report templates are equal. Consider these factors:

  • Personal vs. business – personal templates focus on income and household expenses; business templates include profit & loss, cost centers, and tax categories.
  • Time period – monthly templates are common, but you may need quarterly or annual. Some templates have a “period” drop-down.
  • Level of detail – do you need 10 categories or 50? A simple template is easier to maintain; a detailed one gives more insight.
  • Charts and dashboards – if you present to a manager or client, choose a template with visual summaries.
  • Manual entry vs. import – if you export bank transactions, look for a template with a “data” tab designed for pasting raw data.

If you’re unsure, start with a built-in template and modify it. You can always add rows, delete unused ones, or change the chart type.

Common Problems and How to Fix Them

Budget reports often break in predictable ways. Here are the most frequent issues:

  • Division by zero – when a budget is $0 but actual is positive, the variance % shows #DIV/0!. Use =IF(B2=0,0,(C2-B2)/B2) as shown above.
  • Formulas not copying – when you drag a formula down, relative references change. If you need a fixed cell (like a tax rate in $B$15), use absolute references with dollar signs ($B$15).
  • Wrong totals – if you insert a row inside a SUM range, Excel usually expands the range automatically, but if you insert a row at the bottom, the range may not include it. Double-check the range in the formula bar.
  • Negative variances confusing – decide whether a negative variance is good (spent less than budget) or bad (revenue below target). Use conditional formatting to color positive/negative differently.
  • Charts not updating – if you add a row, the chart’s data range may not expand. Right-click the chart, select Select Data, and update the range.

Another common misconception is that you need a macro to make a budget report. In reality, most reports are simple SUM/IF formulas. Macros are only needed for automating data imports or generating multiple reports at once.

Costs, Safety, and Alternatives

Excel templates are free if you use the built-in ones. Third-party templates can range from free to $50 or more, but many “premium” templates are just nicely formatted versions of what you can build yourself. Always be cautious when downloading templates from unknown websites — they may contain macros (VBA code) that could be malicious. If a template asks you to enable macros, only do so if you trust the source. Before running any macro, save a copy of your workbook, because macros cannot be undone with Ctrl+Z.

Alternatives to Excel for budget reports include Google Sheets (free, cloud-based, similar formulas), specialized budgeting apps like Mint or YNAB (for personal finance), and accounting software like QuickBooks or Xero (for business). If you need collaboration, Google Sheets is often easier because multiple people can edit simultaneously. But Excel remains the most flexible for custom reports, especially if you need complex formulas or pivot tables.

Frequently Asked Questions

Can I use a budget report template for a business? Yes, but look for one that includes income, cost of goods sold, operating expenses, and net profit. Many business templates also have a “variance” column and a chart comparing budget vs. actual. If you need to track multiple departments, use a pivot table to summarize by department.

How do I make a budget report automatically update from my bank statements? Excel can import CSV or QFX files from your bank. Use Data > Get Data > From File > From CSV to import the file, then use SUMIFS to aggregate by category. This requires some setup but avoids manual entry. For ongoing automation, you might need Power Query (Data > Get Data > From Other Sources).

What is the difference between a budget and a forecast? A budget is a fixed plan for a period, set in advance. A forecast is an estimate of what will actually happen, often updated monthly. In a budget report, you might have columns for budget, actual, and forecast — the forecast helps you see if you’re on track to meet the budget by year-end.

How do I show a variance as a percentage in Excel? Use the formula =(Actual-Budget)/Budget and format the cell as a percentage. To avoid #DIV/0! when the budget is zero, wrap it in IF: =IF(Budget=0,0,(Actual-Budget)/Budget).

Can I make a budget report in Google Sheets instead? Yes, Google Sheets has similar functions (SUM, IF, SUMIFS) and free templates. The main difference is that you access templates via File > New > From template gallery. Formulas are mostly identical, but XLOOKUP is not available in Google Sheets — use VLOOKUP or INDEX/MATCH instead.

Why does my template show #REF! errors? This usually happens when you delete a row or column that a formula references. To fix it, undo the deletion (Ctrl+Z) or edit the formula to point to the correct range. If the template is complex, it may be easier to start fresh from the built-in template.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.