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.

Solutions

• 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.
• Older versions of Excel only provide general CSV options when saving (defaulting to Big5 encoding), which is the main cause of garbled text. Recommend using newer versions of Excel (such as Office 365), and selecting the "CSV UTF-8 (Comma delimited) (.csv)" option when saving.