Apparently, many users working with the Excel spreadsheet editor very often noticed that sometimes the size of the saved or edited file can increase significantly for no apparent (as it seems) reason. At the same time, files can be inflated to such a volume that it becomes not only inconvenient to work with them, say, in terms of sending by e-mail, but also to perform some actions in the program itself, which begins to slow down incredibly. Let's try to consider the basic simple actions that allow us to slightly reduce the final size of the table object, and we will proceed from understanding the work in Excel for beginners. Some methods can be shown to be quite complex and time-consuming, therefore, ready-made solutions will be given as a means of simplifying their implementation.
Reducing Excel File Size : Key Actions
So, before making a decision about changing the size of a table file, we’ll try to find out what can be in it that catastrophically increases the space occupied by the file on the hard disk or removable media. As a rule, not to mention some specific aspects, most often the final factors are most highly influenced by the following factors:
- Outdated file format
- Change logging for file sharing
- unnecessary formatting of empty cells (rows and columns);
- too many review inserts;
- the presence in the book of hidden sheets, objects and names;
- a large number of formulas and relationships;
- Large sizes of inserted graphics, etc.
When performing some of the following actions, it should be noted that they can be calculated exclusively on versions of Microsoft Excel 2007 and higher. However, some operations can be applied in older packages like the 2003 modification.
Changing the format of the saved file
Let's start with the most primitive solutions. The basics of working in Excel for beginners just relate to the aspect of choosing the format of the saved table structure. Some ordinary users, especially those who prefer obsolete versions of the office suite, are accustomed to believing that the main default is the XLS format. Until some time it was. But this type of file, although still in use, is hopelessly outdated. Instead, a relatively new XLSX format is proposed as the main one when saving the table, which is smaller in comparison with the old one.
How to reduce the size of the Excel file in this case, apparently, everyone already guessed. When saving a document in the type field, you just need to select the XLSX format.
However, few people know that the program has several “native” formats, and the binary XLSB format looks the most interesting in terms of size reduction. It is even smaller in size.
Thus, when saving a document, you can select it as well. But there are some difficulties. Note that if you need to import data from an Excel file, for example, in the 1C program, you cannot use the binary format of the book, since it is not supported in 1C.
Check document sharing
One of the key problems that can usually be encountered at enterprises or in offices is the opening of a shared table for editing a file by several users. In this case, an increase in size is also possible, but for the reason that all actions to change the contents of the table are recorded in a special journal, which is stored directly in the document and is an integral part of it.
In principle, the easiest and most logical solution would be to shut down shared access, but if you still need it, you can turn off logging.
In Microsoft Excel 2007 or in any other version of the editor, this can be done through the review menu, in which the access control item for the book is selected, and logging is disabled through an additional tab (by default, all user actions are saved in it for a 30-day period )
How to reduce the size of the Excel file by reducing the working range?
Another age-old problem of many users is that they completely forget that absolutely all the cells present on the sheet or in the book are not needed. For comfortable work, only those that have data are required. The rest when opening or performing other actions are recalculated automatically, which leads to a decrease in the speed of work. You can determine the end of a range by using the keyboard shortcuts Ctrl + End in Excel, which are identical for all office-type programs. In this case, the frame moves to the very last cell, taking into account both the row and the column.
If the end of the range is very far in all directions, select the lines below the last line containing the data, then use the combination of Ctrl + Shift and the down arrow, and delete the selected range, for example, by selecting the appropriate command in the RMB menu. The same procedure should be done with extra columns, but use Excel hotkeys with a right arrow.
Note: note that you cannot use the Del delete key. This will only result in the cells being cleared, and not in the actual deletion of the cells themselves. You can select the desired rows and columns with the mouse while holding the left button, moving directly along the vertical and horizontal panels with digital or letter designations of the cells. It is not necessary to translate rows into columns and vice versa for the convenience of working or displaying information, although here it is optional. After completing all the steps, the file must be saved.
What to do with redundant formatting?
A very large Excel file can also be reduced by removing unnecessary formatting (using different fonts, fills, styles, indents, etc.) both for empty cells in the operating range and for those containing data. In this case, you need to select the necessary cells, and then on the main tab from the block of editing tools in the drop-down list select the item for cleaning formats.
Delete Review
Inserting an unnecessary number of reviews, the use of which is not always advisable, also adds weight to the final file. To delete all objects of this type, use the review tab and the delete item for the selected cell range. If reviews are needed for some reason, it’s best to put them in a separate column and insert for each row as needed.
Delete hidden sheets
Now let's see how to reduce the size of the Excel file by performing some operations with hidden sheets. Some users may object to their presence in the book. But what if the table wasn’t created by you? The fact that the sheets are not shown among the active ones does not mean at all that they are not in the book, or they are not empty.
In this situation, right-click on the sheet label at the bottom of the window and select the "Show" item from the context menu. A similar operation can be performed from the "Format" section. If necessary, simply delete the sheets in which there is no data.
Removing formulas and relationships
Finally, we proceed to the most difficult option. Sometimes it is advisable to get rid of the formulas and relationships used in the cells, replacing them with the usual values. How to reduce Excel file size? To do this, just select the desired row or column range, then copy it to the clipboard using RMB or Ctrl + C, and then, without removing the selection, select the insert from the RMB menu and use the "Values" item in the paste options.
However, this method is quite inconvenient if there are a large number of sheets in the book. All values can be replaced in the Visual Basic Editor (Alt + F11) by writing a small macro shown in the image above. Again, there is no need to convert Excel to rows in columns. This is relevant only if you need to change the orientation of the main table for easy viewing or editing. There is no other practical benefit from such transformations.
Note: when using pivot tables, it is also advisable to disable the saving of source data directly to the file by selecting the data tab in the table properties.
Delete unnecessary names
As for the presence of unnecessary names, you can view them by using the combination Ctrl + F3, however, they may also be hidden.
Absolutely all the names present can be displayed, again, by executing the special macro shown above. After that, get rid of everything that you do not need.
If the task is to delete absolutely all names and from all sheets, apply another macro.
Note: you should not get carried away with the use of macros to automate computational processes, since their excessive number can significantly slow down the program and increase the size of the final file.
Troubleshooting image problems
Finally, let's try to figure out how to reduce the size of the Excel file with pictures. Such actions will not hurt if the graphic files inserted into the tables have high resolution and are initially large. The easiest option is considered to be pre-editing the size and resolution of graphic objects in any editor (at least in Paint), but such a technique with a large number of images looks very time-consuming, and the actions taken take longer. You can use its own compression tools in the spreadsheet editor itself (it is recommended to reduce the resolution to a range of about 96-150 pixels per inch). To perform such operations, you can use the format menu and select the compression item, further indicating the application of the action either to the selected object or to all images.
As with hidden names, invisible images on the selected sheet are deleted by executing the macro shown above in the VBA editor.
Total
By and large, this is all that can be said about how to reduce the size of the Excel file. Obviously, some actions for an ordinary user look too complicated, others simply will not be needed. But, as it is believed, the simplest and best option to reduce the size occupied by a table object on the hard drive is to save it in the latest formats (it is best in binary, since it is unlikely that a regular user will export data to some other specific program). It remains to say that the names of some sections of the menu, submenu items and commands, depending on the version of the editor itself, may differ (or be absent altogether). However, in most recent versions of Excel, all of the solutions described work without problems.