Optimal Monthly Budget Tracking with Google Sheets

Creating an Advanced Monthly Budget Tracker in Google Sheets

In this article, I will guide you through the process of creating a comprehensive monthly budget tracker in Google Sheets. While Google Sheets does offer a basic monthly budget tracker template, it lacks a crucial component that is essential for investors and individuals serious about their financial goals – a dedicated savings/investing section. In this tutorial, I will demonstrate how to make the necessary modifications to incorporate an investing/savings section into the existing tracker template.

The default Google Sheets template primarily focuses on tracking monthly income and expenses. However, to truly have a holistic view of your finances, it is crucial to have a dedicated section for monitoring your savings and investments. By adding this section, you can effectively track your progress towards your financial goals and make informed decisions about allocating funds.

In this post tutorial, I will provide step-by-step instructions on how to modify the Google Sheets template to accommodate the inclusion of an investing/savings section. By following these instructions, you will be able to create a comprehensive monthly budget tracker that caters to your specific financial needs.

Having a well-rounded budget tracker that incorporates both income and expenses, as well as savings and investments, will empower you to take control of your financial journey. By leveraging the power of Google Sheets and making the necessary modifications, you can create the ultimate monthly budget tracker that aligns with your financial goals and aspirations.

How to find the template

I will guide you step by step through the process. You might find this familiar as it resembles a template available on Google Sheets. However, I have made modifications to include a crucial element that was missing – a dedicated savings section. Here’s what you need to do:

  1. Open Google and click on the Google Apps button.
  2. Scroll down and select Google Sheets.
  3. At the top right, you’ll see templates. Look for the monthly budget template.
  4. If you can’t find it, click on “Template Gallery” where it should be one of the first options.
  5. Click on the monthly budget template, and you might think you’re done. However, you’ll notice that it looks a bit different from mine because it lacks a savings section.
  6. Having a savings section is crucial, especially if you’re interested in investing and stocks. It’s important to track the money going into accounts like TD Ameritrade, Yada Savings, or Robinhood.
  7. I believe Google missed a significant opportunity by not including a savings section, so I’ll show you how to modify this beautiful tracker. I must admit that Google did a great job with the layout.

By following these steps, you’ll be able to enhance the existing template and incorporate a savings section to meet your financial needs. It’s important to go beyond living paycheck to paycheck and prioritize investing and building wealth.

Tutorial

If you’re short on time and don’t want to go through the lengthy process of building this out, you can find all of my trackers on my Patreon. I have them available for download, and you can find the links in the description. I’m not trying to promote it, just letting you know it’s an option.

Before we begin, make sure to smash that like button and subscribe if you haven’t already. It really helps the algorithm and allows more people to find my content and get organized with their finances. Now, let’s dive right in.

To start, we’ll need to make some adjustments to the template. It might not look the prettiest, but bear with me. First, you can choose to keep the header at the top if you prefer. In my version, I got rid of it, but it’s a personal preference.

Delete the header and the text associated with it. Now, we need to add more rows. Highlight columns H to M, then right-click and select “Insert 6 to the right.” A pop-up may appear, asking for confirmation. You can choose to disable this prompt for the next 5 minutes if you want.

Now we have more real estate to work with. Next, highlight all the cells again and go to the end of column S. Drag it out a bit to create more space. This allows us more room to work with. The next step is to move the section I have on the right side. In my version, I moved it there.

To do this, highlight the section and cut it (Ctrl+X). Go to cell N7 and paste it (Ctrl+V). This will move the section, including the functions, which is crucial. Make sure the functions move with it.

Now, we have empty space to use. We’ll decide how to fill it in a bit. Next, move the starting balance. Cut it again (Ctrl+X). Go to cells P and Q, merge them, and then paste the starting balance (Ctrl+V). This places the starting balance in a better position, in my opinion.

The next step is to create the savings section. It’s simple. Copy all the cells (Ctrl+C) and paste them in cell N16 (Ctrl+V). Now, the income is moved over there, and we’re making progress in putting everything together.

Please note that this is an abridged version of the steps provided. For the full instructions, including how to fill in the sections and complete the tracker, please refer to the video or the content available on my Patreon.

Saving

Now, we’ll work on the bottom section because we want to change “Income” to “Savings.” Click on the cell and simply type “Savings” and press Enter. That’s all set. Do the same for the “Income” section below. Rename it to “Savings” as well. If you prefer to name it “Investing,” feel free to do so. It’s up to you whether you want to use “Saving” or “Investing.”

Next, let’s go back to the section where we copied and pasted everything earlier. We need to make a few changes here. Currently, it says “H25” in the formula, and as you can see, “H25” is located over here. Unfortunately, the copy and paste doesn’t work perfectly in this case, so we need to make some adjustments.

Instead of “H25,” click on the actual cell in your spreadsheet. For example, if you’re on cell Q24, click on it. Now, instead of “H,” change it to “N.” The formula should be “$N$24.” Follow along and do the same for other cells. Where it says “J,” change it to “N” as well. It will appear black, but that’s okay for now. Press Enter, and it should show zero.

