Extending the Savings Budget: Categorized Date, Sort Order, Account Hiding, and Quick Navigation

jarrettsorensen · September 8, 2026, 2:20pm

Overview

The stock Savings Budget sheet (as documented in Docs: Savings Budget sheet) computes each category’s “Actual” for a period by matching transactions against the Transactions sheet’s Date column. I’ve personally been using Tiller’s Categorized Date column to recategorize a transaction into a different budget month than the one it actually posted in, but the Savings Budget sheet did not support this. It also has no built-in way to exclude transactions from Hide-flagged accounts from your actuals, no ability to sort the budget by a custom Group/Category order, and no quick way to flip between periods without scrolling a dropdown.

This post documents four independent additions I made on top of the stock template:

Make “Actuals” respect Categorized Date when it’s set, falling back to Date otherwise.
Filter out transactions belonging to accounts marked Hide in the Accounts sheet.
Sort the budget table by custom Category Sort Order / Group Sort Order columns instead of alphabetically.
Add one-click year/month navigation buttons instead of using the dropdowns (they’re not super fast, but are convenient).

Each one is independently useful — you don’t need all four to benefit from any single one.

Why this was needed

Categorized Date: without it, a transaction posted in the beginning of one month but recategorized into the previous month’s budget would count against the wrong month’s Actual — a common source of “why doesn’t this number match what I categorized” confusion.
Account Hide: accounts you’ve marked Hide in the Accounts sheet (e.g. a kid’s small savings account you don’t want cluttering reports) still have their transactions (interest, transfers, etc.) counted in your category Actuals by default. There’s no built-in exclusion.
Sort Order: the stock sheet only sorts Groups/Categories alphabetically. If you want a specific visual ordering (e.g. Income first, then Housing, then Debt, etc.) there’s no supported way to do it.
Quick navigation: switching periods requires opening two dropdowns (year, then month) every time. A single click per direction is faster for regular reviews.

How It Works

1. Categorized Date support

All of the actuals aggregation formulas (Q10, T10, L14, L24, and the uncategorized-count helper at BE26) were changed to pick, per transaction row, whichever date should count toward a budget period:

If Categorized Date is set and is a clean first-of-month value (day = 1, no time-of-day fraction), use Categorized Date.
Otherwise, fall back to the raw Date.

This effective-date logic is expressed once as an ARRAYFORMULA(IF(...)) and reused across all the actuals formulas.

Important gotcha if you try this yourself: Google Sheets’ QUERY function’s date '...' literal comparison in a WHERE clause only works against a genuine cell-range reference — it does not work against a computed/derived array, even one containing perfectly valid date values. If you wrap your date column in IF(...) like this and then try WHERE Col1 >= date '2024-01-01', every row will silently fail to match, and your QUERY will return zero rows with no error. The fix is to never expose a “date” to QUERY’s WHERE clause at all — instead, precompute a plain 1/0 “is this row in the target period” flag using ordinary numeric comparisons, and filter with WHERE Col1 = 1. All the formulas below use this pattern.

2. Account Hide filtering

Every actuals formula above also joins each transaction against the Accounts sheet by account name, and excludes the row if that account’s Hide column is set to Hide, using a VLOOKUP/REGEXEXTRACT combination. The REGEXEXTRACT is needed because the Transactions sheet’s Account column stores the short account name (e.g. "Checking"), while the Accounts sheet’s Account column stores the full Tiller-style name with suffix (e.g. "Checking - 1234 (ABCD)"). The regex strips everything from the last - ... (...) segment onward so the two can be matched by exact short name.

3. Custom Group/Category sort order

The master row-ordering formula in AZ10 was changed from a plain alphabetical ORDER BY to sort by two new numeric helper columns pulled from the Categories sheet — Group Sort Order and Category Sort Order — falling back to 999999 (sorts last) for any row missing a value.

Gotcha we hit doing this: the first attempt used IFERROR(VLOOKUP(...), 0) as the fallback instead of a 999999 fallback wrapped in VALUE(...). Mixing text and number types in a QUERY sort column causes ORDER BY to silently no-op — the rows come back in QUERY’s default order regardless of what you asked for. Always coerce to a single consistent type (here, VALUE(...)) before sorting.

