In addition to performing a simple sort based on a single column of data, Excel's custom sort feature allows you to sort multiple worksheet columns.
Sort By Multiple Columns of Data in Excel Example
For help with this example, see the images to the right.
In this example, we will sort our data by two columns - first by the age and then by the date.
- Enter the following data records into cells A1 to C4:
A1 - Name
B1 - Age
C1 - Start Date
A2 - Joe
B2 - 34
C2 - 11/3/2009
A3 - Sam
B3 - 56
C3 - 10/3/2009
A4 - Mary
B4 - 34
C4 - 4/5/2008
- Drag select cells A1 to C4 to highlight them.
- Click on the Home tab of the ribbon.
- Click on the Sort & Filter icon on the ribbon to open the drop down list.
- Click on Custom Sort in the drop down list to bring up the Sort dialog box.
- Under the Column heading in the dialog box, choose Sort by Age from the drop down list to first sort our data by the Age column.
- Under the Sort Order heading in the dialog box, choose Smallest to Largest from the drop down list to sort the Age data with the smallest (youngest) values at the top of the list and the largest (oldest) at the bottom.
- At the top of the dialog box, click on the Add Level button to add a second sort option.
- Under the Column heading in the dialog box, choose Sort by Date from the drop down list to use the Date column as our second sort option.
- Under the Sort Order heading in the dialog box, choose Newest to Oldest from the drop down list for the Date column.
- Note: Excel uses the second sort option (in this case Date) ONLY if it finds identical data in two or more cells in the first sort column (Age).
- Click OK in the dialog box to close the dialog box and sort the data.
- The data should be sorted such that the data record for Joe is listed first, followed by Mary, and then by Sam.
Interpreting the Sort Results
- When Excel sorted the data by age, it found that Joe and Mary were the same age. It therefore sorted those two records using the second sort option - start date - to determine which record should be first in the list. Since Joe has the more recent start date, his record was placed first.
- Since Sam's age does not match Joe and Mary's age, Excel does not sort Sam's record by start date. His record is sorted only by age and, because it is greater than Joe's and Mary's age, his record is placed last in the list, even though his start date is more recent than Mary's.
Note: Excel assumes that the first row of data in your table contains column headings - in the example above - Name, Age, Start Date. It uses these headings as choices under the Column option in the Sort dialog box.
If the first row of your data does not contain headings, remove the check mark from the My data has headers option at top right of the dialog box (see image 2 above).
Excel will then use the column letter - such as Column A, Column b - as choices under the Column option in the dialog box.