How to Reduce File Size by 6MB in the Tiller Foundation Template

[bdunks]

Overview

I’ve discovered that we can reduce the Tiller-Foundation-Template.xlsx file size by over 6MB without sacrificing functionality. This enhancement can potentially improve:

• File load/launch time.
• Navigation experience and performance.
• File close time, especially when saving to OneDrive, which sometimes struggles with large files.

The primary cause of the large file size is the “Monthly Budget” sheet, making up 90%+ of the total file size. By recreating this sheet, you can significantly reduce the file size to around 300kb (for a fresh install) and maintain a leaner workbook even with lots of transactions and add-ons – my workbook with 1,500 transactions and many add-ons is 750kb.

Standard Installation

1. Download the revised Monthly Budget workbook .
2. Remove the existing “Monthly Budget” tab from your workbook (you may want to back up first).
3. If you can’t find the tab, it might be hidden; please unhide it to locate it.
4. Follow this guide to copy the downloaded template into your Excel workbook and to connect the formula references to your local workbook data.

Manual Installation

If you find any errors with the standard installation, particularly when using community add-ons, follow these steps to manually create a new “Monthly Budget”:

1. Create a new sheet named “Sheet1” where you will copy the necessary data and formulas from “Monthly Budget.”
2. Unhide and activate the “Monthly Budget” tab.
3. Use the “Name Box” to type “A1:AF516” and copy the selection (see screenshot below for guidance).
4. Switch to “Sheet1” and paste the selection.
5. Adjust formatting as needed:
   • Apply “Column Widths” using Paste Special.
   • Hide unnecessary columns and disable “Gridlines” in the “View” Ribbon.
   • Match the row heights with the original sheet (it might be a bit tedious for rows 1-13. Set to height “20” for Row 14+).
6. Delete the old “Monthly Budget” tab and rename “Sheet1” to “Monthly Budget.”

Screenshot of Step 3: “Name Box”:

Permissions

Feel free to copy, use, and modify. I hope to see this improvement incorporated into the standard download.

Notes

• You might also consider removing the “Install Help” tab for an additional 300kb reduction if you haven’t already.
• I’ve observed this issue in worksheets as far back as August 22, 2022. It might not affect earlier workbooks, but unfortunately, I can’t verify.

[randy]

Intriguing, @bdunks. Can you share a little bit more information about what you changed about the workbook when you created your “revised Monthly Budget workbook”?

[bdunks]

Hi @randy,

Great question! I had drafted the troubleshooting steps in the original post and decided to remove them to keep the post concise. I probably should have left some more details.

The Tiller Foundation Template is designed brilliantly. With the smart use of Spill/Dynamic Array formulas, it should have a really low footprint. However, I noticed a lot of lag when building additional sheets.

I saw the 6MB file size, which is the size on disk. Excel unpacks this in memory, and it can impact performance. I had a hypothesis that one of the sheets was extending to all rows, but couldn’t find an offending column or formula that’d been “manually dragged down” to all rows. I decided to dig deeper.

1. I unzipped .xlsx file using 7-zip, ran a quick size scan, and found >97% of the size was in sheet7.xml
   

   

2. The XML showed the sheet dimensions extended to all worksheet rows. I identified this as the Monthly Budget template based on some of the data & formulas in the XML. :
   

   

3. The majority of the row data in the XML was just repeated style information, with no saved data, formulas, etc:
   

   

4. I selected rows 517 - 1048575 (517 is the first unformatted row for columns L & M) and used Home → Clear → Clear All, which reduced the size to about 3MB.

5. I repeated steps 1 - 3 (unpacking and examining the XML).

6. I found a lot of style artifacts in the XML still repeated for every row from 517 - 1048575, even after clearing them from the application.

The finding in Step 6 really doesn’t make any sense to me. The style information should be removed using “Clear All” and/or “Clear Formatting.” I tried re-clearing, manually setting to standard formats, etc., and just couldn’t get the data to clear from the style XML.

Ultimately, I decided to recreate the sheet from scratch, copying & pasting only the necessary range. Maybe there is another way within the original sheet, but I couldn’t figure it out.

I hope this helps!

[randy]

Quick update… we are making good headway on some sidebar template-insertion tech with hopes to share in the Excel add-in broadly very soon. One of my projects for this release was cleaning up the template masters. I used the steps you outlined in detail and managed to get the standalone Monthly Budget master from 5.7MB to under 100kB.

Thanks again for digging into this, @bdunks.

[richl]

Agreed this was a very helpful post.

[kkapdolee]

You don’t need a new sheet to do this. You can just go to the Monthly budget sheet, select from all the rows from 150 (or whatever is your last row + 1) all the way down to the last row of excel (row 1,048,576) using ctrl + shft + down arrow, and then right click, delete. Then save.