How to Make an Easy Budget Spreadsheet
by Janet Dagostino, Savvy Spreadsheets
In this tutorial, I will show you how to make a super easy budget spreadsheet in Excel. You just need to have basic to intermediate Excel skills to make this incredibly helpful personal finance tool. Use it every month to see where all your money’s going!
This template is the foundation for our very popular, free budget spreadsheet, the Super Starter Budget. In the next few weeks, I’ll show you how to add additional features to make your budget spreadsheet work just like ours. Of course if you’d rather not build it yourself, feel free to download the Super Starter Budget using the link above.
Let’s get started!
Open up a new Excel document and save it as whatever you want. I’ll call mine “January Budget.”
Your workbook will consist of three sheets, “Setup,” “Expenses,” and “Month Detail.” Rename the tabs at the bottom accordingly.
Next, color the entire background of each sheet to a light grey (238, 236, 225). The RGB values of my colors are indicated in the parentheses. You can use the same colors or any other colors of your choice. For all three sheets, do the following: set the height of row 1 to about 9, set the width of column A to about 1.5, and format the text of B2 to bold and magenta (192, 28, 99).
The Setup Sheet
Title and Formatting
On the Setup sheet, set the width of column B to 4, column C to 14, E to 4 and column G to 14. Set row 2 wider to about 50. In B2, type the name of your budget. Make the title stand out by increasing the font to 40. So far, your spreadsheet should look something like this.
In B4, type BUDGET TIME FRAME and bold it. Select the range B4:D4, make the background white, and merge the cells. In B5, type in this month. Select B5:C5, color the background light yellow (255, 255, 165), and merge the cells. In D5, type in this year, center the text, and color the cell light yellow.
Finally, add borders to the range B4:D5.
Throughout the template, I will be adding borders to specific sections in the same fashion – thin inside and outside borders (“all borders”) in the entire range (in this case B4:D5), thick outside borders to the header range (B4:D4), thick outside borders to any bottom or totals section (none in this case), and finally thick outside borders around the whole section (B4:D5).
In cell F4, type in bold MONTHLY NET INCOME.
Select F4:G4, color the background white, and merge the cells. In F5, enter in your monthly net income amount (what you bring home each month after taxes) and change the format to currency. Merge F5:G5, color the background cells light yellow, and align the text right. Add borders to the Monthly Income section, similarly to how we added borders to the Time Frame section.
Next we create the expenses section. In cell B8, type in bold “EXPENSE CATEGORIES.” In G8, type in bold BUDGET, and center the text. Select B8:G8 and color the background green (155, 187, 89). Type 1 through 10 in B9 through B18. Color the background of B9:B18 white and the background of C9:G18 to light yellow. In B19, type TOTAL and in G19, enter the SUM formula =SUM(G9:G18). Select B19:G19, bold it, and color the background green. Change the format of G9:G19 to Currency. Finally add borders to the entire Expenses section B8:G19. Remove any inside vertical borders between C8:F19.
Now that we have the Expenses section set up, enter up to 10 expense categories and a monthly budget for each in the yellow expenses section. You can change these later as needed.
Your Setup sheet should now look something like this.
The Expenses Sheet
Title and Formatting
Now that our Setup sheet is complete, let’s move on to the Expenses sheet. Increase the height of row 2 to about 30. In B2, type EXPENSES and increase the font size to 26.
The Expenses Table
Select B4:E7, and insert a table with headers. Name this table Expenses. Change the background color of your header row to the same green you used in the Setup sheet, and change the font to black (or automatic). Change the background color of your data rows to light yellow. Rename your table headers as follows, DATE, DESCRIPTION, AMOUNT, and CATEGORY. Next, increase the column widths to around 15, 35, 13, and 20, for Date, Description, Amount, and Category, respectively. Change the formatting of the Date data rows to “Short Date,” and the Amount data rows to “Currency.” Finally, create borders in and around your table.
Add a list data validation to the category data rows. To do this, first create a named range. Go to the Setup tab and select C9:C18, and define the range name as Categories with “Workbook” as the scope.
Then, go back to the Expenses tab, select E5:E7, and add a data validation. The validation should allow a “List” with the Source =Categories. Make sure the in-cell dropdown is checked on.
Your Expenses table is set up!
The Month Detail Sheet
Title and Formatting
Now we can move on to the last sheet, the Month Detail sheet. Increase the height of row 2 to about 30. In B2, enter the following formula =Setup!B5&” “&Setup!D5 and increase the font size to 26. Change the column widths of B through I, to around 4, 24, 14, 3, 14, 14, 14, and 3 respectively.
The Savings Summary
Type SAVINGS SUMMARY in B4 and TOTAL in D4. Select B4:D4, bold the font, change the background color to magenta, and the font color to white. Center the text in D4. In B5, type NET INCOME, in B6 type ACTUAL SPENDING, and in B7, SAVINGS. In D5, enter the formula =Setup!F5. In D6, enter the formula =SUM(Expenses[AMOUNT]). In D7, enter the formula =D5-D6. Change the number format of D5:D7 to “Currency.” Bold the range from B7:D7. Select the range B5:D7 and change the background color to white. Add borders to the Savings Summary section in the usual way. Remove the inside vertical border between B4:C7 (if you see one).
The Spending Summary
Type SPENDING SUMMARY in F4 and TOTAL in H4. Select F4:H4, bold the font, and change the background color to green. Center the text in H4. In F5, type BUDGET SPENDING, in F6 type ACTUAL SPENDING, and in F7 DIFFERENCE. In H5, enter the formula =Setup!G19. In H6, enter the formula =SUM(Expenses[AMOUNT]). In H7, enter the formula =H5-H6. Change the number format of H5:H7 to Currency. Bold the range from F7:H7. Select the range F5:H7 and change the background color to white. Add borders to the Spending Summary, and remove any visible inside vertical borders between F4:G7.
Spending by Category
Type SPENDING BY CATEGORY in B9, BUDGET in F9, ACTUAL in G9, and DIFFERENCE in H9. Select B9:H9, bold the font, and change the background color to green. Center the text in F9:H9. Select B10:H19 and color the background white. In B20, type TOTAL SPENDING. Select B20:H20, bold the font, and change the background color to green.
Now it’s time to enter a bunch of formulas. In B10, enter =IF(Setup!C9=””,””,Setup!B9). This formula links to the values in the Setup sheet (and makes sure there is a blank if nothing is entered). In C10, enter =IF(Setup!C9=””,””,Setup!C9). In F10, enter =IF(Setup!C9=””,””,Setup!G9). In G10, enter =IF(Setup!C9=””,””,SUMIFS(Expenses[AMOUNT],Expenses[CATEGORY],’Month Detail’!C10)). In H10, enter =IF($C10=””,””,F10-G10). Copy and paste the formulas from B10:H10 into B11:H19. Now add the total formulas at the bottom. In F20, enter =SUM(F10:F19), in G20, enter =SUM(G10:G19), and in H20, =SUM(H10:H19).
Format the entire section F10:H20 to Currency. Now add your borders as usual and remove any visible inside borders between C9:E20. Add a light grey (191, 191, 191) shading (fill pattern style 25% grey) to the Actual column if you’d like.
Your final Month Detail sheet should look something like this:
Your basic Excel budget spreadsheet is all done! Now check out the next post for detailed instructions on using your easy budget spreadsheet.