How to Determine What Is Causing Large Excel File Size

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.

How to Determine What Is Causing Large Excel File Size: 10 Ways

In this article, 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 internet, the received/downloaded file may contain some hidden worksheet. To know the actual number of the working sheets, follow the steps below.

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:

On a sheet name, right-click the mouse > Click Unhide.

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:

Save a copy of the file in Excel Binary Workbook (*.xlsb) format.

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.

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 unused or unnecessarily used sheets which is responsible for increasing your file size. So, check whether there is any unnecessary 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:

Select your first empty row or column. Just press SHIFT + SPACE (for Row) or CTRL + SPACE (for Column).

Press CTRL + SHIFT + DOWN arrow (or RIGHT arrow) to select 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 the Delete option from the Context Menu. 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 that does not actually need any formatting.

Last but not least, be careful about clear formatting as clearing also can remove the dollar sign and other important features.

For clearing formatting:

Select the data > Go to the Home tab > Click Editing > Clear > Select Clear Format.

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:

Click the picture and right-click on it > Select Size and Properties.

The Format Picture dialogue box will show up. From here, you can assign a new height and width of the picture to reduce the size.

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 a large size by copying the sheets or group of sheets to a new file. For copying the sheets:

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 sizes.

Download Practice Workbook

Determine What Is Causing Large File Size.xlsx

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 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. Have a great day!

<< Go Back to Excel Reduce File Size | Excel Files | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!