Create A Budget In Excel –

Step-by-Step Guide

This is an easy guide to creating a monthly budget worksheet in Excel. If you’re living from paycheck to paycheck, I’ll show you how to get a month ahead of the game. This means paying the current month’s bills with last month’s income.

May 15, 2021

Making a budget in Excel may seem quite complicated, especially if you are not familiar with the program. But don’t fret, it’s not rocket science.

If your computer has Microsoft Office, you already have access to Excel, so there is no need to purchase any fancy software or apps. I will show you how to add a few simple formulas that will automatically calculate your data. Easy Peasy!

For the purpose of keeping this simple, the example in this post is for those of you who get paid bi-weekly, but it can easily be adjusted for other pay schedules.

What Is A Budget?

 A budget is a financial tool that will show you how much money you make each month and how you will spend it. With this budget, you can track your spending and learn to prioritize your monetary obligations.

 For some people, “Budget” is a bad word! It means no longer having control of your money and having to make sacrifices. For these people, it means they will no longer be able to purchase what they want, when they want. However, for those of us who have embraced the practice of budgeting, we know this is totally untrue.

 Budgets don’t force you to sacrifice. A budget puts you in charge of choosing what you want to sacrifice; and, what you choose not to sacrifice. In fact, a budget will help to preserve and, in most cases, improve your current lifestyle.

How To Create A Budget In Excel From Scratch

 Although you may choose from the many templates in Excel, I prefer to use this simple one you can set up from scratch and customize to your budgeting needs.

Step 1: Getting Started

 Open Excel; go ahead and save and name your file. Here you have a blank canvas with many possibilities!

Today, we are going to create a zero-based budget spreadsheet. This method allocates all your income to expenses & debt payments, and savings. In simpler terms:

Income – Expenses – Savings = 0

This allows you to account for every penny; giving you a better understanding of identifying all sources of your monthly income, where you plan to spend it, and how much of it you plan to save.

Step 2: Formatting The Budget Worksheet

 We will set up all the categories and, then, go back and automate it with formulas.

On row 1 select columns B-F (put your cursor on cell 1B, left click and drag to 1F) and click on Merge & Center (see diagram below). Choose what size you want the font to be (mine is 20 for line 1 and 11 for all others), but don’t type anything in the field yet.

Step 3: Set Up Your Income Category 

Although many people think of their income as the paycheck they receive from working, but there are many other sources of income you need to consider for this budget. Your income is all money you receive during the month through a paycheck, investment, business venture, side hustle, retirement, social security, alimony, allowance, etc… 

To set up the Income section of your spreadsheet select cells 3B-14F (put your cursor on cell 3B, left click, drag across to 3F and down to 14F), click on the Border icon, and click All Borders. Although you may need more or less, I am using my diagram as the example.

Select 3B-3F, click Color Fill icon, and choose a color for your Category Titles. I chose a light gray.

In field 3B type Income, in 3C type Date, in 3D type Estimated, in 3E type Actual, and in 3F type Difference.

Add your Income line items (sources of income) in column B with the last one being Total Income.

Add a Color Fill to the Total Income line item and the Difference Column. Each Color Filled Line Item and Column should be a different color throughout the spreadsheet. These will be the fields you will add formulas to later.

Step 4: Set Up Your Expenses Category 

Expenses are essentially the cost of living, In other words, expenses are the amounts of money a family spends for goods and services. These are usually your reoccurring monthly necessities.

Using the same directions for the Income section, create a similar section for Expenses as shown in the diagram below.

As mentioned earlier, you want to choose a different color for Line 37 Total Expenses.

Step 5: Set Up Your SUBTOTAL Category 

The next section is where you will subtract your total expenses from your total income. Hence, a subtotal of the remainder income to divide between your Funds and Savings accounts. 

Next, select B39-F42, and insert All Borders (the lines I am using are from my example, but yours may differ depending on how many line items you have in each category).

Select the Total Income Line, right click your mouse, and Copy.

Select B40-F40, right click and Paste. 

Repeat this process with the Total Expense Line in B41-F41.

In Field B42 type SUBTOTAL, D42 type Estimated, E42 type Actual, F42 type Difference, and choose a Color Fill. 

Your spreadsheet should look similar to the diagram below, but customized for your needs.

Step 6: Set Up Your Funds and Savings Category

Because all your necessary expenses have been met, the Funds and Savings sections are where you have quite a bit of flexibility. Here you will divide the remainder of your income where you see fit. Funds and Savings accounts are totally customizable to your own personal preference. My Funds accounts are primarily money saved for expenses anticipated to occur in the near future, such as emergencies, yearly taxes, holidays/birthdays, car maintenance, and maybe even a vacation. My Savings accounts are accounts set up with the future in mind, such as retirement, college, etc…

