{"id":3396,"date":"2018-11-22T20:54:31","date_gmt":"2018-11-22T20:54:31","guid":{"rendered":"http:\/\/officetuts.net\/excel\/?p=3396"},"modified":"2024-03-26T09:41:33","modified_gmt":"2024-03-26T09:41:33","slug":"reduce-excel-file-size","status":"publish","type":"post","link":"https:\/\/officetuts.net\/excel\/examples\/reduce-excel-file-size\/","title":{"rendered":"11+ Ways to Reduce Excel File Size"},"content":{"rendered":"\n

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.<\/p>\n\n\n\n

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 the write and read time of your disk and RAM.<\/p>\n\n\n\n

Fortunately, there are a few ways you can make changes to considerably reduce Excel file size.<\/p>\n\n\n\n

I created a guide with the best ways to save a few megabytes. You don\u2019t have to use all of them, and you probably shouldn\u2019t just want to present some ideas which you can choose to apply.<\/p>\n\n\n\n

1.  Remove unnecessary worksheets<\/h2>\n\n\n\n

As you know Excel workbook consists of worksheets. If there are some worksheets in your workbook that you don\u2019t use, delete them.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

The reduction in size may be minimal, if the additional worksheets are empty, to very big. It all depends on the data inside them.<\/p>\n\n\n\n

2.  Remove hidden worksheets<\/h2>\n\n\n\n

The following workbook looks like it has only one worksheet.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

But you can hide not only rows and columns<\/a> but also worksheets. To check whether you have any worksheets that are hidden, right-click a worksheet tab and click Unhide<\/strong>.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Now a new window, called Unhide<\/strong> will appear.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

You can unhide only one worksheet at a time, so in this case, you have to do it twice.<\/p>\n\n\n\n

Now, when you have all sheets visible you can delete the last two by right-clicking each time and selecting Delete<\/strong>.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

3.  Remove data formatting<\/h2>\n\n\n\n

What I mean by data formatting is things such as font color<\/a>, type, and weight. But also background color, table borders, strikethrough, underline, etc. It\u2019s usually important if you have empty cells that use some formatting, of which you are not aware.<\/p>\n\n\n\n

It will only have a minimal impact on the file reduction.<\/p>\n\n\n\n

In order to clear all formats from a worksheet, you have to click on the data and use Ctrl + A<\/strong> twice or click the select all<\/strong> button.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Now, click Home >> Editing >> Clear >> Clear Formats<\/strong>.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

All the data you have inside this worksheet will be preserved, but all formatting will be gone.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

4.  Remove conditional formatting<\/h2>\n\n\n\n

The Clear Formatting<\/strong> button is going to remove all formatting from the worksheet. But if you want to remove only conditional formatting<\/a> from the table you can do it.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Click any cell inside the worksheet<\/a> and click Home >> Styles >> Conditional Formatting >> Clear Rules >> Clear Rules from Entire Sheet<\/strong>.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Unlike the previous method, this method clears only conditional formatting<\/a>.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

5.  Remove hidden rows and columns.<\/h2>\n\n\n\n

When you work with data inside an Excel worksheet, sometimes you want to hide some rows or columns<\/a> to make your spreadsheet more readable.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

But when you get more and more data, the file can grow to large sizes. In this case, you can select all rows or columns<\/a>, right-click them, and click Unhide<\/strong>.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Now, when all your rows and columns are unhidden, you can decide whether you really need them, and delete if necessary.<\/p>\n\n\n\n

6.  Remove unnecessary formulas<\/h2>\n\n\n\n

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.<\/p>\n\n\n\n

This is how you can do it.<\/p>\n\n\n\n

Go to Home >> Editing >> Find & Select >> Go To Special<\/a>. You can also use F5<\/strong> and then click Special\u2026<\/strong>.<\/p>\n\n\n\n

A new window will appear. Select Formulas and keep all the checkboxes checked.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

In this way, you can select all the cells with formulas. Press Ctrl + C<\/strong> to copy all of them.<\/p>\n\n\n\n

Now, you have to paste values. Don\u2019t use Ctrl + V<\/span><\/strong> because you will paste them as formulas, and not as values.<\/p>\n\n\n\n

In order to paste them as values, go to Home >> Clipboard >> Paste >> Paste Values >> Values<\/strong>.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

7.  Compress images<\/h2>\n\n\n\n

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.<\/p>\n\n\n\n

There are two ways you can do it.<\/p>\n\n\n\n

The first way is by using an 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 a noticeable loss in image quality.<\/p>\n\n\n\n

In my case, the reduction in size is more than 60%.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

8.  Convert Excel files to binary format (xlsb)<\/h2>\n\n\n\n

The XLSX is an open XML spreadsheet file format<\/strong>. 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 of XML files.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

This way of saving a file<\/a> makes them easy to use by the third-party application but at the same time, they are larger in size.<\/p>\n\n\n\n

What is the XLSB format?<\/h3>\n\n\n\n

XLSB is an Excel Binary Workbook<\/strong> file. It uses a proprietary file format.<\/p>\n\n\n\n

Advantages of the XLSB format<\/h3>\n\n\n\n