4. One-click period navigation

A small Apps Script bound to the spreadsheet drives four button images placed over cells H1 (prev year), J1 (next year), H2 (prev month), J2 (next month):

var MONTH_NAMES_ = ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'];

function setBudgetPeriod_(year, monthIndex) {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Savings Budget');
  sheet.getRange('I1').setValue(year);
  SpreadsheetApp.flush();
  sheet.getRange('I2').setValue(MONTH_NAMES_[monthIndex]);
}

function stepMonth_(direction) {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Savings Budget');
  var year = sheet.getRange('I1').getValue();
  var monthIndex = MONTH_NAMES_.indexOf(sheet.getRange('I2').getValue());
  if (monthIndex === -1) monthIndex = 0;
  monthIndex += direction;
  if (monthIndex < 0) { monthIndex = 11; year -= 1; }
  if (monthIndex > 11) { monthIndex = 0; year += 1; }
  setBudgetPeriod_(year, monthIndex);
}

function stepYear_(direction) {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Savings Budget');
  var year = sheet.getRange('I1').getValue();
  var monthIndex = MONTH_NAMES_.indexOf(sheet.getRange('I2').getValue());
  if (monthIndex === -1) monthIndex = 0;
  setBudgetPeriod_(year + direction, monthIndex);
}

function prevYear() { stepYear_(-1); }
function nextYear() { stepYear_(1); }
function prevMonth() { stepMonth_(-1); }
function nextMonth() { stepMonth_(1); }

It intentionally uses plain calendar arithmetic (not “only jump to periods that have budget data”) so navigation always works even into months you haven’t budgeted yet. Insert any small image/drawing over the four cells and assign the matching function from the “Assign script” menu.

How to Use It

Add Categorized Date support

The Savings Budget sheet already has a hidden helper area (columns BD/BE, roughly rows 28+ on the default template) that resolves column letters by header name, so formulas don’t break if you reorder your Transactions/Categories columns. You’ll add one new row to that block, then replace five formulas.

Step 1 — Add a “Categorized Date” helper row.
Find the row in your BD/BE block where Date, Category, Amount, and Account are already resolved for the Transactions sheet (each has a label in column BD and a formula in column BE that looks like =SUBSTITUTE(ADDRESS(1,MATCH($BD<row>,INDIRECT("'"&$BD$<anchor-row>&"'!$1:$1"),0),4),1,"")). Insert a new row directly below the Account row:

Column BD: type Categorized Date
Column BE: copy the same formula pattern used by the row above it, but change the $BD reference to point at this row (the one containing Categorized Date). For example, if your Account row’s formula is =SUBSTITUTE(ADDRESS(1,MATCH($BD36,INDIRECT("'"&$BD$32&"'!$1:$1"),0),4),1,"") and your new Categorized Date row is row 37, your formula becomes =SUBSTITUTE(ADDRESS(1,MATCH($BD37,INDIRECT("'"&$BD$32&"'!$1:$1"),0),4),1,""). The $BD$32 part doesn’t change — that’s the anchor cell containing the literal text Transactions, and it’s shared by every row in that block.
This cell should resolve to the column letter of your Transactions sheet’s Categorized Date column (e.g. S).

Step 2 — Replace T10 (“Actuals in Period”) with:

=QUERY({ARRAYFORMULA(IF((IFERROR(DAY(INDIRECT("Transactions!"&$BE$37&"2:"&$BE$37)),0)=1)*(IFERROR(MOD(INDIRECT("Transactions!"&$BE$37&"2:"&$BE$37),1),1)=0),(INDIRECT("Transactions!"&$BE$37&"2:"&$BE$37)>=$BE$2)*(INDIRECT("Transactions!"&$BE$37&"2:"&$BE$37)<EOMONTH($BE$2,0)+1),(INDIRECT("Transactions!"&BE33&"2:"&BE33)>=$BE$2)*(INDIRECT("Transactions!"&BE33&"2:"&BE33)<EOMONTH($BE$2,0)+1))),INDIRECT("Transactions!"&BE34&"2:"&BE34),INDIRECT("Transactions!"&BE35&"2:"&BE35)},"SELECT Col2, SUM(Col3) WHERE Col2 <> '' AND Col1 = 1 GROUP BY Col2 ORDER BY Col2 LABEL Col2 'Category', SUM(Col3) 'Actuals in Period'")

