Editing Troubleshooting of common Excel errors


== Troubleshooting of Excel errors ==

{{LanguageSwitcher | content = [[Troubleshooting of Excel errors | EN]], [[Troubleshooting of common Excel errors in Mandarin | 漢字]] }}

=== 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: [https://errerrors.blogspot.com/2020/03/resolve-too-slow-to-delete-data-rows-in-excel.html 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. {{exclaim}} 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. [https://support.office.com/en-us/article/Choose-between-the-64-bit-or-32-bit-version-of-Office-2dee7807-8f95-4d0c-b5fe-6c6f49b8d261 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 [https://zh-tw.libreoffice.org/discover/calc/ LibreOffice Calc] to operate ODS ([https://en.wikipedia.org/wiki/OpenDocument 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 [https://zh-tw.libreoffice.org/ 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).<ref>[https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3 Excel specifications and limits - Excel]</ref>

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 [https://workspace.google.com/intl/zh-TW/products/sheets/ 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.


== References ==
<references />

[[Category: Software]]
[[Category: Spreadsheet]] 
[[Category: Excel]]  
[[Category: Data Science]]