How to Lose weight in Excel (related skills and precautions)

Part 1: how to lose weight in an Excel Workbook
Apart from the macro virus in the workbook itself (described at the end of this article on how to identify the macro virus), the following causes may cause abnormal expansion of the workbook:

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

Me
We all know that a worksheet has 65536*256 cells, which is a very large range. When we apply some format settings, we usually apply them directly in the whole row, whole column, or multiple rows and
It is not appropriate to apply multiple columns. I believe that few people will use full columns or full rows. After a large number of unused cells are added with some formats, Excel does not always know that this cell is
These cells are usually included during calculation. In addition, the diversity of formats (including fonts and colors) will inevitably lead to a larger size of Excel files. 1. Use a small amount of format styles, such as fonts and colors. After all, the advantage of Excel is not the presentation of documents and la S (that is the strength of word and PowerPoint)
2.
Delete the blank cell. Find the bottom-right cell in the worksheet (Note: Do not rely too much on Excel unless you are confident
"Positioning" => "last cell"), I mean you can find this cell manually. Use the DELETE command of the entire line and entire column, and press Ctrl + end to locate
There is the last cell of the Data. delete all the cells and save them. In practice, when someone presses Ctrl + Shift + down, the entire cell is selected and the formula is filled, as a result, the capacity of the entire worksheet increases, so please note that.

II. There are many images or other drawing images, or the image is incorrectly formatted.

Select the appropriate image format
　　
In
There are generally three types of image formats referenced in the document: BMP, JPG, and GIF. The image stored in BMP format has a high fidelity, but the size is usually several to dozens of times the size of the other two formats, while the photos and scanned images
If the image is saved in GIF format, the distortion is very serious. Therefore, it is recommended that the image be saved in JPG format before being imported into the document. The size of the image in this format is much smaller than that in BMP format, in addition
The GIF format is small, while the fidelity is similar to the BMP format.
　　
Introduce images using "insert"
　　
There are two ways to insert an image. The first is to save the image
Save as a JPG file, select "insert → image → from file", open the "insert image" dialog box, select the image file to be inserted, and click "insert. The other is to use "Drawing ",
"Microsoft photo editor", etc. Open the JPG file, select and copy it, and click "Paste" in the document to insert it. We strongly recommend that you use the first method.
The image quality is the same, but the size of the document formed by the first method may be dozens of times smaller than that of the second method! Some people are afraid of trouble, but this result brings a lot of benefits, that is, to make your table smaller. it is worth doing. clear graphic objects completely: for example, some images we have drawn by ourselves. For example, if you do not want to use them now, you can choose to delete rows or columns, and the cleanup will not be cleared, they may just narrow down. At this time, we can use a method mentioned in this version "Wandering wind ".

1. Find a text box first (add one if it cannot be found)
2. Select the text box and press F5 ---> positioning condition ---> Object --> OK
3. Clear by Delete
4. Save and close
5. Check the size of the current file. open the file and check whether it is slow.
3. The formula and name are too many or the range of cells referenced by the formula, name, or PivotTable is too large, or the formula is converted to a value.
Data that is no longer used or cannot be used for a long period of time, especially when referenced by a formula link, can be converted to a numerical value for storage, avoiding a large amount of formulas occupying the table memory space, this is also the main cause of slow running speed. For example, when you connect to the network, the path may be too deep to be saved.
Select the formula area to be converted, right-click and copy it, do not move the mouse, right-click and select-select and paste it, and select a value in the displayed dialog box. In this way, the formula is converted to a value, which greatly reduces the table size.

By
The reason is similar to the second one. When we define a name, write a formula, and specify the data source of the pivot table, the graph is usually convenient, and the large cell range is specified. For example, if column A contains a title
10 data records (A1: A10) with the title "name". Now we want to define a name, for example, "name ".
Insert => name => specify => the first line, which is convenient, but in this case, the name "name" references A2: a65536 instead of the actual A2: A10. You can imagine the difference between the two.
Don't you?
At this time, some friends will say: I am doing this because my data is constantly increasing. I don't want to change this name every time I change it.

