Sorting with Custom Lists in Excel

Custom List Sort Every once in a while I come across a sorting problem where a column of data needs to be sorted in a manner not consistent with ascending or descending order. Thankfully, Excel allows the sort order to come from a Custom List.

As an example I have some data with Date, Name, and Type as column headings, where the Type data can be one of five values: Breakfast, Lunch, Dinner, Snacks, Exercise. I want the data sorted by Date, in ascending order, then by Type, in the order listed above, not in ascending order: Breakfast, Dinner, Exercise, Lunch, Snacks, which is how I get the data.

In years past I would have solved this problem by creating another column (TypeCode) with a VLOOKUP formula keying off the Type data and referencing a new table created (on Sheet 1) with the sort order I wanted, as depicted below.

Sort Data with VLOOKUP Column

TypeCode is a VLOOKUP formula value that gives me the sort order I want, but with an extra column in my data and another worksheet with the lookup table. Too much extra stuff for this little problem.

Create a Custom List

A Custom List can be created in Excel 2010 by choosing File > Options > Advanced > Edit Custom Lists. In Excel 2011 choose Excel > Preferences > Custom Lists. Click Add then type the list entries — press enter or return to separate entries — then click OK.

Create a Custom List in the Sort Dialog Box

In our example we're going to add a Custom List from the Sort dialog box. I select a cell inside my data then choose Data > Sort to bring up the Sort dialog box. As you can see below, the first level is sorted by Date from Oldest to Newest. The second level column is Type where I select Custom List... from the Order drop-down box, which will bring up the Custom Lists dialog box.

Sort Dialog Box Custom List Order

Here I click Add, and type in my list: Breakfast, Lunch, Dinner, Snacks, Exercise then click OK.

Custom Lists dialog Box Add

You now see the new Custom List in the second level Order column of the Sort dialog box where you can now click OK to have Excel complete the sort.

Sort dialog box Order by Custom List

The data is now sorted by Date (oldest to newest) and by Type using the Custom List.

Data with Sort

Sorting with a Custom List makes things simpler when Excel doesn't have a default sort order to fit your needs.

Archived comments

Comments migrated from the previous version of the site. Adding new comments is disabled.

MichaelFebruary 18, 2012 at 05:13 AM

How do you circumvent the 255 character limit?

Gregoryexcelsemipro.comFebruary 19, 2012 at 12:41 AM

I'm not sure what you are referring to here. In Excel 2003 the length of cell contents (text) is 32,767 characters. 1,024 are displayed in the cell. (All are displayed in the formula bar.) All newer versions of Excel have at least this much capacity for text in a cell. There is a column width limit of 255 characters, which means that to display more than 255 characters in a cell you would have to turn on the Wrap Text option. To do this use the keyboard shortcut Ctrl+1 to bring up the Format Cells dialog box, click the Alignment tab, then check the Wrap Text box under Text Control, and click OK.

NickOctober 10, 2012 at 05:01 AM

Is there a limit to the number of characters in a cell that can be sorted e.g. I have a column of cells with each cell containing a text string of up to 300 characters. Excel (2010) appears to only look at the first 256 characters in each cell, ignoring the rest.

Gregoryexcelsemipro.comOctober 10, 2012 at 05:06 AM

You guessed it, 256 characters is the limit.

NickOctober 10, 2012 at 05:46 AM

Do you know if this documented anywhere?

Gregoryexcelsemipro.comOctober 25, 2012 at 11:07 PM

There is some documentation on the number of characters in a cell. The limit for sorting is 255, which I tested. The limit for characters in a cell depends on the version of Excel. Here's a reference for Excel 2003, and one for Excel 2010. In short, in Excel 2003 you can have 32,767 characters in a cell. Only 1,024 display in a cell; all 32,767 display in the formula bar. In Excel 2010, 32,767 characters are allowed in a cell and I'm not sure how many are displayed.

Create a Custom List

Create a Custom List in the Sort Dialog Box

Enjoyed this guide?

Archived comments