Excel Workbook Optimization Tips

Excel Tutorials

1 A large number of small graphic objects exist in the worksheet

If a large number of small graphics objects exist in the worksheet, the volume of the file may increase without the user's knowledge, which is a common "Excel obesity". You can use the following two methods to check for this symptom in an Excel worksheet.

(1) Press the button on the worksheet to bring up the "Locate" dialog box, click the "Locate Condition" button, select the object item in the "Locate Condition" dialog box, click OK, as shown in Figure 1. Then see if there are many selected objects displayed on the worksheet.

If you have more than one worksheet in your workbook, you need to find it in each worksheet in this way. For more tips on the "positioning" feature, see Tip 28.

Note: Hiding objects in a column or row cannot be seen using this method.

(2) using VBA to count the objects in the workbook to see how many objects actually exist on each worksheet, if the number is unreasonable, there is a problem. Press to open the VBA Editor window, click the menu insert → module to insert a new module, by default, Module 1, and then enter the following code in Module 1 's Code window:

Subcountshapes ()

Dimnasdouble

Dimwsasworksheet

Dimcontentasstring

Foreachwsinworksheets

N=ws. Shapes.count

content=content& "Worksheet" &ws. name& "have" &n& "objects" &vbcrlf

Next

Msgboxcontent

Endsub

Finally, by pressing the F5 key to run this code, you can see the results of the inspection. In a seemingly blank worksheet, there are a large number of drawing objects, as shown in Figure 2.

If you confirm that there are a large number of objects in your worksheet that users do not need, you can handle them in two ways:

(1) Just in the positioning method, when the object is all in the selected state button to delete them.

(2) Use macros to remove these unwanted objects more precisely in multiple worksheets. For example, you can delete objects that are less than 14.25 points (0.5cm) in height and width as needed.

The code to delete only objects of a specific size on the active worksheet is:

Subdelshapes ()

Dimspasshape,n

Foreachspinactivesheet.shapes

Ifsp.width<14.25andsp.height<14.25then

Sp. Delete

N=n+1

EndIf

nextSP

MsgBox &n& Objects deleted

Endsub

The code for deleting objects of a specific size in all worksheets is:

Subdelallshapes ()

Dimwsasworksheet

Dimspasshape

Dimnasdouble

Dimcontentasstring

Foreachwsinworksheets

Foreachspinws.shapes

Ifsp.width<14.25andsp.height<14.25then

Sp. Delete

N=n+1

EndIf

Next

content=content& "Worksheet" &ws. name& "deleted &n& Object" &vbcrlf

N=0

Next

Msgboxcontent

Endsub

The causes of these puzzling objects may be the following. Paste directly into a worksheet after copying content from a Web page, instead of using paste selection. By inadvertently using the Drawing toolbar's line tool or other drawing tool, you unconsciously insert a small line or other graphic object into the worksheet, which is small and almost invisible to the naked eye. Then, a large number of small drawing objects are produced by copying the cells. A picture or other drawing object is inserted in the worksheet, and the height of the operation is set to a value of 0 or less, and a large number of objects are generated by copying.

A drawing object is inserted in the position of the row or column, with the property of the object "changed by size (default)", then the row or column is hidden, or the row height or column width is small, so that the inserted object cannot be seen. The objects in the worksheet are set to an invisible property (Visible=false), or the line and fill color of the object is the same as the background color, so that the object cannot be seen.

2 worksheet format or conditional formatting in a larger range

Carefully observe the worksheet scroll bar, if the slider is very small, and drag the slider down to the end, you can reach a large line number or column label, but the actual use of the worksheet area is very small, as shown in Figure 3. This shows that a large area may be formatted or conditional format, these are not used cells, can have a large impact on the size of the file.

The solution is as follows. Click to the next line of the line number that you really want, press the key combination, select all the extra rows (or enter the line number in the Name box, such as 2000:65,536), click the menu edit → clear → format (or all). Similarly, you can also clear the formatting of extra columns.

Sometimes, a user does need to preset formatting for a blank area in a worksheet for future data, but use the correct method, or it can result in a large number of unused areas being preformatted and file volume.

If you want to format a uniform cell in a large range of rows or columns, you can choose to format the entire row or column of cells instead of selecting only one part of the row to format separately. The former does not cause the file volume to increase the problem, the latter will add file volume. Try it out. In two Excel files, the cell format and cell format are set for the a1:a65536, and the purpose is to set the format for column A, but the final file is 100 times times larger than the size.