Sorting Chinese in Microsoft Excel & Word

Or: why doesn't this work the way I expect?

Most Chinese users expect Chinese characters to sort by number of strokes or by phonetics. But in most non-Chinese versions of Word and Excel, Chinese characters will sort by Unicode code point values. To add the stroke and phonetic sort options, you must install Chinese proofing tools for Office.

See the page on MS Office Language Packs and Proofing Tools for information on obtaining these features. The following instructions assume you've installed one or more language packs for Chinese.

Let's start with the example of 孔乙己(Kong Yiji). Apologies to Lu Xun. :-)

Without an Office language pack, the default sort is by Unicode. This is generally in Kangxi radical order, although I believe it gets weird once you get into the number ranges of rare characters. In this example the sort is by the Kangxi radicals 乙, 子, and 己, which are numbers 5, 39, and 49 (and in the wrong order by the Simplified system: 7, 74, 72).

In Word, sorting by stroke starts working after you've installed an Office language pack for Chinese, with no adjustments to your settings, but you'll need to change something in Options to enable phonetic sorting.

Just click the Sort button while Chinese is selected, and you'll see "Stroke" listed first in "Sort Text":

To get "Phonetic" sort working properly for Chinese characters in Word, or to get "Stroke" and "Syllabary"/"Phonetic" sort to work in Excel, you must switch to a Chinese editing language.

In Excel or Word, go to the File tab, select Options, and then Language. Look for "Choose Editing Languages". Select a Chinese option, click the "Set as Default" button, and then click the "OK" button. Close all Office apps (including Outlook), then proceed. If you don't see Chinese listed there, you need to install at least one Office language pack for this language.

Now everything should begin working as expected. Here are some examples, with our old friend 孔乙己:

After installing an Office language pack for Chinese and selecting Chinese as the editing language, you'll have more options. In the Excel "Custom Sort..." dialog box, click the "Options..." button and you'll find stroke and phonetic ordering. In PRC mode "Syllabary" means Hanyu Pinyin, and the characters are now sorted alphabetically by the letters J, K, Y (己, 孔, 乙).
In Chinese (Taiwan) editing language mode (or Hong Kong or Macau), "Phonetic" means Zhuyin Fuhao. In this example, characters are sorted in "Bopomofo" phonetic order by the initials ㄎ, ㄐ, ㄧ (孔, 己, 乙). By installing both the Chinese (PRC) and Chinese (Taiwan or HK or Macau) packs, both types of phonetics will be available.
Most Chinese users expect the characters to sort by number of strokes. This works in both Simplified (PRC) and Traditional (Taiwan / Hong Kong / Macau) editing modes. In Chinese editing modes if you try to sort from "A to Z" or "Z to A" it will sort by stroke anyway, but in this example we see the custom options dialog, and the characters sorted by 1, 3, and 4 strokes (乙, 己, 孔).

OK? Feel free to send questions, comments, or suggestions anytime.

« « Back to FAQ index