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

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.

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.

Highlight the cells that you want to duplicate
Highlight the cells that you want to duplicate

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.

Drag down using the blue handle
Drag down using the blue handle

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.

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.

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.

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 

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: 

20 thoughts on “How to Add Custom Categories to the Google Sheets Monthly Budget Template”

  1. You are the best! Thank you for your expert knowledge I’m really computer savey but even the missing category problem stumped me. I laughed at myself when you pointed out the solution. I should have known lol.

  2. Daniela Silverio

    do you know how to change the colors of this sheet? I’ve been trying and I can’t change anything I like to use a color-coded system.

  3. Wow! This was incredibly helpful. I had already made my budget, but it wasn’t recognizing my new categories. I found this webpage, started over with a new budget, followed all your directions and now I’m all set. Really appreciate how detailed your instructions are. You saved me a lot of time and frustration. I just wish I’d seen this the first time.

    1. Thank you. It took me some time to learn this spreadsheet, but I do love it. I’m actually overhauling my budgeting content to make it easier to follow. This comment is motivating me to get it done. Thank you again.

  4. This was very helpful but when I attempt to add in “Transactions!E5:E1000”, it doesn’t stay. It goes back to “Transactions!E5”. Any ideas?

    Thanks!

    1. Did you click Save? If you follow the instructions in my video, it should work and you don’t have to do it more than once. Did you do it once and run into an issue with the spreadsheet? Or did you do it once and then just check to see if the formula was changed? I lay this out in my video and have not run into any problems logging transactions beyond the initial amount in the template. This month I’m up to 50+ transactions already. Help me better understand the problem you’re having and I may be able to help.

      1. Thanks for your quick reply. I entered “Transactions!E5:E1000” into the formula box multiple times because it wasn’t there each time I checked. Should it be in the formula box each time I check? I haven’t run into any issues with the Sample sheet yet as I just created it. I did try the steps on a spreadsheet I created before finding your website and I’m still not able to generate a specific category. Since it’s the end of the month, I’m not going to bother with that particular sheet anymore.

        1. Ok, gotcha. I recommend that you follow my video and/or article every step and make that sample budget (changing the formulas) and then copy that every month. Let me know how it goes in August. The purpose of entering the formula above is to make sure all the categories are reflected in the drop-down for lots of transactions. A good way to check that it works is to just go down to row 500 or something like that and enter a sample transaction, including the category. If the category shows up and the transaction reflects on the Summary page, it works. I wouldn’t worry too much about checking back on the formula after it’s entered. This hasn’t been an issue for me. Please update me on whether this works for you!!

  5. How do I edit the name in “categories” and then have it reflect in the “transactions” tab in the drop down. For example I have a category named “Mortgage.” I want to change it to “Mortgage-House 1.” I can edit it in the Monthly Budget tab but then it won’t carry over to the “transactions” tab in the drop down menu. It stays “Mortgage” instead of “Mortgage-House 1.”

    Thanks!

  6. I’ve run into this issue before but I just can’t remember what I did to fix it. I just googled it and figured it out but I can’t find it again.

  7. I noticed that you make copies of the whole document for each month. I just duplicate both sheets and the change the months in the formulas. That may be my problem since I noticed that in the data validation of the top category box it’s using a sheet from Sep 2019, most likely my first month with this budget. I tried changing the criteria to the current month and year but it say to put in a valid range. I have no idea what to do. This kind of stuff is not my forte’

  8. Thanks for doing that! It seems like such a simple thing. It’s not like I was trying to solve a difficult formula or anything. They need to fix that.

  9. Thanks for these great instructions. I have multiple sources of income and therefore on the Transactions sheet on the Income side I have several rows of entries. My problem is that on the Summary sheet on the Income side it is only reflecting the first row of income from the Transaction sheet. I have tried to follow your videos/instructions for adding custom categories (https://michaelsaves.com/budgeting/add-custom-categories-google-sheets-budget/) and Irregular expenses (https://michaelsaves.com/save-money/google-sheets-budget-irregular-expenses/) and customizing budget (https://michaelsaves.com/budgeting/google-sheets-budget-template/#opentemplate) for editing Data Validation. Any further tips/instructions on how to get multiple rows of income to reflect in the summary would be greatly appreciated. Thank You!

    1. I do not use the spreadsheet for income, but I’m not sure why the steps that you use for expenses wouldn’t apply to the other side. The template has 6 rows for income: Savings, Paycheck, Bonus, Interest, Other and Custom Category. Are you using MORE than 6 income sources? If so, I would add the custom categories like I did in the video on the expenses side. Then, on the transactions tab, do the data validation while hovering over J5, which says paycheck. Did you watch the video in this article? https://michaelsaves.com/budgeting/google-sheets-budget-template/. I would make a new spreadsheet, try to follow the instructions in the video while watching it, and if it’s still a problem email me a screenshot of your summary tab and transactions tab to michaelsavesmedia@gmail.com. Good luck!

Leave a Comment

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

Scroll to Top