Meraz Al Nahian
The article will show you how to make a family budget in Excel. Excel is a very useful tool to calculate mathematical problems in a very efficient way. And there are more other features to analyse those mathematical problems. In our daily life, we have to bear a lot of costs in different fields. And if we don’t keep track of our everyday costs, we may have to suffer at the end of the month because of wasting money without any estimation. So it’s better to keep a note of our daily budgets and Excel is the best option on this matter.
Download Practice Workbook
2 Ways to Make a Family Budget in Excel
When you make a family budget plan, you need to identify/classify your budgets first. There are some fixed costs and most others are variable costs. Some costs we have to bear at the beginning of a month and the rest we need to continue to bear for the remaining days of a month. In the following images, I’ll show you some examples of budgets/costs we usually maintain in our daily life.
Suppose you earned 20,000$ per month. The following budgets of the image are the expenses for the first period of a month. We need to pay the house rent, education expenses (if your child is a student), internet bills, etc. which do not change over time usually. So we classify them as monthly Fixed Costs. There are also some other costs that change over time but we have to pay for them at the starting period of the month like electricity bills, gas bills, etc. because they depend on usage. As a result, we classify them as Variable Cost.
The above picture was an example of starting monthly costs. Now I’ll show you the budgets for which we have to pay daily.
Later, in this article, I’ll show you how to make this family budget template.
1. Making a Family Budget by Using Formulas
When we look at our daily life costs, it’s just a summation and subtraction process. We earn money and we spend it. We just need to subtract the total expenditure from our daily income to make our family budget. For that reason, we can use Microsoft Excel to calculate our daily budget by making some formulas. Let’s go through the following description.
Steps:
- First, convert your daily budget data to a table.
- To convert your data to a table, first select your data and go to Insert >> Table.
- After that, a dialog box will show up. Make sure you select My table has headers and click OK.
- After that, your data will convert to a table for the Fixed Costs.
- Similarly, we convert the Variable Cost section to a table.
- By the way, we used a Text Box to put the date over there.
- After that, we created sheets for the three days. You can make more copies of your daily budget if you want. Here, I’ve divided daily budgets into two types: Casual and Occasional.
- Now, insert your costs in the corresponding cells. This is just an example, you may have other fields to spend your money on.
- Next, use the following formula to calculate total monthly fixed costs.
=SUM(C7:C12)
The formula uses the SUM Function and returns the total monthly fixed costs in cell C13.
- Use a similar formula to calculate variable monthly costs
- Thereafter, type the following formula in cell C16 and press ENTER.
=B16-C13-E13
The formula will return the Present Balance or remaining balance.
- After that, copy the Present Balance and paste it as Values into the daily budget sheet of the first day. It will be considered as the Previous Balance for this sheet.
- Next, insert your daily budgets and calculate them with the SUM function.
- After that, use the following formula to calculate the Present Balance after the first day. You will use the same formula to calculate the remaining balance for the following days of the month.
=B16-C13-E13
Here, in the following images, I’m going to show you the budget infrastructure for day 1 and day 2.
You can make more sheets to store your family budget and follow the same procedure to calculate them.
Now, if you want to make a summary of your expenses, you can Consolidate these Multiple Sheets into one Pivot Table. To do that,
- First, select Data >> Get Data >> From Other Sources >> Blank Query.
- After that, the Power Query Editor will open. Give your query a name and type the following formula in the query formula bar and press TAB.
=Excel.CurrentWorkbook()
- This will return all the tables of your workbook. We don’t need the 2nd column, so we select the first column header, open the context menu by right-clicking, and select Remove Other Columns.
- After that, click on the marked icon of the following picture, uncheck Use original column name as prefix and click OK.
- Next, you will see all the budget data in the query. In addition, you can refresh your data type. You can do this by selecting the Transform Tab of the Power Query >> Detect Data Type. The process is not shown here because it’s optional.
- Thereafter, select Close & Load >> Close & Load To…
- After that, the Import Data window will appear.
- Select PivotTable Report and click OK.
- You will see the Pivot Table appear in a new worksheet. There are two fields in the Pivot Table which are Amount and Cost Type. Drag the Cost Type and Amount ranges into the Rows and Values Fields
- After that, you will see the data merged in the Pivot Table. There’s one thing we need to filter out which is the Total Otherwise, this Total amount will double the budget cost which is redundant.
- Click OK.
- Now, all the budget costs will merge together and you will see the total costs of each section like Groceries, Transport, Laundry,
Also, you will see the Grand Total of your expense too
Thus you can make a family budget in Excel by using formulas. Also, you can analyze them using a Pivot Table.
Read more: How to Make a Household Budget in Excel (2 Smart Ways)
Similar Readings
- How to Make a College Budget in Excel
- Make a Budget in Excel for College Students (with Quick Steps)
- How to Make a Wedding Budget in Excel (2 Suitable Methods)
2. Using Built-in Template of Excel to Make a Family Budget
There are tons and tons of built-in templates in the Microsoft Excel store. You can use one of them. Let’s go through the process below.
Steps:
- First, go to File.
- After that, select New and search for family budget in the search bar. You may find a template in the suggestion.
- You will see various templates on the family budget after that. Here are some of them. Select any of your choosing.
- Next, you will see a preview of this template. Just click on Create.
- Finally, your template will appear after downloading.
Thus you can make a family budget in Excel by searching templates in the Microsoft store.
Read more: How to Do Budgeting and Forecasting in Excel (2 Suitable Ways)
Practice Section
Here, I’m giving you the dataset of this article so that you can practice these methods on your own.
Conclusion
Family Budget planning is important when you take responsibility for the family. I simply shared the family budget template that I use so that it may help you to understand when you should spend money and when you should not. If you have any better methods or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website ExcelDemy.
