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

Microsoft Excel Show & Tell
1

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.

image
image799×112 5.89 KB

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”:

image
image856×335 21.3 KB

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.
2

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”?

3

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

    image
    image1088×969 89.1 KB

  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. :

    image
    image948×277 38.4 KB

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

    image
    image1140×333 9 KB

  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!

1 Like