Build a Project Budget in Excel

A project budget in Excel is a list of cost lines, each with an estimate, a total, and a place to record what you actually spend. You only need a few formulas: multiplication for each line, SUM for totals, and simple subtraction and division for variance and % used. This guide builds a complete budget for a small website project step by step, so you can copy the layout for any project you plan or manage.

How the project budget is laid out#

Use one row per cost line and one column per piece of information. Here is the finished budget for a website redesign:

Below it, rows 10 to 12 hold the contingency and the total budget, which we add in Step 3. Only the input columns (Qty, Unit cost and Actual) are typed. Everything else is a formula, so the budget updates when an estimate changes.

Step 1: Set up the columns and inputs#

Type the headers in row 1 and the cost lines below. A few habits make the sheet easier to use:

• One item per row. Don't put two costs in one cell, like "Design + build". You can't total them separately later.
• A Category column. It lets you total by type of cost in Step 6.
• Units in the item name, such as "(days)" or "(months)", so everyone reads the quantity the same way.
• Freeze the header row with View > Freeze Panes > Freeze Top Row, so the headers stay visible.

Turning the range into an Excel table (Ctrl+T; Mac: ⌘T) is optional but useful: formulas fill down by themselves when you add a cost line.

Step 2: Calculate each budget line#

Each line's budget is quantity × unit cost. In E2, type:

=C2*D2

The result is 5,400 (12 days × 450). Fill it down to row 7 (Windows: Ctrl+D; Mac: ⌘D). If the developer estimate grows from 20 to 23 days, change C3 and the budget for that line updates to 11,500.

Building the budget from quantity and rate, instead of typing the total, shows your working. Anyone reviewing the budget can see why a line costs what it does.

Step 3: Add subtotals, contingency and a total with SUM#

Total the cost lines with SUM. In E8:

=SUM(E2:E7)

The subtotal is 21,000. The SUM function guide covers the syntax, and Alt+= (Mac: ⌘+Shift+T) inserts the SUM for you.

Most projects add a contingency: an extra amount for costs you can't predict yet. Put the rate in its own cell so it is easy to change. If D10 holds 10%, then:

E10:  =E8*$D$10
E12:  =E8+E10

The contingency is 2,100, and the total budget in E12 is 23,100. The $ signs lock the reference to D10, which matters if you copy the formula. Never type the 10% inside the formula; a separate cell makes the assumption visible.

Step 4: Track actual costs and variance#

As invoices and timesheets arrive, enter the amounts in the Actual column. Labour actuals often come from hours logged; the timesheet guide shows how to total them.

Variance is the difference between budget and actual. In G2:

=E2-F2

Fill it down. A positive number means the line is under budget, and a negative number means it is over. The designer line shows -450: 450 over budget. The total variance in G8 is 2,000, so the project is 2,000 under budget so far, with photography still unpaid.

Some teams calculate actual minus budget instead, so over-budget lines are positive. Either is fine, but write the rule next to the header so nobody reads it backwards. The budget vs actual guide covers favourable and unfavourable variances in more detail.

Step 5: Show % used and percent change#

% used shows how much of each line's budget is spent. In H2:

=F2/E2

Format it as a percentage with Ctrl+Shift+% (Mac: Control+Shift+%). The designer line shows 108.3%, so it has gone over. For the whole project, =F8/E8 gives 90.5% of the subtotal, or =F8/E12 gives 82.3% of the total including contingency.

When an estimate is revised, show how much it changed with the percent change formula, (new - old) / old. If the developer budget goes from 10,000 to 11,500:

=(11500-10000)/10000

The result is 15%. In a real sheet, point at the cells that hold the old and new budget instead of typing numbers. The percent change lesson practises this exact formula.

Step 6: Summarise by category and flag overspends#

To total one category, use SUMIF on the Category column:

=SUMIF(A2:A7,"Labour",E2:E7)

Labour comes to 18,600 of the 21,000 subtotal, so it is the cost to watch. To flag lines that have gone over, add a status column:

=IF(F2>E2,"Over budget","OK")

You can also highlight overspends in red with Home > Conditional Formatting > Highlight Cells Rules > Less Than on the Variance column, using 0 as the value.

If the project runs over several months, the same structure works with one Actual column per month. To plan when the money goes out, not just how much, see the cash flow forecast guide. For a personal or household version, the monthly budget template uses the same formulas.

Common project budget mistakes#

These are the errors that most often make a budget total wrong:

1. Typed totals instead of formulas. A total typed by hand doesn't update when a line changes.
2. A SUM range that misses new rows. If you add a line below row 7, check that the subtotal now includes it, or use a table.
3. Contingency buried in a formula. Keep the rate in its own cell.
4. Mixed signs. Decide whether variance is budget - actual or actual - budget, and stick to it.
5. Dividing by an empty budget. =F2/E2 returns #DIV/0! for a line with no budget yet. Use =IFERROR(F2/E2,0) if that happens often.

Practise it#

Budgets come up constantly at work and in job tests, and they rest on two skills: totals and percentages. Try the exercise on this page, then practise SUM in a live spreadsheet: you type the formula and it checks it instantly. The percent change lesson covers the rest. If you're preparing for an interview task, the guide to the basic Excel test for employment shows what's usually asked.