Now, let’s scroll down with the spreadsheet. You may see a prompt asking not to show it again for the next five minutes. Click OK. We’ll continue making adjustments. You’ll notice that all the cells now say “N” as intended.

Next, let’s move to the “Difference” section in column D. It’s going to be very similar. Change the “H” to “N” in the formula. Press Enter. Click on the cell, and then drag it down to update the other cells below. All the cells should now say “N.” Double-check that “R,” “Q,” and “P” are correctly labeled.

Transactions

Great! We’re heading in the right direction now. We have the necessary setup here, but now we need to focus on the “Transactions” section. Click on “Transactions” and copy all the cells as we did before. Instead of using the Ctrl+C and Ctrl+V shortcuts, right-click and choose “Insert 5 to the right” to create those extra cells. You can adjust the width of the cells if needed.

Now, paste the copied cells (Ctrl+V) into cell L. You’ll notice that the formatting is no longer blue, but that’s because the cells are not merged. Select all the cells, right-click, and choose “Merge” to merge them into one cell. This should fix the formatting issue.

Now, let’s change “Income” to “Savings” since this will be our savings section. Simply type “Savings” and press Enter. That looks good. Let’s also adjust the alignment of the cells to make it more visually appealing. You can bring the description column (A) in a bit, as well as columns F and K. This will help fit everything on the screen. You can also adjust the date column if needed.

Now that we can see everything more clearly, let’s input the specific categories for expenses. You might not have all the categories listed, so feel free to modify them to suit your needs. For example, you can remove or add categories like “Food,” “Gifts,” “Health,” “Home,” and so on. If any of the categories don’t apply to you, you can remove them.

Similarly, in the paychecks section, you might have additional income sources or side hustles you want to include. Make sure to adjust the categories accordingly. Additionally, we need to change “Savings” to the appropriate name. For example, if you changed it to “Test” in the “Summary” section, you’ll see that change reflected in the “Transactions” tab dropdown menu.

Remember, the data in the “Summary” section is pulling from these tabs, so any changes made there will be reflected in the dropdown menus. Take some time to review the income and expense categories, making any necessary adjustments to suit your specific situation.

Data Validation

To make the necessary adjustments, let’s first identify where the data is coming from. It’s from cells N24 to N29. To set up the data validation, follow these steps:

  1. Highlight the range N24 to N29.
  2. Right-click and select “Data validation.” It may be a bit difficult to see since it’s on the far right of the screen.
  3. In the data validation settings, change the criteria from “Summary H and I” to “List from a range.”
  4. Click on the “Summary” tab to select the desired range.
  5. Delete the previous range and highlight the range N24 to O29.
  6. Click “OK” and save the changes.

Now, if you want to change the selection, for example, to “Test 2,” simply type “Test 2” and hit Enter. When you click on the dropdown menu in the “Transactions” tab, you’ll see that the selection has been updated to “Test 2.”

Next, you can input the categories that apply to you. The categories “Category 1,” “Category 2,” and “Category 3” are just placeholders. You can copy and paste the relevant categories from your existing data. Copy both the planned and actual values (Ctrl+C) and paste them (Ctrl+V) into the corresponding cells. Delete any irrelevant categories by selecting and deleting them.

To make the sheet more visually appealing, you can remove the unnecessary border. Now that everything looks clean, you can focus on determining your planned and actual values. Review your expenses, such as rent or mortgage, transportation, debt, insurance, food, personal expenses, and any other relevant categories. Adjust the amounts according to your preferences and financial goals.

For the transactions section, you can copy and paste the data to save time. Copy the transactions (Ctrl+C) and paste them (Ctrl+V) into the appropriate cells. Remove the “Description” and “Savings” columns if you find them irrelevant.

If you encounter an invalid input error, it might be due to a glitch or misspelled data. Check the data validation settings by clicking on the cells with the red arrows. Ensure that the entered values match those in the data validation list.

By following these steps, you should be able to adjust the sheet according to your specific needs and preferences.

Summary

Now that all the necessary data is filled in, if we go back to the “Summary” tab, you’ll notice that everything is starting to look complete. The great thing is that you don’t have to worry about adjusting the expenses or income fields since they should be correct. However, it’s always a good idea to double-check to ensure accuracy. Remember, the “Plan” section is pulling data directly from the input you provided, so it should be automatically filled in for you.

Let’s take a closer look at how the calculations are working. In the “Summary” tab, you can see that the “Plan” field is summing up all the values you entered in the respective categories. For example, it’s summing up all the planned expenses and displaying the total in cell D22. Similarly, the sparkline in cell C17 is reflecting the values entered in the “Plan” column, and the min and max values for the sparkline are derived from cells C17 and C18.

The income section follows the same principle. You don’t need to make any changes here; the calculations should be accurate. Just take a moment to review the values and ensure they align with your inputs.

