How to Make a Budget in Excel: Step-by-Step Monthly Budget Template

shape
shape
shape
shape
shape
shape
shape
shape
How to Make a Budget in Excel: Step-by-Step Monthly Budget Template - featured image

A budget in Excel shows what money comes in, what goes out and what is left. It is easy to build, easy to change, and it can grow from a simple list into a full yearly tracker with charts. This guide shows how to build a monthly budget from scratch, with an income table, an expense table, formulas for totals, remaining money and percentages, a planned versus actual comparison, conditional formatting for overspending, and a pie chart of where the money goes. The example uses round numbers that you can check by hand. It is useful for personal budgets, household plans, small business expense sheets and client templates that freelancers build and sell.

Updated November 2026

In this guide

  1. Plan the layout first
  2. Step 1: list your income
  3. Step 2: list your expenses
  4. Step 3: add the formulas
  5. Step 4: set a savings goal and calculate what is left
  6. Step 5: add planned versus actual columns
  7. Step 6: highlight overspending with conditional formatting
  8. Step 7: add a pie chart of expenses
  9. Make the budget reusable for every month
  10. Use a transaction list with SUMIFS
  11. Tips for a better budget
  12. Budget for a small business or freelancer
  13. Quick recap
  14. Practice problems
  15. Common mistakes

Plan the layout first

Before you type, decide what the sheet must answer. A monthly budget needs four things: your income, your fixed expenses such as rent and insurance, your variable expenses such as groceries and fuel, and your savings goal. Use one sheet for the monthly budget and, later, a second sheet for the actual spending. Keep inputs in one area and formulas in another, label every table and use one currency format. A clear layout is more important than decoration, because you will come back to the file every month.

Step 1: list your income

In A1 type Monthly budget. In A3 type Income, and in B3 type Amount. Under it, list each source, such as Salary 3,000 in row 4 and Other income 0 in row 5. In A6 type Total income and in B6 use =SUM(B4:B5), which returns 3,000. If your income varies, enter the lowest amount you can rely on, so the budget stays safe. Format the amounts as currency with no decimals, to keep the numbers easy to read.

Step 2: list your expenses

In a second table, list the expense categories with the planned amount for each. For the example, use these figures.

CategoryPlannedShare of income
Rent90030%
Utilities1505%
Groceries45015%
Transport2006.7%
Insurance1204%
Entertainment1806%
Total expenses200066.7%

The planned amounts add up to 2,000, since 900 plus 150 plus 450 plus 200 plus 120 plus 180 is 2,000. The share column divides each amount by the total income, so rent is 900 divided by 3,000, which is 30 percent.

Step 3: add the formulas

Put the planned amounts in B11:B16, with the heading row above. In B17 use =SUM(B11:B16) for the total expenses. In C11 use =B11/$B$6 and fill it down, then format the column as a percentage. The dollar signs lock the total income cell, so the reference stays correct when you copy the formula down. The shares for the six categories add up to 66.7 percent of income. If you are not sure about percentage formatting, our guide to percentage formulas in Excel explains it.

Step 4: set a savings goal and calculate what is left

Put a savings goal as a percentage in a labeled cell, for example 20 percent in B19. The savings amount is =B6*B19, which is 600 for an income of 3,000. The money left to spend or save is =B6-B17-B20, where B20 holds the savings amount. For the example, 3,000 minus 2,000 minus 600 is 400. A positive number means the budget works. A negative number means that you plan to spend more than you earn, so you need to cut an expense or raise the income. Format the result cell so that negative numbers appear in red.

Step 5: add planned versus actual columns

A budget is only useful if you compare it with what really happened. Add a column named Actual next to Planned, and a column named Difference with =B11-C11, where B holds the plan and C holds the actual spending. A positive difference means you spent less than planned, and a negative one means you overspent. If groceries were planned at 450 and the actual spending was 520, the difference is minus 70. Total each column with SUM. At the end of the month, the total difference shows whether you finished above or below the plan. Keep the actual figures in a separate column for each month if you want a yearly view.

Step 6: highlight overspending with conditional formatting

Select the Difference column, go to Home, Conditional Formatting, Highlight Cells Rules, Less Than, and type 0. Choose a red fill. Now any overspent category stands out at once. You can add a green fill for values above 0. Another useful rule is to shade the Share column with a data bar, which shows which categories take the most income. Keep the colors few and meaningful, so that the sheet remains easy to read.

