Introduction: Personal Budget Spreadsheet
Many college students have limited income and a wide variety of expenses. Keeping track of these expenses and making sure that overspending doesn’t occur is a crucial skill to have. This Instructable describes how to make a simple monthly budgeting spreadsheet using Microsoft Excel 2013.
This Instructable assumes that you have basic working knowledge of how to use Microsoft Excel. If you have never worked with Excel before, these instructions may be confusing.
Step 1: Setting Up the Structure of the Spreadsheet
- About halfway down the first page, create categories for income, expenses and savings. You will enter relevant data below these category headers later on. See Figure 1 above for reference.
- The space left above these sections will contain graphs and charts that will serve as visual aids for the spreadsheet.
Finally, give your spreadsheet a title. This is a monthly spreadsheet, so have the month and year as part of the title. Also rename the sheet you are working on as follows:
- Right-click on the sheet tab at the bottom left of the screen.
- Select Rename from the pop-up menu.
- Type in the month and year.
Step 2: Adding Income
- List whatever your sources of income are under the income heading. Some examples of incomes could be scholarships, money from family, or wages earned at work. Sum the values by highlighting the incomes and using the SUM function in the appropriate cell.
Step 3: Adding Expenses
- Similarly, list expenses and savings under their headings. Common expenses for college students include tuition, textbooks, rent, utilities bills, groceries, and entertainment. It is your prerogative how you want to categorize things.
List each expense (e.g., Chipotle - $7.50), and assign an appropriate category to each expense (e.g., Food). Assigning a category will make the expenses easier to sort later on. This might be helpful in tracking expenses.
Similar to incomes, sum the values in the appropriate range by using the SUM function.
Step 4: Filters
- Filters are useful for keeping track of expenses as well. Create a filter for the “Item” column, and “Category” column. By doing this you can hide and show expenses in each category or each item within that category.
Step 5: Savings
- If you have a goal for the amount of money you want to save per month, list that under the Target Savings cell. The amount of expenses subtracted from your total amount of income should go below the Actual Savings cell.
- Conditional formatting is useful here. For example, if the actual savings value is less than target savings, use a filter to turn the value red. Or, if it exceeds target savings, use a filter to make it green. It is up to you how to denote this.
Step 6: Graphing Your Budget
Graphs are an important and useful tool for visualizing a monthly budget. This section will show you how to create a pie chart for expenses. A pie chart is useful for looking at expenses because it shows the proportion of spending in each expense category.
- To start, the data under the expenses category must already be entered. We filled in some sample items to serve as a demo.
- Under the Insert tab at the top of the page, go to the Charts section and click on the pie chart icon (circled in the image above). You can select from a variety of different pie chart types - for this Instructable, we selected a doughnut graph.
- You should see a blank white box appear on the screen - this is where the chart will appear. Right-click on the chart and click Select Data. Alternatively, go to the Chart Tools tab at the top of the page, and click Select Data.
- Select all the items in the Category and Amount columns, as shown in the second image above. A “Select Data Range” dialog box will appear and list what data you have selected.
Step 7: Graphing Your Budget Cont.
- On the right side of the dialog box, you should see the category of each item. There may be repeats. If there are repeats, deselect every repeated category. This will reduce clutter and make the doughnut graph easier to read. Click OK when done to see the final chart.
- A representative title can be added to the doughnut graph (e.g. Expenses) by clicking “Chart Title” on the graph. Other adjustments can be made such as the layout of the graph. This can be done by clicking the Quick Layout button under the Design tab for the graph.
There are many other types of charts in Excel that can be used to visualize your data. Experiment with using bar graphs, line graphs and others to determine what works best for you.
Step 8: Using the Spreadsheet
- Throughout the month your income, expenses, and your subsequent savings will change. It is important to update this spreadsheet with your income and expenses as you spend and earn throughout the month.
- Update the spreadsheet frequently (everyday is a good practice). By consistently keeping your income and expenses up-to-date you will be able to have a more specific understanding of your budget.
- When updating your expenses, be specific. Include the item purchased and the date.
Use the graphs to your advantage. Understand what you are spending most of your money on and when you are spending the most money during the month.
It would be wise to continue to track your budget after just a single month. In order to do that, the sheet must be duplicated.
- To duplicate the sheet, select the sheet that you desire to duplicate. Then click Format in the cells section of the Home tab and select Move or copy sheet, shown above.
- In the dialogue box, check the “Create a copy” checkbox and select “(move to end)” to place the duplicated sheet at the end of the workbook. Don’t forget to rename the sheet to the appropriate month and to clear the previous month’s expenses, income and savings from the previous month.