In the first method (which requires great patience), we first use the positioning function of excel to check whether there are hidden contents in the table. We press "CTRL+G" to open the positioning function, and click "Positioning Condition" button.
In the pop-up location condition window, select the object option, and then click OK.
We need to wait patiently for a period of time, and the length of waiting depends on the contents of your form. For example, I waited for about 5 minutes for a form that didn't show anything in 1M, and excel didn't respond many times.
Please be patient. After waiting, as shown in the figure below,
You will see that many "contents" are selected in the table, such as symbols, blank graphics and so on. I guess it is caused by a lot of "junk" content left by those account managers in the process of editing and saving many times. We will directly press the "Delete" key on the keyboard to delete the selected content. Please note that this process also needs to wait, and excel will not respond many times during the process. ...
After deleting these objects, we click the "File"-"Save As" button to save the cleaned form as "Problem Form 1" for comparison with the original form.
As can be seen from the above figure, the size of the cleaned form is greatly reduced, so we open the "Problem Form 1" again. This time, the form was edited and saved normally, and the problem was solved.
The second method (this method is simple but flawed), in order to facilitate comparison, I can make the contents in the table. Please note that I have made three formats on the picture, namely "text color", "merged cells" and "text is not bold".
Let's click the File-Save As button directly.
In the pop-up "Save As" interface, we first changed the "file name" to "Question Form 2" for comparison with the previous two original forms.
In the save type drop-down menu, we select CSV (comma separated). Please note that there are two types of CSV in the drop-down menu, and the "CSV UTF-8" option above is more suitable for a large number of files in English format.
After saving, let's compare the above three files.
As you can see, the size of our "Problem Form 2" CSV file is only 1KB, which is smaller than the form we just cleaned up. Why? As I said before, there is a flaw in this method. We open "Question Form 2", as shown in the figure, and we find that all the formats in the form have been cleared.
We just added three different formats to the table, namely "text color", "merged cells" and "text is not bold". After the conversion, all three formats are lost. The reason is that CSV (Comma Separated Values) files, also known as comma separated values or character separated values, store table data (numbers and text) in plain text form, and do not contain any format, so if you use this method, the format of the original table will be completely lost.
Write in the back: The above two methods to solve the problem that the excel spreadsheet file is too large and the operation is stuck have their own advantages and disadvantages. The first method will not lose the normal table content and format, but it will take a lot of time and patience. If the file is too large, cleaning may not succeed; The second method is simple and quick, but it will cause the format of the original table to be completely lost. If the format of the original table is more important than the content, please use this method with caution.