Introduction: Monthly Budgeting in Excel

Picture of Monthly Budgeting in Excel

Many college students have little or no experience in handling money. Students should write down the monthly cost of tuition and fees, books, room and board, utilities, food, public transportation, personal car, entertainment and extra-curricular activities. In order to keep a smart budget, learning the difference between needs and wants is a major factor. The student who understands the difference is on the right path for keeping up with their budget.

Step 1: Using Excel

Picture of Using Excel

Making a budget can help you keep track of where you money goes, and help you set goals to get more out of your money. Excel is a great way to do this.

We'll be using Excel 2010 for this tutorial.

Excel is based on “cells,” boxes that can contain numbers or the results of mathematical formulas. These are the parts of Excel you will need to know for this tutorial.

Name Box: This is the name of the active cell. In this case, the cell being edited is A1.

Formula Bar: Rather than type in the cells, cells are edited in the formula bar.

Active Cell: The cell being edited at the moment is called the active cell, and is highlighted with a thicker black outline.

Column Letters: Cells are given a letter depending on which column they're in. The active cell is in row “A.”

Row Numbers: Cells are given a number for they're column. The active cell is in row “1.”

Step 2:

Picture of

To start, the budget needs some basic parts, such as a date and an outline. By the end of this step, it should look like the picture.

Getting Started
1. First, name the budget template by entering “Monthly Budget” into cell A1. Save the file as “Monthly Budget” as well. This will allow you to always begin your monthly budgets from the template you’re about to create.

2. In cell A2, enter “Month”. In cell A3 enter “Year”. Cells B2 and B3 will be the input cells for the month and year of the budget sheet. In our example, we’ll assume the budget is for October of 2011. Type “10” into cell B2 and “2011” into cell B3. You can change these inputs as the months and years change.

3. Tip: if the text you enter into a cell doesn’t fit, you can always resize the width of the cell by clicking on the border between the two column headers (for example, the line segment between the column headers “A” and “B”) and dragging the column to a more appropriate width.

The Budget Table
1. Now you will assemble your Budget Table, a data table allowing you to input budgeted amounts for particular categories of expenses such as “Food” or “Entertainment.” In cell A5, enter “Expenses”. In cell B5, enter “Budget”. In cell C5, enter “Spent”. Finally, in cell D5, enter “Difference”.

2. In the cells of column A, underneath “Expenses,” enter the categories of expenses that you’re anticipating for your monthly budget. In our example, in A6, we enter “Rent/Utilities” , A7 is “Food”, A8 is “Transportation”, A9 is “School”, and A10 is “Entertainment”. After all expenses categories have been created, enter “Total” at the bottom of the column (in our example, this is cell A11).

Step 3:

Picture of

By entering the correct formulas into the "total" row, the budget can automatically sum your goals, spending and the difference.

1. In cell B11, enter the formula “=SUM(B6:B10)”; this will sum the dollar amounts in the cells B6 through B10 and show the total in cell B11.

2. Right click cell B11 and select “copy” in the box that appears. Right click and select “paste” on cell C11 and again on D11. Excel will  modify the cells from the formula in cell B11 so that C11 and D11 will their own columns.

Step 4:

Picture of

Cells can be formatted to show different things. This table is for keeping track of money, so we can make it show dollar signs and cents without us having to manually enter it,

Select the cell group B6 through D11 by dragging the mouse across this rectangular area of cells. Format the cells as “Currency” cells by clicking [Home]> [Format]> [Format Cells] and then selecting the “Currency” category in the “Numbers” tab of the “Format Cells” window. We’ll use “2” decimal places and select the option of having negative numbers appear in red font. 

Step 5:

Picture of

With the table made to show budgets, total spendings, and the difference, you'll need a section to keep detailed track of spendings.

Now you will put together your Expenses Table, another data table allowing you to input the particular expenses of each calendar day into columns of your expense categories. In cell A13 enter “Notes” , cell B13 is “Date”, and in C13, enter your first expense category (in our example, we’ll enter “Rent/Utilities into cell C13). Enter each expense category into the adjacent cells in row 13.

Step 6:

Picture of

Select cells B14 through B44, under the “Date” column. Format the cells as “Date” cells using the same method shown earlier, only this time select the “Date” category in the “Numbers” tab of the “Format Cells” window. In our example we’ll use “MM/DD/YY” for our date format.

Step 7:

Picture of

In cell B14, enter the formula “=DATE(B3,B2,1)”; this cell will output the date based on the inputs you entered into cells B2 and B3 earlier.

In cell B15 , enter the formula “=B14+1”; this cell will add one day to the date in cell B15. Highlight cell B15, right click, and select "copy". Now drag and highlight cells B16 through B44, right click, and select "paste". The dates should fill themselves in.

Step 8:

