How cell format can affect the sort order in Excel

Applies to
Microsoft Excel 2000

After sorting a list of Excel figures, are some of the numbers not in the order you expect? A common reason for this problem is that some numbers may be formatted and stored as text instead of as numbers. When you sort data that includes values formatted and stored as text and as numbers, Excel sorts the text values separately from the number values.

In the following example, the sales value for Tennis is formatted as text, and there were no sales for Soccer.

Data before sort

When you sort this data by the Sales column in ascending order, you will get the following result:

Data after sort

Because the sales value for Tennis is formatted as text, its row is sorted below the row for Safari, even though the sales value for Tennis is much smaller than the sales values for Windsurfing, Golf, and Safari. If the sales value for Tennis had been formatted as a number, it would appear above those entries. Because no sales value is entered for Soccer, Soccer ends up at the bottom of the list instead of at the top. Blank cells always sort last, no matter how they are formatted.

How numbers can end up formatted and stored as text

To sort values properly, you need to understand that Excel sorts text and number values differently, and that numbers can inadvertently end up in text format. A number you enter in Excel can become formatted and stored as text, instead of as a number, when you:

  • Enter an apostrophe at the beginning of a number.
  • Enter a number in a cell that's already formatted as text.

If a cell is formatted with the default general number format, text you enter in that cell is left aligned and numbers you enter are right-aligned. Alignment can help you find numbers that are stored as text, as they will then be left-aligned instead of right-aligned. However, changing the alignment, or other cell formats, doesn't change how Excel stores a value that has already been entered in a cell. Even changing the number format of a cell won't change a value that has been stored as text into a value stored as a number, or vice versa. To store the value as a number again, you must first reformat the cell with a number format, and then re-enter the number in that reformatted cell.

Numbers from external data

When you import data into Excel from an external source, such as a database or a Web page, Excel may recognize the type of data as either numbers or text. If your external data source stores numbers as text, Excel may also store them as text. For example, if a field in the external database contains numbers for some records and text for other records, Excel may store the values the same way, so that some of the values in a column of numbers are stored as text.

Finding numbers that are stored as text

As mentioned above, alignment can help you find numbers that are formatted and stored as text. Sometimes, however, those numbers may look the same as other numbers, especially when they are large enough to fill the entire cell so that you can no longer see if they're left- or right-aligned. The easiest way to find incorrectly formatted numbers is by sorting them, and then identifying and examining any rows that are out of order.