Repeat the directions above for the Income category and create a category for Funds and a category for Savings (as shown in the previous diagram).

Step 7: Set Up Your FINAL BALANCE Category

Then, repeat the directions for B39-F42 to create Lines 58-62. Once again your lines may differ depending on how many line items you have in each category.

Now, we are finished with the layout and will proceed to inserting the formulas.

Step 8: Automate Your Budget

Although it’s a little extra work to automate your budget spreadsheet, it will make the monthly routine of budgeting so much easier. You will just need to input a few numbers and leave your calculator in the drawer!

Granted, adding formulas may seem intimidating, I will simplify the process for you.

Cell Formatting

To start, you want to make sure your Date, Estimated, Actual, and Difference cells are formatted correctly.

In the Income section, select the cells in the Date column. In the Cell section of your toolbar, click Format and a menu will pop down. Click Format Cells. A window will open where you will choose Date, 3/14/01, and OK.

Then, you select the cells for Estimated, Actual, and Difference columns. In the Cell section of your toolbar, click Format and a menu will pop down. Click Format Cells. A window will open where you choose Currency, ($1,234.10), and OK.

Formulas

To automatically calculate the Difference between your Estimated and Actual income, input the formula “=SUM(E4-D4)” in the Difference cell of each row. Note, replace the F4 and E4 with the cells corresponding to your Estimated and Actual sections. Repeat this for each row of income.

Or, rather than type the formula in each cell, to repeat this formula in each row of the Difference column, select the first cell in the Difference column, input the formula “=SUM(E4-D4)”, click enter so you see “0.00”. Select that cell again, move your cursor until you see the skinny + in the bottom right corner, left click and drag down the column, just short of the Total Income row.

Next, add the formulas to the Total Income Line. In cell D14 input “=SUM(D4:D13)” and click enter so you see “0.00”. Select that cell again, move your cursor until you see the skinny + in the bottom right corner, left click and drag across the Actual and Difference cells.

Admit, that wasn’t too difficult, right?  Now you will get to repeat these steps with the Expense, Funds, and Savings sections.

Below are examples of what formulas you would use for the SUBTOTAL and FINAL BALANCE sections.

Step 9: Save As A Template & Create Monthly Tabs

At this point, you want to save your Budget Worksheet as a template. This will allow you to copy and paste it into your monthly tabs without losing the formulas.

Click on File >> Click Save As >> Save as type (click on the arrow for the drop down menu >> Choose Excel Template (*.xltx) >> click SAVE

Now, let’s create Monthly Tabs. Go to Sheet 1 at the bottom of your budget worksheet.

Right Click on Sheet 1 >> Click Rename >> Type in MASTER >> Click Enter

To the right of your Master sheet, Click the Blank Sheet tab to add a new sheet. Using the same directions for the Master tab, rename this tab the month that you want to start with. Then, create a tab/sheet for the rest of the months in the year.

Voila! Your Budget Spreadsheet is done. Now all you have to do is input your numbers.

Related Articles:

• Pay Off Your Credit Card Debt Fast
• 12 Ways To Save Money

Now is a good time to PIN or Bookmark this post, so you can refer back to it at any time. Choose your image below.

My  Favorite Blogging Resources

These are resources I use, have used, or my colleagues highly recommend.

Bluehost – Although Bluehost is the most popular hosting company that WordPress endorses, it is my favorite because of all the wonderful features they offer their clients. They have the cheapest introductory offer that includes a FREE domain for the first year, and has top notch 24/7 customer service. I plan to use them again in the near future for my next site. Start your blog HERE today.

*If you purchase a Bluehost Plan through my link, I will be happy to assist you with the Basic Set Up for FREE. Email me a copy of your paid invoice/receipt at [email protected].

Tailwind – I have been working with Tailwind since I started blogging. They are the #1 pin schedule and so much more. Their wonderful features save you time and allow you to grow your blog faster. If you haven’t already, sign up for a FREE month HERE.

Canva – a great tool for creating custom Pinterest pins, logos, templates, and social media images. They have a FREE version that provides stock photos and graphics that you can start off with. Sign up for the FREE version HERE.

Elegant Themes – Premium Themes for websites. Divi offers a library of over 200 layouts to choose from, but I personally like to build my own. Either way, you can create a beautiful and professional looking website. Get your Elegant Theme HERE.

UptownDowntownGal – That's right, Hire Me! I am a Virtual Assistant.  I design websites and provide tech support on a freelance basis to help you take your business to the next level. Contact me at [email protected].