If you’re using the free Google Sheets monthly budget template to manage your personal or family finances, you may have run into a problem while adding custom categories.
I’ve experienced this myself and found a solution that could save you a lot of time and frustration.
Google Sheets budget template: Add custom categories and fix transaction tab issue
In this article, I’ll explain how you can add as many custom categories as you want and still have them show up on the transactions tab.
Watch the video above or follow these four steps and you won’t have to deal with this problem again!
In this post:
- Create a new sample monthly budget template
- Add your custom categories to the Summary tab
- Use Data Validation on the Transactions tab
- Copy your sample monthly budget every month
1. Create a new sample monthly budget
To avoid issues with data transferring over from month-to-month, create a sample monthly budget and make a copy of that every month.
Go to Google Sheets, select “Monthly budget” and rename the spreadsheet “Sample Monthly Budget.”
If you add custom categories to a sample monthly budget instead of your actual monthly budget, you won’t have to repeat the steps every month.
Instead, you’ll just make a copy of the sample monthly budget. More on that later…
2. Add your custom categories to the Summary tab
Next, let’s add your custom categories to the Summary tab. As you can see in the picture below, the bare-bones template has only three custom categories.
You can add as many as you want, but don’t just start adding new lines. That causes problems.
When I first started using Google Sheets, some of my custom categories from the Summary tab didn’t show up on the Transactions tab in the drop-down.
So when I logged a transaction, it wasn’t automatically updating the summary page.
After lots of research (and trial and error), I was able to figure out how to add custom expense categories without any headaches. Here’s what to do:
1. Add more rows at the bottom of the template
The default Google Sheets budget template has three custom categories and room for only three more, ending on row 44.
If you need to add more than three additional categories, click “Add 1000 more rows at bottom.”
When you click the button, you’ll get the following warning: “You’re trying to edit part of this sheet that shouldn’t be changed accidentally. Edit anyway?”
Don’t worry — it’s safe to click “OK” and continue.
2. Go to the “Custom category 3” row of the expense
Next, navigate to the last pre-filled row of the Google Sheets monthly budget template on the expense side, which is “Custom category 3.”
It’s very important that you don’t enter anything in those blank rows yet.
3. Highlight the cells you want to duplicate
Now you want to highlight the cells to duplicate. To add custom categories on the expense side, you should be on row 41 for “Custom category 3.”
You want to highlight cells B, C, D, E and F on row 41.
4. Drag down using the blue handle
Once you’ve highlighted the cells that you want to duplicate, a blue handle will appear on the bottom of row 41 and column F. Simply drag it down however many rows you want.
If you want to add three custom categories, you would drag it to row 44.
5. Rename the new categories anything you want
After following the steps above, you’ll have new custom expense categories with generic names like “Custom expense category 4.”
At this point, you can safely rename them to reflect the expense categories you want in your budget.
3. Use Data validation on the Transactions tab
After you’ve added custom categories to the Summary tab, your work isn’t quite done yet. You want to switch over to the Transactions tab to make sure they all show up in the drop-down.
Before you leave the Summary tab, make a note of the last row that you added.
Here’s an example: If you added five rows, “Custom category 8” is where you will have stopped. That’s row 46 on the spreadsheet.
Now from the Transactions tab, navigate to the category drop-down and see if your categories are all there.
From my experience, if you added more than three custom expense categories, some will be missing. Using my example, only six of the eight custom categories are in the drop-down.
Fortunately, the solution is easy. Right click on the category drop-down (row 5, column E) and select Data validation at the very bottom.
Next to Criteria, look for the box that says Summary!B27:C44. You want to adjust the last number of this formula to match the number of the last row for your custom categories.
Using the example I just provided, I would change 44 to 46 since “Custom category 8” is where I stopped.
UPDATE: Also, you want to update the Cell Range or the change will only apply to E5. First, go to the bottom of the Transactions tab and add 1000 rows. Then, in the Cell Range field, change it from Transactions!E5 to Transactions!E5:E1000. That way, the categories will be reflected in the drop-down for 1,000 rows of transactions.
Once you make this change, click Save and go back to the category drop-down from the Transactions tab. All of your categories should now be there!
4. Copy your sample monthly budget every month
The problem should now be fixed, but how do you make sure it doesn’t happen to you next month? That’s why I suggested a sample monthly budget template.
You want to copy the same sample monthly budget every month, not your actual budget.
Making a copy of your sample monthly budget is easy. Just open the spreadsheet, click File and then Make a Copy from the drop-down.
You can then rename it for the current month and start budgeting.
Was this post helpful? Leave me a note in the comments below.
And if you’re already budgeting with Google Sheets, please see my article that lays out my personal budgeting strategy.
I make a few tweaks to the Google Sheets monthly budget template to save time every month!
More MichaelSaves.com Stories You May Like: