11 March 2010 by Ross McLean
Back in January I posted a few ideas to reduce workbook size. This post got quite a few comments, so I’d thought I’d write them up into a proper post.
Also Gorden has noticed a strange thing happening, so I wanted to highlight that a bit better too.
Here is a check list!
- Zip the file!
- Used Range see here.
- Removing any VBA code
- Remove Formatting.
- Use Rob Bovey’s code clearer
- Duplicate pivotcaches (ethoros)
- Excel Filter Ranges (Gordon)
- Embedded Pictures (Jon von der Heyden)
- XL 2007, Store as .xlsb (sam)
- VlookUps, External data (Charlie Hall)
Please check back in the original post for explanations of these if they don’t make sense.
And here the one Gordon recently found, which is the odd one! What do you make of it?
I recently found a method that can reduce the file size of an Excel file containing significant amounts of VBA, sometimes by as much as 50%.
- Note file size
- Open file but do not enable VBA/macros
- Make no changes
- Save & close file
- Check file size again
Even after using Rob Bovey’s Code Cleaner this can sometimes make a huge difference to the size of the file. Many of the workbooks I manage are read-only and reside on a network share somewhere, and as this can make these files much smaller (1300KB down to 700KB etc.) it improves load times, particularly over VPN connections, resulting in an improved user experience.
The files work exactly the same after this is carried out and although I’ve done some examination to see what might have changed I’ve not managed to find anything. Most likely some internal decrofting* is responsible, but I’ve really no idea why this works.
I’ve just tried this on a file and it went form 7.2 megs to 6.6 megs, not bad for doing nothing.
I cant think what it would be that causing this, maybe P-code compilation or something?
* decrofting, some sort of Scottish thing. Crofting is small scale land sharing for framing, so decrofting must be the un sharing of land… or something!
Categories: Spreadsheet Design •
1 Comment »
18 January 2010 by Ross McLean
As you work and work on a spreadsheet it has a tendency to grow in size. This is caused by adding more data, formulas and charts etc, but as file size grows workbooks can become unstable. I’m not sure what the current thinking is, but workbooks over 100mb always used to worry me. I know some folks will have bigger ones that are stable, but it’s just a rough guide.
Anyway it turns out that it’s not only the data, formulas and charts that effect work book size, but other, possibly less obvious things too.
Luckily there are some steps we can take to help reduce workbook size, making them more stable and reducing the file size on disk – think e-mail.
The effect will differ massively depending on the workbook you start with, but for illustration, this shows what happen to a workbook after applying each method in isolation.
What are the options?
- Zip the file – and remember if you’re trying to get the file under a certain size, maybe to get it through a corporate firewall, other zip applications are about, which can give smaller file sizes than the built in windows one.
- Used Range – Clearing up the used range means that empty cell don’t get sotred in the file, Excel is not great at clearing up it’s owe file structure, good news is its easy to fix, see here.
- Removing any VBA code from the file can help reduce the file size – it’s not always possible, but if it is, maybe you could try it! Also watch out of userform with massive graphic files embed in them – classic!
- Remove Formatting. Having lots of complex formatting is a key driver for file size, removing unnecessary or overly complex formatting can drastically reduce the size of you files.
- Rob Bovey’s code clearer, strips out VBA models can re imports them, this can help clean them up – if you been working on a bit VBA project, this can help – big time!
So there you go, a few ideas about how to reduce file size, maybe these will help someone.
Tags: File Size
Categories: Spreadsheet Design •
10 Comments »
28 August 2009 by Ross McLean
Beyond Comments in Excel.
I’m not sure how it happen. I’m not sure when it happen. At first I was shocked, then I felt used, now just anger. [;-))))]
Somehow Mike Alexander has hacked into my laptop, and started to steal my blog post ideas. I use ATnotes to jott down any ideas I have for blog posts, here what’s on my desktop at the mo:
As you can see top of the list is “x things I hate about Comments in excel”. Well the old piglet has just posted this – nice work. So slightly after, slightly following here’s my list:
- Can’t change default colour via UI
- Cant change the default shape via the UI(?)
- They look rubbish – mac, google anyone
- Can’t set the start up position – robustly
- Damage formatting of cells
- Don’t display correctly with frozen panes etc
- Can’t get rid of the line (?)
- The line & arrow are poor quality
- Are as technically advanced as a stone wheel.
- Have never been significantly updated
- Indicator triangles are wrong colour by default (green is a formula error?!)
And I bet there’s more. Unlike Mike though I’m not implementing them for tool tips. I’m interested in error feedback.
Modern programming technologies like .Net, have error providers, these are controls developers can use to track and respond to errors, proactive and reactively. Comments are dumb. They’re just there, and from a user experience P.O.V they don’t offer much. Sure there’re better than nothing, but only just.
Luckily we can work around this. Its really quite easy to put together a formula which can check for errors, then display a message accordingly. It won’t solve all of my issues but it’s another tool in the tool box. Here’s an example from a project I recently worked on. In this case data was entered into a “table” layout, errors where reported at the top of the document. I used a set of functions to the right hand side of the input table to check the important cells in the table.
Here’s another example but for a more orthodox form. It’s clear to see the benefits over the comments here. If the user enters the right data then no errors message is given, but when it’s wrong, bingo they can get some meaningful feedback, you can also add an additional checks at the end to see if there are any outstanding error on the sheet. As you can imagine you can make this as simple or as complex as your needs require.
So there you go, if you’re building a worksheet or form thats going to get used a lot, by a lot of different people, then maybe you should take the time to implement custom error messages, and get ride of those rubbish old comments!!!
P.S here’s the last example: MIE Tool Tips Comments Example
Tags: Examples, UI
Categories: Spreadsheet Design, UI Design •
2 Comments »
31 July 2009 by Ross McLean
Advances in Excel lightboxes! I’m not sure about you but my spreadsheet is just not complete unless I have some sort of lightbox viewer going on.
Juice have an example here, I have approached it in a slightly different way, and linked to a chart via a picture control, the example work book can be downloaded below.
And if you liked Advances in Excel lightboxes stay turned for Advances in Vista like buttons from shapes….what a world we live in.
Here’s the file: M.I.E Light Box
Tags: Charts, Downloads
Categories: General, Spreadsheet Design, White Noise •
9 Comments »
15 June 2009 by Ross McLean
Is anyone else starting to see this happen?
I’m finding it more and more common as people update their PC’s and laptops, they get the new edition of Office (2007), and just don’t realise that the file formats have changed and their files can’t be seen by others user. That, and when you do point it out to people, it’s real easy to forget to save files to an older format. Is there a default file type setting in Office 2007? How are people dealing with this problem? I can only see it getting worst over the next few years, as 2007(+) becomes more widely used.
Update: Here is the link to the file converter pack.
Tags: File Formates, XL 2007
Categories: Spreadsheet Design •
6 Comments »
