Troubleshooting of common Excel errors

Troubleshooting of Excel errors

🌐 Switch language: EN, 漢字

Excel Performance Issues

• Alternative solutions when dealing with large datasets
  • When Excel data contains approximately one million records, operations may take several hours. Consider using a database instead. Process data in the database first, then export to Excel format, rather than performing data processing or calculations directly in Excel.
  • When deleting approximately 10,000 rows that meet specific criteria is too slow, filter for rows that meet different criteria, then copy and paste to a new worksheet - this will be faster. If direct copy-paste also takes more than 5 minutes, choose to paste values only. Related article: Resolve too slow to delete data rows in Excel
• Reducing data processing complexity
  • Select all worksheet cell data, copy, then paste special values to a blank worksheet. Removing formulas will speed up operations on the new worksheet. Note that time format values may be corrupted and become long strings of numbers, requiring additional cell formatting to time format.
  • Multiple filter criteria consume more system resources. Instead of using filter criteria, using functions for processing will be faster.
• Computer performance
  • Upgrading software from 32-bit to 64-bit version enables more efficient memory usage. e.g. Choose between the 64-bit or 32-bit version of Office - Office Support
  • Close unused Excel files to conserve computer performance
• Switching to different software
  • First use LibreOffice Calc to operate ODS (OpenDocument Spreadsheet) or Excel XML file formats, which process faster. Then export to Excel file format.

Excel CSV Import Field Misalignment

When Excel opens CSV files, field misalignment occurs

Cause:

• If CSV file field values contain line break symbols (Return symbol), Excel will display errors when opening: what should be the same field gets split across lines and becomes a second data record.

Solution:

• Use LibreOffice to open the CSV file, then convert to Excel format. Recommend opening the file with Office Excel again to confirm there are no issues.

This text file contains more data than can fit in a single worksheet

When importing CSV files into Excel, encountering "This text file contains more data than can fit in a single worksheet."

Cause:

• CSV file data records exceed the maximum number of records that a single worksheet can accommodate: 1,048,576 (rows).

Solution:

• Limit the number of records

Resolving Excel File Save as CSV Causing Garbled Text

Problem

When saving Excel files containing text in other languages as CSV files, the content becomes garbled.

Root cause

Older versions of Excel only provide general CSV options when saving (defaulting to Big5 encoding), which is the main cause of garbled text.

Solutions

There are three solution methods

• Upload the Excel file to Google Sheets, then download as CSV file. CSV files exported from Google Sheets will be UTF-8 encoded, avoiding garbled text issues.
• Using the Open-source software LibreOffice to open the Excel file, then when saving the file, select "Text CSV (.csv)" format and choose the "Character set: Unicode (UTF-8)" option.
• Using newer versions of Excel (such as Office 365), and selecting the "CSV UTF-8 (Comma delimited) (.csv)" option when saving.

Resolving "Support 32767 characters in a cell only" Error When Exporting Database Data to Excel Files

Problem Description

When exporting data from database to Excel files, the error message [ERR] Support 32767 characters in a cell only appears.

Root Cause

Excel cells have a maximum capacity of 32,767 characters . When a field in a database record contains content exceeding this limit, the [ERR] Support 32767 characters in a cell only error message occurs.

Solution

This can be resolved through the following two approaches : 1. Use file formats that do not have the 32767 character limitation 2. Use the SUBSTRING function to reduce content length

MySQL Query That Causes the Error

SELECT 
  title, 
  content
FROM my_column

Corrected MySQL Query

SELECT 
  title, 
  SUBSTRING(content, 1, 32767) AS content
FROM my_column