Create a Budget Tool with Google Sheets

Disclaimer: The information provided in this blog post is for educational and informational purposes only, and should not be considered as financial advice. The content presented here is not intended to be a substitute for professional financial advice, and readers should seek the guidance of a licensed financial advisor before making any financial decisions.

> Click here to jump straight to the main content <

I’ve been tracking my expenditures ever since I created my Google Sheets expense tracker back in 2006, and it’s been a great help in keeping my finances in check.

In fact, using the expense tracker I created, after being laid off due to company “restructuring” (read: trimming the fat … and I guess I was part of the fat! 🙄) a few years back, I was able to live off of my existing cash on hand for over a year.

Types of Budgets

There are a number of techniques or rules you can follow when creating a budget, but I’ll be using the 50/30/20 rule, as I find it quite flexible. Using the expense tracker I created in previous posts, we’ll be able to easily modify it with the 50/30/20 rule to act as a budgeting tool.

Creating the Budget Spreadsheet

Ok, so now that we’ve covered some basics, let’s get to the fun part: creating the budget spreadsheet!

Step 1: Create the Expense Tracker Sheets

As mentioned above, you can read over my Google Sheets Expense-tracker - Part 1 and Part 2 posts if you want to follow the steps to create the expense tracker spreadsheet.

On the other hand, if you prefer to just download a copy, you can click here to copy the template I made.

I’ve also made a blank template of the finished spreadsheet that we’ll build in this post, which builds off the other template. So if you prefer to just copy it instead of creating your own from the following steps, you can click here to copy that template instead.

Step 2: Add Some Categories

Based on the 50/30/20 rule, which is how I’m going to break things up, I’ll be adding 3 new categories to the Monthly Overview sheet:

• Essentials
• Non-Essentials
• Savings

Setting up the Category Choices

1. From the Monthly Overview sheet, select the entire A column by left-clicking on the A header.

   

2. Next, right-click on the selected column and select the “+ Insert 1 column to the right” option from the menu, and then repeat a 2nd time, so that we end up with 2 extra columns inserted.

   

   

3. Do some quick formatting:

   1. Left-click in cell A2 to select it, and without releasing the left mouse button, drag the mouse to the right to select cells A2 to C2, and then click the Merge button () in the toolbar at the top of the window to merge the 3 cells.

   2. Repeat the same thing for cells A3 to C3.

4. Now we’ll add the actual categories:

   1. In cell B6, enter “Category”.

   2. Left-click in cell B7 to select it, and without releasing the left mouse button, drag the mouse down to the last cell in the expenses area, which for me would be cell B17.

   3. Right-click on the selected cells, and select the Drop-down option () from the menu.

      In the Data validation rules window that pops up on the right, overwrite the two fields with Option 1 and Option 2 with the first 2 category names (“Essentials” and “Non-Essentials”), respectively, and then click Add another item and add the third category name (“Savings”).

      

      Click the Done button to save the changes.

Step 3: Categorize the expenses

Ok, so now that we have the categories set up, we need to categorize the expenses we’ve added to the sheets.

Just to recap and make sure we’re on the same page, here’s what my Monthly Overview sheet now looks like:

So, to categorize the expenses, we’ll use the Category column we added in the previous step. Simply select the cell in the Category column for the expense you want to categorize, and select the category from the drop-down list.

I’ve set “Retirement” as a Savings category, “Dining Out” and “Miscellaneous” as Non-Essential, and everything else as Essential. Set yours as you see fit.

Step 4: Set up the Current Month & Monthly Average columns

In the last post, column B in the Monthly Overview sheet was used to show the average amount spent for each of our expenses.

We’re now using that column for the categories, as what was previously in column B has now been pushed over to column D.

1. In cell C6, enter “Current Month”, and in cell D6, replace “Amount” with “Monthly Average”.

2. For any expenses that don’t generally vary from month to month, like rent/mortgage, insurance, internet, etc., enter the amount (or simply copy it over from the matching cell in column D as it should be the same amount) in the Current Month column (column C).

Note: I’ve adjusted some of my descriptions and amounts to more closely match my current expenses.

Step 5: Bring in the “Current Month” amounts

Next up, we’re going to add some formulas in the Current Month column to pull in the amounts from our expense sheets that will be the sum of expenses from only the current month.

For me, I’ll be starting with cell C14, which is the first cell in the Current Month column that has a formula in it for me, and will be for Groceries.

1. In cell C14, enter the following formula (replacing “Groceries” in the 2 places as needed if you named your sheet something else):

   =SUM(IFERROR(FILTER(Groceries!$C$5:$C, EOMONTH(Groceries!$A$5:$A,)=EOMONTH(TODAY(),)),0))
   

2. Next, I’ll add the same formula in cell C15 for Gas & Transport, replacing “Groceries” with “Gas & Transport” in the formula:

   =SUM(IFERROR(FILTER('Gas & Transport'!$C$5:$C, EOMONTH('Gas & Transport'!$A$5:$A,)=EOMONTH(TODAY(),)),0))
   

   Note in the above formula that I’ve had to add single quotes around the sheet name (‘Gas & Transport’), as it contains a space in the name. If your sheet name doesn’t contain a space, you can omit the single quotes.

