How do I reduce excel file size?
The most often asked question.
Well, in this step by step tutorial I’ll walk you through various best practices to reduce excel file size and how to optimize excel workbook to work better.
#1 Save your workbooks in a Binary format
This is my favorite tip and it always works great.
I have been recommending this tip since I started writing this blog.
In general, when you save your workbook in a default format (xls or xlsx) your data will be saved in XML format but this will take little extra space because of tag specifications.
But when you save in binary format, Excel will automatically convert your data into 0 and 1 so this will trigger compression and faster load time.
The other advantage is it supports Marcos so you can easily write code to automate certain tasks.
#2 Reduce Excel formatting
Who doesn’t like colours?
Colours improve readability, you always feel happy to read well-formatted dashboards but you need to be little careful otherwise someday you will most likely open google and type “why is my excel file so large”
So, you need to be very selective and apply formatting only where it’s needed.
#3 Sort data to get better results
Obviously sorting data will not reduce your file size but it improves faster accessing.
Excel works based on a set of instructions or steps.
Say, for example, if you are trying to find a product ‘iPhone’ in column A, excel will start from the beginning by checking each cell and it will stop if found else next cell.
Same steps go on till it reaches the end.
But in case if you had sorted ColumnA based on ‘A-Z’ then products would have been already arranged in alphabetical order so this resulted in faster access with fewer system resources.
#4 Remove formulas in the datasheet
No doubt, you use various formulas for data crunching.
Very frequently you use Vlookup to get data from another workbook similarly text formulas like Trim, Mid, Right, Left etc to extract a certain piece of information.
But these formulas are no longer needed once you have done with the importing.
The simplest way is to get rid of them as soon as possible – otherwise, you tend to forget (I forget most of the times).
#5 Apply formula referencing method
This point is actually linked to my above tip.
There are some cases where you can’t remove formulas in your data sheet.
For example, A column which calculates working hours into days by using a simple formula like (Working hours/8). Every week when you add more data you would need this formula to get the calculation done.
So, to resue this formula simply copy and paste it on top row as a text like below.
Then you can delete formulas using paste special.
#6 Avoid volatile formulas
Formulas like Rand, Randbetween & Today etc are useful, but they are problematic as well.
They change/update as soon as you hit the refresh button, this results in unnecessary processing time and followed by a slowdown.
So, avoid these type of volatile formulas as much as possible.
#7 Apply conditional formatting for only required cells
As the name suggests conditional formatting works based on predefined condition. If true, formatting will be visible else no.
But this is where things can go wrong, you may apply conditional formatting to an entire column instead of a specific range and the worst part is you will not be able to see this.
You may use the below method to clear unwanted formatting.
#8 Delete unnecessary sheets
It’s a kind of basic step.
You need to periodically review all your sheets in a workbook, there is a high chance that you may have some unwanted sheets like when you double click on the pivot table the data will be expanded in a new sheet.
Also in general, excel will add three blank sheets but in case if you don’t need all of them better you delete some.
#9 Limit number of pivot tables
It’s a debatable topic.
Pivot tables are good for summarizing data, but they shouldn’t be overkill.
Each pivot table will need certain elements to work properly so every time when you add a new pivot table there is some additional weight will be added to your workbook.
So its better to limit pivot tables unless they really required.
#10 Do not keep pivot table data in a file
Once you have done with all the pivot table formatting you may not be required to keep base data in your workbook.
Unnecessarily this is will increase workbook file size.
Simply delete your base data, this will definitely reduce excel file size…but many people argue that end users may need base data for their analysis.
There are two solutions for this problem.
#1 You can just retain base data in your working report if anyone needs it just send them as and when required
#2 The other way is to educate your users to double-click on Pivot table so that they would be able to see the complete data
#11 Do not add any images unless really needed
No doubt images add lot of value to your report but you need to be careful about the usage. Images defiantly need more space so as your excel workbook.
Delete unnecessary images and shapes from your workbook, review your reports periodically to keep it clean.
Conclusion:
No doubt Excel is a great analytical toll but it comes with limitations. One of the limitation is workbook file size.
By following above tips you should be able to reduce excel file size and mange your reports well.
Do you have any other tips to reduce excel file size if so please comment bellow.