How to Make a Budget in Microsoft Excel

Money management can be easier by use of tools to track and organize your expenses and income.

Simple budgets on Excel are a great starting point, as you can choose to use an existing template or create one of your own to suit your needs.

Table of Contents

• Step 1: Open a blank worksheet on Microsoft Excel
• Step 2: Give your worksheet a title and create categories for income and expenses. 
• Step 3: Determining the period for your budget
• Step 4: Setting up the budget to see a difference between actual and budgeted income/expense
• Step 6: Fill/Update your budget with actual and budgeted numbers
• Step 7: Analyze your budget
• Step 8: Formatting tips

Step 1: Open a blank worksheet on Microsoft Excel

Step 2: Give your worksheet a title and create categories for income and expenses.

You can further subcategorize the generic income and expense lists, to create a more organized and a specific view for your budget. This is optional, as you can choose to keep broad categories for your budget as well.

Step 3: Determining the period for your budget

You can choose whether to have to a daily, weekly, bi-weekly, monthly or an annual budget. It depends on several factors including the frequency of your salary remittance, your financial activity, and how often you are likely to update the budget.

Step 4: Setting up the budget to see a difference between actual and budgeted income/expense

Regardless of the period you use for your budget template, divide each time period into three parts:

• Budgeted income/expense
• Actual income/expense
• Difference between the budgeted and actual income/expense

Step 5: Using formulae to streamline your budget

Using simple formulae in the budget template will enable you to conduct accurate computations, will minimize redundancies and create efficiency.

• To calculate the difference between actual and budgeted income/expense, use the following formula:

• To find out the total values for your income and expenses, use the SUM formula

• Use AutoFill to drag and copy the formulae to the other cells

Step 6: Fill/Update your budget with actual and budgeted numbers

Using your previous bank data and receipts, input the required numbers into your budget. This exercise will need to be repeated based on the selection of your time period for the template. 

Step 7: Analyze your budget

As you go through the numbers, you will be able to see the discrepancies easily in the difference column, which will tell you how to budget and spend appropriately for the next time period. This is a great first step towards personal money management and gives you a snapshot of where the changes need to be made or if you need to account for other unforeseen expenses over time.

Step 8: Formatting tips

• Select the entire range and use the currency feature to format the cells to $ or currency of choice.

• Use conditional formatting to give a visual representation to your actual and budgeted expenses.
  • Select the column required and click on Conditional Formatting

• Select Highlight Cells Rules 🡪 Greater Than and the formatting rule as per the screenshot below

• Next, select Highlight Cells Rules 🡪 Less Than and the formatting rule as per the screenshot below

• You will have a worksheet that highlights all the exceeded expenses in red and in green if they have been less than the budgeted amount.

• Use charts and graphs

This helps give a visual representation to your data. Excel has many kinds of charts and graphs that can be used to visualize the required information.

• Select the columns/rows 
• Click on Insert and select the thumbnail for the type of chart you need.

Bonus: Using a built-in budget template in Microsoft Excel

• Open Excel, click on File 🡪 New From Template

• In the top right Search bar, type “budget” and hit enter to see a list of budget templates available for Excel.

• You can select one of these templates and fill out your financial information in the pre-made fields, wherein the formulae and the categories have already been set up. These templates can further be edited to suit your needs.