Google Sheets budget template: How to add custom categories and fix transaction tab issue

How to Add Custom Categories to the Google Sheets Monthly Budget Template

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: 

  1. Create a new sample monthly budget template
  2. Add your custom categories to the Summary tab
  3. Use Data Validation on the Transactions tab
  4. 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.”

Google Sheets, select "Monthly budget" and rename the spreadsheet "Sample Monthly Budget."

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.

Only 3 custom categories with unedited Google Sheets budget template

Only 3 custom categories with unedited Google Sheets budget template

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 lot 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.

Add 1000 rows to Google Sheets budget template

Add 1000 rows to Google Sheets budget template

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.

2. 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.

Highlight the cells that you want to duplicate

Highlight the cells that you want to duplicate

3. 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.

Drag down using the blue handle

Drag down using the blue handle

4. 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.

Rename your new custom expense categories

Rename your new custom expense categories

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.

From Summary tab, note your last row

From Summary tab, note your last row (46 in this example)

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.

From Transactions page, see if all categories are in the drop-down

From Transactions page, see if all 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.

Data validation in Google Sheets

Data validation in Google Sheets

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.

Next to Criteria, look for the box that says Summary!B27:C44 and adjust the last number

Next to Criteria, look for the box that says Summary!B27:C44 and adjust the last number

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!

All categories are in the drop-down after Data validation

All categories are in the drop-down after Data validation

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.

Copy sample monthly budget and rename it

Copy sample monthly budget and rename it

Final thought 

I hope that this post was helpful. 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!

Leave a Reply