Budgeting is one of the best ways to keep your finances on track. It helps you monitor actual income and spending while comparing it with your planned numbers.
Microsoft Excel is a great tool for budgeting, because of its many automation and analysis features. Let’s learn how to budget with Microsoft Excel.
1. Gathering Data for Your Budget (What’s An Aggregator?)
The first step in any budget is gathering your data. This means tracking your income streams, expenses, and any other cash inflows or outflows that you anticipate. There are two key approaches for this: finding the data manually, or by using an aggregator service.
Pulling data manually would involve adding your income from a bank statement, payroll stub, or online account, for example. Then, you’d track expenses by looking at bank accounts, credit cards, automatically-paid bills, and more.
The alternative is to use an app or tool to aggregate your data. Basically, you connect your accounts to these tools and let them pull together your financial data. Since most people have multiple accounts, it helps to use a tool that pulls all the data together.
You don’t have to log into or visit each individual account to see your financial activity. We’ll use these tools to export your financial data into a Microsoft Excel spreadsheet and download it to your computer.
The choice is yours, but there are factors that may help you decide. If you only have a small handful of accounts with a limited number of transactions, pulling data manually would likely work well for you.
But if you’re building a complex budget with many accounts and transactions, using an aggregator real time-saver. It also helps ensure accuracy: entering transactions and totals manually can easily lead to mistakes that could prove costly.
Here are a few financial data aggregators you should check out while setting up your budget system:
- Mint – this service is one of the most popular and works with almost every account.
- Monarch – founded by ex-Mint employees, this is a paid option that’s worth a look. They keep your data private and offer an excellent mobile app to review transactions on the go.
- Personal Capital – this tool focuses more on showing you the balance of your accounts, but it also aggregates transactions.
Most of these tools also offering budgeting within the app. But, Excel is much more powerful and totally customizable.
In our example, we’ll use data from Mint. Go to the Transactions tab in Mint. On the sidebar on the left side, you can choose from the list of accounts to export your data.
For a budget, it’s a good choice to click on All Accounts. Scroll to the bottom of the page, then choose Export All Transactions. This lets you download the transactions to a single CSV file that you can open in Excel.
2. Adding Income, Expenses, and Savings
Once you’ve gathered or downloaded your budget data, it’s time to set up a budget layout and input the information into it. Here, we’ll build a simple budget with three individual groups of data: income, expenses, and savings. Of course, these will vary and can be tailored to your specific circumstances.
Begin by adding a layout. Again, you have plenty of flexibility, but a good approach is stacked categories with numbers in the adjacent rows. For example, Income is listed at the top, with Budget and Actual numbers highlighted side by side next to it. Immediately to the right, add a column for Difference. Leave it blank for now. To build a yearly budget, you can copy and paste these across to list figures by month. For simplicity, we’ll use three months in this example.
Repeat this design for your other categories, and then place a Totals section at the bottom. This will give you an at-a-glance overview of your budget performance.
Now, it’s time to input your numbers. In the Budget columns, you’ll want to list your targeted, or budgeted, amount. This may be the same for each month, or it might vary. Perhaps you know that you’ll be receiving an annual bonus in the month of February, and you’ll want to adjust the income number upward.
In the Actuals column, place your actual transaction totals. This is how you actually performed, and you’ll use it to track performance. It measures real, not projected, income, expenses, and savings.
If you’re pulling your data manually, simply type in the numbers into the individual cells. If you’ve exported from Mint or another aggregator, you can save time by totaling, copying, and pasting values into your budget spreadsheet. Whatever method you choose, you’ll soon have the basic framework of your budget built in Excel.
3. Adding Budget Formulas
Once you’ve inputted your raw data, it’s time to put Excel to work making calculations for you. A good starting point is column E, which is the first of the columns labeled Difference. You’ll use this cell to track your actual performance relative to your budget for a given category.
Click into the first empty cell, which is cell E3. Begin by typing an = sign, which tells Excel that you’ll be entering a formula. Then, pause for a moment to consider the logic of a budget. In the case of income, you want your actual figure to meet or exceed your budgeted number. When an actual value is less than a budget value, you want the Difference to show as a negative number.
With that in mind, click on your first actual number, which is inside cell D3. Then, type – and click on your first budget number, which is inside cell C3. Your formula in cell E3 reads:
=D3-C3
Hit Enter on your keyboard, and Excel will return your value. Here, the numbers match, so that’s 0. Repeat the same steps for the other months in row 3. From there, to capture other categories of income, hover over the lower right corner of one of the cells containing formulas. Drag your cursor downward, and Excel will automatically update the formula for the values in rows 4 and 5.
From here, you can repeat the process for the Expenses and Savings categories. Input the formula once in each of the Difference columns, then copy it downward to capture other categories. Remember that for Expense, you’ll want to subtract column D values from column C; expenses should be negative numbers if they exceed actuals.
Then, add in your Total formulas. The first would be in cell C6:
=SUM(C3:C5)
Again, you can copy the formula across the row to instantly update the other totals.
For the Totals in row 18, you’ll input formulas only in the Budget and Actual columns. These will subtract the above subtotals. In cell C18, your formula will be:
=C6-C12-C16
Repeat it for the data in columns D, F, G, I, and J.
In just a few clicks, you’ve built an automated budget in Excel.
4. Color Coding Your Budget Sheet
All of your data is now visible, but it might not be easy to intuitively understand at a glance. That’s where Conditional Formatting comes in. Conditional Formatting is a smart feature built into Excel. It analyzes data and applies color coding to it based on conditions that you specify.
Before you start, it pays to consider the logic of a budget once again. This ties back into how formulas are built. Positive differences are good. In the case of income, they mean you earned more than expected. In the case of expenses, they mean you spent less than expected. With this in mind, it would be logical to have positive numbers highlighted in green, matching numbers in yellow, and negative numbers in red.
Of course, you could use the Fill Color option and add all of these manually. But Excel will do the work for you, thanks to Conditional Formatting. To get started, click and drag to select all of the data in the first Difference column. Here, that’s column E. Then, hold down Control on your keyboard (Command on Mac). Highlight the data inside the other two Difference columns, which are H and K.
With the data selected, go to the Home tab on Excel’s ribbon. Click on the Conditional Formatting dropdown menu, and then hover over Color Scales. You’ll see several options available. The one you’re looking for, using the color scheme outlined above, is the first option. Hover over it, you’ll see that it’s called Green – Yellow – Red Color Scale.
Click on it, and you’ll watch each selected cell change colors. They now match the desired colors, with positive values in green, matches in yellow, and negatives in red. This lets you quickly scan for outliers, without having to spend time working through the data.
5. How to Analyze the Data in Your Budget
Once you’ve built out your budget spreadsheet, remember that it’s never a static file. Instead, you’ve built a living document that you can (and should) update regularly. Thanks to the formula-driven design, any change you make to a budgeted or actual value will automatically update the entire file.
Similarly, you can add additional months, simply by adding new columns and copying your formulas across. You could also add new categories, representing new income, expense, or savings streams that impact your overall budget. Excel will also let you build smart illustrations like charts and graphs with the data you have contained in the budget.
The design we explored is but one of many possible layouts. By using a stacked approach and logic-based Conditional Formatting, this one lets you immediately understand your real-time financial position at a glance.
This ease of use makes budgeting more manageable, and you’ll be more likely to stay engaged with it. In a challenging world and an evolving financial landscape, this is the perfect way to meet your goals while staying financially healthy and secure.
Data Validation in Excel