Cell Formats Affect How Excel 2000 Sorts Numbers 

When you sort a list of Microsoft Excel figures, are some of the numbers not in the order you expect? A common reason for this problem is that Excel has stored some of the numbers as text instead of numbers. When you sort by a column that includes data stored as both text and numbers, Excel sorts the text values separately from the numbers.

For example, suppose you want to sort the sales amounts in the following illustration from smallest to largest. In the example data, you typed the value for Tennis into a cell that was formatted as text. You had no sales for Soccer, so you left that cell blank.

You sort by the Sales column in ascending order. Here's what you get:

Because the value for Tennis is text, Tennis is sorted below Safari. The value for Soccer sorts to the bottom of the list because blank cells always sort last.

There are several ways to modify your data so it will sort as you expect, with Soccer and Tennis as the first and second rows. To decide how best to proceed you need to understand how Excel stores different types of values.

How Excel Stores Numbers and Text

Entering numbers as text  There are two ways that a number you type in Excel can be stored as text instead of as a number:

• Type an apostrophe at the beginning of a number, or
• Type a number in a cell that's previously been formatted as text.

Formats of numbers stored as text  In a cell that has the default General number format, text that you enter is left aligned and numbers are right aligned, so the alignment can help you find numbers that are stored as text. 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, you must also re-enter the number in the reformatted cell.

Numbers in 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.