How to Create a Budget Using Microsoft Excel

by BrendanK28 in Living > Organizing

651 Views, 4 Favorites, 0 Comments

How to Create a Budget Using Microsoft Excel

final.PNG

Microsoft Office comes with a lot of useful applications including Microsoft Word, PowerPoint, Excel, and Access. Most people use Microsoft Word and PowerPoint due to them being user friendly. It’s useful to know how to use Microsoft Excel even though it’s not the most user-friendly program. Excel is a unique application that allows user to create mathematical formulas and convert those formulas into charts once the numbers are entered. Using Microsoft Excel is no easy task, but the following instructions will guide you step by step how to create a budget using Microsoft Excel.

** Before we have you jump into Microsoft Excel, let us introduce to you some terms that are most common with Microsoft Excel. **

Supplies

A Computer

Microsoft Excel

A list of monthly expenses and income

cell.png


This is a cell. This is where you will type your data. Keep in mind that you can make the cell bigger or smaller so it can fit all your data.

name box.png

This is called the “Name Box”. This will show you what cell you are working in. Look at the screenshot below for reference.

formula bar.png

This is the formula bar. This bar will show you what text or formula is entered in the specific cell that was selected

This will be useful to remember throughout the instructions. IF the text that is entered in a cell is spilling over into the other cell, double-click the middle of both cell columns. This will re-size the column to fit all the text.

Let’s get started!

Excel Icon.png

Click on the Microsoft Excel icon on your computer and then open a blank spreadsheet. Keep in mind that the icon might be slightly different if you are using a Mac computer.

highlight A1-D1.png

First thing first is to highlight cell A1 all the way to cell D1. Once Highlighted make the cell Blue by selecting the color option at the top bar.

2019 income.png

Then in cell A2, type “2019 Income

$0.00.png

In cell A3, type “$0.00”. Don’t worry, we’ll go back and enter your actual data when we go further into the instructions.

green fill.png

Highlight cell A2 and A3 and set them to the color Green, or any variant of green that you prefer.

months.png

Please enter the names of the past 3 months starting from cell B2 to cell D2. You’ll be able to add more months to the budget if needed later.

incomes.png

We know everyone has a different number of sources for income, but for these instructions let’s just put “Income #1” in cell A4, “Income #2” in cell A5, and “Income #3” in cell A6.

income values.png

Next step is to enter “$0.00” in the cells B4 to B6. Do the same in C4 to C6 and D4 to D6. Your screen should look like the screenshot above.

highlight expenses.png

Now it’s time to create the expenses for the budget. In cell A9, type “2019 Expenses”. Right below it, in cell A10, type “$0.00”. Once the number has been entered, highlight cell A9 and A10 and add an Orange color to it.

expenses.png

Type “Expense #1” in cell A11 and continue your way down all the way to A15. Make sure to increase the “Expense#” by one. See screenshot above for reference.

ex months.png

Enter the same months that you entered in cells B2 to D2 in the cells B9 to D9

expenses values.png

Enter “$0.00” in cells B11 to B15. Do the same for cells C11 to C15 and D11 to D15.

format cells.png
howtoforamt.png

Now it’s time to make sure that we set the Number Format to Currency. This will make sure that the cells remain in currency if there is a change. Highlight cells B3 all the way to cell D6. Right-click and select “Format Cells”. A new window will appear after that. In the new window, select “Currency”. Make sure the decimal place is set to 2, and then click Okay. Make sure we also set the Currency format in cell A3.

format expenses.png

Follow the same steps that you did in Step #16 but this time do it for cells B10 all the way to D15. Don’t forget to apply the Currency Format in cell A10.

total income sum.png

Now it’s time to create the formulas. Click on cell A3 and type in “=SUM(B3+C3+D3)”. This will add the 3 cells and put the total in Cell A3.

first month sum.png

Now, lets add the income for each month. Click on cell B3 and type “=SUM(B4+B5+B6)”. This will add the numbers in those cells and place the total in B3.

second month sum.png

Now that the first month is complete, let’s move on to the next. Click on cell C3 and type “=SUM(C4+C5+C6)”. This will add the numbers in those cells and place the total in cell C3.

third month sum.png

Now on to the next month, click on cell D3 and type “=SUM(D4+D5+D6)”. This will also add the numbers in those cells and add the total in cell D3.

total expense sum.png

Let’s move on to the expense section. Select cell A10 and type in “=SUM(B10+C10+D10)”. This will add the total of the 3 months to cell A10.

1st month sum.png

Now it’s time to add the expenses for each month. Select cell B10 and type “=SUM(B11+B12+B13+B14+B15)”.

2nd months sum.png

Let’s move on to the next month. Select cell C10 and type “=SUM(C11+C12+C13+C14+C15)”.

3rd month sum.png

Let’s move on to the last month. Select cell D10 and type “=SUM(D11+D12+D13+D14+D15)”.

B3-B10.png
C3-C10.png
D3-D10.png

Click on B1 and enter the formula “=SUM(B3 – B10)”. This shows the actual income for that month after all expenses have been paid. Now click on C1 and enter the formula “=SUM(C3 – C10)”. Lastly, click on D1 and enter the formula “=SUM(D3 – D10)”.

A3-A10.png

Click on A1 and type the formula “=SUM(A3-A10)”. This will provide the income of all the months combined.

real income values.png

Now it’s time to enter your actual data. Replace the “Income #” text with the name of your job(s).

Budget is Complete!

real expense values.png

Enter the actual amount of money that you make in the income section based on the month and income. Do the same with the Expense section. Once you have all your information entered, the formulas will do all of the math and properly display how much money you made, spent and have left over in those months. Feel free to customize your budget if needed. Don’t forget to modify the formulas if changes are made.