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 TO COPY EVERY MONTH.”
If you add custom categories to a sample monthly budget instead of your actual monthly budget, you won’t have to repeat the steps outlined in this article 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:
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.
Go to the “Custom category 3” Row of the Expenses
Next, navigate to the last pre-filled row of the Google Sheets monthly budget template on the expenses side, which is “Custom category 3.”
It’s very important that you don’t enter anything in the blank rows yet.
Highlight the Cells You Want to Duplicate
Now you want to highlight the cells to duplicate. To add custom categories on the expenses 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.
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 as many rows as you want.
I like to add nine rows so that row 50 is my last row of expenses.
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. 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. (That’s 50 from my example above.)
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. Fortunately, there is a solution.
Here are the steps:
- Highlight the first cell under Category on the Expenses side of the Transactions tab (Row 5, Column E).
- From the Data menu, select Data validation.
- To the right, it should say Data validation rules. Select “Value contains one from range E5:E1033.”
- In the Criteria Summary field, click on the “Select data range” icon next to the =Summary!$B$27:$C$44 formula.
- Change the last two numbers of the formula to match the number of your last row from the Summary spreadsheet. (That would be 50 from the example.) Select OK.
- Click Done at the bottom of the “Data validation rules” screen.
To see the step-by-step process, check out my video tutorial starting at the 6-minute mark.
Once you make this change, 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, see my related guide that lays out my personal budgeting strategy.
I make a few additional tweaks to the Google Sheets budget template to save time every month!