Of course, it's right. Who would want to do this?

When
When I'm sure that the range referenced by the name I defined cannot be fixed, I use the "dynamic name" method ". Sounds a little familiar, right? For the sake of simplicity, let's assume that
The data is continuously added after A10, that is, we hope that when we add it to A15, this "name" will point to A2: A15 as I wish, all of this is done automatically. Then you
You can find "name" in the "insert" => "name" => "Definition" dialog box and change the reference location to = offset ($ A $, 0, 0, counta ($:
$ A)-), and then click "add ". Please stay in the step. Do not rush to close this dialog box. Now you can place the cursor in the box of "reference position" to verify the result. See
? The flashing area in the worksheet indicates the range of cells referenced by the current "name.

This is just a simple demonstration. Using this technique may make us get the results we need in the most economical way.

Similar techniques can be used to specify the data source of a pivot table in formula reference. Of course, I won't recommend you write a formula like this = counta (offset ($ A $, 0, counta ($ A: $ )), instead, we recommend that you define the name first, and then write the formula as follows: = counta (name)

This effect is more obvious in the array formula, unless you have enough patience and courage, please do not reference a large cell range in the array formula, especially those unnecessary cells.

Note that the above check should be for all worksheets in the workbook, including hidden

4. VBA code, especially the influence of user forms (VBA is not familiar with it)

Now many of my friends have learned how to use VBE to build their own projects. This is an exciting thing! But today we will discuss how to deal with the side effects of the VBA project and how to compress it?
Many tests have proved that user forms are one of the reasons for increasing the file size. Repeatedly rewriting the module code in the project also increases the file size more or less (we can understand this: repeatedly rewriting the project code will always leave some traces and fragments)

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

As a developer, we should consider whether it is really necessary to use a user form? For a simple example, if you only want to receive user input values, you can use the inputbox method or attribute instead of using a form. At the same time, the worksheet itself can be used for user interaction interface and should be fully utilized.

5. Influence of external links (especially dead links)

Yes
The workbook may contain some external references, so that we can share some information about other workbooks. If your workbook contains external links, you can use "edit" =>"
The link is displayed in the dialog box. By default, when a file is opened, you always try to link the source file to refresh the data. When saved, the link changes are recorded.

When the location or content of the source file changes, dead links may occur.

I personally do not advocate the use of links to share data between different files. This is neither the most convenient method, but may encounter some problems during file delivery. Of course, I know that data sharing is meaningful, but I often try other ways to achieve it.

Sat. Considerations about automatic saving options

Select
In this option, each time you save a document, only the modified part of the document will be saved, which is faster, but the size of the document will also increase, even if you delete the document. Current computer speed
The speed of enabling this function is not obvious, but the cost is the sharp expansion of the document size. We recommend that you do not select this option. Close this option and then make some modifications to the document. However
After saving, you will be surprised to find that the document size will be greatly reduced! However, when performing this operation, please note: whether your computer is equipped with an ups uninterrupted power supply. If the power supply is disconnected without notice, data may be lost. Therefore, it is very important to press Ctrl + S to save the file from time to time. I often do this, and we recommend that you do this.

