For beginners] Explanation of causes of Excel being slow and countermeasures.

In this article, we will use our 20+ years of Excel experience to explain how to optimize files, efficient data management techniques, and even how to review settings in an easy-to-understand way for beginners.

This article will help you learn to use Excel lightly and work much more efficiently. Let's begin with a detailed look!

What causes Excel to be heavy?

Caused by large amounts of data

When large amounts of data are entered into Excel, the process takes time and the file becomes heavy. Especially if the data contains more than tens of thousands of rows or complex calculations, memory usage will increase, causing Excel to run slower.

Measures:

1. Split data into multiple sheets
2. Delete or move unnecessary data to another file
3. Summarize data using pivot tables
4. Consider using database software (e.g., Access, SQL) for large data sets

Impact of heavy functions and formulas

Excel has many powerful functions for performing complex calculations. However, the more functions there are, the longer it takes to perform a calculation and the lower the overall performance of the file. In particularVLOOKUPand ... andINDEX MATCHArray formulas, for example, can cause slow processing when there is a large amount of data or when the reference range is large.

Measures:

1. Use simple functions whenever possible
2. VLOOKUPinstead ofXLOOKUP(Excel 2019 or later)
3. Paste calculation results as values to reduce unnecessary calculations
4. Minimize the scope of formula references to the minimum necessary.

Presence of unwanted objects or images

If many images, shapes, and graphs exist in an Excel file, thefile sizecan become large and take a long time to open. High-resolution images and complex graphs in particular can be a major factor in making files heavy.

Measures:

1. Delete unwanted images and shapes
2. Compress images to the minimum necessary resolution.
3. Complex graphs should be made as simple as possible
4. Minimize decorative elements.

How to lighten the load of Excel

File Size Optimization

If an Excel file is heavy, it can be lightened by deleting unnecessary data or ranges in the file.

Optimization Procedure:

1. Delete unused sheets
2. Delete empty rows and columns
3. Clear formatting of unneeded cells
4. Delete unused named ranges
5. Save the file as a different name and use the compression option

Add-in deactivation procedure

Add-ins are useful tools that extend the functionality of Excel, but Excel can become slow if unused add-ins are installed.

Add-in deactivation procedure:

