First, there are a large number of small picture objects in the worksheet, which leads to the increase of files, which is the most common reason for file puffiness. Possible reasons: (1) Copy the content from the webpage and paste it directly into the worksheet without using selective paste; (2) Inadvertently clicking on straight lines or other drawing objects in the drawing toolbar, and unconsciously inserting small straight lines or other graphics into the file, because they are too small to be seen by the naked eye, a large number of small drawing objects are generated by copying cells; (3) Insert drawing objects such as pictures into the worksheet. In operation, set the height and width to 0 or a very small value, and generate a large number of objects by copying. (4) Insert a drawing object at the position of a row or column, and the size and position of the object change with the cell (default), then hide the row or column, or set the row height or column width to a small value so that the inserted object cannot be seen. (5) The objects in the worksheet are set to invisible attributes (Visible=false) or.
Judgment method: (1)CTRL-G, select "object" in the positioning condition, and many selected small objects will be displayed after confirmation (objects in hidden columns or rows cannot be seen by this method). (2) Count the objects with VBA to see how many objects are in the worksheet and whether they are consistent with the number of objects you need in your worksheet.
sub-count shapes()dim n n = active sheet。 Shapes.countmsgbox "This worksheet * * * has"&; N&"object" terminator
Solution (1) If you confirm that there are no objects or controls you need in the worksheet, use CTRL-G to locate, select "Object" in the location criteria, and press DEL to delete after confirmation, and all the objects in the worksheet will be deleted. (2) using VBA to delete, small objects whose height or width is less than a certain value can be deleted as needed, such as
Sub delshapes() Dim sp As Shape, n is used for each sp in the ActiveSheet. Shape if sp. Width & lt 14.25 or sp. Height & lt 14.25 and then' less than 0.5cm, set sp. delete n = n+ 1 end if next sp msgbox " * * * deleted " &； N&"object" terminator
Second, set the cell format or conditional format in a large range in the worksheet.
Possible reasons: In the process of operation, the cell format or conditional format is set or copied in a large area (not the whole row and column), but the actual area is not much, resulting in less worksheet content and large file.
Judgment method: the drag slider of the worksheet scroll bar is very small, and dragging the slider down can reach a large line number or column label. The column label or line number displayed by dragging the slider to the bottom or rightmost position is the address with the actual cell format set.
Solution: 1 Find the really needed line number of the next line, press CTRL+SHIFT+ DOWN ARROW, select all the redundant lines (you can also enter the line number in the name box, such as 2000:65536), and edit-clear-format (or all). Similarly, clear the format of redundant columns. 2. If you need to set uniform cells in a large range of rows or columns. You can choose to format the cells in an entire row or column, instead of selecting only a part of the rows and columns to format them separately, and then format individual cells in the rows and columns separately as needed. Formatting the cells in an entire column or column or even an entire worksheet will not cause the file to swell. Setting the cell format of A 1: A65536 is completely different from setting the cell format file of A 1: A65535 to be too small. 3. If you want to set a uniform format for cells with different formats for the whole column or row, you should select the whole row or column, clear the cell format or set the cell format to normal first, and then set other cell formats for the whole row or column. Direct settings sometimes do not necessarily make files lose weight. 4. Edit-Positioning can also be used for conditional formatting. Select Conditional Format in Location Condition, and then delete the conditional format in Format-Conditional Format. 3. Set the data validity of a large area.
Reason: It is basically the same as the second item. Selecting a large area to set data validity, or copying cells with validity to a large area, especially setting "Input Method", "Input Information" and "Error Prompt" in the validity setting is more hidden and generally difficult to find.
Judgment method: the file size has not been reduced after clearing the cell format in the redundant area, just like the reason why the file is bloated due to the cell format, so it is necessary to consider whether it is caused by the validity setting.
Solution: Select the range of redundant cells and data validity, and "clear all" on the pages of setting, input information, error warning and input method respectively.
Fourth, the formula is complicated and lengthy.
Because the cell formula is complex and too long, the file size will increase. Solution: For a large number of repeatedly used formulas (including relative references's formula), the original file in the following example is simplified by defining the name, and the file is reduced to 1 17K after defining the name.
Click to browse the file.
This method is sometimes very effective in reducing the weight of documents. I once compressed a file nearly 3M to over 700 K.
Fifth, caused by a bug in EXCEL itself.
Reference/dispbs.asp？ BoardID = 2 & ampID=6200 1
Reason: In the worksheet, select all cells and the rows are hidden. Enter the cell address of any line after 32,768 lines in the name box (for example, A32800), set a fixed line height (for example, 20), select all cells and the most appropriate line height, and the progress bar will begin to display. After the progress bar disappears, save the workbook with the file size of1.42m. No matter what method is used to clear the format (whether it is clearing the format, clearing all cells or overwriting with other worksheets), the worksheet cannot be shrunk. Suspected to be a BUG in EXCEL, which exists in EXCEL2000 and EXCEL 2003.
Solution: Select the whole worksheet, format-line-line height, set any line height, and save. At this point, the file returns to its normal size. Example: Click Browse File.
Add two more articles
File enlargement caused by the background of intransitive verbs worksheet
Because the larger picture is used as the background of the worksheet, the file is enlarged.
Solution: Do not use worksheet background unless it is absolutely necessary. If it is really necessary, the picture as the background should be as small as possible.
Seven, the format of the inserted picture in the worksheet affects the size of the file.
The pictures inserted into the worksheet are in BMP format, or copied and pasted into the worksheet directly from the drawing software.
Solution: convert BMP file into JPG format, try to adjust the picture to the size actually displayed in EXCEL in drawing software, and then insert it into the worksheet. Don't paste pictures directly into the worksheet.