How to Make a Personal Budget on Excel
Keep track of your income and spending for 1 or 2 months before creating your Excel budget., Evaluate your income at the end of the first or second month to determine your average monthly earnings., Look at what you have spent money on during the 1...
Step-by-Step Guide
-
Step 1: Keep track of your income and spending for 1 or 2 months before creating your Excel budget.
Unless you are self-employed, it may be easier to use your take-home pay instead of your gross income. , You may want to set up broad categories, such as housing, food, and debt repayment, and then set up sub-categories within them, such as mortgage or rent, utilities, and repairs under the housing category. ,, Name it "Budget" or something similar. ,, Under the word "Income," list all your sources of income for each month, including your paycheck, your spouse's paycheck, any bonuses or commissions you receive, interest or dividends, and any other sources of income.
Under the list of income, type the words "Total Income."
The # sign represents the first row of income, and #2 represents the final row of income. "B" is the column you are totaling.
It should be for January. , The formula will automatically add up your income for each month.
Instead of "letter," type the corresponding letter of the column (C, D, E, etc.). , Type this next to the column for December.
Type the =sum formula into each box in that column to total your entire income for the year. ,, For example, type the word "Housing" in the row under "Expenses" in column A. , Include items such as repairs, the cost or rent of your mortgage, utilities, telephone and Internet service, furnishings, and landscaping. , The formula will calculate how much you've spent for housing during each month. , You'll be able to compare your spending with what you would like to spend by looking at the "Total" number compared to the "Estimated." Adjust the "Estimated" number as needed. , Use the =sum formula to total the amount of each category and compare it to your estimated amounts. , In the next column over, type =sum(B#:
B#2), where # is the first row number of the list of expenses and #2 is the final row number of the list of expenses.
Repeat in the remaining columns. , In column B, type =sum(B#-B#2), where # is the row number of your total income and #2 is the row number of "Total Expenses." The formula will subtract your expenses from your income and allow you to see whether you've spent more than you've earned.
Repeat with the remaining columns. -
Step 2: Evaluate your income at the end of the first or second month to determine your average monthly earnings.
-
Step 3: Look at what you have spent money on during the 1 or 2 month period
-
Step 4: and make categories for your budget based on that.
-
Step 5: Open Excel
-
Step 6: and create a new spreadsheet.
-
Step 7: Save the spreadsheet.
-
Step 8: Type the name of each month in the top row of the budget spreadsheet
-
Step 9: and start in column B.
-
Step 10: Type the word "Income" in column A of the second row.
-
Step 11: Type =sum(B#:B#2) in the box next to the box containing "Total Income."
-
Step 12: Type =sum(letter#:letter#2) for the remaining columns of months.
-
Step 13: Add a final column to total your income for the year.
-
Step 14: Type the word "Expenses" in the next row underneath the Income section in column A.
-
Step 15: Choose a category to insert underneath expenses.
-
Step 16: List any expenses for housing in the rows below.
-
Step 17: Type "Total" in the final row of the "Housing" category
-
Step 18: then type the =sum formula in each column for each month.
-
Step 19: Type "Anticipated" or "Estimated" in column A of the row below "Total
-
Step 20: " and then type in what you expect to spend on housing during each month based on your previous tracking.
-
Step 21: Continue to set up sections on the Excel spreadsheet for each category of your personal budget.
-
Step 22: Type "Total Expenses" in column A in the row beneath all your budget sections.
-
Step 23: Type "Over/Short" in the row beneath "Total Expenses" in column A.
Detailed Guide
Unless you are self-employed, it may be easier to use your take-home pay instead of your gross income. , You may want to set up broad categories, such as housing, food, and debt repayment, and then set up sub-categories within them, such as mortgage or rent, utilities, and repairs under the housing category. ,, Name it "Budget" or something similar. ,, Under the word "Income," list all your sources of income for each month, including your paycheck, your spouse's paycheck, any bonuses or commissions you receive, interest or dividends, and any other sources of income.
Under the list of income, type the words "Total Income."
The # sign represents the first row of income, and #2 represents the final row of income. "B" is the column you are totaling.
It should be for January. , The formula will automatically add up your income for each month.
Instead of "letter," type the corresponding letter of the column (C, D, E, etc.). , Type this next to the column for December.
Type the =sum formula into each box in that column to total your entire income for the year. ,, For example, type the word "Housing" in the row under "Expenses" in column A. , Include items such as repairs, the cost or rent of your mortgage, utilities, telephone and Internet service, furnishings, and landscaping. , The formula will calculate how much you've spent for housing during each month. , You'll be able to compare your spending with what you would like to spend by looking at the "Total" number compared to the "Estimated." Adjust the "Estimated" number as needed. , Use the =sum formula to total the amount of each category and compare it to your estimated amounts. , In the next column over, type =sum(B#:
B#2), where # is the first row number of the list of expenses and #2 is the final row number of the list of expenses.
Repeat in the remaining columns. , In column B, type =sum(B#-B#2), where # is the row number of your total income and #2 is the row number of "Total Expenses." The formula will subtract your expenses from your income and allow you to see whether you've spent more than you've earned.
Repeat with the remaining columns.
About the Author
Christine Flores
Dedicated to helping readers learn new skills in home improvement and beyond.
Rate This Guide
How helpful was this guide? Click to rate: