x
Financial Literacy

How to Make a Budget in Excel

If you’re trying to achieve financial independence or just keep better track of your money, you need to be using a budget. It’s non-negotiable. While there are plenty of budgeting apps out there, you may prefer to create one in Excel. However, Excel can be overwhelming and complicated. But fear not. I will teach you how to make a budget in Excel, and it’s honestly simple. I would not lie to you. 

Once you get the hang of it, you’ll see that creating a budget makes a big difference in organizing your financial life.

So let’s get down to it.

Creating an Excel Budget

The first decision you need to make when creating a budget in Excel is whether you want to use an Excel template or build your own budget from scratch. Either method has pros and cons.

If you use a custom template, you already have a structure to work off of, which can make life considerably easier. On the other hand, many of Excel’s templates are clunky and now outdated.

We’ll go over how to create a budget worksheet from scratch later. Let’s start with Excel’s custom budget templates.

Using an Excel Template

  1. Go ahead and open up your Excel application. Keep in mind that your version of Excel may look a bit different than mine as I post screenshots below. However, these instructions should work across all releases.

    Scroll up to “File,” and click “New.” A window will pop up, and you’ll see various premade templates.

A Microsoft Excel window displaying various spreadsheet templates | Investment U

2. Move your mouse to the search bar that says “Search all templates” and type “budget.” When you do, various budget templates will pop up in your application window. These will include templates like “Manage My Money,” “Personal Budget,” “Family Budget,” “Household Budget” and a whole lot more.

3. Let’s say you want to set up a personal budget. Go ahead and click on that template. You’ll see an Excel sheet pop up that includes graphs, summaries and various line items.

A screenshot of a Personal Budget premade template in Microsoft Excel | Investment U

4. It is important not to mess with the formulas, otherwise you will break the spreadsheet. So don’t do that.

Adjust the numbers in your Excel budget under monthly income, monthly expenses and monthly savings to reflect your actual situation. (If your template opened on a page labeled “Summary,” with the other lists missing, do not edit the numbers on that first sheet. Instead, click the sheet titles along the bottom of the Excel sheet and update the numbers on those sheets.) As you add in those numbers, you’ll see the formulas under the “Summary” section of the spreadsheet change based on your entries.

Of course, you can also customize the various rows in this budget by inserting or deleting line items.  For example, let’s say you’ve paid off your student loans and you no longer have that expense.

However, you want to budget $75 per month for the cleaning service that comes to your house. I get it; I don’t like to clean my house either. 

There are two ways you can do this, and both of them are quick.

First, highlight the row that says “Student loans” by clicking on the row number.

A personal budget template in Excel with the row for student loans highlighted | Investment U

Then simply right-click on the highlighted row and click “delete.” Poof, it’s gone from your Excel budget.

Next, click on the same row number to highlight it, then right-click on the highlighted portion. Select “Insert” from the list of options that appears.

Voila! A new row has been inserted, and now you can type in “Cleaning service,” add the date if you so desire, and place the appropriate amount budgeted for the line item.

When you work with an Excel template, that’s really all there is to making a budget. See – not that complicated!

If you’re a true Excel beginner, this is probably the way to go. But if you’re looking for something a bit more custom, let’s talk about how to build a budget in Excel from scratch.

 

Creating from Scratch

1. Open a New Blank Workbook

To begin, open up your Excel and start with a new blank document. To do this, you can either double click the “Blank Workbook” icon, which will be the top left template, or go up to “File” and click “New.”

The new blank workbook will open.

2. Create a Tab for Your Income

Go to Cell A3. Write the words “Income Source.” This column will represent where you got your income from, such as your paycheck, a side hustle or an investment dividend. Make sure to bold the text for the column headers.

In the next columns, add the following headers.

  • Date
  • Planned
  • Actual
  • Surplus / Deficit

A new Excel budget made from scratch with five column headers | Investment U

You can center the headings to make the columns look neater if you like. 

I went ahead and added some sample data below my column headers. Plus I added a row for totals at the bottom.

An Excel budget with some fake data filled in | Investment U

3. Add Formulas to Automate Your Spreadsheet

Click on the cell you’d like to add your formula to. So, for my sheet, I’ll click C9.

Then click on the “fx” button at the top.

A budget with fake data in Excel with the formula builder circled in red | Investment U

When you click that symbol, the formula builder will pop up on your screen. 

Now double click on the “SUM” formula in the formula builder box.

The formula builder

That will place the formula “=SUM( )” into cell C9 of your Excel budget. The formula builder will show that you are summing cells C4:C8.