Picture of

In cell C6, enter the formula “=SUM(C14:C44)”; this cell (C6) will output the sum of all dollar amounts entered into the corresponding column in the Expenses Table (cells C14 through C44). Enter the same formula into cells C7 through C10, each time paying attention to the input cells of the formula. For example, C7 denotes the amount “Spent” in the “Food” category, so you will enter the formula “=SUM(D14:D44)” into cell C7.

 After that's done, select the cell group C14 through G44. Format the cells as “Currency” cells. (For a reminder on how to do this, go to step 4.)

Step 9:

Picture of

In cell D6, enter the formula “=B6+C6”. Right click cell D6 and select "copy", then select cells D7 to D10, right click, and select "copy".

Now that you have completed the steps, your ready to begin budgeting on your own. You can add in your own numbers in what you have spent and what you want your budget to be. You can even customize the “expenses” to fit your needs.

Save this as a template, you can use it month to month by changing the numbers in cell B2 and B3.

Once the budget is made, it's time to set goals. In cells B6 through B10, estimate how much you plan to spend. Set reasonable goals that will help you save.

As you spend money, keep track of when and how much you spend, open up your budget, and put it in the spending table that starts in row 13. When you put expenses in there, it will automatically compare what you've already spent (in cell C6 to C11) with your budget (B6 to B10) and show you the difference in D6 to D10.

The key to using a budget is keeping it current. As a new month comes, make a new budget and set new goals.

Step 10: Video Demonstration

Here is a quick video demonstration to help you see all the steps in action.

Also attached is the example budget.


Comments

Tampa Bay (author)2011-10-17

When defining terms in the beginning of the instructions there should have been photos to help show where these cells and things were located. These instructions were wordy, but they appealed to the reader by making it personable. More photos whould have been helpful. When discribing how to enter in the date, you stated that we had already done this. You should have told us to do it like we did when we sent the currency. The last step says that you "copy" in the final D cells, when you ment to say "Paste".

moroccan12 (author)2011-10-17

The use of visuals to help the audience understand each step was well done, I understood perfectly what what going on and what I was supposed to do in each part. I like that in step 1 you defined a dew key terms that are important to the use of the excel program. However, there were some grammatical errors: step 1 the identification of rows and columns are backwards in the visual (rows are numbered and columns are lettered) and in the last sentence the wrong they're is used; step 3 the last sentence in missing a word or two making it confusing; step 7 has a minor ambiguity in the explanation of the formula (why is the 1 included?); and step 9 you say to add column B and column C values when trying to get the difference.

writer281 (author)2011-10-17

The instruction set looked intimidating with all of the wordy sentences. I think bold headings and bulleted lists for each heading would be less intimidating and make for easier navigation. There was a grammatical mistake where you were explaining how to format cells B, C, and D to give totals., where the sentence sounded like it was missing a few words. Aside from this, it was very detailed and thorough, just be careful with the lengthy and wordy sentences. It can get very confusing.

ccafox (author)2011-10-17

The intro caught my attention because I am a college student but I believe you address it to the general public and then give an example of college students. The explainations and images were easy to follow although it is a bit long. The video is something that I found interesting I can see how a video would cathc the attention of people who are not willing to read the steps. I also like the fact that you give advise to your audience throughout the instructions.

nlanger92 (author)2011-10-13

Very good explanation of Excel. I use that program a lot in math and it can be very complicated. This explained it very simply and step by step. Great Job!

EngUA313 (author)2011-10-12

This was extremely detailed and the pictures and highlighting of certain parts really helped the user understand what to look for. The video of the whole thing really helped as well! -Maryam N.

blogging313 (author)2011-10-12

Great job on being clear and detailed with each step. Some of the steps seemed a little wordy and could be cut down a little bit but you were very thorough with your descriptions.

N.Hitchcock

cjmueller (author)2011-10-12

I thought it was a very detailed tutorial. One aspect I would consider revising is the length of each step. The instructions would be easier to follow if you created sub-sections for each number.

bmg3 (author)2011-10-11

I found the pictures to be very organized and labeled very effectively.However, the instructions themselves were somewhat lengthy. Overall, good job.

KH2323 (author)2011-10-11

Very helpful, but Step 2 seems wordy. Breaking up the two sections of Step 2 might make for easier readability.

LIenglish313 (author)2011-10-11

Explaining the steps to prepare an excel budget is no small feat,
considering all the cell selections and formulas.
Should probably have an final example budget that is filled out more though.

ksteven1 (author)2011-10-10

This looks great! It's very easy to follow (which is saying something because I'm horrible at Excel).

I like that you added that step about the "difference" column. That was the only thing I was confused about during the testing. This is very well done.

Good job!

artworker (author)2011-10-06

Cool! Add the final excel file also.