# Remove a Closed account from Balances sheet without losing historical data **URL:** \ **Category:** gSheets Templates\ **Tags:** google-sheets, how-to, showtell-votes\ **Created:** [May 13, 2024, 7:35pm UTC](https://community.tiller.com/t/remove-a-closed-account-from-balances-sheet-without-losing-historical-data/26060 "2024-05-13T19:35:03Z")\ **Posts on this page:** 19\ **Page:** 1
**Author:** ![mu3484343](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/mu3484343/32/18812_2.png) [@mu3484343](https://community.tiller.com/u/mu3484343)\ **Post date:** [May 13, 2024, 7:35pm UTC](https://community.tiller.com/t/remove-a-closed-account-from-balances-sheet-without-losing-historical-data/26060/1 "2024-05-13T19:35:03Z")
I checked the community a bit but couldn’t get a general sense what the best practice was for the following: _I have some newly Closed accounts that are no longer able to connect (since they are Closed and the Institution has no more active accounts), however, the last pulled balance and the account is still showing up in the Balances sheet. This is causing a skewed Net Worth and Asset or Liability amount, since it’s counting the balance of an account that’s closed. If I set the Closed account to Hide (Column D on the Accounts sheet), it removes it from Balances but it also removes all historical data from Net Worth and other important historical sheets, which is not the intention._ _In Mint by Intuit, it was as easy as going to the account management page, selecting “Inactive” under the status, and setting the date of Inactivity. Historical (e.g. Net Worth) data was preserved, but the account was removed from the Balances section._ So, here is my solution to this issue, which requires just a handful of one time edits. It works as intended for Balances and Net Worth as described above. 1. On the Accounts sheet, I chose Data Validation and added another value called “Closed” under the “Hide” option for Column D. I then added a “Closed” value in column “D” (“Hide”) for each of the account rows I wanted to hide from Balances but retain all historical sheets like Net Worth. 2. On the Balances sheet, cell K3, I adjusted the formula as follows: ```auto =iferror(sort(filter({INDIRECT(Y11),INDIRECT(Y12),INDIRECT(Y13)&if(isblank(INDIRECT(Y14)),""," ("&INDIRECT(Y14)&")"),today()-INDIRECT(Y15),Indirect(Y16)},indirect(Y17)="Asset",indirect(Y18)<>"Hide",indirect(Y18)<>"Closed"),2,true, 4, true, 3, true)) ``` All I did with this formula was add _,indirect(Y18)\<\>“Closed”_ after _indirect(Y18)\<\>“Hide”_ This will just filter out any Accounts with “Closed” in column D (the “Hide” column, in the “Accounts” sheet) for Assets. 1. Also, on the Balances sheets, cell R3, I adjusted the formula as follows: ```auto =iferror(sort(filter({INDIRECT(Y11),INDIRECT(Y12),INDIRECT(Y13)&if(isblank(INDIRECT(Y14)),""," ("&INDIRECT(Y14)&")"),today()-INDIRECT(Y15),Indirect(Y16)},indirect(Y17)="Liability",indirect(Y18)<>"Hide",indirect(Y18)<>"Closed"),2,true, 4, true, 3, true)) ``` All I did with this formula was add _,indirect(Y18)\<\>“Closed”_ after _indirect(Y18)\<\>“Hide”_ This will just filter out any Accounts with “Closed” in column D (the “Hide” column, in the “Accounts” sheet) for Liabilities. 1. Finally, since this Closed account now has a 0 balance, I went into the Balance History sheet and made sure to insert a new row, copying and pasting a previous row with the now Closed account. I updated the Date/Time of that new row to be the date of account closure and set the balance to 0. 2. The only issue remaining is that the Institution that the closed account was connected with no longer connects (this will only happen if ALL accounts are closed at the Institution), so I’m seeing a “1 Feed needs refreshing” message on the Tiller extension in Google Sheets. So, the solution (for me) was to remove that Institution from the Tiller Console. Removing the Institution with no remaining active accounts from Tiller does NOT remove the data from your current sheet. It will, however, remove the data from the Tiller server so keep that in mind in case you need to redownload data into a new sheet at some point in the future. This is a non-issue for me so I removed the inactive account/Institutional connection from the Tiller Console and now no longer see the error trying to connect to an account that is inactive. Now, I’m able to see all Closed accounts reflected in historical sheets (such as Net Worth), while also no longer seeing that account appear on the Balances sheet, and also no longer seeing a connection error in the Tiller Google extension. ---
**Author:** ![yossiea](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/yossiea/32/4643_2.png) [@yossiea](https://community.tiller.com/u/yossiea)\ **Post date:** [May 14, 2024, 2:41am UTC](https://community.tiller.com/t/remove-a-closed-account-from-balances-sheet-without-losing-historical-data/26060/4 "2024-05-14T02:41:06Z")
Try changing the last entry in Balance History to 0. ---
**Author:** ![morgan](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/morgan/32/6841_2.png) [@morgan](https://community.tiller.com/u/morgan)\ **Post date:** [May 16, 2024, 11:51pm UTC](https://community.tiller.com/t/remove-a-closed-account-from-balances-sheet-without-losing-historical-data/26060/5 "2024-05-16T23:51:51Z")
I like the idea to mark something “inactive” but keep all of the data visible in some sheets. We don’t have a solution for this but your use case makes sense. ---
**Author:** ![sfergus1](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/sfergus1/32/10442_2.png) [@sfergus1](https://community.tiller.com/u/sfergus1)\ **Post date:** [May 19, 2024, 3:24am UTC](https://community.tiller.com/t/remove-a-closed-account-from-balances-sheet-without-losing-historical-data/26060/6 "2024-05-19T03:24:16Z")
I’ve handled this in the past by manually recording a transfer out on the old account that reduced the balance to 0 (and then manually adjusting the balance), with a matching transfer in on the new account at the new institution for the starting balance. This is in keeping with double-entry account principles, so you’re not “inventing” money out of thin air. This way it won’t skew your net worth and asset amounts. Admittedly, this really only solves half of your problem. An “Inactive” option would be nice for closed accounts where we want to retain the data. ---
**Author:** ![Mark.S](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/mark.s/32/14650_2.png) [@Mark.S](https://community.tiller.com/u/Mark.S)\ **Post date:** [August 13, 2024, 8:42pm UTC](https://community.tiller.com/t/remove-a-closed-account-from-balances-sheet-without-losing-historical-data/26060/7 "2024-08-13T20:42:47Z")
> [@mu3484343](#): > > On the Balances sheet, cell K3, I adjusted the formula as follows: > \ > All I did with this formula was add `,indirect(Y18)<>"Closed"` after `indirect(Y18)<>"Hide"` Thank you for sharing this, it’s just what I was looking for 👍 I made the same change to cell `R3`, which is for `Sorted Liabilities`. (Cell `K3` is `Sorted Assets`) ---
**Author:** ![MidnightPrzm](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/midnightprzm/32/9704_2.png) [@MidnightPrzm](https://community.tiller.com/u/MidnightPrzm)\ **Post date:** [August 24, 2024, 10:43am UTC](https://community.tiller.com/t/remove-a-closed-account-from-balances-sheet-without-losing-historical-data/26060/8 "2024-08-24T10:43:15Z")
> [@mu3484343](#): > > =iferror(sort(filter({INDIRECT(Y11),INDIRECT(Y12),INDIRECT(Y13)&if(isblank(INDIRECT(Y14)),“”," (“&INDIRECT(Y14)&”)"),today()-INDIRECT(Y15),Indirect(Y16)},indirect(Y17)=“Asset”,indirect(Y18)\<\>“Hide”,indirect(Y18)\<\>“Closed”),2,true, 4, true, 3, true)) Just want to add the formula for the Asset vs Liabilities. Assets (K3) ```auto =iferror(sort(filter({INDIRECT(Y11),INDIRECT(Y12),INDIRECT(Y13)&if(isblank(INDIRECT(Y14)),""," ("&INDIRECT(Y14)&")"),today()-INDIRECT(Y15),Indirect(Y16)},indirect(Y17)="Asset",indirect(Y18)<>"Hide",indirect(Y18)<>"Closed"),2,true, 4, true, 3, true)) ``` Liabilities (R3) ```auto =iferror(sort(filter({INDIRECT(Y11),INDIRECT(Y12),INDIRECT(Y13)&if(isblank(INDIRECT(Y14)),""," ("&INDIRECT(Y14)&")"),today()-INDIRECT(Y15),Indirect(Y16)},indirect(Y17)="Liability",indirect(Y18)<>"Hide",indirect(Y18)<>"Closed"),2,true, 4, true, 3, true)) ``` ---
**Author:** ![mu3484343](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/mu3484343/32/18812_2.png) [@mu3484343](https://community.tiller.com/u/mu3484343)\ **Post date:** [August 24, 2024, 12:59pm UTC](https://community.tiller.com/t/remove-a-closed-account-from-balances-sheet-without-losing-historical-data/26060/9 "2024-08-24T12:59:59Z")
I edited the original post to include both formulas (Assets and Liabilities) and updated the content to remove my comments trying to troubleshoot the issue since it’s a working solution. ---
**Author:** ![Mark.S](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/mark.s/32/14650_2.png) [@Mark.S](https://community.tiller.com/u/Mark.S)\ **Post date:** [August 24, 2024, 4:12pm UTC](https://community.tiller.com/t/remove-a-closed-account-from-balances-sheet-without-losing-historical-data/26060/10 "2024-08-24T16:12:01Z")
Here is an extension of this solution for the Net Worth sheet. People often want to Hide accounts from the Net Worth report for time periods after the account was closed, but still want them visible for when the account was open. This uses the Accounts sheet Last Update date for the Closed date. The Last Update date can be changed by changing the Balance History for the closed account. Change Net Worth sheet cell `AC7` to add the following to the `QUERY`’s `WHERE` clause: ```auto AND ("&AX23&" <> 'Closed' OR ("&AX23&" = 'Closed' AND "&AX17&" >= date '"&TEXT($AI$2, "yyyy-mm-dd")&"')) ``` Where: `AX17` = Last Update date (aka Closed date for closed account) `AI2` = Net Worth report starting date range Here are the before/after Net Worth sheet cell `AC7` formulas: BEFORE: ```auto =query(indirect(AX24),"SELECT "&AX21&", "&W4&", "&AX13&", "&AX14&", "&AX15&" WHERE "&AX13&" <> '' AND "&AX23&" <> 'Hide' ORDER BY "&AX21&", "&W4&", "&AX14,-1) ``` AFTER: ```auto =QUERY(INDIRECT(AX24), "SELECT "&AX21&", "&W4&", "&AX13&", "&AX14&", "&AX15&" WHERE "&AX13&" <> '' AND "&AX23&" <> 'Hide' AND ("&AX23&" <> 'Closed' OR ("&AX23&" = 'Closed' AND "&AX17&" >= date '"&TEXT($AI$2, "yyyy-mm-dd")&"')) ORDER BY "&AX21&", "&W4&", "&AX14 ,-1) ``` Where I have changed the formula format for better readability. ---
**Author:** ![MidnightPrzm](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/midnightprzm/32/9704_2.png) [@MidnightPrzm](https://community.tiller.com/u/MidnightPrzm)\ **Post date:** [August 27, 2024, 7:02am UTC](https://community.tiller.com/t/remove-a-closed-account-from-balances-sheet-without-losing-historical-data/26060/11 "2024-08-27T07:02:10Z")
Awesome. That will definitely help everyone. If you add the following 3 symbols `` ` (without the spaces) before and after the formulas, it will create a code block and make it easier for people to just copy and paste the correct formulas without them accidentally copying extra symbols or letters. Would be helpful for new users who aren’t as experienced. Thank you again. ---
**Author:** ![MidnightPrzm](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/midnightprzm/32/9704_2.png) [@MidnightPrzm](https://community.tiller.com/u/MidnightPrzm)\ **Post date:** [August 27, 2024, 7:18am UTC](https://community.tiller.com/t/remove-a-closed-account-from-balances-sheet-without-losing-historical-data/26060/12 "2024-08-27T07:18:41Z")
Will this affect the new Spending Trends solution that has replaced the Net Worth solution? Also, I am unsure if this is related but I input this formula into the Net Worth sheet cell `AC7. ` On one of my sheets where I haven’t updated the accounts and balance sheets, the formula pulls in all the data, and I do not see any errors. On my main sheet I have updated the accounts sheet, and I believe the Balance Sheet. All the sheets included in the new foundations template. On this template I receive the error “Did not find value ‘Hide’ in MATCH evaluation.” The formula that I copied was the last one titled “AFTER.” And nothing populates in the Net Worth sheet. ---
**Author:** ![Mark.S](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/mark.s/32/14650_2.png) [@Mark.S](https://community.tiller.com/u/Mark.S)\ **Post date:** [August 27, 2024, 3:51pm UTC](https://community.tiller.com/t/remove-a-closed-account-from-balances-sheet-without-losing-historical-data/26060/13 "2024-08-27T15:51:24Z")
> [@MidnightPrzm](#): > > Will this affect the new Spending Trends solution that has replaced the Net Worth solution? No, Spending Trends just reports today’s Net Worth. The Net Worth sheet reports historical Net Worth. > [@MidnightPrzm](#): > > “Did not find value ‘Hide’ in MATCH evaluation.” Did you maybe accidentally change the right-most Hide column header name on the Accounts sheet? That would also break the Spending Trends Net Worth value. ![image](https://us1.discourse-cdn.com/flex020/uploads/tillermoney/original/3X/3/5/35c77e090f73abb409c780ba4e65c533bcdde06c.png) ---
**Author:** ![MidnightPrzm](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/midnightprzm/32/9704_2.png) [@MidnightPrzm](https://community.tiller.com/u/MidnightPrzm)\ **Post date:** [August 29, 2024, 6:02am UTC](https://community.tiller.com/t/remove-a-closed-account-from-balances-sheet-without-losing-historical-data/26060/14 "2024-08-29T06:02:36Z")
No my headers for my accounts sheet are labeled correctly ![image](https://us1.discourse-cdn.com/flex020/uploads/tillermoney/original/3X/4/c/4c6ad5057e26c213cf59c2c023c96245ccf689e2.png) Edit: I went ahead and restored the Net Worth sheet and inserted the formula into the cell `AC7. ` I must have messed something else because now I am no longer receiving any errors. Thank you. ---
**Author:** ![buzzmaster1](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/buzzmaster1/32/9377_2.png) [@buzzmaster1](https://community.tiller.com/u/buzzmaster1)\ **Post date:** [September 14, 2024, 6:20pm UTC](https://community.tiller.com/t/remove-a-closed-account-from-balances-sheet-without-losing-historical-data/26060/15 "2024-09-14T18:20:39Z")
I have finally gotten around to trying to do this in my sheet but it looks nothing like this. I am looking at the hidden columns and they do not correspond to this at all. Wondering if there is a version issue? ---
**Author:** ![Mark.S](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/mark.s/32/14650_2.png) [@Mark.S](https://community.tiller.com/u/Mark.S)\ **Post date:** [September 14, 2024, 6:33pm UTC](https://community.tiller.com/t/remove-a-closed-account-from-balances-sheet-without-losing-historical-data/26060/16 "2024-09-14T18:33:20Z")
Note that this is for Google Sheets and not Excel, that could cause a difference. Here is the version info I see in Tiller Community Solutions. ![image](https://us1.discourse-cdn.com/flex020/uploads/tillermoney/original/3X/a/f/afff7aee4edb6b70b239e168f81b912ff613494f.png) ---
**Author:** ![buzzmaster1](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/buzzmaster1/32/9377_2.png) [@buzzmaster1](https://community.tiller.com/u/buzzmaster1)\ **Post date:** [September 15, 2024, 3:58pm UTC](https://community.tiller.com/t/remove-a-closed-account-from-balances-sheet-without-losing-historical-data/26060/17 "2024-09-15T15:58:22Z")
Oops , I am in Excel, it is totally different. I always thought I was pretty handy in excel but I have been looking at the formulas trying to see where I could insert something that does not show accounts that have zero balance for the selected range but sadly it seems beyond my capabilities. ---
**Author:** ![Mark.S](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/mark.s/32/14650_2.png) [@Mark.S](https://community.tiller.com/u/Mark.S)\ **Post date:** [September 15, 2024, 4:13pm UTC](https://community.tiller.com/t/remove-a-closed-account-from-balances-sheet-without-losing-historical-data/26060/18 "2024-09-15T16:13:23Z")
I moved your [original post](https://community.tiller.com/t/inactive-closed-accounts-in-spreadsheet/27837) into the Excel category. I haven’t looked into doing this in Excel, yet, as Sheets is my primary usage. Sheets uses a really cool QUERY function for this in the Net Worth sheet that doesn’t exist in Excel. I do have an Excel sample data spreadsheet linked to Tiller that I sometimes dabble with, so I might poke around in it, but not sure if/when I’ll get to it 😕 ---
**Author:** ![buzzmaster1](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/buzzmaster1/32/9377_2.png) [@buzzmaster1](https://community.tiller.com/u/buzzmaster1)\ **Post date:** [September 15, 2024, 4:56pm UTC](https://community.tiller.com/t/remove-a-closed-account-from-balances-sheet-without-losing-historical-data/26060/19 "2024-09-15T16:56:25Z")
thanks! Obviously, this is not a critical feature but it would make the report much cleaner. ---
**Author:** ![vest\_money](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/vest_money/32/16640_2.png) [@vest\_money](https://community.tiller.com/u/vest_money)\ **Post date:** [January 1, 2025, 9:59pm UTC](https://community.tiller.com/t/remove-a-closed-account-from-balances-sheet-without-losing-historical-data/26060/20 "2025-01-01T21:59:00Z")
Anyone able to provide the formulas that should be entered into excel to do this? On the balances sheet excel uses the CHOOSE function rather than INDIRECT. ---
**Author:** ![nicoleg](https://sea2.discourse-cdn.com/flex020/user_avatar/community.tiller.com/nicoleg/32/14664_2.png) [@nicoleg](https://community.tiller.com/u/nicoleg)\ **Post date:** [February 24, 2026, 4:13pm UTC](https://community.tiller.com/t/remove-a-closed-account-from-balances-sheet-without-losing-historical-data/26060/21 "2026-02-24T16:13:21Z")
Just curious if Tiller has addressed this systematically, instead of these work arounds? Closing accounts is not unusual - feels weird that we’re having to jump through hoops to do it. Mark the account as closed in Tiller - it should do all the necessary edits. Credit cards happen all the time! My accounts tab ends up very out of date quickly to say the least.