If there were a single most important aspect of personal finance, I’d say that it is learning how to create a budget in Excel.
Over the years, I’ve ventured into budgeting apps that should make budgeting easier. But each time, I’ve found myself back in Excel creating a budget template that works for me.
If you’ve ever been in Excel to create a budget, then you’ve quickly realized that they have a plethora of pre-made budget options available. Those are cute, but I find that they hardly are robust enough to do a really good job at budgeting.
So instead of just blindly choosing one that looks the prettiest, I suggest that you learn how to create your own budget in Excel from scratch.
Why We Budget in Excel
With there being so many budget apps there, why still budget in Excel?
I find that question almost on par with, “Why carry cash in a debit card world?” There are just some tried and true methods that shouldn’t be done away with.
Early in my personal finance journey, I registered with a website called Mint.com. This free platform allowed me to not only budget, but it also allowed me to track my financial goals and calculate my net worth.
Though the app—like many since—boasts being able to accurately categorize your spending, I found that I was having to do a lot of clean up work.
For instance, if I bought some personal items at the grocery store, it would label it a groceries although they weren’t. You get the point.
I figured if I’m going to have to do “manual overrides” all the time, I might as well just do manual inputs.
So instead of trying to find an app that fits what our budgeting needs are, my husband and I created our own budget template in Excel that we use to manage all of our finances.
Within Excel, we can create charts and do analysis on our spending just as well (if not better) than any app that’s currently on the market.
Step-by-Step Guide to Creating a Budget in Excel
Before you actually create your budget in Excel, you need to determine which budgeting method you’re going to use.
For this example, I’ll be using zero-based budgeting.
If you’ve never heard of zero-based budgeting, it’s essentially budgeting such that you have a remaining balance of zero.
This doesn’t mean that you necessarily spend all of your income. Instead, it just means that you’ve allocated every penny that you expect to receive during a given time period.
1. Open a Blank Workbook in Excel
To open a blank workbook, simply open Excel from your start menu. You’ll be taken to a screen where you can choose to open a blank workbook or pre-made templates.
Select ‘Blank Workbook’
Save your Workbook
The first thing that you want to do after opening your workbook is to save it. Ideally, you should save it as something like Household Budget.
To save your workbook, simply select File > Save As > and type in Household Budget. Be sure to select the correct folder that you want to save your budget.
Rename your Tab
The great thing about an Excel budget is that you can keep historical information within one workbook.
For instance, our budgeting file contains budgets from previous years. This allows us to reference prior years if we need guidance on how much funds we should allocate for a particular line item.
It also makes it easier for the next year because we can just copy and paste the prior year’s expenses and formulas.
To rename your tab, hover your mouse over the tab at the bottom of the worksheet. It should automatically be named Sheet1.
Right-click the tab and select Rename
I recommend simply renaming it as the current year. That way you can easily refer back to it in future years.
2. Create your Income Columns
Your income will be the first thing that you list on your budget. After all, with no money coming in, there is no money going out.
Within your budget, you’ll need to account for all income sources, including side hustle income and money from your business or 9 to 5.
To create the income portion of your budget, select cell A1 and type in the current month. If you budget biweekly (like us), you may also put the pay period ending date. I’ll be using a month for this example.
Below the date or month, select cell A2 and type in ‘Income’. This will help you differentiate between what is income and what is an expense.
In cell A3, type in ‘Source’. This will be your column header under which you’ll list all of your income sources for that budgeting period.
In cells B3, C3, and D3, type in ‘Estimated’, ‘Actual’, and ‘Difference’, respectively. Under these column headers, you’ll put the estimated and actual income. You’ll also create a formula to calculate the difference between the two.
To easily show that cells A3-D3 refer to income for that specific month or pay period, you’ll want to have the ‘Month’ & ‘Income’ header span across all four of these columns.
To do this, select cell A1-D1 and click the ‘Merge & Center’ button on your toolbar ribbon. Repeat this for cells A2-D2.
List your Income Sources
Once you have the columns set up, you can go in and set up standard or recurring income sources.
List your standing or recurring income sources in column A beginning in cell A4.
For the sake of this example, I’ve added five income sources. You can add as many as you need based on your own personal finances.
In column B, beginning in cell B4 type in the amount you estimate to receive from each source. Column C (Actual) should remain empty until you actually receive that income and have an exact amount. For this example, I’ve put in values.
To calculate the difference between what you estimated and what you actually received, you’ll need to input a formula into column D. The formula that you will use is: =C4-B4.
Copy this formula into each of the rows that have an income source. You can also do this manually by typing the formula into each row and changing the cell number to match the row (Ex. C5-B5, C6-B6, etc).
Total up your Income
To total up your income, you’ll add another row beneath all of your income sources that will calculate this for you.
In Cell A11 type in ‘Total’.
Note that I chose cell A11 arbitrarily. I simply wanted to leave enough room in case there are more than five income sources that need to be accounted for.
In cell B11, type in the formula =SUM(B4:B10)
Although there are no quantities in cells B9 or B10, this formula is written such that if a quantity is added later on, it would include it in the sum.
Do the same in cell C11 and D11, remembering to change out to C4:C10 and D4:D10, respectively.
You should now have a total of your estimated income, actual income, and the difference between the two.
Note: I will cover formatting, such as adding colors and formatting text in the video that’s included in this post.
3. Create your Expense Columns
To create your expense columns, you’ll follow many of the same steps from the income portion of your budget.
Type in ‘Expenses’ in cell A13 and merge it across to cell D13.
Type in ‘Expense Name’, ‘Estimated’, ‘Actual’, and ‘Difference’ in cells A14-D14, respectively.
Determine your Expenses
If you’re not sure what expenses to include in your budget, I’ve got you covered. I created a list of over 90 possible household expenses that may apply to you.
Don’t worry if you don’t incur these expenses every month or every pay period. You can always allocate zero dollars to the expense if you need to.
I suggest following the outline in this blog post so that you can group these expenses into categories that make sense.
This is important because at the end of each month, quarter, or at the end of the year, you want to be able to see where the majority of your spending took place. You can make adjustments to your spending with that information.
List your Expenses
For this example, I’ve listed the 15 common budgeting categories; however, you can list individual items that may apply to you.
List your expenses in column A beginning in cell A15.
Enter in your estimated and actual expense amounts in columns B and C, respectively.
Remember that your actual should remain empty until you actually incur an expense in that area and have an exact amount. I’ve added values for this tutorial.
If you have three expenses that are $50, $20, and $30, to add them up you’ll use the formula =50+20+30. The total of $60 will then be calculated in the cell.
Use the difference formula that you used earlier in column D, beginning at D15. Remember, that formula is =C15-B15 (changing the number to match the row).
4. Calculate your Balance
It’s important that there is a way to make sure that your expenses aren’t exceeding your income. To do this, you’ll create a row that will calculate the difference between your expected & actual income and your estimated & actual expenses. This is essentially your balance.
In cell A31, type in ‘Balance’
In cell B31, type in the formula =B11-B30 to calculate the difference between your expected income total and expected expenses total.
Repeat this formula in cell C31, being sure to change the letters to ‘C’ to match the respective column.
For a zero-based budget, your balances should be zero. If you find that you have a balance that’s negative, you’ve somehow spent above your income. If you find that your balance is greater than zero, you have excess funds that you can apply toward your debt or to savings.
The goal is to have a zero balance—meaning every penny of your income has been allocated to something.
As you add more expenses, your balance will automatically calculate to show you if you’re on track with your estimated budget or not. Make adjustments to your spending as you go to avoid being over budget.
5. Add your Budget to Google Drive or DropBox
If you want to have your budget on the go, I recommend creating it in Google Sheets. This is Google’s online version of Excel that you can access anywhere on a computer or through the mobile app.
You will follow the same instructions listed above to create your budget, with some minor differences in the interface. Or, you can simply create your budget in Excel and upload it to your Google Drive or Dropbox.
Either way, you’ll be able to access it on the go. Access is important when you’re tracking expenses as they occur.
There are so many great benefits of creating your budget in Excel.
Though I only show the basics, you can further customize your budget to include graphs and other charts. It’s useful to have visuals for your finances so that you can see things at a glance.
I hope that this tutorial on how to create a budget in excel from scratch was helpful. Let me know your thoughts in the comments.