How to Build a Budget in Excel
A basic budget tracks your income and expenses over a set period of time. These may be business or personal expenses, and the budget may track daily, weekly, monthly, quarterly or annual expenditures. Excel offers tools to make budget building easier, and you can even combine time periods to build monthly budgets from your weekly budgets or annual budgets from the monthly variety. Practice by building a monthly household and annual budget, and then modify the format to fit your needs.
Other People Are Reading
Instructions
-
-
1
Launch Excel and open a new workbook.
-
2
List all of your income sources each month in column A, starting in row 2. This may include employment income, freelance income, royalties, interest and gifts. In row 1, type "Income."
-
-
3
Type "Total Monthly In" under your last income item.
-
4
Type "=SUM(" (without the quotes) in the column B cell next to the "Total Monthly In" notation. Do not press "Enter" at this point.
-
5
Highlight all cells to the right of your income sources in column B with your mouse, then release the mouse button. The cell addresses (letter and number codes identifying the cells' row and column placement) should appear next to your SUM function. Press "Enter," which should automatically finish the formula by adding the closing parenthesis. If not, type it in manually.
-
6
Click out of that cell and then back inside it. The formula you just added should appear in the formula bar at the top of the page, looking something like "=SUM(A2:A5)" which tells you that the cell will add the cells specified to produce a total. Any number you type into the specified cells will be added together in your total cell.
-
7
Skip column C and type "Expenses" in column D, row 1. List all of your expenses, including mortgage, utilities, credit card payments and insurance, below "Expenses."
-
8
Type "Total Monthly Out" under your last expense entry, then use the same method you used for income to add the SUM formula to your expenses in column E.
-
9
Skip column F and type "Difference" in cell G1 (column G, row 1).
-
10
Type "=SUM" in cell G2, and then click in the cell where you typed your total monthly income formula. Type a minus sign after this cell address when it appears in G2, and then click in your total monthly expenses sum cell. Type a closing parenthesis after the formula and press "Enter." Cell G2 will now automatically subtract your total expenses from your total income, showing you the difference between them.
-
11
Click on row B, and then press and hold the "control" key and click rows E and G to select all three rows. Click the dollar sign in the "Number" section on the "Home" tab to tell Excel that all numbers in these cells should read as dollar amounts.
-
12
Double click the tab at the bottom of your worksheet and rename the sheet "January." Right click the "January" tab and select "Move or copy," and click the "Create a copy" box. Rename your new sheet "February," and then make additional copies for each month.
-
13
Select the next blank worksheet in your document. Excel defaults to creating three blank sheets for each new workbook, so this will likely be "Sheet 2." Rename it "Annual Totals."
-
14
Type "Month" in cell A1 and "Profit or Loss" in cell B1 and C1. List the names of the months in cells A2 through A13.
-
15
Click inside cell B2 and type an "equal" sign, then click the "January" sheet tab and cell G2. Press "Enter." This brings the January total difference (profit or loss) to the new sheet. Repeat this for each month.
-
16
Add an annual total difference in cell B13 by using the same SUM formula you used on the other sheets. Convert column B to dollar format as you did on your other worksheets.
-
17
Type the dollar amounts corresponding with each income or expense item each month. Excel will automatically add and subtract for you, showing your profits and losses.
-
18
Add any formatting you like to your Excel budget using the style, alignment and font formatting options on the "Home" tab.
-
1
Tips & Warnings
Microsoft offers premade budget templates online that you can download and alter according to your needs (see "Resources").
