• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

Excelypedia

The Excel Encyclopedia of The Internet

  • Tutorials
  • Functions
  • Formulas
  • Templates

Budget in Excel

May 24, 2021 by Andrew Childress
Color code Excel budget

Microsoft Excel is a great tool for budgeting, because of its many automation and analysis features. Here's how to budget with Excel.

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.

Budget in Excel
Aggregator tools like Mint allow you to connect your financial accounts, then automatically review all of your transactions.

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.

Decide on your financial categories, then lay out an “actual versus budget” table like this one.

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.

How to budget Excel
Begin to compare actual versus budget results, and your spend management will quickly become clear.

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
Budget formulas Excel
Difference formulas help you see the performance by each category.

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.

How to budget in Excel
Pull down the difference formula to extend it to each category.

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)
How do I budget in Excel
Sum up the columns to reach a subtotal by budget and actual.

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.

Budgeting with Excel
Repeat what you’ve already done across columns to automate your calculations.

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.

Color code Excel budget
Turn on conditional formatting to highlight your budget performance.

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.

How to color code Excel budget
It’s much easier to spot wins and losses in your budget once you enable conditional formatting.

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.

Category: Excel TutorialsTag: Excel, How To, Instructions, Tutorial

About Andrew Childress

Previous Post:Excel data validationData Validation in Excel
Next Post:Frequency in ExcelFrequency Excel

Sidebar

Recent Posts

  • How to Do a Contingency Table in Excel
  • Format Cell Alignment in Excel
  • Default Width in Excel
  • Translate in Excel
  • Switch Windows in Excel

Categories

  • Basics
  • Examples
  • Excel Formulas
  • Excel Functions
  • Excel Shortcuts
  • Excel Templates
  • Excel Tutorials
  • Uncategorized

Excelypedia

Master Excel to add a highly valuable asset to your employability portfolio. Learn how to analyze data in Excel.

    • Facebook
  • Twitter
  • Instagram
  • YouTube

Learn

  • Functions
  • Formulas
  • Shortcuts

Legal

  • Privacy Policy
  • Cookie Policy
  • Terms & Conditions

Help

  • FAQ
  • Contact Us

Copyright © 2023 · Excelypedia · All Rights Reserved

Back to top