How to Make a Budget In Excel: How-To Guide and Free Templates!

If we’re talking about personal finance tools, Excel is the real OG budgeting software. And surprisingly, learning how to make a budget in Excel is incredibly easy. This how-to guide will take you from a blank spreadsheet to fully functional Excel budget in about an hour or less.

But, why use Excel for budgeting?

Excel gives you the freedom to create a budget that fits your exact needs. Budgeting apps have gotten pretty good, but they still don’t give you the kind of flexibility and control you get with Excel. With Excel, you get to decide which categories and subcategories you add, how you organize everything, etc.

The other reason is that if you already have the Microsoft Office Suite, then you already have Excel! That’s free access to a customizable budget that calculates your finances for you.

In this how-to guide, you’ll learn exactly how to make a budget in Excel. And at the end of this step-by-step tutorial, you can find our favorite free Excel templates (including the one I made for this article).

How to make a budget in Excel from scratch

Are you ready? I’m about to walk you through the entire process of creating a budget in Excel.

Step 1: Open a blank spreadsheet

Open Excel, and click on “Blank Workbook.” Don’t forget to name your file, and save as you go.

Look at it… so many possibilities!

Step 2: Start formatting your budget

There are two key components to your budget – your income and expenses. I like setting up the overall layout of my budget before I start inputting information about either. And while you can put those components on separate pages, we’re going to keep them on the same spreadsheet.

One tip before we get started: I highly recommend getting familiar with your toolbar, what you can do through a right-click, and learning a few key commands. “Merge and Center” in the toolbar is going to be used a bit while you’re setting things up, and there is a drop-down that lets you find other merge options.

Labeling your budget

For both your income and expenses, we’re going to be using similar categories – the name of the source, date (when applicable), planned vs. actual, and the difference. You can add custom fields if you’d like, but these are the most important and common.

How to set up your spreadsheet for your income:

• Select rows 1-3 of columns A-Q and merge and center. Label this spreadsheet with the month and year.
• Select rows 4-5 of columns A-E and merge and center. Label this “Income.”
• Select rows 6-11 of columns A and B and merge across. Label this “Source.”
• You’ll label the columns to the right “Date,” “Planned,” “Actual” and Difference.”
• In cell A12, write “Total.”

How to set up your spreadsheet for your expenses:

• Select rows 13-14 of columns A-E and merge and center. Label this “Expenses.”
• Select rows 15 of columns A-E and merge across. Label this “Housing.”
• Label the columns to the right “Due Date,” “Planned,” “Actual” and “Difference.”
• In cell A26, write “Subtotal.”

We’ve just created a mini spreadsheet for housing expenses, and we’ll do the same thing for transportation, food, entertainment, personal, kids, and health. If this were my budget, I’d add pets in there too. This is really the best part about learning how to make a budget in Excel – you can customize it for your exact needs.

You’re also in control of the overall look of your budget, and you can right-click on cells you’d like to format, then click “Format Cells.” This is where you find fill, borders, etc.

Designing the rest of the spreadsheet:

• Our example is separated into three major columns. When I used as many rows as I wanted in columns A-E, I narrowed column F, and started formatting columns G-K, formatting for expenses the same way we did for the other side.
• After the middle section was used up, I narrowed column L, and started formatting columns M-Q.
• Financial obligations like debt and savings have their own section in this budget, but you do you!
• The last section is for totals – planned, actual, and difference. You could easily turn this into a zero-based budget by adding another line in the final section where you set everything to zero.

Step 3: Add your income and automate with formulas

The first part of this step is to input your income. Make sure you add every possible source – main income, side hustles, any other type of monthly payments, etc.

Once you’re ready to add formulas, click on the “Formula” tab on the top of your menu, then find the “AutoSum” button. You won’t need to click on it yet; I just want you to know where it is before starting.

Adding formulas

• To total your income: Highlight everything in your “Planned” income column (in the example that’s C7-C12), then click “AutoSum.” Do the same for your actual income.
• To calculate the difference: In the first cell of the “Difference” column, enter the formula “=SUM(D7-C7).” The formula is subtracting the amount in the “Actual” cell from what’s in the “Planned” cell. Use that formula for each income source, but change the name of the cells as you go.

The last step with your income is to “AutoSum” what’s in the difference column. After we do that in my example, we’ll see that this couple is working with $200 less this month.

Step 4: Add your expenses and automate with formulas

Now it’s time to add everything in for all of your budgeting categories, be as specific or general as you want.

A note on categorizing expenses

Budgeting in Excel gives you full control to organize in a way that makes sense to you. We all manage our spending differently, so why not have a budget that reflects that!? Here’s what I mean:

• Our example has a section for “Health” and that’s where I would put my monthly health insurance premium. You could categorize things differently and have a section called “Insurance,” keeping your health, home, and auto insurance in one place.
• Even though there is a dedicated space for “Financial Obligations” like debt, it makes more sense to me to put my mortgage under “Housing.” Do what makes sense for you!

