Sorting by a Custom List in Excel

Sort by a Custom List in Microsoft Excel

You can use a custom list to sort in a user-defined order. Excel provides built-in, day-of-the-week and month-of-the year custom lists, and you can also create your own custom list.

1. Select a column of data in a range of cells, or make sure that the active cell is in a table column.
2. On the Home tab, in the Editing group, click Sort & Filter, and then click Custom Sort.
The Sort dialog box is displayed.
3. Under Column, in the Sort by or Then by box, select the column that you want to sort by a custom list.
4. Click the drop down button under Order and select Custom List.
5. In the Custom Lists dialog box, select the list that you want. Or you can type in you own list by cliking the Add button and typing; for example High, Medium, Low, separated by comma.
6. Click Ok twice.

Sort Rows

1. Select a row of data in a range of cells, or make sure that the active cell is in a table column.
2. On the Home tab, in the Editing group, click Sort & Filter, and then click Custom Sort.
The Sort dialog box is displayed.
3. Click Options.
4. In the Sort Options dialog box, under Orientation, click Sort left to right, and then click Ok.
5. Under Row, in the Sort by box, select the row that you want to sort.
6. Do one of the following:
By value
1. Under Sort On, select Values.
2. Under Order, do one of the following:
1. For text values, select A to Z or Z to A.
2. For number values, select Smallest to Largest or Largest to Smallest.
3. For date or time values, select Oldest to Newest or Newest to Oldest.
By cell colour, font colour, or cell icon
1. Under Sortn, select Cell Color, Font Color, or Cell Icon.
2. Click the arrow next to the button, and then select a cell color, font color, or cell icon.
3. Under Order, select On Left or On Right.

Sort by more than one Column or Row

You might sort by more than one column or row when you have data that you want to group by the same value in one column or row, and then sort another column or row within that group of equal values. For example, if you have a Department and Employee columns, you can first sort by Department (to group all the employees in the same department together), and then sort by name (to put the names in alphabetical order within each department).

Again, if you want to find a way of categorising employees into a seniority list when such employees have the same date of employment, consider sorting by another column beside the Date Employed column. You can sort by up to 64 columns.

For best results, the range of cells that you sort should have column headings.

1. Select the entire range of cells with two or more columns of data, or make sure that the active cell is in a table with two or more columns.
2. On the Home tab, in the Editing group, click Sort & Filter, and then click Custom Sort.
The Sort dialog box is displayed.
3. Under Column, in the Sort by box, select the first column that you want to sort.
4. Under Sort On, select the type of sort by doing one of the following:
1. To sort by text, number, or date and time, select Values.
2. To sort by format, select Cell Color, Font Color, or Cell Icon.
5. Under Order, select how you want to sort by doing one of the following:
1. For text values, select A to Z or Z to A.
2. For number values, select Smallest to Largest or Largest to Smallest
3. For date or time values, select Oldest to Newest or Newest to Oldest.
4. To sort based on a custom list, select Custom List.
6. To add another column to sort by, click Add Level button, and then repeat steps three through five.
7. To copy a column to sort by, select the entry, and then click Copy Level.
8. To delete a column to sort by, select the entry, and then click Delete Level.
You must keep at least one entry in the list that is being used in the sorting.
9. To change the order in which the columns are sorted, select an entry, and then click the Up or Down arrow to change the order.

General Issues/Problems with Sorting

If you get unexpected results when sorting your data, do the following:

>Check to see if the values returned by a formula have changed. If the data that you have sorted contains one or more formulas, the return values of those formulas can change when the worksheet is recalculated. In this case, make sure that you reapply the sort or do the sort again to get up-to-date results.

Unhide rows and columns before you sort. Hidden columns are not moved when you sort columns, and hidden rows are not moved when you sort rows. Before you sort data, it's a good idea to unhide any hidden columns and rows.

Check the locale setting. Sort orders vary by locale setting. Make sure that you have the proper locale setting in Regional Settings or Regional and Language Options in Control Panel on your computer (details of control panel setting is not covered here).

Enter column headings in only one row. If you need multiple line labels, wrap the text within the cell. Detaigls of text wrapping in cells will be covered in later posts ion this blog, it will be covered under "Editing Workbook."

Turn on or off the heading row. It's usually best to have a heading row when you sort a column to make it easier to understand the meaning of the data. By default, the value in the heading is not included in the sort operation. Occasionally, you may need to turn on or off the heading so that the value in the heading is or is not included in the sort operation. Select 'My data has headers' in the sort dialog box to exclude column headers in the sort data. Then clear 'My data has headers' to include the first row of data in the sorting because it is not a column heading.