Replace every $BE$37 with wherever your new Categorized Date helper cell landed (Step 1), and every BE33/BE34/BE35 with your sheet’s existing Date/Category/Amount helper cells respectively. $BE$2 is the currently-selected period’s start date (already on the stock sheet).

Step 3 — Replace Q10 (“Actuals To Period”) with:

=if(BE22,iferror(QUERY({ARRAYFORMULA(IF((IFERROR(DAY(INDIRECT("Transactions!"&$BE$37&"2:"&$BE$37)),0)=1)*(IFERROR(MOD(INDIRECT("Transactions!"&$BE$37&"2:"&$BE$37),1),1)=0),(INDIRECT("Transactions!"&$BE$37&"2:"&$BE$37)>=$BE$3)*(INDIRECT("Transactions!"&$BE$37&"2:"&$BE$37)<$BE$2),(INDIRECT("Transactions!"&BE33&"2:"&BE33)>=$BE$3)*(INDIRECT("Transactions!"&BE33&"2:"&BE33)<$BE$2))),INDIRECT("Transactions!"&BE34&"2:"&BE34),INDIRECT("Transactions!"&BE35&"2:"&BE35)},"SELECT Col2, SUM(Col3) WHERE Col2 <> '' AND Col1 = 1 GROUP BY Col2 ORDER BY Col2 LABEL Col2 'Category', SUM(Col3) 'Actuals To Period'",0),{"Category","Actuals To Period"}),{"Category","Actuals To Period"})

Same substitutions as Step 2. $BE$3 is the stock sheet’s existing “start of tracked history” cell (used for lifetime-to-date totals).

Step 4 — Replace L14 (a running SUMIFS total lower on the sheet) with:

=sumifs(INDIRECT("Transactions!"&BE35&"2:"&BE35),ARRAYFORMULA(IF((IFERROR(DAY(INDIRECT("Transactions!"&$BE$37&"2:"&$BE$37)),0)=1)*(IFERROR(MOD(INDIRECT("Transactions!"&$BE$37&"2:"&$BE$37),1),1)=0),INDIRECT("Transactions!"&$BE$37&"2:"&$BE$37),INDIRECT("Transactions!"&BE33&"2:"&BE33))),">="&$BE$3,ARRAYFORMULA(IF((IFERROR(DAY(INDIRECT("Transactions!"&$BE$37&"2:"&$BE$37)),0)=1)*(IFERROR(MOD(INDIRECT("Transactions!"&$BE$37&"2:"&$BE$37),1),1)=0),INDIRECT("Transactions!"&$BE$37&"2:"&$BE$37),INDIRECT("Transactions!"&BE33&"2:"&BE33))),"<"&(EOMONTH($BE$2,0)+1))

Step 5 — Replace L24 (same pattern, current-period-only) with:

=sumifs(INDIRECT("Transactions!"&BE35&"2:"&BE35),ARRAYFORMULA(IF((IFERROR(DAY(INDIRECT("Transactions!"&$BE$37&"2:"&$BE$37)),0)=1)*(IFERROR(MOD(INDIRECT("Transactions!"&$BE$37&"2:"&$BE$37),1),1)=0),INDIRECT("Transactions!"&$BE$37&"2:"&$BE$37),INDIRECT("Transactions!"&BE33&"2:"&BE33))),">="&$BE$2,ARRAYFORMULA(IF((IFERROR(DAY(INDIRECT("Transactions!"&$BE$37&"2:"&$BE$37)),0)=1)*(IFERROR(MOD(INDIRECT("Transactions!"&$BE$37&"2:"&$BE$37),1),1)=0),INDIRECT("Transactions!"&$BE$37&"2:"&$BE$37),INDIRECT("Transactions!"&BE33&"2:"&BE33))),"<"&(EOMONTH($BE$2,0)+1))