Hit enter again, and now you’ll see the sum total of column C pop into cell C9. If you used the same numbers as my screenshots, you’ll see the number 4,000.

You should be able to copy and paste C9 into D9, but if that doesn’t work in your Excel version, simply repeat the process above for D9. The sum total for column D should read 3,911.44.

To calculate the surplus or deficit for the first income source, “Paycheck 1,” click into cell E4. Hit the = key and then click on cell D4. 

Then enter a minus sign like into E4. Now click C4.

Cell C4 is subtracted from cell D4 using a sum formula

Hit enter, and the total deficit of -14.28 will appear. Copy cell E4 and paste it below in each row: E5, E6 and E7.

The totals will appear for each row. Finally, go to cell D9 and copy the SUM formula into row E9.

You’ll now have calculated your total budget deficit, which is -88.56.

Of course, you can customize each row to reflect your real sources, dates and numbers. The formulas should update to reflect your new values.

Now it’s time to create the expense side of your budget to track your monthly bills and other expenses. 

To do so, let’s go ahead and create another tab.

You can do that simply by moving your cursor to the bottom of the screen where it says Sheet1.

Double click on the word “Sheet1” so that it is highlighted. Then, type “Income” to name your sheet.

Now move your mouse to the right to click on the + sign. This will create a new sheet called Sheet2. Double click on the word “Sheet2,” and rename it “Expenses.”

You can go back to the Income sheet and copy the column titles in cells A3 to E3. Then click back into Expenses and paste them into the same cells in the new tab.

Change the label “Income Source” to “Expense Category.” I went ahead and filled in some fake data for my screen shot.

The surplus and deficit formula this time will be the reverse of what it was on the income side. Click cell E4 and hit =. Then click C4, type a minus sign –, click D4, and hit enter. Copy E4 into cells E5, E6 and E7.

Create a Total row, and use the AutoSum formula just as you did on the income side in cells C9, D9 and E9.

The expense side using autosum formulas

4. Creating Your Totals

You have two options here. You can add the totals from both the income and expense sheets on the first sheet, the Income sheet, or you can create a new, third sheet.

I recommend the former. So click back into the Income sheet.

Create column labels for Planned, Actual and Surplus/Deficit as you did before. I recommend leaving two rows as a gap to help keep your data separate.

Then create row labels for Income and Expenses.

Finally, leave a blank row and then create a Balance row. This is where you will calculate your income minus your expenses.

To input the value for your planned income, click into cell C13 and hit the = sign. Then navigate back up to cell C9, your total planned expenses, and click on it. Hit enter.

Voila.

Total rows for income, expenses, and balance

You see that in row C13 of your Excel budget you now have your planned total income pulled down into the new table. Go ahead and pull the values for your total actual income and your total surplus or deficit, and then from your Expenses sheet pull your planned total expenses, actual total expenses, and total surplus/deficit figure.

Now, to get your balance for your planned income and expenses, click into cell C16. Enter the following formula…

=C13-C14

Hit enter, and this will subtract your planned income from your planned expenses for a total balance of 2,670.

That’s a nice amount of leftover cash!

Go ahead and do the same thing for the next column, your actual income minus your actual expenses.

This should give you a total actual balance of 2,560.44.

Your actual surplus will be your actual balance minus your planned balance. So, in cell E16, enter this formula…

=D16-D15

You now see in your Excel budget that you have a deficit of -109.56. You would get the same result, by the way, by totaling cells E13, your income deficit, and E14, your expenses deficit.

You have less money remaining for the month than you planned. That’s not great. We’ll have to start spending a bit less money!

The total balances for income and expenses | Investment U

Customize as Much as You Want

That’s really all there is to making a budget in Excel! You can leave it just as it is, and this worksheet should perfectly serve your purposes.

Of course, the more skillful you get with Excel, the more you can customize your budget to fit any of your specific needs.

Feel free to format it the way you prefer, changing colors, font sizes and such. Insert charts and graphs if they are helpful.

There are lots of tutorials out there to help you improve your skills regarding how to make a budget in Excel. Now, keeping more money in your bank account? Helping you do that is what we’re here for!

Brian M. Reiser,
Contributing Writer for Investment U 

If you’re looking for more help on how to make a budget in Excel, financial literacy and the basics of investing, be sure to subscribe to our free daily e-letter by entering your email in the subscribe box below. 


About

Brian M. Reiser has a Bachelor of Science degree in Management with a concentration in finance from the School of Management at Binghamton University.

He also holds a B.A. in philosophy from Columbia University and an M.A. in philosophy from the University of South Florida.

His primary interests at Investment U include personal finance, debt, tech stocks and more.

Articles by
Related Articles