We all know what it is like to have pay bills at some point in our life. It isn’t always the easiest thing to do and figure out. However, it can become more difficult if you add in diapers and wipes or even clothing. I know how hard that can be because I’ve been there and I am there. I’m a full-time student and a mom who must run her kid to school every day and I really don’t have much time to try and budget every single expense we have as a family. Not even once a week. So, if I can put it in an Excel worksheet and just update it if need be I will and currently do that now. I would like to help someone out there with making a worksheet in excel to help them keep track of their income and expenses.I will be showing you how to make a quick and simple budget in excel. I will go through the steps to get a nice-looking budget and it will also be easy to edit if need be.
Step 1: Opening Excel in Windows 10
You will go to the Home tab on your computer screen.
In you menu under the home tap you should be able to search or see Excel available to you.
Once you open Excel it will ask you want kind of temple you want. For this project we will just use a "Blank Worksheet".
It will open the worksheet and you well see a blank sheet with cells. Excel is made up of columns and rows. If you have a column and row it is called a cell. Columns are labeled with letters like A, B, C ect. Rows are labeled with numbers like 1, 2, 3 ect. So, if you are labeling a cell it will look like this.
A1, B1, C1.
Step 2: Saving Your Excel Worksheet
You always want to start off by saving your worksheet so if you quit working on it what work you did have will have been saved.
You can save your Excel sheet by going to the top left corner on the tabs and click the "File" tap.
You then go to "Save As", then click on "Browse".
Another page will come up and you'll re-name the file. I re-named mine "Budget". Always name your files a name you'll remember.
You find the fine you want it under just by clicking on it. Then you'll just click "Save" and it will take you right back to the worksheet. If you did it correctly you'll see at the top of the worksheet the file name you saved it under.
Step 3: Starting the Budget
You want a title for the budget.
I went to cell "A1" and typed "Monthly Budget"
I then selected columns A:J row 1 and merged and centered them together.
You'll hold down your left mouse key to select more than one cell.
You will find the merge and centered button under the "Home" tab under the "Alignment" section.
You'll click on it and it will make those columns and rows into one and center the text.
Step 4: Date; Month; Year
You want to add the date to your paper and also label the month you are doing and also the year.
We will do a date that will automatically update every time you open the Excel sheet.
We will start off by labeling
- "A2" type "Date"
- "A3" type "Month"
- "A4" type "Year"
- "B2" type the formula "=TODAY()".
- "B3" type the month "September" this is the month I'm currently in.
- "B4" type the year "2017" again this is the year I'm currently in.
You will notice that September doesn't fit will in that little space it goes into the C column. We can changed that by going to the “Home” tap then “Cells” section and click on “Format”. You will then click on “Autofit Column Width”.
Step 5: Income
- "A6" type "Income".
- "B6" type "Andrew" or whoever is in the household that brings in income.
- "C6" type "Janeal" or whoever is in the household that brings in income.
- "D6" type "Other Income". My husband has other income that he brings in.
I used my family for this example.
Below them I will insert the amounts.
- "B7" type "2500" and that is just want my husband brings home on the low in. I always low ball his paycheck.
- "C7" type "N/A" because I don't work yet but I wanted to make sure my name was on the budget sheet because I live in the household.
- "D7" type "1845"
You will notice in the picture I also used the autofit feature for column C.
Step 6: Expenses
- "A8" type "Expenses"
- "B8" type "Mortgage"
- "C8" type "Car Payment"
- "D8" type "Utilities"
- "E8" type "Groceries"
- "F8" type "Entertainment"
- "G8" type "Savings"
- "H8" type "Baby Stuff"
- "I8" type "Insurance"
You will see I have changed the column width of most of them. Once you change them they will remain that size unless you change them.
Step 7: Adding More Months
I will finish out the years. I will add the years so they are under the expenses in column A.
- "A9" type "September"
- "A10" type "October"
- "A11" type "November"
- "A12" type "December"
I just finished out the year I'm currently in.
Step 8: Amounts for Expenses
- "B9" type "550"
- "C9" type "250"
- "D9" type "200"
- "E9" type "350"
- "F9" type "200"
- "G9" type "400"
- "H9" type "60"
- "I9" type "135"
I then used the feature of "Flash Fill" on the ranges B9:B12, C9:C12, D9:D12, E9:E12, F9:F12, G9:G12, H9:H12, and I9:I12
What flash fill will do is copy and pasted to the other cells that you drag your mouse too. It is a very next feature to use rather than doing all the steps for copying and pasting.
Step 9: Totals
When we do a budget, we are looking for totals of differences between what we have coming in for income and what we have going out for expenses.
- "J6" type "Total"
- "J8" type "Total"
Now that we have labeled them we will add the formulas under the Total section. Formulas will automatically change if your income numbers change. The formula we will use is going to be a range. A range is a set of cells that you have selected.
- "J7" type the formula of "=SUM(B7:D7)"
- "J9" type the formula of "=SUM(B9:I9)"
Then you can use the flash fill to fill in the rest of the months so take the formula from "J9" to drag it down and flash fill that same formula you used in "J10", "J11", and "J12".
Step 10: The Difference
In step 9 we talked about finding the difference between income and expenses. So, in step 10 we will find the difference between the two.
- "A14" type "Difference between Income and Expenses 1x Month".
- "J14" type "=J7-J9"
The 1x means just one month not all the months. In this example I will only be doing September but this will have to be updated every month if the numbers in one of the expenses changes. I would use the merge and center feature on the difference box in cell “A14”. I’d merge the columns A14:D14 together. I also left aligned it as well. You will find the alignment buttons under the “Home” tab under the “Alignment” section.
Step 11: Money Signs
I will convert all the numbers to a currency so they show a dollar sign. I’ll select all the cells who have number in them representing money by holding down the “Ctrl” button and left clicking on the cells with my mouse. They should be highlighted once you select them. Once you have them selected you’ll go to the “Home” tap and under section “Numbers” you’ll see a little arrow right of it and you’ll click on that. You’ll then select currency under the category area and now you’ll have a dollar sign and two numbers after the decimal point. However, I don’t like any numbers after my decimal point so I go in and delete that since I’m using an estimation for my number. So, you can delete that by make sure the decimal places box says “0”.