Step 6 — Replace BE26 (uncategorized-transaction count, used for a warning indicator elsewhere on the sheet) with:

=iferror(index(QUERY({ARRAYFORMULA(IF((IFERROR(DAY(INDIRECT("'Transactions'!$"&$BE$37&"$2:$"&$BE$37)),0)=1)*(IFERROR(MOD(INDIRECT("'Transactions'!$"&$BE$37&"$2:$"&$BE$37),1),1)=0),(INDIRECT("'Transactions'!$"&$BE$37&"$2:$"&$BE$37)>=$BE$2)*(INDIRECT("'Transactions'!$"&$BE$37&"$2:$"&$BE$37)<EOMONTH($BE$2,0)+1),(INDIRECT("'Transactions'!$"&$BE$33&"$2:$"&$BE$33)>=$BE$2)*(INDIRECT("'Transactions'!$"&$BE$33&"$2:$"&$BE$33)<EOMONTH($BE$2,0)+1))),INDIRECT("'Transactions'!$"&$BE$34&"$2:$"&$BE$34)},"select count(Col1) where (Col2 = '' and Col1 = 1)" ),2),0)

After these six steps, every category’s Actual will use Categorized Date wherever it’s set to a clean first-of-month value, and the raw Date otherwise — with no other visible change to the sheet’s layout or behavior.

Add Account Hide filtering

Add two more helper rows to the same BD/BE block, pointing at your Accounts sheet’s Account and Hide columns (same pattern as Step 1 above, but with the anchor pointing at your Accounts sheet instead of Transactions).
Add this block as an extra column inside each of the five QUERY/SUMIFS formulas from the Categorized Date steps above, and add Col4<>'Hide' (for the QUERY-based ones) or "<>Hide" as an extra SUMIFS criteria pair (for L14/L24):

L14 Example:

=sumifs(INDIRECT("Transactions!"&BE35&"2:"&BE35),ARRAYFORMULA(IF((IFERROR(DAY(INDIRECT("Transactions!"&$BE$37&"2:"&$BE$37)),0)=1)*(IFERROR(MOD(INDIRECT("Transactions!"&$BE$37&"2:"&$BE$37),1),1)=0),INDIRECT("Transactions!"&$BE$37&"2:"&$BE$37),INDIRECT("Transactions!"&BE33&"2:"&BE33))),">="&$BE$3,ARRAYFORMULA(IF((IFERROR(DAY(INDIRECT("Transactions!"&$BE$37&"2:"&$BE$37)),0)=1)*(IFERROR(MOD(INDIRECT("Transactions!"&$BE$37&"2:"&$BE$37),1),1)=0),INDIRECT("Transactions!"&$BE$37&"2:"&$BE$37),INDIRECT("Transactions!"&BE33&"2:"&BE33))),"<"&(EOMONTH($BE$2,0)+1),ARRAYFORMULA(IFERROR(VLOOKUP(INDIRECT("Transactions!"&$BE$36&"2:"&$BE$36),{ARRAYFORMULA(REGEXEXTRACT(INDIRECT("Accounts!"&$BE$29&"2:"&$BE$29),"^(.+)\s-\s+[^()]*\([^()]*\)$")),INDIRECT("Accounts!"&$BE$30&"2:"&$BE$30)},2,FALSE),"")),"<>Hide")

L24 Example:

=sumifs(INDIRECT("Transactions!"&BE35&"2:"&BE35),ARRAYFORMULA(IF((IFERROR(DAY(INDIRECT("Transactions!"&$BE$37&"2:"&$BE$37)),0)=1)*(IFERROR(MOD(INDIRECT("Transactions!"&$BE$37&"2:"&$BE$37),1),1)=0),INDIRECT("Transactions!"&$BE$37&"2:"&$BE$37),INDIRECT("Transactions!"&BE33&"2:"&BE33))),">="&$BE$2,ARRAYFORMULA(IF((IFERROR(DAY(INDIRECT("Transactions!"&$BE$37&"2:"&$BE$37)),0)=1)*(IFERROR(MOD(INDIRECT("Transactions!"&$BE$37&"2:"&$BE$37),1),1)=0),INDIRECT("Transactions!"&$BE$37&"2:"&$BE$37),INDIRECT("Transactions!"&BE33&"2:"&BE33))),"<"&(EOMONTH($BE$2,0)+1),ARRAYFORMULA(IFERROR(VLOOKUP(INDIRECT("Transactions!"&$BE$36&"2:"&$BE$36),{ARRAYFORMULA(REGEXEXTRACT(INDIRECT("Accounts!"&$BE$29&"2:"&$BE$29),"^(.+)\s-\s+[^()]*\([^()]*\)$")),INDIRECT("Accounts!"&$BE$30&"2:"&$BE$30)},2,FALSE),"")),"<>Hide")