1. [Click on the "File" tab
2. [Select "Options
3. Click [Add-ins
4. [Select "COM Add-in" from the "Manage" drop-down list and click "Configure
5. Uncheck the unwanted add-ins and click "OK

How to clear the cache

Excel uses an internal cache toWe are trying to speed up the process.However, this cache accumulates when used over a long period of time,On the contrary, the operation becomes slow.There are times when

How to clear the cache:

1. Exit all Excel.
2. Open Task Manager and terminate all Excel processes running in the background
3. Delete files in the following folders
   • C:\Users[user name]\AppData\Roaming\Microsoft\Excel\
4. Restart Excel

How to deal with Excel files that won't open

File Repair Procedure

Even if an Excel file is corrupted and can no longer be opened, it may be possible to recover it using a repair tool.

File Repair Procedure:

1. Open Excel
2. [Click on the "File" tab
3. [Select "Open
4. Select the location where the file in question is located.
5. Right-click on the file, click the arrow next to "Open" and select "Repair

If this method does not repair the problem, try the following

• In another Excel file, select "Object" from the "Insert" tab and insert the corrupt file
• Open the file in a text editor to check and modify the data in XML format

How to start in Safe Mode

Dr. Yamada: "If Excel won't start properly, Safe Mode can help."

Mr. Sato: "Safe mode? What is that?"

Dr. Yamada: "Safe mode is a way to start Excel with minimal functionality. In a car, it's like the 'lean burn' mode where the car runs on the engine alone."

Safe Mode disables add-ins, etc., and allows Excel to run with minimal configuration, which helps narrow down the cause of the problem.

How to boot in safe mode:

1. Keyboard. Ctrl Hold down the key and double-click the Excel icon
2. When the message "Do you want to open in Safe Mode?" appears, click "Yes. Click "Yes" when the message "Open in Safe Mode?

If it works fine in Safe Mode, an add-in or a specific setting is most likely the cause of the problem. Enable them one by one to determine the cause of the problem.

Importance of virus checking

Dr. Yamada: "One of the causes of Excel files not opening is virus infection."

Mr. Sato: "What? Excel files can get viruses too?"

Dr. Yamada: "Yes, unfortunately that is true. Excel files can contain small programs called macros, which can be planted with malicious code."

Virus infection of an Excel file can affect your entire computer. Regular virus checks are important.

Virus checking tips:

1. Use reliable antivirus software
2. Keep antivirus software up-to-date
3. Perform full scan periodically
4. Do not open suspicious email attachments.
5. Treat files containing macros with special care.

If infection is suspected:

1. Disconnecting a Computer from the Network
2. Run a full scan with antivirus software.
3. Change Password
4. Consult with your IT department or an expert.

Excel's autosave feature and its impact

How to check auto save settings

Dr. Yamada: "Excel's autosave feature is convenient, but it's important to check your settings."

Ms. Sato: "Can I change the auto save setting?"

Dr. Yamada: "Yes, you can change it. Auto-save is a useful feature, but if it is done too often, it can get in the way of your work."

The auto-save function is a convenient feature that periodically saves your work, but it can also be a source of load depending on your settings.

How to check auto save settings:

1. [Click on the "File" tab
2. [Select "Options
3. [Click on the "Save" tab
4. Check the "Save information for autorecovery at the following intervals" checkbox and time setting

Recommended setting:

• 10 minute intervals for normal operations
• 15-20 minute intervals for large files or files containing complex calculations

Causes of heavy autosave

Dr. Yamada: "Frequent autosaving can slow down Excel."

Ms. Sato: "Why?"

Dr. Yamada: "Every time you auto-save, Excel has to save all the data and calculations. With large amounts of data and complex calculations, this can be quite burdensome."

The main cause of heavy autosave:

1. Large amounts of data (tens of thousands of rows or more)
2. Lots of complex formulas and functions.
3. Lots of graphs and figures.
4. Auto save intervals are too short.
5. Hard disk space is low

Measures:

1. Set a longer interval between automatic saves.
2. Delete unnecessary data and calculations
3. Put complex calculations on a separate sheet.
4. Ensure hard disk space is available.

Manual Save Recommendation

Dr. Yamada: "When dealing with large files, I recommend manual saving."

Ms. Sato: "Manual saving? But I'm worried about forgetting to save it..."

Dr. Yamada: "There is certainly that concern, but once you get the hang of it, you will be fine. Manual preservation has the advantage that you are in control."

Manual saving is a way to ensure that important changes are saved while avoiding the unnecessary burden of automatic saving.

Tips for manual preservation:

1. Always save any important changes you make.
2. Make a habit of saving at regular intervals (e.g., every 30 minutes)
3. Always save before taking a break.
4. Use the shortcut key (Ctrl+S) to save quickly
5. Save each work break as an alias and keep a work history

Ms. Sato: "I see. But I still might forget to save it..."

Dr. Yamada: "Yes, that's right. If you are concerned, you can use a combination of automatic and manual saving. Set a longer interval for automatic saving, but save important changes manually. This way, you can strike a balance between safety and efficiency."

Relationship between Excel version and optimization

Update to the latest version

Dr. Yamada: "Is your Excel version up to date?"

Ms. Sato: "Well, I have never checked..."

Dr. Yamada: "The latest versions often include performance improvements. Regular updates are important."

Updating to the latest version is one important way to lighten the load of Excel.

Advantages of the latest version:

1. Bug fixes: previous issues may be resolved
2. New additions: new tools are available to streamline your work
3. Improved performance: internal processing may be optimized and operation may be lightened
4. Enhanced security: protects against new threats

Update Procedure:

1. [Click on the "File" tab
2. [Select "Account
3. [In the "Product Information" section, click on "Update Options
4. [Click "Update" to install the latest version

Ms. Sato: "Isn't updating difficult?"

Dr. Yamada: "No, basically it is easy. However, we recommend backing up your data before a major update. So it's a matter of just in case."

Effects of old programs

Dr. Yamada: "If you continue to use an older version of Excel, not only will you not be able to use newer features, but it will also run slower."

Mr. Sato: "Really? But I'm a little afraid to change the version I'm used to using..."

Dr. Yamada: "I understand that feeling. But let's also consider the risk of continuing to use the old version."

Problems with older versions:

1. New features are not available: You may miss opportunities to improve operational efficiency
2. Possible performance degradation: compatibility issues with newer operating systems can slow things down.
3. Increased security risk: older versions are more likely to have security holes found
4. Compatibility issues with other software: it can be difficult to work with new software

Measures:

1. Update systematically: avoid sudden changes and get used to the new version gradually.
2. Get training: learn how to use new features to reduce transition anxiety
3. Prepare a test environment: Check in advance that problems do not occur in the actual environment.

Mr. Sato: "I see. I guess I'll just have to get used to it little by little."

Dr. Yamada: "That's right. It may seem confusing at first, but once you realize how the new features streamline your work, you'll enjoy it."

Version compatibility issues

Dr. Yamada: "Be careful when exchanging files between different versions of Excel."

Mr. Sato: "What? What's going to go wrong?"

Dr. Yamada: "Yes. When a file created with a newer version is opened with an older version, some functions may not be available, or the display may be corrupted."

Version-to-version compatibility issues are one of the unavoidable challenges of using Excel. Particular attention should be paid when working in teams or exchanging files with business partners.

Examples of compatibility issues:

1. New functions are not available: for example, the XLOOKUP function is only available in Excel 2019 or later
2. Formatting is broken: problems are especially likely to occur with conditional formatting and placement of shapes
3. Macros do not work: different versions of VBA may not work properly
4. Pivot tables behave differently: functions in newer versions may not display correctly in older versions

Measures:

1. When saving a file, save it in an older version format:
   • [File] → [Save As] → Select older version in File Type
2. Compatibility check:
   • [File] → [Information] → [Check Issues] → [Check Compatibility].
3. Open and check important files in different versions
4. Use the same version for the entire team, if possible
5. Refrain from using features that are prone to compatibility problems (consider alternatives if absolutely necessary)

Ms. Sato: "I see. But it sounds like a lot of work to have to be so careful..."

Dr. Yamada: "It may indeed seem daunting. But by making these measures a habit, many problems can be prevented in advance. And they enable us to work together smoothly."

Why are Excel functions so heavy?

Revision of calculation method

Dr. Yamada: "One of the reasons Excel runs so slow is the use of inefficient functions."

Mr. Sato: "What? I thought functions were useful..."

Dr. Yamada: "Functions are certainly useful. But depending on how they are used, they can be counterproductive. For example, using the VLOOKUP function a lot can make calculations take longer."

Reviewing calculation methods can greatly improve Excel's behavior.

Tips for efficient function use:

1. Use INDEX-MATCH or XLOOKUP instead of VLOOKUP
2. Minimize the use of array formulas
3. Use the SWITCH function instead of complex IF statements
4. Use SUMIFS or COUNTIFS instead of SUMIF or COUNTIF (for multiple conditions)

Mr. Sato: "Wow, so there are efficiencies and inefficiencies in functions."

Dr. Yamada: "That's right. By choosing the right function, we can get the same result faster."

Organize calculation sheets

Dr. Yamada: "If calculation sheets are not organized, Excel can become sluggish."

Ms. Sato: "Organize calculation sheets? How exactly do I do that?"

Dr. Yamada: "That's right. For example, if you leave unused formulas in place, or if data is scattered around, it puts an extra burden on Excel's processing."

How to organize calculation sheets:

1. Delete unnecessary sheets
2. Combine related calculations on the same sheet
3. Minimize the scope of data references
4. Paste calculation results as values to reduce unnecessary calculations
5. Utilize pivot tables instead of formulas

Ms. Sato: "I see. So it's important to be organized."

Dr. Yamada: "That's right. An organized calculation sheet not only makes Excel run lighter, it also makes you more efficient."

Delete unnecessary functions

Dr. Yamada: "Unused functions or duplicate calculations can cause Excel to run slower than it should."

Ms. Sato: "What? I thought that unused functions could be left alone..."

Dr. Yamada: "No, no, even with functions you don't use, Excel tries to calculate them. When these pile up, it becomes a huge burden."

Procedure for removing unnecessary functions:

1. [On the "Home" tab, select "Search and Select" → "Formulas
2. Review all formulas and delete or replace with values what is not needed
3. Delete unused items in the named range
4. Minimize use of the INDIRECT function (this function is particularly processing heavy)
5. Review SUBTOTAL functions applied to large ranges

Mr. Sato: "I understand. But it seems difficult to determine which functions are necessary and which are not..."

Dr. Yamada: "Indeed, it may seem difficult at first. But it is important to always be aware of the purpose of the file and think about whether each function is really necessary. Once you get used to it, you will be able to make decisions naturally."

By implementing the above measures, you can greatly improve the behavior of your Excel files and increase your work efficiency. Excel is a very powerful tool, but it is important to use it properly. Optimize your files regularly to maintain a comfortable Excel environment.