In this tutorial, we are going to learn to make a monthly budget in Excel. Budgets help you increase your savings or profits by helping you stay within the estimated costs and expenses.
We will learn to create a budget both automatically and manually, step-by-step.
Steps to create a budget in Excel
Let’s get started with this step-by-step tutorial to creating a budget in Excel.
1. Creating a budget using templates
Let’s create a budget automatically using templates. Follow these steps.
- Open a new Excel workbook.
- Click on More Templates.
- Type “budget” in the search box.
- Select your favorite budget template based on your needs and liking.
You have a variety of budget templates to choose from this list.
- Once you’ve selected a template, click Create.
Take a look at this neat personal monthly budget template that was created automatically.
This ready-made template eases your task of manually creating a budget. You simply have to add the figures that are relevant to you and you’re good to go.
Look at how easily you can add the different kinds of expenses like transportation, loans, insurance, taxes, housing expenses, and more. Not to forget the sub-categories that are already created for you. How convenient, isn’t it?
Notice that the template also allows you to enter estimated cost as well as actual cost for an item. It also automatically calculates the difference between actual and estimated costs for you.
Excel’s budget templates are completely automated. This makes your task even more time-saving and allows managing data in a clean and systematic way.
Creating a budget in Excel from scratch
Let’s get started with creating a monthly budget manually in an Excel sheet.
- Open a new Excel workbook.
- Type a heading named “Income”.
- List down all sources of income like salary, dividends, etc.
- Type “Total” in the last once you have listed all your income sources.
- Now, type in your income values.
- In the totals row, sum all your income values using the sum function.
- Or press Alt + ’=’ to let Excel automatically sum the above values for you.
- Press ENTER to complete the formula.
- Select the figures and add ‘Accounting’ format to them to display numbers with a $ sign.
- Go to the Home tab, pull down on the box named ‘General’, and select Accounting.
Now, it’s time to list all your expenses and calculate your savings. We will take an example of a personal monthly budget of a person. Let’s get started.
- Skip a cell below ‘Total’ and create a new heading named ‘Expenses’.
- Now, list down all your expenses under this heading.
- Add ‘Savings’ at the bottom of the list after ‘Total’.
Here’s how we have listed below the monthly expenses.
- Now, to calculate total expenses, press ALT + ‘=’ or use the sum function to sum all expenses.
- Hit ENTER to complete the formula.
- To calculate savings, find out the difference between total income and total expenses. Here’s how you can do it.
- Type ‘=’ in the savings cell, select the total income, type ‘-‘ and then select the total expenses.
- Press ENTER to complete the formula.
You can see that we have successfully calculated the savings amount for the month.
You can also create an ‘Estimated’ sub-section in the Expenses heading if you wish to compare your actual costs or expenses with estimated or planned costs.
Here’s how an expenses list with actual and estimated expenses would look like.
The expenses for each month are created in a fresh new sheet within the same workbook.
To create a new sheet, click on the + and start making the budget for a new month in a new sheet every time.
Graphically representing a budget
To graphically represent your expenses on a chart, here are the steps to be followed.
Learn more here in detail to manually create a bar chart in Excel.
- Select the range with expenses and the two titles named Actual and Estimated.
- Go to the Insert tab.
- Click on Recommended Charts.
- Select a chart style you like that best represents your data.
- Click OK to create a chart.
- Double-click on the Chart title to rename the chart as you like.
Learn more about editing legends and axes on a chart in Microsoft Excel here.
Conclusion
This article was all about creating a budget and representing it graphically in Microsoft Excel. Feel free to comment below if you have any doubts regarding creating a budget in Excel.
References- Excel Easy