For most people who deal with small files, the reduction in file size is not that important. But for those who deal with thousands of rows of data, making their files smaller can be very important.
Reducing file size is not only important for people who want to save some disk space, but also for those who send Excel files via email. The smaller size is also important for reducing write and read time of your disk and RAM.
Fortunately, there are a few ways you can make changes to considerably reduce Excel file size.
I created a guide with the best ways to save a few megabytes. You don’t have to use all of them, and you probably shouldn’t just want to present you some ideas which you can choose to apply.
- 1. Remove unnecessary worksheets
- 2. Remove hidden worksheets
- 3. Remove data formatting
- 4. Remove conditional formatting
- 5. Remove hidden rows and columns.
- 6. Remove unnecessary formulas
- 7. Compress images
- 8. Convert Excel files to binary format (xlsb)
- 1. Remove Pivot Cache
- 10. Split your workbook
- 11. Create zip file
- 12. Check if you have CSV files, instead of xlsx
1. Remove unnecessary worksheets
As you know Excel workbook consists of worksheets. If there are some worksheets in your workbook that you don’t use, delete them.
The reduction in size may be minimal, if the additional worksheets are empty, to very big. It’s all depends on data inside them.
The following workbook looks like it has only one worksheet.
But you can hide not only rows and columns but also worksheets. To check whether you have any worksheets that are hidden, right-click a worksheet tab and click Unhide.
Now a new window, called Unhide will appear.
You can unhide only one worksheet at a time, so in this case, you have to do it twice.
Now, when you have all sheets visible you can delete the last two by right-clicking each time and selecting Delete.
3. Remove data formatting
What I mean by data formatting is things such as font color, type, and weight. But also background color, table borders, strikethrough, underline, etc. It’s usually important if you have empty cells that use some type of formatting, of which you are not aware.
It will only have a minimal impact on the file reduction.
In order to clear all formats from a worksheet, you have to click on the data and use Ctrl + A twice or click the select all button.
Now, click Home >> Editing >> Clear >> Clear Formats.
All the data you have inside this worksheet will be preserved, but all formatting will be gone.
4. Remove conditional formatting
The Clear Formatting button is going to remove all formatting from the worksheet. But if you want to remove only conditional formatting from the table you can do it.
Click any cell inside the worksheet and click Home >> Styles >> Conditional Formatting >> Clear Rules >> Clear Rules from Entire Sheet.
Unlike the previous method, this method clears only the conditional formatting.
When you work with data inside Excel worksheet, sometimes you want to hide some rows or columns to make your spreadsheet more readable.
But when you get more and more data, the file can grow to large sizes. In this case, you can select all rows or column, right-click them, and click Unhide.
Now, when all your rows and columns are unhidden, you can decide whether you really need them, and delete if necessary.
6. Remove unnecessary formulas
If you have too many formulas inside your worksheet, check whether you really need them. If you need formulas to calculate some value but they are no longer necessary you can convert them to values.
This is how you can do it.
Go to Home >> Editing >> Find & Select >> Go To Special. You can also use F5 and then click Special….
A new window will appear. Select Formulas and keep all the checkboxes checked.
In this way, you can select all the cells with formulas. Press Ctrl + C to copy all of them.
Now, you have to paste values. Don’t use Ctrl + V because you will paste them as formulas, and not as values.
In order to paste them as values, go to Home >> Clipboard >> Paste >> Paste Values >> Values.
7. Compress images
Images are usually big in size if you compare them to text files. If you have a lot of them, you can probably save a lot of space.
There are two ways you can do it.
The first way is by using the external application. One of the most popular is tinypng. You can drag and drop your files there in order to considerably reduce your image size without noticeable loss in image quality.
In my case, the reduction in size is more than 60%.
8. Convert Excel files to binary format (xlsb)
The XLSX is an open XML spreadsheet file format. This means that normally the Excel file is written as an XML file. If you change the xlsx extension to ZIP and open the file you will notice, that it consists mainly from XML files.
This way of saving a file make them easy to use by the third-party application but at the same time, they are larger in size.
What is XLSB format?
XLSB is an Excel Binary Workbook file. It uses a proprietary file format.
Advantages of the XLSB format
- The file size can be up to 50% smaller.
- The write and read can be noticeably faster, even as much as 50%, which can be very noticeable for larger files.
Disadvantages of the XLSB format
- Normally, you know whether the file is saved as a standard Excel file or as a file with macros because they have different extensions and icons. If you save a file as an Excel binary, you can’t know it before you open it. It can be risky if you open a file that is not trusted and execute malicious code.
- The XLSB uses a proprietary binary format. If you use third third-party applications you should use XLSX because binaries can’t be open outside Office Suite.
- If you use Power Query, you should save your files as XLSX because it can’t read the XLSB format.
1. Remove Pivot Cache
When you create a pivot table from your table, Excel automatically creates a pivot cache that holds a copy of your table in the form that is convenient to use for the application. In other words, when you modify the pivot table, Excel uses this cache instead of the source table.
Now, if you make changes in your source table, Excel modifies pivot cache and operate on this cache.
As soon as you create a pivot table, the file will grow. And the increase in size can be very significant. This is what I got after adding a pivot table to my file.
There are a few ways you can consider in order to save space.
Delete pivot table
When you delete a pivot table, the pivot cache is also deleted. The file size is exactly the same as it was before creating a pivot table.
Delete pivot cache
If you want to keep pivot table, but still have your file smaller, you can do it. It won’t reduce file size that much as deleting pivot table, but there will be some reduction.
In order to delete pivot cache in your file, follow these steps.
- Click any cell inside your pivot table.
- Choose PivotTable Tools >> Analyze.
- In PivotTable area, click Options.
- On the PivotTable Options window, uncheck Save source data with file and check Refresh data when opening the file.
- Click OK.
Now, you can see a slight reduction in file size.
10. Split your workbook
Let’s suppose, you have a few sheets inside your workbook. Each sheet represents the day of the week.
If the file becomes too big, it may be wise to split them into separate files.
11. Create zip file
If you send any file via email, it’s a good idea to compress the file before sending. You can use 7zip, which is a free file archiver. But you can also use the built-in Windows application.
To compress a file right-click it and choose to Send to >> Compress.
And you save a bit of space.
12. Check if you have CSV files, instead of xlsx
XLSX files weight less than CSV files. Check if you have any files that are not Excel files, but CSV. Often when you download data from the third-party application, they come in the CSV format. It can be hard to notice, especially if you deal with small icons.
Take a look at these two files. It’s hard to notice which one is an Excel file and which one is comma separated. And CSV is much larger in size.
It’s especially hard when you have to deal with a lot of files.
Personally, I always display files extensions, so it’s easy for me to notice these files.
If you want to display both, file name and files extension, open the directory in Windows file explorer and check View >> Show/Hide >> File Name extensions.
Now, you can clearly see which files are Excel files and which one are comma-separated (CSV).