(replace $BE$36 with your Transactions sheet’s Account column helper, and $BE$29/$BE$30 with your new Accounts sheet Account/Hide helpers from step 1 above).

Add custom sort order

Add Group Sort Order and Category Sort Order columns to your Categories sheet.
Add two more helper rows resolving those column letters (same pattern as above).
Update AZ10’s ORDER BY clause to sort by the new numeric columns (wrapped in VALUE(...), 999999 fallback) ahead of the existing alphabetical tiebreaker.

Example:

=QUERY({$AF$10:$AF,$AG$10:$AG,$AE$10:$AE,$AM$10:$AM,$AQ$10:$AQ,$AH$10:$AH,$AJ$10:$AJ,arrayformula(match($AF$10:$AF,Unique({{"Income";"Expense";"Transfer"};sort($AF$10:$AF)}),0)),arrayformula(iferror(value(vlookup($AE$10:$AE,{INDIRECT("Categories!"&$BE$40&"2:"&$BE$40),INDIRECT("Categories!"&$BE$46&"2:"&$BE$46)},2,false)),999999)),arrayformula(iferror(value(vlookup($AE$10:$AE,{INDIRECT("Categories!"&$BE$40&"2:"&$BE$40),INDIRECT("Categories!"&$BE$45&"2:"&$BE$45)},2,false)),999999))},"SELECT Col1, Col2, Col3 WHERE Col3 <> '' AND Col6<>'Hide' AND (Col4 IS NOT NULL OR Col7 IS NOT NULL OR (Col5 IS NOT NULL AND Col5 <> 0)) ORDER BY Col8, Col9, Col2, "&IF(I3="Budget","Col4 DESC, ",IF(I3="Savings","Col5 DESC, ",""))&"Col10, Col3",1)

Add quick navigation

Open Extensions → Apps Script, paste in the script above.
Insert four small images/drawings over H1, J1, H2, J2.
Right-click each image → the three-dot menu → Assign script → enter prevYear, nextYear, prevMonth, nextMonth respectively.

FAQ

Will this break if I don’t use Categorized Date? No — the effective-date logic falls back cleanly to the raw Date column for any transaction without a clean first-of-month Categorized Date, so it’s a strict superset of stock behavior.

Do I have to fill in Sort Order for every category? No — any category without a sort order value simply sorts after everything that has one (via the 999999 fallback), so you can add ordering incrementally.

What if my Accounts sheet has multiple blocks/duplicate “Account”/“Hide” column names? The header-lookup helper (MATCH(...)) finds the first matching column name, so as long as the column you actually maintain by hand is the leftmost one named Account/Hide, this works correctly even if a synced/secondary account list elsewhere on the same sheet also happens to have columns with the same names.

Permissions

Is it ok for others to copy, use, and modify your workflow? Yes

Topic		Replies	Views
Budget Review sheet - Summarize expenses, income, and budgets by month or quarter gSheets Templates showtell-votes	32	1075	February 26, 2026
Need Help with Update to Budget Plan Google Sheets	55	2221	August 27, 2026
:trophy: Budget Plan - Google Sheets gSheets Templates tcs-gallery , tcs-budget-plan	328	27725	February 13, 2026
Docs: Year-to-Date Comparison Sheet gSheets Templates tracking , tcs-ytd , tcs-gallery	35	6188	May 5, 2020
:trophy: Categories By Month gSheets Templates tcs-categories-by-month	61	6038	April 16, 2026