Sometimes, it might happen that your Excel file is too large for no apparent reason. There are multiple occasions and scenarios when this can happen.
In the example below, we will explain why this might happen.
Excel File Unexpectedly Large
Some of the reasons for files being too large are:
- Hidden Worksheets
The hide option in Excel actually has three possible states: 1) Unhidden; 2) Hidden and; 3) VeryHidden.
Usually, people are familiar with the first two options, and they are pretty easy to find. If we create another sheet in our Workbook, we can easily hide the second sheet by right-clicking on it, and then choosing Hide as an option:
When we see the list of sheets now, we will not be able to see the sheet, which might mean that we have some hidden data from us, making our file larger.
We can also make our sheets very hidden, and we can only do that through Visual Basic. First, we will create another sheet. Then we will open the VBA by clicking ALT + F11 on our keyboard. This will open the window in which we can access the properties of our sheet, and change the visibility to xlSheetVeryHidden:
When we return to the Workbook again, we will not be able to Unhide the sheet by clicking on any of the visible ones and then right-clicking on it:
As seen in the picture above, only Sheet 2 is visible. The only way to make Sheet 3 visible again is to open the VBA and choose xlSheetVisible for this sheet.
- Formulas and Data. If we have a very big amount of data and various formulas in our file this can affect the file sizing. We can continually optimize the formulas or remove something that is not entirely necessary. Circular references can affect this, as well as some formulas that might be complex to calculate large groups of data, such as VLOOKUP. To inspect any possible errors we always use error checking. To do this, we will go to the Formulas tab, to Formulas auditing, and then to Error Checking:
In this dropdown, we have Circular References as well, and they can be traced and inspected in this field.
- Formatting. If we use formatting often, for example, conditional formatting or styles, this can increase the size of our files. To reduce the file size, we can use the formatting less often.
- Embedded objects. If we have a lot of embedded objects, such as charts, images, or some other documents, this can increase the size of the file. To avoid this, we can always use external objects, not embedded objects.
- Unused worksheets. In addition to having the hidden sheets, we can have some sheets that are not being used or contain unnecessary data. These sheets should be deleted or removed.
- File format. Out of all the Excel formats, older ones (such as .xls) are larger in size in comparison with the new ones. If you go to File >> Save as you can inspect all possible saving options:
- If you cannot resize the file with any of these options mentioned above, you can always resize the file by compressing it. For this purpose, we have applications such as WinRar or 7Zip. To do this, we will save the file, go to its location, right-click on it, and choose Add to archive: Bottom of Form:
