I’ve recently started addressing this issue. Until now, I had all of my data since inception (March 2018) in one workbook which made everything incredibly slow. With the new year I wanted to revamp everything and only have the current year’s data in the Tiller-linked sheet. Initially I tried to export a copy of the workbook to excel but ran into problems with formatting and custom functions. So, I’ve come up with a different plan that seems to be working pretty well, is straight forward, and is pretty similar to your plan @dminches.
- Create a copy of the linked google workbook (File > Make a copy)
- In the copied workbook:
a. remove all data from the current year (e.g., transactions, balance history, statements, etc.) if any exist.
b. set all periods to “Custom” with range from 1/1/20XX to 12/31/20XX.
- In the original workbook:
a. remove all data from previous year(s)
b. update budget amounts to reflect new targets/goals, if necessary.
To address @john.warren3’s question above, I’ve set cell E1 on the categories sheet to the following formula:
This will automatically change the column headings to the months for the current year. To have the months accurately reflected on the copied sheet, this value will need to be changed to 1/1/20XX.
The obvious drawback to this approach is that you lose access to historical data within the current year’s workbook so periods such as “last year/quarter/month” don’t work as intended. But, by setting the copied workbook periods to reflect the appropriate year, I’m able to quickly view an end of the year snapshot for all of my historical data. Moreover, I can still quickly access specific data within that year should I need it.
I should note that I’ve only recently made these changes so I can’t say with absolute certainty that there are no adverse events as a result. But, since you’re only manipulating old data that isn’t changed after it’s fed into the workbook by Tiller, there shouldn’t be any problems that I can think of. But, only time will tell.