3. Repeat for the rest of the cells, which for me would be cells C16 to C18 for Dining Out, Clothing, and Miscellaneous, respectively.

4. Add a formula in cell C19 (or whichever cell for you has the “Total Monthly Expenses” text) to calculate the total of the current month’s expenses:

   =SUM(C7:C18)
   

5. And finally, in cell C21 (or whichever cell for you has the “Total Monthly Remaining” text) to calculate the total remaining amount for the month:

   =D3-C19
   

After the above steps, my Monthly Overview sheet now looks like this:

Step 6: Add in the missing Monthly Average formulas

In the previous post, we brought in the monthly averages for each of our expenses, but we didn’t do that for the new expense sheets we added. So we’ll take care of that now.

In case you’ve forgotten, the formulas we need to enter here are quite simple, and are just bringing in the already calculated monthly averages from the expense sheets.

1. In cell D17 (of the first empty cell in column D for you), enter the following formula, replacing the sheet name as needed:

   =Clothing!C3
   

2. Repeat for any other expense sheets you have, replacing the sheet name as needed. For me, I only have cell D18 left to fill in for the Miscellaneous sheet, so I’ve entered:

   =Miscellaneous!C3
   

And now my sheet looks like this:

Step 7: Creating a Budget Breakdown

Ok, so now that we have the current month’s expenses and monthly averages, we can start creating the actual budget breakdown! 🥳

We’ll be sticking with the Monthly Overview sheet for this, and will be adding a new section at the top of the sheet, with a graph below it.

Adding the Budget Breakdown section

1. In cell F1, enter “Budget Breakdown”, and then merge across cells F1 to I1 to create the header.

2. In cells F2 to I2 (from left to right), enter the following text:

   • Category
   • Budgeted
   • Amount Spent
   • Remaining

3. In cells F3 to F5 (from top to bottom), enter the following text:

   • Essentials
   • Non-Essentials
   • Savings
   • Total

4. In column G, enter the following formulas:

   • cell G3:

     =D3*0.5
     

   • cell G4:

     =D3*0.3
     

   • cell G5:

     =D3*0.2
     

   • cell G6:

     =D3
     

5. In column H, enter the following formulas:

   • cell H3:

     =SUMIF(B7:B22,"=Essentials",D7:D22)
     

   • cell H4:

     =SUMIF(B7:B22,"=Non-Essentials",D7:D22)
     

   • cell H5:

     =SUMIF(B7:B22,"=Savings",D7:D22)
     

   • cell H6:

     =SUM(G3:G5)
     

6. In column I, enter the following formulas:

   • cell I3:

     =G3-H3
     

   • cell I4:

     =G4-H4
     

   • cell I5:

     =G5-H5
     

   • cell I6:

     =G6-H6
     

7. Optional: apply some styling to the new section. I’ve simply copied my styling from the All Monthly Expenses section, and applied it to the new section.

You can do the same by selecting/highlighting cells A5, and A6 to D6

and then clicking the Paint format button () in the toolbar at the top of the window, and then clicking cell F1.

I then simply added some borders and number formatting to the cells, so that my Budget Breakdown looks like this:

Adding the Budget Breakdown graph

Ok, so now that we have the budget breakdown section, we can add a graph to visualize it.

1. Select/highlight cells F2 to I6, and then click the Insert chart button () in the toolbar at the top of the window.

2. In the Chart editor window that pops up on the right, on the Setup tab, I’ve set the following criteria:

   • Chart type: Stacked column chart
   • Stacking: Standard
   • Series: “Amount Spent” and “Remaining” (I removed “Budgeted” as I didn’t want it to show on the graph)

   and at the bottom, I have the “Use row 2 as headers” checked.

3. Switching to the Customize tab, I’ve set the following criteria:

   • Chart style: “3D” is checked
   • Chart and axis titles: I changed the Title text to “Budgeted Amount Spent vs. Remaining”, and set the “Title format” to centered.

Here’s a visual of the above for clarity:

And then finally, I’ve moved the chart over and resized it a bit to fit in the space of cells F8 to I21.

Job done!

And that’s a wrap! We’re finally done and now have a functional, dynamic, budget and expense tracker … whew! 😅🎉🎉

Here’s a final look at my Monthly Overview (and budget!) sheet:

---

Conclusion

This spreadsheet might be a bit on the advanced side for some, so hopefully you were able to follow along without issue. If not, you can always copy the template I made to use as your starting point.

I hope you find this spreadsheet useful, and will be able to use it to help manage your own personal finances and budget. Like I said at the beginning, I created a less flashy version of this one many years ago, and it’s been a great help in keeping my finances in check.

As always, let me know in the comments below if you found this useful, or if you have any questions or comments!