Rafiul Hasan
Excel is mainly used for creating datasheets and performing calculations without the help of a calculator. Business organizations can’t think to pass a single day without using Excel. However, for business purposes, Excel files need to be transferred from person to person. But it takes a lot of time if the file size is too much. Again, any change in the datasheet takes a long time to update. So it is very important to determine the cause of the large file size in an Excel workbook. In this article, I will show you I will show how to determine what is causing large file size in Excel.
Download Practice Workbook
10 Suitable Ways to Determine What Is Causing Large Excel File Size
In this section you will find 10 suitable ways for determining what is causing large file size in Excel. Let’s check them now!
1. Check for Unnecessary Hidden Worksheet
If you have received an Excel file from someone else or downloaded it from the net, the received /downloaded file may contain some hidden worksheet. To know the actual number of the working sheets, follow the steps below
- Firstly, go to the Review tab> click Workbook Statistics.
- Then a pop-up will appear showing the actual number of sheets.
Let’s say the pop-up is showing that the number of sheets is 3 for the active file but you can see only 2 sheets. The unnecessary sheet may have been hidden but it is still increasing the file size. For unhiding the sheet:
- Here, on a sheet name, right-click the mouse> click Unhide.
- Then, the hidden sheets will show up. Select the sheet you want to unhide> click OK.
- Hence, your hidden sheet will appear. You can now delete them if you don’t need them.
So, the hidden file is one of the main reasons for the large file size in excel.
2. Check Whether Binary Format(.xlsb) Reduces File Size or Not
An Excel file is saved as (.xlsx) file format by default. But, when you save it to binary format (.xlsb), the file size reduces automatically. So, if you need an Excel file with a short size, check the format of the file because the default (.xlsx) format is one of the reasons for increasing the file size.
And if you want to convert the file into binary (.xlsb) format:
- First, save a copy of the file in Excel Binary Workbook (*.xlsb) format.
- Then, compare the Binary format with the Default format. You will notice that the Binary format has consumed less memory than the Default format.
So, the default format is also responsible for increasing the file size.
Read More: Reduce Large Excel File Size by 40-60% (12 Proven Methods)
3. Presence of Unused Worksheet Causing Large File Size
Excel file size increases with the introduction of new working sheets. Your Excel file may contain an unused or unnecessarily used sheet which is responsible for increasing your file size. So, check whether there is any unnecessary working sheet in your file that doesn’t contain any formula or any other features but rather has some raw data only. If so, just copy the data you need to another sheet and delete the unused sheet and it will help you reduce the file size.
4. Determine Blank Space in Excel File
One of the most common reasons for increasing the file size in Excel is the blank space in the working sheet. To determine whether there is any blank space in the sheet, just press CTRL+END and the cursor will take you to the last active working cell.
If it takes you many rows or columns past your data, then all the engaging cells are responsible for increasing the memory size. If you want to fix this and delete all these extra cells, follow the steps below:
- Firstly, select your first empty row or column. Just press SHIFT+SPACE (for Row) or CTRL+SPACE (for Column).
- Now, press CTRL+SHIFT+DOWN ARROW (or RIGHT ARROW) for selecting the very down or very right cell of the worksheet.
- After that avoid pressing the DELETE button. Just right-click on the mouse and choose Delete. Remember, pressing DELETE and selecting Delete will not show the same result.
5. Unnecessary Formatting Causing Large File Size
Your file may require the raw data presented in a special way to make it eye soothing and reader-friendly. But as soon as you format your cells in the worksheet, the file size increases proportionally. So, I will recommend you not to make any unnecessary formatting to your data. Of course, you need formatting for your job purpose or for your clients, but I am speaking about over formatting which is done for pleasing the eyes only, and for any internal file which does not actually need any formatting.
And last but not the least, be careful about clear formatting as clearing also can remove the dollar sign and other important features vanished.
For clearing formatting:
- Select the data> go to the Home tab> click Editing> Clear> select Clear Format.
Read More: [Fixed!] Excel File Too Large for No Reason (10 Possible Solutions)
Similar Readings
- How to Reduce Excel File Size with Macro (11 Easy Ways)
- Compress Excel File to Zip (2 Suitable Ways)
- How to Compress Excel File to Smaller Size (7 Easy Methods)
6. Applying Manual Calculations
The calculation option in an Excel sheet is usually set to Automatic and it is very helpful in performing the calculator faster. But at the same time, it is also responsible for causing large file sizes to some extent. So, for large-size Excel files, it is helpful for reducing the file size. For this, just go to the Formula tab and then select Manual from the Calculation Options.
7. Determine and Reducing Picture Size in Excel
If your Excel file contains inserted pictures of a large scale, then it may also increase your file size. To decrease the scale size of your picture:
- Firstly, click the picture and right-click on it> select Size and Properties.
- Then, the Format Picture dialogue box will show up. From here, you can assign new height and width of the picture to reduce the size.
Read More: How to Reduce Excel File Size with Pictures (2 Easy Ways)
8. Check for Unused Pivot Tables & Charts
One of the major reasons for file sizes getting larger is because of the presence of Pivot tables and charts. Pivot tables are mainly used for getting a quick summary of data. It increases the file size significantly. So, it should be checked whether there is any unnecessary pivot table in the Excel sheet. If a pivot table is just for one-time use, it is better to remove them quickly as it will help make the file size shorter. The same goes for the chart. So, I will recommend you avoid the unnecessary use of pivot tables and charts.
9. Check for Hidden Cells
Excel files containing hidden cells are also responsible for increasing the file size. In this case, you have to just unhide the hidden cells and delete them. It will help you decrease the size of the Excel file.
10. Copy Sheets to a New File
You can also check which sheet is occupying large size by copying the sheets or group of sheets to a new file. For copying the sheets:
- Firstly, right-click on the sheet name> select Move or Copy.
- Now, select (new book) and mark create a copy.
Finally, save the new workbook and check the size of the file. You will be able to notice the change for very large file size.
Conclusion
In this article, you have learned how to determine what is causing large file size in Excel. I hope from now on you can quickly determine what is causing large file size in an Excel workbook. If you have any better methods or questions or feedback regarding this article, please don’t forget to share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website ExcelDemy. Have a great day!
