Today we will analyze why Excel is "slow". How can I get your Excel started quickly?
Excel starts slowly.
If the startup is slow, there may be many reasons, as follows:
Plug-in loading
If too many plug-ins are installed for Excel, or some plug-ins are "too heavy", Excel will start slowly!
Every time you start Excel, there will be a startup interface. If you see "open: XXX" in the interface, it means that a plug-in is being loaded. If a plug-in takes too long, it proves that the plug-in loading causes Excel to start slowly.
Excel startup interface
Solution: Delete unnecessary plug-ins and manually enable infrequently used plug-ins as needed. Deleting plug-ins can be done by using the uninstall function of plug-ins. If you temporarily disable some uncommon plug-ins, you can uncheck them in COM add-ins or Excel add-ins first, and then uncheck them when necessary. This function is in the development tool, as shown in the following figure:
Manage add-ins in development tools
If you install some plug-ins, Excel can't start normally at all; Or even if you can enter Excel, you can't uncheck some plug-ins. At this time, you can try to start Excel in safe mode, and then uncheck the plug-in after starting.
Excel security mode startup: first press and hold the Ctrl key of the keyboard, then double-click the icon of an Excel program or an icon of an Excel file, and then a window will pop up to ask you. Select Yes to start safe mode.
Excel security mode
Another way to start the safe mode is to use the running function of the system, press Windows+R on the keyboard to bring up the running window, and then enter excel-safe.
Printer connection
When your Excel sets the default printer, Excel will try to connect it every time it starts. However, due to network failure, driver damage, printer replacement or shutdown, computer relocation and other reasons, Excel will disconnect the default printer at this time.
However, if Excel is still connected every time it starts, it will cause Excel to keep trying to connect to the printer until it finally times out, resulting in slow startup.
Solution: You can change the default printer in Excel. It is suggested to change it to some virtual printers. For example, "MicrosoftPrinttoPDF" in the picture below is a virtual printer. Or, if you go to repair the network and other problems, please make sure that you can connect to the default printer normally.
Excel default printer settings
Floating object
"Floating objects" refer to the contents that can be above the worksheet cells, that is, some objects that can be moved freely, such as shapes, text boxes, pictures, controls, charts, embedded files, etc.
If you copy and paste content from an application such as a website into Excel, it is easy to paste some hidden objects into Excel files, and too many floating objects will also lead to slow startup.
To determine whether there are floating objects in the Excel file, you can open the selection pane to view it. The shortcut key is Alt+F 10, and you can see the list of shapes in the current worksheet in the selection pane interface. Just select and delete unnecessary shapes.
Selection pane
To clear all the shapes in the current worksheet at once, press Ctrl+G, select positioning conditions, select objects, and then press Delete to delete them all.
0 1727
File is too large
Indeed, due to too much data in the file, the size of the file is very large, such as files of more than 20M or even hundreds of m. Starting these heavyweight files will definitely slow down Excel!
Give priority to reducing the file size by reducing data, such as separating the data source from the report, or splitting a large report into multiple small reports.
If the file is still large, you can try another method:
Save the Excel file as an extension. Xlsb format, and the file will become smaller immediately after saving, achieving one-second slimming! Moreover, saving files in this binary format will make the loading time faster, and files in this format can also store VBA codes, which is different from. Xlsx in daily use.
However, it should be noted that xlsb files cannot be opened in wps, or this format can not be uploaded to the company's system.
These situations are some common reasons for slow startup, and gradual elimination will help you solve the common slow startup problem of Excel.
Excel calculation response is slow.
After starting Excel, sometimes there will be a problem of slow response when inputting worksheets. I believe everyone is deeply touched. I have summarized some common reasons for your reference:
Cross-workbook reference formula
Many friends who make goods often have this situation. For example, the commodity file is listed in one file, and then the report file is the sales data table. If you need to match the property fields of goods, you often use formulas across workbooks.
Also check whether there are any defined names linked to other workbooks or temporary Internet files. Cross-workbook formulas in the name will also slow down the file response speed.
This cross-workbook formula is very slow to update. Try not to use it! If you must use, paste the data into a value after matching. You can keep the formula in the first row so that it can be filled when you need to refresh. There are other schemes that use PQ or SQL to make cross-workbook references.
A more variable function is used.
Volatility refers to a function or function that causes Excel to recalculate every time. For example, sometimes when we open a file, Excel will ask you if you want to save it, even though the contents have not been modified. This is because there are mutable functions or functions in the file.
* * Variable functions, such as rand, randbetween, indirect, offset, cell, info, etc. These suggestions should not be used too much; Still don't refer to the values of these functions too much.
Variable functions, such as conditional format, need to be evaluated by Excel every time, so any formula used in conditional format is actually variable. There are also some other functions that cause fluctuations, such as automatic filtering, adjusting row height and column width. When you perform these operations, the formula will be recalculated.
Too many formulas
Even if it is not a variable function, because the data in the file is too large, for example, when recalculating, it will start to react slowly. At this time, you can also use the method described above to paste all the recalculated cells into values as much as possible, leaving only the formula in the top row. When you need to update, double-click the quick fill formula.
Or during editing, switch to manual calculation mode first, and then press F9 manually when the formula needs to be recalculated.
Calculation options
At the same time, I also have a suggestion, please don't use array formulas too much in the worksheet. It is ok to use some simple array formulas occasionally, which can make the writing more concise. However, if you indulge in array formulas and write them more and more complicated, this situation will lead to too much redundant data in the calculation process and affect the response speed!
Moreover, writing complex array formulas is inconvenient for automatic expansion, difficult to maintain and easy to lose hair! If you think you must use array formula to solve it, it's time to start learning VBA and PowerQuery!
Occupy unnecessary cells
You can use Ctrl+End to switch to the last cell used in this worksheet to see if there is any abnormality, so as to verify the actual use range of the table. If there are abnormal cells, please delete the whole row or column of abnormal cells.
Upper performance limit of formula
If you don't encounter the above problems, but still feel slow, it is really in the traditional formula and pivot table, which has reached the upper limit of Excel performance. At this time, it is necessary to add new tools to improve the efficiency of data processing! For example:
SQL: used to replace cross-workbook reference files; Used to replace Vlookup and other functions.
VBA: Using VBA can avoid recalculation of functions and improve performance; Operating data in the form of VBA memory array is n times faster than operating worksheet! Of course, VBA can also achieve many amazing functions that traditional operations can't. The key is your creativity.
Excel bi: pq can realize richer functions than SQL and more efficient than formulas. PP is the best tool to deal with "big data" in Excel, and millions of rows of data can also respond quickly.
Hardware problem
I will talk about the hardware problem in the end, mainly because it may not be so easy to upgrade the hardware configuration in our work scene. It would be great if you already have a high-profile office computer.