Now, let’s address the issue with the savings section. Currently, the “Savings Actual” field shows zero because we haven’t connected it properly. In cell Q22, where it says “Transactions R,” delete that entry. Instead, go to the “Transactions” tab and locate the appropriate cell for the actual savings value. In this case, it should be “Transactions N.” Enter that reference in cell Q22 and press Enter. You can also delete the content in cell Q24 since it’s not required.

Additionally, you mentioned that the “Transactions” section is still black, but we want it to have some color. To resolve this, go to the “Transactions” tab and click on cell M1. Highlight all the cells in the “Transactions” section and ensure that cell M1 is the active cell. This will give the section the desired color.

By following these steps, you should have all the data properly connected and displayed in the “Summary” tab. It’s always a good idea to review the information to ensure accuracy and make any necessary adjustments.

Troubleshoot

To troubleshoot when something isn’t working, you can follow these steps. In this case, you mentioned that certain fields are not filled out correctly and that they are still labeled as “R” and “Q,” and the color is black. To resolve this, you can do the following:

  1. Change the labels: Instead of “R,” you can label it as “Transactions N.” Similarly, instead of “Q,” label it as “Transactions M.” This will ensure that the correct data is pulled.
  2. Adjust the colors: To change the colors of the fields, you can drag the cursor down from the top cell to the desired range, and then click “OK.” This will apply the specified colors to the selected cells. In this case, you mentioned wanting purple and blue colors. Once you apply the changes, you’ll see that the fields are now properly colored.

With these adjustments, you should find that everything is now in place and functioning correctly. Take a moment to review the information and ensure that it aligns with your expectations. For example, check that the “Difference” field is correctly calculated, and verify that the “Actual” values are pulling from the correct cells (in this case, cell O).

Additionally, it’s important to double-check the savings plan to ensure it’s not pulling data from the income column (column I). If it is, you’ll want to make the necessary adjustments so that it reflects the correct savings values.

By following these troubleshooting steps, you can address any issues and ensure that the spreadsheet is functioning accurately.

Final Checks

It’s always important to double-check and ensure that everything is functioning correctly in the spreadsheet. We’re almost done with the process, and the tasks ahead are relatively straightforward. We have successfully entered all the transactions and filled out the summary section. The next step is to add the chart to the spreadsheet.

However, before we proceed, there are a couple of additional things to address. Let’s start with the start and end balances. Currently, the end balance is showing as 7,000, but that’s not accurate. Based on the original information provided, the end balance should be 5,169. To correct this, we need to adjust the formula in cell D14. Replace the current formula with “=D13+(I18-C18)-O18”. This formula accounts for the starting balance (cell D13), subtracts the expenses (cell I18 minus C18), and deducts the savings (cell O18), giving us the correct end balance.

Furthermore, there seems to be a discrepancy between the end balance values in two different areas of the spreadsheet. In one place, it shows 4,878, while in another, it shows 5,169. Upon investigation, it appears that the duplication of the “Debt and Other” category is causing this inconsistency. To resolve this, delete the duplicate entry so that each category appears only once. This will ensure accurate calculations and prevent double counting.

With these adjustments, everything should be looking good so far. You can also verify the correctness of the “Difference” section by subtracting the start balance from the end balance. Additionally, the calculations for the “Expenses vs. Income” section should already include the savings, so there’s no need to add it separately.

Moving on to the chart, creating it is relatively simple. To add a graph to the spreadsheet, go to the “Insert” tab and select the chart type that suits your data visualization needs. Follow the prompts to select the data range and customize the chart appearance as desired.

By following these steps, you should have an accurate and complete spreadsheet. Remember to review the information, ensure that all calculations are correct, and make any necessary adjustments.

Chart

Let’s format the chart now. Select the chart and choose a column chart type. To avoid an error, unmerge the cells. Unfortunately, this needs to be done one by one. Once all the cells are unmerged, double-click on the chart to open the settings. For the x-axis, select the appropriate range and click OK. Scroll down to “Add series” and select the range for the actual data. Click OK.

To match the colors from the previous chart, double-click on the chart and adjust the background color to a light gray shade. Also, change the grid lines to the same color. Make the title text bold for better visibility. Resize the chart to fit the overall layout.

Next, remove the gray border around the chart by going to “Chart Style” and selecting a lighter gray shade. Uncheck “Major Grid Lines” to remove all grid lines.

To match the formatting of other elements, select the range of cells above the chart and set the fill color to the same light gray shade. Adjust the height of the row if necessary. Copy the text “Monthly Budget” from cell C3, go to cell C5, and paste it there. Repeat this process for the other labels, such as “Monthly Spendings” and “Monthly Report.”

Finally, make any additional cosmetic adjustments as desired, such as deleting or shrinking unnecessary rows or columns. Aim for a uniform and visually pleasing appearance.

With these formatting changes, the chart and the entire spreadsheet should now be properly set up. You can test the functionality by entering different values and observing how the calculations and balances adjust accordingly. This spreadsheet provides a comprehensive monthly budget tracker, including savings accounts, expenses, and overall financial status. It also emphasizes the importance of investing.

Leave a Comment

Your email address will not be published. Required fields are marked *

Shopping Basket
Scroll to Top