Once you have all of your expenses in your budget, you’ll “AutoSum” each column the same way you did with your income, but you’ll use a slightly different formula for the difference. The formula you’ll want to use here is subtracting the “Actual” from the “Planned.”

Step 5: Find your monthly spending totals

For our spending, we’re going to find three different totals – planned total, actual total, and the difference between the two. Don’t even think about grabbing a calculator or paper and pencil. Excel does all of the math for you!

Planned Total

We’re going to add the “Planned” subtotal for each section of our expenses and financial obligations. To do so:

• Click on the cell for planned monthly spending total (it’s O25 in our example)
• Start typing the formula “=SUM( ”.
• Instead of manually entering the names of the cells, use your mouse to click on the first “Planned” subtotal then type “+” then the next subtotal, and so on. Excel will highlight each of those cells as you go.
• Once you’ve done that with every subtotal, then close the formula with a “)”. Before hitting enter/return, check to see that each cell name is separated by a plus sign and that you didn’t put a plus sign at the end.
• The formula in our example looks like =SUM(C26+C35+I9+I17+I24+I30+I35+O12+O20).

Actual Total

We’re doing the same thing here except using the “Actual” subtotals. So in our example, the cell the “Actual Total” goes in is P25, and the formula looks like =SUM(D26+D35+J9+J17+J24+J30+J35+P12+P20).

Difference Total

Use a formula to subtract the “Actual” total from the “Planned” total. In our example, that’s =SUM(O25-P25).

Step 6: Final check-in

This isn’t exactly a zero-based budget, but it still works on a similar principle – spend only what you make.

In our difference total, our fictional couple spent $75 less than they planned for the month, even though they made $200 less than planned. They managed that while also paying down debt and putting money towards savings. Good job Peter and Anna!

They went over and under in a few different areas, and you can see those adjustments and fluctuations in the “Difference” column.

That column tells you a lot, and you should use it to inform future monthly budgets. In my house, we’ve had to do this pretty often with food spending. We have three kids, and they keep eating more as they grow, so we naturally have to spend more on groceries. Over the course of several years, I went from spending $50 a week to $150 a week on groceries. My budget had to reflect that change to keep working for our family.

Step 7: Using your Excel budget for next month

This is super simple! First, go up to “File” and click “Save As Template.”

When you’re ready for the next month’s budget, click on “File” and open “New from template.” Your template will be under “Personal.” If you want to keep each month together in a single workbook, making each month a new sheet, select the entire budget and copy and paste it into a new sheet.

When you’re ready to use, you can replace this month’s info with last months. BUT, do not just highlight and delete what’s in each section – you’ll lose the formulas! They’re easy enough to add back in, but having them set up is why learning how to make a budget in Excel is so valuable.

Or, you can highlight and delete the info you’ve put into those first free columns. You’re not deleting what’s in the “Total” or “Subtotal” field. And, you’re not deleting anything in the difference column.

You can see which columns in this picture:

After you delete that info, you keep your formulas, but everything is set to $0. You’ll notice that your formulas will start working as soon as you start putting info in for the new month.

Free Excel budget templates

If you’re new to Excel, you could probably use this how-to guide and create your very own Excel budget spreadsheet in an hour or less. But if that doesn’t sound like your idea of an hour well spent, then here are links for some free Excel templates:

• It’s Your Money– This site has both paid and free Excel templates for cash flow budgeting, envelope budgeting, simple budgets, etc. There are spreadsheets for small businesses, Dave Ramsey debts spreadsheet, coupon tracker, and more.
• Vertex42– These are Excel and Google Sheets friendly spreadsheets for a yearly and monthly budget, transactions, and a spending report.
• Mint– Yep, that’s the free money tracking tool Mint! They have free Excel budget sheets for families, students, etc.
• Federal Trade Commission– Okay, it’s not an Excel spreadsheet, but it is a free budget worksheet put out by Consumer.gov.

Our favorite FREE Excel budget spreadsheets

We asked M$M readers to share their favorite Excel spreadsheets – the ones they’re personally using to pay off debt and save more money. We got some great submissions, including a spreadsheet that helped this couple pay off $51,000 of debt in 18 months on a teacher’s salary.

You can get our reader’s submissions and the example I created for this post by signing up below (I’ve wiped it clean, but the formulas are still in there for you!). If you have your own submission, drop us a line and we’ll add it!

[convertkit form=5023311]

The final word on how to make a budget in Excel

There’s not much more to say about how to make your budget, rather I’ve got something to say about how you use it.

When you keep a budget (Excel or not), you get a nice big picture of everywhere that your money is going. If we look back at our fictional couple, we can see where they can make small adjustments to save more. We can also see that once they wipe out their student loan debt, they’ll have an extra $2,000 a month to put towards retirement.

A budget is a powerful financial tool and learning how to make a budget in Excel lets you build a tool that fits you perfectly.

Sharing is caring!

August 2, 2019Posted in: Tips And Hacks