How to Reduce Excel File Size

When working in Excele, some tables achieve a rather impressive size. This leads to the fact that the size of the document increases, sometimes even dozen megabytes and more. An increase in the weight of the Excel book leads not only to an increase in its place occupied on the hard disk, but that even more importantly, to slower the speed of performing various actions and processes in it. Simply put, when working with such a document, the Excel program begins to slow down. Therefore, the question of optimization and reduction of such books becomes relevant. Let's figure out how to reduce the file size in Excele.

Book Size Reduction Procedure

Optimize the born file should be immediately in several directions. Many users do not recognize, but often the book Excel contains many unnecessary information. When the file is small for this, no one pays special attention to this, but if the document has become cumbersome, it is necessary to optimize it in all possible parameters.

Method 1: Reducing the Operating Range

The operating range is the area, the actions in which I remembers Excel. If the document is counting, the program recalculates all cells of the workspace. But it does not always correspond to the range in which the user really works. For example, an inappropriately delivered gap far away from the table will expand the size of the operating range to that element where this gap is located. It turns out that Excel when recalculating each time it will handle a bunch of empty cells. Let's see how this problem can be eliminated on the example of a specific table.

1. At first, take a look at its weight before optimizing, to compare what it will be after the procedure is performed. This can be done by moving into the "File" tab. Go to the "Details" section. On the right side of the window that opened the basic properties of the book. The first properties are the size of the document. As we see, in our case it is 56.5 kilobytes.



2. First of all, it should be found out how the real workspace is different from the one that is really needed by the user. It is quite simple to do it. We become in any cell of the table and type Ctrl + End key combination. Excel immediately moves to the last cell, which the program considers the final element of the workspace. As you can see, in specifically, our case is a line 913383. Given that the table occupies only six first lines, it is possible to state the fact that 913377 lines are, in fact, useless load, which not only increases the file size, but due to Permanent recalculation of the entire range of the program when performing any action leads to a slowdown in work on the document.

   

   Of course, in reality, such a big gap between the actual working range and the fact that Excel accepts for it is quite rare, and we took such a large number of rows for clarity. Although, sometimes there are even cases when the entire leaf area is considered the workpiece.

3. In order to eliminate this problem, you need to remove all the rows starting from the first empty and until the end of the sheet. To do this, select the first cell, which is immediately under the table, and type the Ctrl + Shift + arrow key.



4. As we can see, after that all elements of the first column, starting from the specified cell and to the end of the table were allocated. Then click on the content right mouse button. In the context menu that opens, select "Delete".

   

   Many users try to delete by clicking on the Delete button on the keyboard, but it is not correct. This action clears the contents of the cells, but does not remove them themselves. Therefore, in our case it will not help.

5. After we have selected the "Delete ..." item in the context menu, a small cell removal window opens. I set the switch to the "string" position in it and click on the OK button.



6. All rows of the allocated range were removed. Be sure to dry up the book by clicking on the floppy icon in the upper left corner of the window.



7. Now let's see how it helped us. We allocate any cell of the table and type the Ctrl + End key combination. As you can see, Excel allocated the last cell of the table, which means that it is now it is the last element of the leaf workspace.



8. Now we move to the "Details" section of the "File" tab to find out how much the weight of our document has decreased. As you can see, it is now 32.5 KB. Recall that before the optimization procedure, its size was 56.5 KB. Thus, it has been reduced by more than 1.7 times. But in this case, the main achievement is not even a reduction in the weight of the file, and the fact that the program is now exempt from recalculating the actually unused range, which will significantly increase the processing rate of the document.

If in the book several sheets you work with, you need to conduct a similar procedure with each of them. This will even further reduce the size of the document.

Method 2: Elimination of redundant formatting

Another important factor that makes the Excel document is heavier, is redundant formatting. This includes the use of various types of fonts, borders, numeric formats, but first of all it concerns the pouring of cells in various colors. So before additionally format the file, you need to think twice, and whether it is necessary to do it or without this procedure it is easy to do.

This is especially true of books containing a large amount of information that themselves already have a considerable size. Adding formatting to the book may increase its weight even several times. Therefore, you need to choose the "golden" middle between the visibility of presenting information in the document and the size of the file, to use formatting only where it is really necessary.

Another factor associated with formatting, weighting weight, is that some users prefer to format the cells "with a margin." That is, they format not only the table itself, but also the range that is under it, sometimes even until the end of the sheet, with the calculation of the fact that when new lines will be added to the table, it will not be necessary to format them every time.

But it is not known when new lines will be added and how many will be added, and such preliminary formatting you take the file already right now, which will negatively affect and at the speed of working with this document. Therefore, if you have applied formatting to empty cells that are not included in the table, it must be removed.

1. First of all, you need to highlight all cells that are located below the range with data. To do this, click on the number of the first empty string on the vertical coordinate panel. Allocated the whole line. After that, we apply the already familiar combination of hot keys Ctrl + Shift + down the down.



