Excel: Effectively Sorting Numbers

Sorting data is typically simple and works at the click of a button. However, sometimes data gets sorted in an unexpected manner.  Have you ever experienced the frustration of numbers displaying like 1, 10, 2, 20 rather than in consecutive order? The reason is that even though some data looks like numbers, Excel recognizes them as text. Since Excel sorts numbers differently than text, the results are skewed. In this article you will discover how to convert data to a numeric format and learn how to sort data stored as text in a custom order that you desire.

HOW DO I KNOW IF MY DATA WILL BE SORTED AS TEXT OR AS A NUMBER
The options for the sort order are a key indicator in determining how the data will be sorted. Numeric data is sorted based upon the entire value and the options include “Smallest to Largest” or “Largest to Smallest”.  Data that is text is sorted from left to right on a per character basis, just as we would alphabetize data from “A to Z” or “Z to A.”  

HOW DO I CHANGE DATA FROM TEXT TO A NUMBER?
The process of converting data to be stored as a number depends upon the data itself. Several common examples are listed below.

Data containing only numbers

1. Type the number 1 into a blank cell and copy it.
2. Highlight the data to be converted.
3. Click Paste, Paste Special, and choose the multiply command.  Click OK.

This multiplies all of the highlighted data by 1 and forces the data to be stored as a numeric value since math functions can only be performed on numeric data. 

Numbers with symbols or consistent letters

Alphanumeric data can be very tricky because data that is manually entered with characters or symbols will be stored as text, whereas data used with a number format mask is stored as numeric.

Example Data to convert:  1-1/2”
In this example non-numeric characters are present in the cell because the -,/, and “ were all manually entered. The desire is to keep the same visual format, but have it stored as a number for sorting purposes.

1. Highlight the data to be converted. Press Ctrl+H to bring up the Find and Replace window.
2. Replace each fraction with its decimal equivalent:  ½ = .5, ¾ = .75, ¼ = .25 , etc..   
3. Replace all extra characters like – or “ with nothing to completely remove them.  Close the Find & Replace box.
4. On the Home tab, click the dropdown in the “Number” section and select “More number formats”.
5. Select the Custom Format and copy/paste this into the blank.       #-#/#”””        Click OK. 
   Example Data should look like 1-1/2” as it originally did, but will now be stored as a number.
   Note: Other formats can be used or created as needed using the proper placeholders.

Example Data to convert:  10S, 5S, 20S
In this example each cell has a number and letter, but because all of the entries consistently have the same letter s at the end a mask can be used.

1. Highlight the entire column or row of data to be converted.
2. Press Ctrl+H to bring up the Find and Replace window. Perform a find and replace for each non-numeric character (S) and replace it with nothing. Close the Find & Replace box.
3. With all of the data to be converted still highlighted, click the dropdown in the “Number” section of the home tab and select “More number formats”.
4. Select the Custom Format and copy/paste this into the blank.       ##”S”       Click OK. 
   Example Data should look like 10S, 5S, 20S as it originally did, but will be stored as a number.
   Note: Other formats can be used or created as needed using the proper placeholders.

HOW DO I CHANGE SORT OPTIONS FOR DATA STORED AS TEXT?
Although the most common sort option for sorting text including “A to Z” or “Z to A”, there are custom sort list options which can be created and used for all future sessions of Excel. By creating a custom sort list you are dictating the order of the sort results.  For example the days of the week should be sorted in the order they occur rather than alphabetically, so a custom list could be created for “Sun, Mon, Tues, Wed, Thurs, Fri, Sat”.

Example Data for custom sort list:  5S, 10S, 40S
Without a custom sort list or using the conversion in the prior step, this data will be sorted as 10S, 40S, 5S because text is sorted based upon the first character of the data rather than the entire value.

1. Start the sort process and in the “Order” drop down select “Custom List…”

2. Click “New list” on the left and enter the items in the order you wish them to be sorted on the right.  Press the Enter Key to separate each option on a new line.

3. Click Add. Click Ok.

This custom sort list will now be available in the “Order” dropdown as an option.

Remember that the key to sorting effectively begins with understanding the format of the cell!