Abrar-ur-Rahman Niloy
Microsoft Excel is an excellent tool that features computation or calculation capabilities, graphic tools, pivot tables, and VBA. The software is mainly designed for managing and working with relatively small datasets. But the file size gets way bigger with a huge dataset and many other reasons. And nothing about large Excel files is helpful except for containing a large amount of data. They are harder to share and every process of them takes longer than required. If you find that your Excel file is too large for no reason, one of these possible solutions might fix your problem.
Excel File Too Large for No Reason: 10 Possible Solutions
An Excel file can be large for many reasons. It can be as simple as containing too much information in spreadsheets to blank cells still in use. As larger Excel files are not exactly amusing to work with, it is often feasible to make them into smaller ones. If you find that, your Excel file is too large for no reason you can seemingly find it, try these tips and tricks that will help reduce your file size. The reduction in size depends on what is causing the file to be large in the first place. Nevertheless, try each one out to find what works for you.
1. Check for Hidden Worksheets
Sometimes there can be already existing spreadsheets in your workbook which you won’t find in plain view. In other words, hidden worksheets. For example, let’s take a look at a workbook with a hidden sheet. Looking at the sheet tabs at the bottom left of the spreadsheet it will look something like this.
As we can see from the figure, there are three spreadsheets in the workbook. But go to the Review tab and select Workbook Statistics from the Proofing group first.
You will find the real number of worksheet information here.
To unhide the worksheets, follow these steps.
Steps:
- Right-click on any of the names on the Sheet tab on the bottom-left of the spreadsheet.
- Then select Unhide from the context menu.
- Select the sheet you want to unhide from the Unhide dialog box.
- Click on OK.
After that, you will see the hidden sheets on the Sheet tab again.
Now explore the spreadsheets that were hidden and if they are not of any use, remove them.
Read More: How to Determine What Is Causing Large Excel File Size
2. Remove Unused Worksheets
You should remove spreadsheets that are no longer used not only for the hidden ones but also for the unhidden ones too. Having too many spreadsheets results in taking up too much space for both containing irrelevant pieces of information and containing blank used cell ranges (details in the next section).
So, removing them is the most logical step if your Excel file is too large for no valid reason.
Read More: How to Compress Excel File for Email
3. Check for Used Range
Another major reason you may find no reason for your Excel file being too large. Because a larger number of cells are in use in your workbook than it appears. Some used cells that take up spaces because of the pieces of information in them and make Excel files large.
To see where your used cell range ended, select any of the cells in the spreadsheet and press Ctrl + End on your keyboard. Ideally, the position should be where the dataset ends.
If we enter a value in a cell outside of the dataset and then delete it, the cell will still be in use even though there is no content in it. This makes the file larger than it is supposed to be.
You can follow these quick and easy steps to remove unused cells from the spreadsheet.
Steps:
- Select the columns starting after where the dataset ends by clicking on the column header.
- Press Ctrl + Shift + Right arrow on your keyboard. This will select all the columns till the end of the spreadsheet.
- Now, right-click on the selection.
- After that, select Delete from the context menu.
- Select the row after where the dataset ends.
- Then press Ctrl + Shift + Down arrow on your keyboard. This will select all the rows till the end of the spreadsheet.
- Now, right-click and select Delete from the context menu.
The cells will no longer be in use after that. The file size should shrink at this point.
Keep in mind that do not merely press the Delete button on your keyboard. Instead, select it from the context menu.
4. Remove Unnecessary Formattings
Formattings help us customize datasets to make them more presentable. But they also make our files large. Even for small datasets, formatting causes a file to have more information. And thus, increases the size of the file. Your Excel file can be too large because of too much formatting. So, remove unnecessary formatting or try not to have too much in the beginning. Or simply, remove all depending on the file size you are having.
To remove formattings, follow these steps.
Steps:
- Select the cell or the range of cells you want to remove formattings from.
- Go to the Home tab on your ribbon.
- Select Clear from the Editing group.
- Now select Clear Formatting from the drop-down menu.
Read More: How to Reduce Excel File Size with Macro
5. Compress Images in Spreadsheets
Sometimes we need to add pictures to our spreadsheets for various purposes. But adding pictures also adds the image data to the Excel file. Which makes the file larger in size. If adding pictures is unavoidable, one way to work around this is to compress the image to make it smaller in size. That way, the file will take up less space after saving.
To compress an image in Excel, follow these steps.
Steps:
- Select the picture by clicking on it.
- A new tab will appear on the ribbon called Picture Format. Select it.
- After that, click on Compress Pictures. You will find it in the Adjust group.
- Next, select your preferred Compression options and Resolution.
- After that, click on OK and you will have your picture compressed.
Read More: How to Reduce Excel File Size with Pictures
6. Optimize Formula Usage
Like any other modifications, formulas also take up more space than normal text or numeric entries. But in some cases, formula usage is unavoidable. Although this may be true, it also takes up more space. So try to optimize formula usage in your worksheets.
While using formulas in a large Excel file look out for the following things.
- Try to avoid involving volatile formulas like RAND, NOW, TODAY, OFFSET, CELL, INDIRECT, and INFO.
- Use pivot tables or Excel tables if formulas can be avoided.
- Try not to use whole rows or columns as references.
- Make sure to avoid formulas involving repeated calculations.
7. Remove Unused Data
Each cell containing data or in use takes up space in your hard drive. This makes the file large. If you have unused datasets in spreadsheets that you no longer need, remove them.
Instead of removing them permanently, try storing them in another file as you no longer need them in the particular file. This way, if you need them for later purposes, you can have them back.
8. Delete Unused Pivot Tables and Charts
Pivot tables and Excel charts also take up space. And in most cases, more than normal cells or formatted datasets. These are excellent tools that help our Excel operations easier. But if you don’t need them, try to avoid using them at all to make the file smaller. This will also make usual Excel operations like opening and saving smoother.
Read More: How to Reduce Excel File Size with Pivot Table
9. Save File as Binary
Up to Excel’s latest version, Microsoft Excel usually saves the files with .xlsx extension. For workbooks that contain macros, the extension is .xlsm. Excel has another format for saving the file in binary format with an extension of .xlsb. These types of files take up less space than .xlsx or .xlsm files. Try this out if you have no other options available.
To save the file as binary, follow these steps.
Steps:
- Click on the File tab on your ribbon.
- Select Save As from the backstage view.
- After that, navigate to the folder where you want to save your file and select Excel Binary Workbook in the Save as type drop-down.
- Then click on Save.
This will save the file in .xlsb format which should be lower than the .xlsx format.
10. Check for External Data Source
If none of the above methods is shrinking your Excel file size, your workbook might contain more data than it should. In that case, you should think again before using Excel to save such datasets. Excel is not a database tool. Instead, it is used as an analytical one. For large databases, you should consider using database tools like Microsoft Access, Microsoft SQL Server, CSV, etc.
Conclusion
These are all the fixes you can use if you find yourself in a situation where your Excel file is too large for no reason. Hopefully, one or a combination of these fixes helped you out. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know below.
