How to Do Excel Encoding (Check and Change)

In this Excel tutorial, you will learn how to
– Check the encoding of an Excel file
– Change the encoding to UTF-8 using the web option
– Change the encoding to US-ASCII by applying the text import wizard
– Set character encoding while opening a CSV file

We have used Microsoft 365 to prepare this tutorial. You can use Excel 2021, 2019, and 2013 as well.

The texts or symbols we see on our computer screens are stored in different forms on the back end. The numerical values are stored with some special characters with the help of encoding. If your Excel file contains multilingual data, it requires a specific encoding to accurately represent all languages used in the document. Different languages use different character sets and encodings. You have to select the appropriate encoding for displaying text correctly. Excel supports different character encodings. For example, Unicode encodings like UTF-8, ASCII, and ANSI.

Download Practice Workbook

How to Check Encoding of Excel File?

When you import or open an Excel file from another source that contains text data, check the encoding to ensure that the text is interpreted correctly.

Step 01: Change File Extension

• To begin, select the Excel file.
• Then, click on the View option.
• Select Show and click on the File name extensions option.

• Thus, you can see the file extension right after the name of the Excel file and its .xlsx. You have to change it to a zipped file (.zip).

• Now, rename the file extension to .zip from .xlsx. Therefore, the file has been converted into a zipped file.

Step 02: Open the ZIP File in Windows Explorer

• Right-click on the zipped file and select Open with option from the Context Menu.
• Afterward, open the file with Windows Explorer.

Step 03: Display Worksheets Folder

• At this time, you can see several folders. From these, select the folder named xl.

• From the folder xl, select worksheets folder.

• Hence, you will find the xml files.
• Now, right-click on the sheet for which you want to check the encoding. Here, I have selected sheet 3.

Step 04: Open XML File in Notepad and Check the Encoding

• Finally, open xml in Notepad and check the first line.
• The first line contains encoding= “UTF-8”. It means the encoding of sheet3 is UTF-8.

How to Change Encoding in Excel?

You can certainly change the encoding of an Excel file. If your Excel file contains text in a language that uses a different encoding than the default encoding of your system, the file will not display the text correctly. So, in this case, you have to change the encoding of the file to display the characters properly.

1. How to Use Web Options to Change Encoding to UTF-8

Use UTF-8 when your text data contains characters from multiple languages or character sets. UTF-8 is a Unicode encoding that can represent virtually all characters from all languages. Again, when you are sharing text data with others, especially across different countries or regions, UTF-8 is a safe choice to prevent character encoding issues. 

I have the following dataset in an Excel sheet. I will change the encoding of this sheet.

• Click on the File menu of the Excel file.

• Then, you will find Options at the bottom. Select Options.

• From Excel Options, choose Advanced.
• After that, scroll down and select the General option.
• From General, click on Web Options.

• Therefore, the Web Options dialog box will open.
• Now, select Encoding.
• From “Save this document as” list, choose Unicode (UTF-8) and press OK.

• As a result, you have changed the encoding of this sheet to UTF-8.

2. Apply Text Import Wizard to Change Encoding to US-ASCII

You can use US-ASCII when your text data contains only basic Latin characters (A-Z, a-z), numerals (0-9), and common punctuation marks. US-ASCII is a simple 7-bit encoding. It requires smaller file sizes compared to UTF-8.

Following is an image of a text file that I will use to demonstrate the steps of changing the encoding to US-ASCII.

• Firstly, click on the Data Tab.
• Then, select From Text/CSV.

• At this moment, select the text file and click on the Import button.

• From the dropdown, scroll down and select US-ASCII.
• Press the Load button at the bottom right.

• Thus, I have imported the text file to the Excel file in US-ASCII encoding.

How to Set Character Encoding While Opening a CSV File in Excel?

If your CSV file contains special characters, or characters from languages other than English (e.g., é, ü, ç), you should set the character encoding to ensure these characters are displayed correctly.

• First, open the Excel app and click on Open.

• Now, select the CSV file.

• Consequently, the Text Import Wizard dialog box has appeared. It has 3 steps.
• First, in File Origin, select Unicode (UTF-8).
• Then, click on Next.

• Secondly, from Delimiters select Comma and press Next.

• Finally, in column data format, select General >> then press the Finish button.

• Since the character encoding of the CSV File has been set while opening in Excel.

Things to Remember

• Excel supports various data types, including text, numbers, dates, and formulas. Make sure to select the appropriate data type for each column.
• UTF-8 supports a wide range of characters from various languages. However, some characters may require multiple bytes to represent. So use the correct characters to ensure accurate encoding.

Frequently Asked Questions

1. What is the UTF-8 format?

Answer: UTF-8 (Unicode Transformation Format-8) is a character encoding standard. It is widely used for representing Unicode characters in digital systems. It is capable of encoding all possible characters in the Unicode standard. The UTF-8 format uses variable-length encoding. It means different characters require a different number of bytes to represent them. It can encode characters from the ASCII character set as well.

2. Does Excel use UTF-8 encoding?

Answer: Excel supports multiple encodings. It includes UTF-8 also. The default encoding used by Excel depends on the version and configuration of the software. In older versions of Excel, prior to Excel 2013, the default encoding for Excel was ANSI (Windows-1252). This encoding does not support all Unicode characters. From the 2013 version, the default encoding for Excel has been changed to UTF-8.

3. What encoding does Excel use?

Answer: The default encoding for new workbooks in Excel is UTF-8. But while opening or saving a file in Excel, users can choose the encoding they want to use. Excel provides a variety of encodings, including UTF-8, UTF-16, US-ANSII, and others.

4. Is Excel XLSX or XLS?

Answer: Excel uses the XLSX file format for newer versions. XLSX is the default file format for Excel 2007 and onward. The XLSX file format is based on the Office Open XML standard. It is an open, XML-based format for office documents. XLSX files contain various XML files to store data, formatting, formulas, and other information. On the other hand, the XLS file format was used in older versions of Excel. XLS files are binary file formats. It stores data, formatting, and other information in a proprietary binary structure.

Conclusion

I hope this article has helped you with all the problems related to encoding. Your Excel files may contain special characters, symbols, or non-standard characters that are not supported by the default encoding. In that case, check and change the encoding. I showed you the steps of checking encoding and changing it to UTF-8 and US-ASCII. By following the given steps, you can easily check and change the encoding in an Excel file with any type of data you want. Please leave a comment if you face any problems regarding this topic.

Excel Encoding: Knowledge Hub

<< Go Back to Excel Files | Learn Excel