File size and graphics glitches

Excel PerformanceLarge Files
5 posts / 0 new
Log in or register to post comments.
Last post
5 years ago
#1
James Wallace A+ 8
File size and graphics glitches

Hi,

I have built a custom workbook for a client and the file size for .xlsb is about 14mb after being released from Modano. I have run a number of add-ins over the workbook to see what is causing the file size to be so big. Removing conditional formatting and applying other optimisations doesn't help much. Also, the sum of individual exported modules always seems to be smaller than a model combining them all (the whole is larger than the sum of the parts).

My client has graphics glitches on some of the worksheets. Switching to another worksheet and back seems to fix them. I have had similar problems in the past (only after upgrading to Office 365 from 2010. I think the file size may be a cause of these issues.

Do you have any suggestions for determining the cause of large file size and then reducing the file size?

It seems to me that file sizes have become much larger since using Modano. In other words, if I had created the custom workbook without Modano it would have taken longer to create but have a smaller file size.

Thanks,

James

Update: I have added a screenshot from my client (please excuse the black covering up identifiable names). The glitch seems to bring across parts of the worksheet you have switched from over top of the worksheet you have switched to. I have had this previously with other Modano files, but I'm not sure if this is Modano related or Excel wide as I am normally in Modano files. As mentioned above, these types of issues only started after moving to Excel 2016/365.

Attachments: 
Excel graphics.png
5 years ago
Michael Hutchens A+ 189

Hi James,

Preventing file size blowout when building Excel models is one of the things that differentiates great financial modelers from good financial modelers, and even great financial modelers sometimes get stuck with an impractically large model.

My main tips would be:

  1. Prevent scope blow out as early as possible in the model scoping process.
  2. Avoiding the use of a huge number of lookup functions (such as SUMIF and SUMPRODUCT) in your models, in favor of using some basic VBA code to sort and filter data that only requires updating periodically (such as monthly data imports and filtering).
  3. Split analysis across multiple workbooks wherever possible, aiming to keep each working no more than 5MB - 10MB in size (*.xlsb format).

Excel 2013+ use different rendering than prior versions of Excel, supporting high-resolution scaling, etc., for Windows 10, etc. We've found that this can be quite buggy at times, and it's not necessarily related to file size.

If you're seeing issues on you client's machine and not yours, you may need to get them to upgrade their computer, or even just their graphics drivers. But graphics and screen drawing is complex stuff so you may need to undertake some trial and error.

5 years ago
Michael Hutchens A+ 189

This forum post I wrote a while ago may also help you:

www.modano.com/forum/5_ways_optimize_performance_excel_2016

M.

5 years ago
James Wallace A+ 8

Thanks, Michael.

1) Unfortunately, the client wanted a weekly model. I determined the section of the model causing the large file size is the worksheet that allocates weekly outputs to monthly outputs.

3) I previously split the annual and dashboard worksheets into a model separate from the main weekly model with a monthly summary. This helped split the file size across two files. My client had the above issue in the separate weekly model.

I agree that the different method of rendering Excel 2016 must be the cause of various graphics issues I have encountered over the last couple of years. Hopefully, Microsoft fixes these in future.

I found your article useful when it came out. I will be re-installing Office soon so will try Excel 32-bit and see if that helps. Would you recommend Excel 2010 or 2016 if I install them on a clean Windows 10 install?

Cheers,

James

5 years ago
Michael Hutchens A+ 189

I would 100% recommend Excel 2016 James, as it's really quite an impressive release of Excel compared with almost all prior versions, and can actually handle most things better than Excel 2010 but for issues like the screen rendering you're experiencing.

Also, Excel 2010 contains some bugs in the theming, and we've seen compatibility issues in some cases with clients using Excel 2013 and Excel 2016 when we kept using Excel 2010.

This is brutal, but I would say that if you're experiencing issues due to file size using Excel 2016, it's often time to re-assess what you're doing and potentially re-scope and re-design your model. If you're still having issues after doing things like avoiding excessive deep-sheeting and use of category-based controls, etc., I think you've probably built a monster that Excel isn't really designed to handle. This is especially the case if it's still troublesome when split across multiple workbooks...

The complexity with financial modeling is always in keeping things as simple as possible, and most modelers really battle with this key challenge.