7. Abnormal exit of the file (or other unforeseen causes) causes damage to the worksheet structure in the workbook
Sometimes, Excel is forced to exit abnormally due to unforeseen reasons (such as power outages. Although there is no dedicated tool to detect the extent of possible damage to the workbook, there is a reason to believe that there is an impact.

If your file does not contain the problems mentioned above and you are sure that the file size is not normal, you can try the following methods:

New
Create a workbook and cut the worksheets in the existing files to the new workbook one by one. Note that the "cut" method is used here. It is neither "moving or copying a worksheet" nor "copying ". That's right. Select first.
In the worksheet, and 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 adjusted slightly.

8. For tables with large tables in the company, you can separate workbooks.

This method can be used when the worksheet size is large, for example, 10 MB or more or the running speed is slow. However, since the separated workbook becomes a worksheet, the number of workbooks will increase constantly.
Folder categories are recommended. 3 workbooks in a table are 30 mb, separated into three worksheets. If each Workbook is 10 MB, the split worksheet is 10 MB and 10 MB.
You can try the difference from running 30 mb. In the company, I asked them to try to separate large tables as much as possible.

________________________________________
Part 2: How to accelerate Excel

Several aspects that may affect the Excel Running Speed

1.
Is the device configuration reasonable? Especially in Office 2003, the function is indeed very powerful, but the corresponding hardware requirements are also improved. Common users use Windows
Windows XP system, and then install Office 2003
Of course, there are other software. Depending on the configuration of your computer, you may choose to install only some (not all) Office components and tools.

2. Load macros.
Excel has several built-in macro sequences, which can help us implement some advanced functions. In addition, we can also write it on our own, or we can find more types of loading macro sequences on the Internet. Add
When loading a macro, I can give the following suggestions: first, make sure that the source of the macro is correct? We strongly recommend that you use anti-virus before the installation. Second, load it when you use it, instead of insisting that all
Load all macros. Currently, anti-virus software is usually installed in the office.

3. This option is automatically retained. These two options give us a certain degree of convenience.
Benefits. However, in some cases (for example, when there are many formulas), especially automatic re-calculation, may lead to slow operation. As mentioned above: the automatic save option is also a factor that may cause file increase. Selective
To close these two options when operating some files, which may be helpful for running speed. (However, pay attention to the data changes after the recalculation is not set)

4. Iteration calculation options. Out
For special requirements, we may allow the workbook to perform Iterative Computing, that is, circular reference (this kind of purposeful circular reference can be used to solve simultaneous equations and other special situations ). However, this loop reference will export
As a result, the worksheet is frequently calculated. When the worksheet changes anywhere, it is re-calculated. Therefore, this is also one of the reasons that affect the Excel speed. Do not check this option.

5. The file is abnormal (mainly due to some situations mentioned in the first part of this article ). This solution will not be described in detail.

6.
Improper use of other external tools may cause problems. For example, some beta software is like the current vsto 2005 beta2
There are some tools for programming excel. Some friends (Please note: It is only possible) will find that after the tool is installed, or even after the tool is uninstalled
Files, especially files with internal macros, are very strange to "eat" the memory. Specifically, Excel will occupy the memory frantically, and thus cannot work properly. In this case, there is a relatively simple
The method is to use: Help => detection and repair
There are two options: Restore the shortcut when fixing; discard the custom settings and restore the default settings. If both options are selected, or at least the second option is selected, this problem can be solved after the repair is completed.
Problem. Note: Before performing this operation, make sure that you are aware of the problems that may result from this operation (for example, the personal data file in outlook may need to be re-specified: the file will not be lost,
Before restarting outlook, go to Control Panel => specify your personal data file in the mail box ). We strongly recommend that you read the help documentation first.
________________________________________
Part 3: How to identify macro viruses?

In
In The VBE (Visual Basic Editor), activate the project resource manager and locate the workbook you want to check.
Check the code in all the modules in the workbook (including the thisworkbook module) and check whether there is any unfamiliar VBA code. Virus code is often incorrectly formatted and contains a large number of strange variable names
Name. Another option is to use commercial antivirus software.

To ensure security, we recommend that you set the macro security level of the office to an intermediate or higher level (Note: if it is set to advanced, you will not receive any notifications, excel automatically disables all macro operations)

________________________________________

Part 4: Conclusion

1.
As I mentioned more than once in newsgroups, Excel is not designed to store data. Therefore, it is not recommended to store too much data in Excel. Work allowed by the Excel Workbook
There is no limit on the number of tables (excel2003), but I recommend no more than 10 worksheets. Even if some of the problems mentioned in the first three parts are solved, and the data volume is so large
The speed must be slow.

2. Before you decide to follow the methods mentioned above, we recommend that you keep a file for backup to a safe place.