"Budget Plan" Tip - Creating New Budget Plan for New Year

The Budget Plan template was designed to be ‘reusable’, so it can still be used if you change your Categories sheet to a new year. If you’d prefer to start clean with a new Budget Plan sheet, it’s relatively easy to do:

  1. Rename your Budget Plan sheet to something like “Budget Plan 2023”
  2. Install Budget Plan like you did the first time, either from the Community Solutions gallery in Google Sheets, or by copying the sheet from the shared template in Excel.
  3. Because your Category sheets formulas will be pointed to the old template, you need to redo them. Go to your “Categories” sheet and select cell E2, which should be just below your January heading in a default sheet (yours may differ if you made changes). Click into the formula bar, paste the following formula and press ENTER:

=IF(ISBLANK($A2),"",IFERROR(SUMIF('Budget Plan'!$E$4:$E$200,$A2,OFFSET('Budget Plan'!$L$4:$L$200,0,MATCH(DATEVALUE(E$1),'Budget Plan'!$M$3:$X$3,0))),0))

Note if you’re first month column (usually January) isn’t in column E, then change the ‘E’ in “DATEVALUE(E$1),” part of the formula to the letter of your first month column.

  1. To fill this into the rest of the cells, select cell E2 again, then grab the “fill handle” in the lower right corner of the cell, and drag to the right until you cover all the way to the right of the sheet. Let go, then grab the handle again and drag down until you get to the bottom of the sheet. That formula should now exist in all your cells, and they should all show $0.00 since you haven’t filled anything into the Budget Plan yet.

Note: If you create multiple Budget Plan sheets, you may want to ‘archive’ your old sheet(s) to reduce the processing load in your template.

5 Likes

Is there a way to use Budget Plan (which I love) without wiping out the prior year’s budget amounts?

The above instructions would help you retain that information, plus use it as a starting point on a new sheet. If you just want to keep everything the same, you can keep the same sheet, it will update to the new year when you update the date in your Categories sheet (editing the January cell on the Categories sheet to ‘1/1/2025’ will also update the Budget Plan period).

1 Like

Do you mean the amounts on the Categories sheet, which are formulas pointed to Budget Plan? If so, since prior year budgets are not being changed, I highlight and Copy the amounts, then Paste Special > Values Only.

This only applies if you’re keeping multiple years of budget amounts on the Categories sheet as described in this article, and using Budget Plan:

Multi-year Budgeting in the Tiller Foundation Template

Hi @jpfieber,
Any idea why I’m seeing these errors? I’m using multi-year budgeting. Here’s what I have done: Copied my 2024 Budget Plan sheet and renamed it to 2025 Budget Plan. Changed the Jan date to 2025 at the top. Went to the Category Sheet and pulled the formula from row 2 of Dec 24 over into row 2 of Jan 25. Changed the references to ‘2024 Budget Plan’ to ‘2025 Budget Plan’, then pulled the formula over to the Dec 2025 column, then pulled all those formulas down the sheet in the 2025 columns. The 2025 Budget Plan now looks like this:


and the Yearly Budget is not populating any information for 2025 either. 2024 and 2023 are working fine on the Yearly Budget.

Do I have too much data from previous years and it’s making the 2025 Budget Sheet crap out or did I not change something somewhere? I’m kind of at a loss here and hoping you have some wise words to share. :slight_smile:

I’d start by getting rid of the row that has errors all the way across (select the row header, right click and choose ‘Delete Row’ and see if that fixes it. If it does, you can always undo that, and try to fix whatever might be wrong with that row.

Nope that didn’t fix it. Is there a formula I need to add or edit in the date columns (M-X) to get them to populate with the calculations entered in H-J?

By default, M3:X3 get their dates from the Categories sheet, so if you updated there, it just follows the date from there. You can over-ride that by entering a date in M3 in the format m/d/yy (eg. 1/1/25).

1 Like

I would start with a fresh copy of Budget Plan. It’s easy to Copy > Paste Values from the user entered green sections to see if that clears the issue.

1 Like

Hi again, so I tried your suggestion @brettanicus, with no success. I use multi-year budgeting and have data going back to 2022. Here is what I’ve done as I try to transition to 2025.

Category sheet:
Added a new column to the right, and drug the formula from rows 1 & 2 into the new column. Changed the references of “2024 Budget Plan” to “2025 Budget Plan”
Added 11 more columns, drug the date formula to the 11 new columns.
Grabbed cell from the top row of the 1st new column (below the new January cell) and drug that to the bottom of that column, then over to the remaining columns.

2024 Budget Plan
Copied and renamed to 2025 Budget Plan
Changed the date in M3 to 1/1/2025.
Updated any start/end dates to 2025.
Copied and pasted the values over from the 2024 Budget Plan into this one and changed dates as necessary.
The 2025 Budget Plan sheet seems to be functioning properly.

Nothing has populated in the Categories sheet for my new 2025 columns. I can’t figure out why it’s not seeing the 2025 Budget Sheet. :woman_shrugging: :cold_sweat:

You’re not getting any values in your Categories sheet for 2025 columns because of the #ERROR values on your copied version of the Budget Plan. We first have to figure out why you’re getting #ERROR. Since so many things could cause that error, start with a clean copy of the template by installing the template from the Tiller Community Solutions extension (not your 2024 copy).

Personally, I would do this in steps to see at which point #ERROR occurs, if at all.

  1. Enter one or two budget items to the Budget Plan template to ensure values appear in the “Total” row (M1:Y1). If you do, then…
  2. Copy > Paste Values from the green sections of your current 2025 Budget Plan into this fresh copy, then confirm you still have totals in row 1. If you do, then…
  3. Proceed to the normal steps of adding the formulas to the Categories sheet as you described.
2 Likes

I finally got it to work for me. I guess I needed to set up the Budget Plan first, THEN the Category sheet. When I updated the Category sheet, I grabbed cell under January (2025) the pulled the formula down the column and then across to December, but that didn’t work. However, when I grabbed that same cell and drug the formula from Jan to Dec THEN pulled them down to the bottom of the sheet it worked.

Thank you so much for your feedback! I hate it when my spreadsheet is broken!

2 Likes