In addition to the macro virus of the workbook itself (how to identify the macro virus is explained at the end of this article), there are several reasons that may lead to the abnormal expansion of the workbook.

One. The format in the worksheet is too complex (such as multiple fonts, multiple colors, conditional formats, etc.). )

As we all know, a worksheet has 65536*256 cells, which is a very large range. When we apply some formats, we often apply them directly to the whole row or column, or to multiple rows and columns for convenience, which is actually inappropriate. I believe that few people will use the whole column, or the whole row. After a large number of unused cells are added with certain formats, Excel does not always know that this cell is unnecessary, but often includes these cells in the calculation. In addition, the diversity of formats (including fonts, colors, etc. It is not difficult to understand that it will inevitably lead to the enlargement of Excel files.

Use as few formatting styles as possible, such as fonts and colors. After all, the advantage of Excel is not the display of documents and layouts (that is the strength of Word, PowerPoint and so on. )

Delete "blank" cells. Find the cell in the lower right corner of the worksheet (please note: unless you are confident, please don't rely too much on Excel's "positioning" => I mean you can find this cell by hand. Then select all the columns to the right of this cell, and then execute the menu command: Edit = > Clear = > All. Next, select all the rows below the cell, execute the same menu command: Edit = > Clear = > All, and then save.

Two. There are many pictures or other drawing graphics, or the format of the pictures is not suitable.

Choose the appropriate picture format There are generally three picture formats referenced in the file, namely BMP, JPG and GIF. The picture saved in BMP format has high fidelity, but its size is often several times to dozens of times that of the other two formats, while photos and scanned pictures saved in GIF format are very distorted. Therefore, it is recommended to save the picture in JPG format first, and then import it into the document. The picture size of this format is not only much smaller than that of BMP format, but also smaller than that of GIF format, and its fidelity is almost the same as that of BMP format.

Use "insert" to introduce pictures.

There are two ways to insert pictures. The first method is to save the picture as a JPG file. Select Insert → Picture → Open the Insert Picture dialog box from file, select the picture file to be inserted, and then click Insert. The other is to open this JPG file with "Drawing" or "Microsoft Picture Editor", select and copy it, and then click "Paste" in the document to insert it. It is strongly recommended that you adopt the first method. Although the image quality obtained by the two methods is the same, the document size formed by the first method may be dozens of times smaller than that by the second method!

Completely delete graphic objects: for example, some graphics we drew ourselves, for example, if you don't use them now, you can't delete the range of rows or columns if you choose to delete them, and it may just be reduced. At this time, you can use a method mentioned in this edition of Wandering Wind.

1. First find one of the text boxes (if you can't find one, add one yourself).

2. Select this text box and press F5- > Positioning Criteria-> Object-> of course

3. Press the Delete key to clear.

4. Save and close

5. Look at how big the file is now, and open it to see if it is still slow.

Three. Too many formulas and names, or too many cell ranges referenced by formulas, names, pivottables, etc. It's too big.

For reasons similar to the second point, when we define a name, write a formula, and specify the data source of the PivotTable, we often specify an excessively large cell range for convenience. For example, in column A, there are 10 data including the title (A 1: A 10) with the title "Name". Now we need to define a name, such as "name". Many people will specify the first line of = > by inserting = > Name = >, which is of course convenient, but in this case, can you imagine the difference between the two?

At this time, some friends will say: I did this because my data is increasing. I don't want to change my name every time.

Of course, you are right. Who would want to do that?

When I was sure that the range of the name I defined could not be fixed, I adopted a method called "dynamic naming". Sounds familiar, doesn't it? Please look at this example: for simplicity, we assume that data is added continuously after A 10, which means that we hope that when adding to A 15, this "name" points to A2: A 15 as I wish, and all this is done automatically. Then you can "insert" = > "name" = >; In the Definition dialog box, find Name, then change the reference location to = offset ($ a $2 2,0,0, Counta ($ a: $ a)- 1, 1), and click Add. Please stay and don't close this dialog box. Now, you can put the mouse in the Reference Location box to verify the results you want. Did you get a look at him? The blinking area in the worksheet indicates the cell range referenced by the current Name.

This is just a simple demonstration. Using this technology, we can get the results we need in the most economical way.

In formula references, you can use similar techniques when specifying the data source of a PivotTable report. Of course, I don't suggest you write the formula = COUNTA (offset ($ a $2,0,0, CountA($ a:a)- 1, 1) like this, but I suggest you define the name first, and then write the formula like this: = Counta (name).

This effect is more obvious in the array formula. Unless you have enough patience and courage, please don't use too many references in the array formula, especially those unnecessary cells.

Please note that the above check should be made for all worksheets in the workbook, including hidden worksheets.

Four. VBA code, especially the influence of user forms.

Now many friends have learned to use VBE to build their own projects. What an exciting thing! But what we are going to discuss today is how to deal with some possible side effects of VBA projects and how to compress them.

Many tests have proved that user forms will be one of the main reasons for increasing the file size. Moreover, rewriting the code of the module in the project repeatedly will increase the file size more or less (we can understand that rewriting the engineering code repeatedly will always leave some traces and fragments)

For this problem, you can consider exporting all modules and forms as files, then saving them and importing them in turn.

As for user forms, developers should consider: Is it really necessary to use some form? For a simple example, if you just accept the user's input value, you can use the InputBox method or property without using a special form. At the same time, the worksheet itself can be used as an interface for user interaction and should be fully utilized.

Five. The influence of external links (especially dead links)

Sometimes, our workbook may contain some external references so that we can * * * enjoy some information from other workbooks. If your workbook contains external links, you can click Edit = > Links dialog box. By default, when a file is opened, it always tries to link the source file to refresh the data. When saving, the link changes will be recorded.

Dead links may appear when the location or content of the source file changes.

Personally, I strongly object to using link to share data between different files. This is not the most convenient method, and there will be some problems in the process of file distribution. Of course, I know that data sharing is meaningful, but I often try to achieve it in other ways.

Six. Considerations about automatic saving options

When this option is selected, only the modified part of the document will be saved each time, which is faster, but the size of the document will also increase, even if the document is deleted. At present, the speed of the computer is relatively fast, and the speed increase brought by turning on this function is not obvious, but the price paid is the sharp expansion of the document size. This option is not recommended. Try turning off this option, slightly modifying the document, and then saving it. You will be surprised to find that the size of the document will be greatly reduced!

Seven. The abnormal exit of the file (or other unforeseen reasons) causes the worksheet structure in the workbook to be damaged.

Sometimes, due to some unforeseen reasons (such as power failure), Excel will be forced to quit abnormally. Although there is no special tool to detect the damage that this situation may cause to the inside of the workbook, there is reason to believe that it has an impact.

If your file does not have the above problems, and you are still sure that the file size is not normal, you can try the following methods.

Create a new workbook and cut the worksheets in the existing files into the new workbook one by one. Note that the Cut method is used here, not Move or Copy Sheet or Copy. Yes, that is, first select the contents in the worksheet, then cut, move the cursor to the target worksheet, and then paste. The only drawback is that the row and column formats of the target worksheet may need to be slightly adjusted.

-

How much data should a 60M file have? It is estimated that this weight loss project is tiring enough ~ ~ ~ I haven't practiced any of the above methods, and I don't know how the effect is.