Excel is an amazing tool for analyzing and visualizing data sets. To visualize and make report we often need to save small chunks of data. These small chunks of data grow over time and make the files heavier. But data is not the only reason behind the large size of your excel file. There are many more things you need consider if you want to reduce the file size of your Excel file.
It is important to have small size of excel file. The first reason is loading time. If your excel file is large (2 mb or larger) you will notice longer loading time, reduced performance, unresponsiveness, etc. All this can be avoided if you keep your file size reasonable. These are the few effective methods to reduce the file size.
1: Remove Unused Data From File
It happens many times that we import data in excel file for temporary use but forget to remove it. This increases the size of the Excel file.
To reduce the size of excel file identify the data that you don't really need. Delete those ranges and save the file. This will reduce the file size.
Pro tip: if you right somewhere down in the sheet and then delete it, excel remembers it and takes all that range in used range and save the file. For example, if your data is in range A2:B10. And for some reason you temporarily write something in cell A100000. Then Excel will take used range as A2:B100000. This increases the file size. Delete such ranges.
2: Remove non essential formulas/calculation.
This is one of the biggest reason for the large size of data. The excel function take memory and CPU time for calculations. They engage the memory in calculations. Although Excel formulas are fast and light but they make file heavy if there are too many complex formulas.
We often use formulas to pull or calculate some calculations that is only for one time and does not need to be dynamic. Identify such formulas and value paste them. This will reduce the file size.
If you got to have a large number of formulas in your excel file than try to have a VBA sub routine for that calculations. Remove the formulas from the worksheet and do those calculations in a VBA subroutine. This will make your file too much lighter than before.
If you don't want to use VBA for calculations and want to have formulas on the sheet, disable the automatic calculations. If you have too many complex formula on sheet, every time you make a change in Excel file those calculations will be recalculated. This will take time and memory and can make your Excel file unresponsive. To avoid this, disable the automatic workbook calculation and set it to manual.
To set formula calculation to manual:
Go to File --> Options --> Formulas.
Now the workbook will be calculated only when you want. To recalculate the workbook use the F9 key on the keyboard.
Using this method, you can reduce the burden on the CPU.
Pro Note: Try not to use Volatile functions like OFFSET, RAND, RANDBETWEEN, etc.
3. Delete Unused Hidden Sheets
When we work a long time on an Excel file, we create many temporary sheets that is required for a short period of time. When we don't use them, we hide them in order to use them later. But we often forget about those sheets. These hidden sheets increase the file size. Identify such hidden sheets and get the rid of them. This will decrease the Excel file size.
4. Remove Unused Pivot Table and Charts
For quick summery of data I create pivot tables. Once I have used them, I delete those pivot tables. Because pivot tables increase the file size. If a pivot table is for one time use, it is better use and scrap them at earliest. Same goes for the charts. Charts are graphics and they increase the size of Excel file significantly. Until and unless the chart is necessary, I suggest to not have a chart in excel file.
5. Remove Unnecessary Formatting from the Large Excel File.
The formatting is done in Excel files to make data easy to understand and readable. But sometimes we do formatting to please the eyes only. There's nothing bad in that but if the size of file increases that it creates problem, we should avoid unnecessary formatting.
By the unnecessary formatting, I mean formatting on the data sheet. The sheet that contains the source data, does not need to be formatted that much. So we should avoid implementing borders and colors to that data sheet. You can do the formatting on the reports and dashboard sheet/
6: See if you can save source data in a Data Base
If you have a large amount of data that you process in Excel but Excel hangs and gets unresponsive while processing that much of data than you should reconsider using excel for saving data. Excel is not a database tool. It is an analytical tool. To save large amount of data that can grow overtime, you should use database tools like Microsoft Access, Microsoft SQL Server, CSV etc.
You can use the Excel's External Data Source feature or Power Query to process that much data. This can help you reduce the file size of Excel Workbook significantly.
7: Save as Excel Binary File
The Excel is evolved to work with other tools and that kind of file takes a large space. This type of Excel file has extension .xlsx that you are familiar with. But if your Excel file does not need to interact with other tools than save the Excel file as Excel Binary File. The extension of this type of file is .xlsb. This has lesser features but has all the essential features of Excel and size of file is significantly lesser than the .xlsx file.
Pro Tip: Always keep a copy of original file before trying to reduce the size of file. So that your data is intact if you do some mistake in duplicate file. Sometimes the reason of big size of excel file is unknown. Try to recreate the file. Do not external JPEG images for beautification of the reports if size is increasing too much.
So yeah guys, these are the ways to reduce the file size of large excel files. I hope it was explanatory enough and helpful for you. If you have any doubts or specific requirement, ask me in the comments section below. Till then keep Excelling.
Related Articles:
Center Excel Sheet Horizontally and Vertically on Excel Page : Microsoft Excel allows you to align worksheet on a page, you can change margins, specify custom margins, or center the worksheet horizontally or vertically on the page. Page margins are the blank spaces between the worksheet data and the edges of the printed page
Split a Cell Diagonally in Microsoft Excel 2016 : To split cells diagonally we use the cell formatting and insert a diagonally dividing line into the cell. This separates the cells diagonally visually.
How do I Insert a Check Mark in Excel 2016 : To insert a checkmark in Excel Cell we use the symbols in Excel. Set the fonts to wingdings and use the formula Char(252) to get the symbol of a check mark.
How to disable Scroll Lock in Excel : Arrow keys in excel move your cell up, down, Left & Right. But this feature is only applicable when Scroll Lock in Excel is disabled.
Scroll Lock in Excel is used to scroll up, down, left & right your worksheet not the cell. So this article will help you how to check scroll lock status and how to disable it?
What to do If Excel Break Links Not Working : When we work with several excel files and use formula to get the work done, we intentionally or unintentionally create links between different files. Normal formula links can be easily broken by using break links option.
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
How to use Excel VLOOKUP Function| This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
How to use the Excel COUNTIF Function| Count values with conditions using this amazing function. You don't need to filter your data to count specific value. Countif function is essential to prepare your dashboard.
How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
