Learn How To Budget In Microsoft Excel

Budgeting is an important skill to learn to be financially successful. According to the U.S. National Financial Capability Study, 19% of individuals reported that their households spent more than their income in 2018. That means that approximately 1 in 5 Americans spend more money than they earn. This can often happen when people do not keep track of their expenditures and buy things without keeping in mind how much money they have. The best way to tackle this problem is through budgeting.

Simple Budgeting

When I was a single, recent college graduate, I didn’t find it necessary to keep a detailed budget. I only had one bank account, so my monthly bank statements were all I needed to keep track of my money. As long as my account balance increased each month, I knew I was doing okay. And I could look at the details of the statement to see where my money was going. This kind of simple budgeting may work fine for some people who do not have much to keep track of. Just make sure that you are saving money and your account balance is not being depleted.

Detailed Budgeting

However, many people would benefit from more detailed budgeting. I found this to be the case after I got married and combined my finances with my wife. Suddenly we had to keep track of multiple incomes, multiple bank accounts, and expenditures on multiple credit cards. To do that, we used Microsoft Excel. We created a workbook to keep track of our monthly earnings and expenditures for the whole year. You can download a template file here:

Keep track of your stable income separately from your variable income

To fill out the spreadsheet, you first need to add your income. I have separated income into two categories: regular monthly income and extra income. Regular income includes your monthly salary or any income you receive every month. On the other hand, extra income is more variable income, such as cashback rewards or money from side jobs. I prefer to separate income in this way so that you can get a good sense of how much monthly income you can depend on receiving on a regular basis versus more circumstantial income which likely changes every month. When you budget, you should plan your expenses in line with only the income you can depend one. If you end up not receiving much or any extra income in a particular month, you do not want to come up short just because you thought you might earn more.

Categorize your expenses to see how you spend your money

Next, create categories for your expenditures. In the template file, I have included categories for Rent/Mortgage, Food – Groceries, Food – Restaurants, and Utilities because these are some of the biggest expenses everyone needs to pay each month. Housing costs are usually the largest expense. Food costs are also fairly high. I have separated food expenses into groceries and restaurants so that you can see how much money you spend eating out or ordering food delivery. You can often save money by cooking more at home, although if you do have the extra money to spend, it is nice to support local restaurants. Utilities is a broad category including electricity, water, sewage, heating, internet, cable, phone, and other services. In addition to these categories, you should add your own as needed to encompass all of your monthly expenditures. Typical categories include Insurance (health, vehicle, house, etc.), Transportation (car loans, gasoline, maintenance, bus passes, etc.), Healthcare, Clothing, Education (student loans), and Entertainment. You may also have categories including Childcare, Pets, Credit Cards (debt or annual fees), Gifts, and Charity.

Excel tip: To insert a new row for a new category, right click on the number to the left of one of the current category labels and select Insert. Alternatively, click on the number and use the shortcut Ctrl, Shift, +. Once you insert a new row, make sure to extend the total and percentage calculations on the right, which you can do by copying and pasting from another cell.

Once you have created all your categories, fill them in with all of your expenses. If you need more space for expenses in a category, follow the tip above but click on the letter above a column rather than the number to the left of a row.

Analyze each category’s percentage of your total income

On the right side of the spreadsheet, your totals will be calculated. Based on the total income and expenditures, you can calculate how much money you saved for the month. These totals are useful in terms of raw numbers to see how much money goes where, but it is more useful to look at the percentage of your total income that you spend in each category. I have already inserted an equation to calculate those percentages for you to the right of each category’s total. This will allow you to see and rank how much money you spend in each category.

Excel tip: If you would like to rank your categories in the spreadsheet, highlight all the rows with expense categories (click and drag down on the numbers to the left of the rows) and select Sort & Filter –> Custom Sort… on the top toolbar in the Editing section of the Home tab. Then choose to sort by whichever column your percentages are in (the default is Column N), and for Order, choose Largest to Smallest and click OK.

If your spending in some of the categories is significantly higher than others, you should consider why this is. Do you tend to spend a lot of money on clothes, for example? Alternatively, is this a one-off situation, such as if your car battery died and you had to buy a new one?

Make a budget, taking into account your typical expenses

Now that you can see what you’re typically spending money on, you can decide what you should be spending money on. Set reasonable goals for yourself and make sure that your desired percentages all add up to 100%. Keep in mind which expenses can be more easily trimmed down (such as entertainment or clothing) and which are more difficult to adjust (such as housing). If you’re looking for guidance, one typical recommendation is the 50/30/20 rule, which suggests that you spend 50% of your income on necessities, 30% on wants, and put 20% towards debt and savings. This is a good starting place, but everyone’s situation is different. For instance, someone with a lower income will likely need to spend more of it on necessities and will have less to spend on wants. It’s better to make your budget based on your situation. The Excel spreadsheet you created represents your current situation. If you are happy with your current situation, then make your budget in line with how you are spending currently. However, if you want to spend less and save more, adjust your budget accordingly. Just make sure that the budget you set for yourself is achievable in the short-term or the long-term. It is not useful to set a budget for yourself that you cannot stick to. This will only make you feel guilty about spending money you have to spend, which could very well derail your budget even further.

Revisit your budget regularly and make adjustments as needed

Now that you have finished your budget, you should continue to track your earnings and expenses on a monthly basis in order to keep on top of your finances and make sure you are sticking to your budget. As circumstances in your life change (new job, new house, new baby, etc.), reevaluate your budget and make adjustments as necessary.