2. After that, the entire row range is lower than a part of the table filled with data, wakes highlighted. Being in the "Home" tab, click on the "Clear" icon, which is located on the tape in the Editing toolbar. A small menu opens. Select the "Clear Formats" position in it.



3. After this action in all cells of the allocated range, formatting will be deleted.



4. In the same way, you can remove unnecessary formatting in the table itself. To do this, select individual cells or range in which we consider formatting the minimum useful, click on the "Clear" button on the tape and select the "Clear Formats" item from the list.



5. As you can see, formatting in the selected range of the table was completely removed.



6. After that, we return this range some formatting elements that we consider relevant: borders, numeric formats, etc.

The actions described above will help significantly reduce the size of the Excel book and speed up the work in it. But it is better to initially use formatting only where it is truly appropriate and necessary than to spend time to optimize the document.

Lesson: Formatting Tables in Excel

Method 3: Removing Links

In some documents, a very large number of links from where values ​​are tightened. This can also seriously slow down the speed of work in them. Especially strongly influenced by external references to other books, although internal references are also negatively reflected in performance. If the source comes from where the link takes information is not constantly updated, that is, the meaning to replace reference addresses in cells to normal values. This is capable of increasing the speed of working with the document. Link or value is in a specific cell, in the formula row after selecting the item.

1. Select the area contained by references. Being in the Home tab, click on the "Copy" button which is located on the tape in the clipboard settings group.

   

   Alternatively, after selecting a range, you can use a combination of hot keys Ctrl + C.

2. After the data has copied, do not remove the selection from the area, and click on it the right mouse button. The context menu is launched. In it, in the "Insert Settings" block, you need to click on the "Values" icon. It has a view of the pictograms with the numbers depicted.



3. After that, all references in the dedicated area will be replaced by statistical values.

But you need to remember that this option to optimize the Excel book is not always acceptable. It can only be applied when the data from the original source is not dynamic, that is, they will not change with time.

Method 4: Format Changes

Another way to significantly reduce the file size is to change its format. This method, probably, most of all helps to squeeze the book, although it is also necessary to use the above options in the complex.

In Excel there are several "native" file formats - XLS, XLSX, XLSM, XLSB. The XLS format was the basic extension for the program version of Excel 2003 and earlier. He is already obsolete, but, nevertheless, many users still continue to be applied. In addition, there are cases when you have to return to work with old files that have been created many years ago even in the absence of modern formats. Not to mention the fact that many third-party programs are working with books with this expansion, which do not know how to process later options for Excel documents.

It should be noted that the XLS extension book has a much larger size than the current analogue of the XLSX format, which at the present time Excel uses as the main one. First of all, this is due to the fact that XLSX files are essentially compressed archives. Therefore, if you use the XLS extension, but you wish to reduce the weight of the book, then this can be done simply stopping it in XLSX format.

1. To convert the document from the XLS format to the XLSX format, go to the File tab.



2. In the window that opens, I immediately pay attention to the "Details" section, where it is indicated that at present the weight of the document is 40 KB. Next, click on the name "Save As ...".



3. The save window opens. If you wish, you can go to the new directory, but most users are more convenient to store a new document in the same place where the source code. The name of the book, if desired, can be changed in the "File Name" field, although it is not necessary. The most important thing in this procedure is to set the "File Type" field "Excel (.xlsx)" book. After that, you can press the "OK" button at the bottom of the window.



4. After saving is manufactured, go to the "Details" section of the File tab to see how much weight has decreased. As you can see, it is now 13.5 Kbytes against 40 Kbytes before the conversion procedure. That is, one only saving in modern format made it possible to squeeze the book almost three times.

In addition, there is another modern XLSB format or binary book in Excele. In it, the document is saved in binary encoding. These files weigh even less than books in XLSX format. In addition, the language on which they are recorded closest to the Excel program. Therefore, it works with such books faster than with any other extension. At the same time, the book of the specified format on functionality and the possibilities of using various tools (formatting, functions, graphs, etc.) is not inferior to the XLSX format and exceeds the XLS format.

The main reason why XLSB did not become the default format in Excel is that third-party programs are practically able to work with it. For example, if you need to export information from Excel into a 1C program, it can be done with XLSX or XLS documents, but not with XLSB. But, if you do not plan to transfer data to any third-party program, you can safely save the document in XLSB format. This will allow you to reduce the size of the document and increase the speed of work in it.

The procedure for saving the file in the XLSB expansion is similar to the one we have been done to expand XLSX. In the "File" tab, click on "Save As ...". In the saving window that opens in the "File Type" field, you need to select the option "Excel Book (* .xlsb)". Then click on the "Save" button.

We look at the weight of the document in the "Details" section. As you can see, it has decreased even more and is now only 11.6 KB.

Summing up the general results, we can say that if you are working with a XLS file, then the most effective way to reduce its size is the energizing in modern XLSX or XLSB formats. If you already use file expansion data, then to reduce their weight, you should properly configure the workspace, remove excess formatting and unnecessary references. You will receive the greatest return if you produce all these actions in the complex, and not limit yourself to one option.