Step 7: add a pie chart of expenses

Select the category names and the planned amounts, go to Insert, choose a pie chart, and add percentage labels. The chart shows that rent takes the largest slice, 45 percent of the 2,000 in expenses, since 900 divided by 2,000 is 0.45. Our pie chart guide explains each formatting step. If you have many categories, a bar chart sorted from largest to smallest is easier to read. Place the chart beside the table, so the figures and the picture are on the same screen.

Make the budget reusable for every month

Create one sheet per month by copying the first sheet, and name the tabs Jan, Feb, Mar and so on. Add a Summary sheet that pulls the totals from each month, using formulas such as =Jan!B17. A chart on the Summary sheet can show income, expenses and savings by month. Use data validation drop-downs for categories, as shown in our drop-down list guide, if you add a transaction list. Protect the formula cells, and leave the input cells open, so you or your client cannot overwrite a formula by accident.

Use a transaction list with SUMIFS

For more detail, add a Transactions sheet with the date, the category and the amount for every purchase. In the budget sheet, calculate the actual amount for each category with =SUMIFS(Transactions!C:C,Transactions!B:B,A11), as explained in our SUMIFS guide. The actual column then fills itself as you add transactions. To limit the total to one month, add a date condition with the start and end dates of the month. This is a very common request from clients, because they only need to enter transactions and the budget updates itself.

Tips for a better budget

Use the 50, 30, 20 rule as a starting point if you are unsure: about half of income for needs, 30 percent for wants and 20 percent for savings and debt. Adjust it to your situation. Add a line for irregular costs, such as annual insurance, by dividing the yearly cost by 12 and saving that amount each month. Review the budget at the same time every month, and change the plan when your life changes. Keep an emergency fund line so that surprises do not break the plan. Back up the file in a cloud folder, and keep one clean copy as a template for next year.

Budget for a small business or freelancer

The same layout works for a freelancer or small business with a few changes. Income comes from invoices, so list each client and the expected payment month. Expenses include software, internet, tools, taxes and a share of home costs. Add a line for tax, because income received is not all yours to spend. A common approach is to move a fixed percentage of every payment, such as 20 percent, into a tax line as soon as it arrives. Include a buffer for slow months, since payments can come late. Track the cost of each month in the same way as a personal budget, and compare planned and actual figures. If you are building the file for a client, add a short instructions box at the top, keep input cells in one color and formulas in another, and test the sheet with a month where expenses are higher than income, so you know the warning colors work. Our annual income calculator can help to estimate yearly income from hourly or monthly pay.

Quick recap

Build an income table, an expense table with planned amounts, a total and a share column, a savings line and a remaining amount. Add actual and difference columns, highlight overspending with conditional formatting and draw a pie chart. Use SUMIFS with a transaction list so that the actual figures update themselves, copy the sheet for each month, and keep the design simple enough that you will really use it.

Practice problems

Try these. Problem 1: income of 2,500 and expenses of 1,900. The money left is 600. Problem 2: rent is 750 and income is 2,500. The rent share is 30 percent. Problem 3: a 15 percent savings goal on an income of 4,000 is 600. Problem 4: planned groceries 400, actual 455. The difference is minus 55, so you overspent. Problem 5: yearly insurance of 600 needs 50 per month in the budget. Build each one in your own sheet and check the formulas against the hand answers.

Common mistakes

The most common mistake is to leave out irregular costs such as gifts, repairs and annual fees, so the budget looks better than reality. Another is to forget to update the actual column. A third is to forget absolute references in the percentage formula. A fourth is to mix income before and after tax. A fifth is to make the sheet too complicated, so it is never used. Start simple with six to ten categories, and add detail only when you need it.

Frequently asked questions

How do I make a budget in Excel?

List your income and expenses, use SUM for totals, subtract expenses and savings from income, and add a planned versus actual column.

What formulas do I need for a budget?

SUM for totals, division for percentages, subtraction for the remaining amount and SUMIFS for actuals from a transaction list.

How do I show overspending?

Use a difference column and conditional formatting to turn negative values red.

Can I reuse the budget every month?

Yes. Copy the sheet for each month and add a summary sheet.

What is a good savings rate?

Many people aim for about 20 percent of income, adjusted to their own situation.

Related guides and free tools

Leave a Reply

Your email address will not be published. Required fields are marked *