Use a custom list to sort or fill in a user-defined order. Excel provides day-of-the-week and month-of-the year built-in lists, but you can also create your own custom list.
To understand custom lists, it is helpful to see how they work and how they are stored on a computer.
Comparing built-in and custom lists
Excel provides the following built-in, day-of-the-week, and month-of-the year custom lists.
Sun, Mon, Tue, Wed, Thu, Fri, Sat
Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday
Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
January, February, March, April, May, June, July, August, September, October, November, December
Note: You cannot edit or delete a built-in list.
You can also create your own custom list, and use them to sort or fill. For example, if you want to sort or fill by the following lists, you’ll need to create a custom list, since there is no natural order.
High, Medium, Low
Large, Medium, and Small
North, South, East, and West
Senior Sales Manager, Regional Sales Manager, Department Sales Manager, and Sales Representative
A custom list can correspond to a cell range, or you can enter the list in the Custom Lists dialog box.
Note: A custom list can only contain text or text that is mixed with numbers. For a custom list that contains numbers only, such as 0 through 100, you must first create a list of numbers that is formatted as text.
There are two ways to create a custom list. If your custom list is short, you can enter the values directly in the popup window. If your custom list is long, you can import it from a range of cells.
Enter values directly
Follow these steps to create a custom list by entering values:
For Excel 2010 and later, click File > Options > Advanced > General > Edit Custom Lists.
For Excel 2007, click the Microsoft Office Button > Excel Options > Popular >Top options for working with Excel > Edit Custom Lists.
In the Custom Lists box, click NEW LIST, and then type the entries in the List entries box, beginning with the first entry.
Press the Enter key after each entry.
When the list is complete, click Add.
The items in the list that you have chosen will appear in the Custom lists panel.
Click OK twice.
Create a custom list from a cell range
Follow these steps:
In a range of cells, enter the values that you want to sort or fill by, in the order that you want them, from top to bottom. Select the range of cells you just entered, and follow the previous instructions for displaying the Edit Custom Lists popup window.
In the Custom Lists popup window, verify that the cell reference of the list of items that you have chosen appears in the Import list from cells field, and then click Import.
The items in the list that you have chosen will appear in the Custom Lists panel.
Click OK twice.
Note: You can only create a custom list according to values, such as text, numbers, dates or times. You cannot create a custom list for formats such as cell color, font color, or an icon.
Follow these steps:
Follow the previous instructions for displaying the Edit Custom Lists dialog.
In the Custom Lists box, choose the list that you want to delete, and then click Delete.
Once you create a custom list, it is added to your computer registry, so that it is available for use in other workbooks. If you use a custom list when sorting data, it is also saved with the workbook, so that it can be used on other computers, including servers where your workbook might be published to Excel Services and you want to rely on the custom list for a sort.
However, if you open the workbook on another computer or server, you do not see the custom list that is stored in the workbook file in the Custom Lists popup window that is available from Excel Options, only from the Order column of the Sort dialog box. The custom list that is stored in the workbook file is also not immediately available for the Fill command.
If you prefer, add the custom list that is stored in the workbook file to the registry of the other computer or server and make it available from the Custom Lists popup window in Excel Options. From the Sort popup window, in the Order column, select Custom Lists to display the Custom Lists popup window, then select the custom list, and then click Add.
Need more help?
You can always ask an expert in the Excel Tech Community or get support in the Answers community.
When sorting information in a worksheet, you can rearrange the data to find values quickly. You can sort a range or table of data on one or more columns of data. For example, you can sort employees —first by department, and then by last name.
How to sort in Excel?
Select the data to sort
Select a range of tabular data, such as A1:L5 (multiple rows and columns) or C1:C80 (a single column). The range can include the first row of headings that identify each column.
Sort quickly and easily
Select a single cell in the column you want to sort.
On the Data tab, in the Sort & Filter group, click to perform an ascending sort (from A to Z, or smallest number to largest).
Click to perform a descending sort (from Z to A, or largest number to smallest).
Sort by specifying criteria
Use this technique to choose the column you want to sort, together with other criteria such as font or cell colors.
Select a single cell anywhere in the range that you want to sort.
On the Data tab, in the Sort & Filter group, click Sort to display the Sort popup window.
In the Sort by dropdown list, select the first column on which you want to sort.
In the Sort On list, choose Values, Cell Color, Font Color, or Cell Icon.
In the Order list, choose the order that you want to apply to the sort operation—alphabetically or numerically, ascending or descending (that is, from A to Z (or Z to A) for text, or lower to higher, or higher to lower for numbers).
In Microsoft Excel, you can use the Sort feature to perform many actions with lists of data. Users are generally looking alphabetize many text values or arrange a list of numbers in order from lowest to highest or from highest to lowest. To proceed, follow the instructions in the next section.
Details on how to sort more quickly are in the second section farther down the page.
Using sort to organize a list
- In Excel, highlight the values in a column that you want to organize.
- Click the Data tab at the top of the program window.
- Click the Sort option.
- Make sure the Sort by option is set to the column that you highlighted. If there are column header names, you can check the My data has headers box in the upper-right corner to use the header name in the Sort by option.
- Under Order, click the down arrow.
- In the drop-down menu that appears, select the option that sorts the data to your liking.
- Click OK to sort the highlighted values.
If you want to sort more than one column at the same time, after completing step 6 above, click the Add Level button. A second row is added to the Sort window. Follow steps 4 through 6 above for each new level added for sorting.
Using the Quick Sort feature
If you only need to organize your data in ascending or descending order, you can use the Quick Sort feature. Highlight the values you want to sort, then on the Data tab, click the Sort A to Z option or the Sort Z to A option.
When you are organizing data for your company in a spreadsheet using Microsoft Excel, you do not have to manually edit the information, if you want to sort it alphabetically. Instead, you can use the application’s sorting function to help you make better sense of the information. Excel has options to sort a single column of data, as well as options to group multiple columns together while you sort them.
Sort One List Alphabetically
Launch Excel and open the spreadsheet that contains the list that you want to sort.
Click to select a range of data, such as A1 through A20, or click the header to select the entire column of data.
Click the “AZ” icon in the Data tab’s Sort and Filter group to sort the list alphabetically from A to Z. Click the “ZA” icon to sort in reverse alphabetical order.
Sort Grouped Lists Simultaneously
Click any cell in the range of data that you want to sort, if you want to group multiple columns to sort simultaneously.
Click “Sort & Filter” in the Data tab’s Sort and Filter group and then click “Custom Sort.” The Sort dialog box will appear.
Click the “My Data Has Headers” check box if your spreadsheet has headers. If you don’t use headers, your columns will be labeled “A,” “B,” “C’ and so on.
Click the “Sort By” drop-down menu, and then click the name of the header of the first list you want to sort in a group. Alternately, click the letter of the column to select it.
Click the “Sort On” drop-down menu, and then click “Values.”
Click the “Order” drop-down menu, and then click “A to Z” to sort alphabetically, or click “Z to A” to sort in reverse alphabetical order. Repeat this process for each column of data in the group that you want to sort.
In Excel, you can take tables or ranges of data and sort them into different orders. For example, you can sort text alphabetically; numbers by size; dates and times chronologically; cells or fonts by color or icon; or you can create a custom sort. Usually you sort by column, but you can also sort by row.
Sort by One Column
- Select a cell in the column you want to sort.
- Click the Sort & Filter button on the Home tab.
- Select a sort order.
- Sort A to Z: Sorts the column in ascending order.
- Sort Z to A: Sorts the column in descending order.
- Custom Sort: Allows you to specify custom criteria, such as multi-level data sorting.
You can also right-click the cell and select Sort.
Sort by Multiple Columns
If you want to sort by more than one column, you need to use a custom sort. For example, you can sort first by the last name column, then by first name. That way, all the Andersons will be listed before the Bakers, and Andy Anderson will come before Bill Anderson.
- Click the Sort & Filter button on the Home tab.
- Select Custom Sort.
The Sort dialog box appears.
To sort by cell or font color, or by icon, click the Sort On list arrow and select an option.
The options for the Order change depending on what you choose for the Sort by variable.
Repeat these steps as necessary to sort by additional columns.
FREE Quick Reference
Free to distribute with our compliments; we hope you will consider our paid training.
If you want to sort your data, follow the next steps:
1. Open the Sort dialog box, do one of the following:
On the Home tab, in the Editing group, click the Sort & Filter list and choose Custom Sort. :
2. In the Sort dialog box, in the Order column, select the Custom List. :
3. In the Custom Lists dialog box, choose the custom sort list or create a new list:
Excel, by default, has four “custom lists”, and you can define your own. Excel’s custom lists are as follows:
- Abbreviated days: Sun, Mon, Tue, Wed, Thu, Fri, Sat
- Days: Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday
- Abbreviated months: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
- Months: January, February, March, April, May, June, July, August, September, October, November, December
Note that the abbreviated days and months do not have periods after them. If you use periods for these abbreviations, they are not recognized (and are not sorted correctly).
To create a custom list, select the NEW LIST option and make your entries (in order) in the List Entries box.
Check data entry for invalid entries
Custom cell format
Splitting text strings without using formulas
Not all sorts in Excel are alphabetical. Sometimes, you need to sort by an irregular set of terms, and when that happens, you need to create a custom sort. Here’s how.
Windows: Must-read coverage
- 10 secret Microsoft-specific keyboard shortcuts in Windows 11
- Plan for a Windows 10/11 reinstall by following these steps
- Windows 11 cheat sheet: Everything you need to know
- Windows 11: Tips on installation, security and more (free PDF)
Sorting is easy in Excel; you literally click an option and Excel does the rest. Occasionally, if Excel’s not certain how many columns to include in the sort, it will ask, but other than that, sorting is one of the simplest tasks you’ll perform in Excel. I’m talking about ascending and descending sorts, which won’t always be adequate. For instance, what if you want to sort by the days of the week: Monday, Tuesday, Wednesday, and so on? A simple sort won’t get the job done. That’s why Excel includes a custom sort feature. You can create a unique sort order, such as the days of the week. This article will show you how to create a custom sort when you have an irregular sort order.
I’m using Microsoft 365 desktop on a Windows 10 64-bit system, but you can use earlier versions. Excel Online will run an existing custom sort, but you can’t create them online. For your convenience, you can download the demonstration .xlsx and .xls files. This article assumes you have basic Excel skills, but even a beginner should be able to follow the instructions to success.
How to sort by terms in Excel
A custom sort is one you define. For instance, you might need to sort T-shirts by small, medium, large and extra-large. Or you might sort temperatures by cold, warm and hot. A regular sort can’t handle these types of requirements. In this case, I’ll refer to these sort elements as “terms.” Fortunately, using the Sort feature, you can easily define a custom sort that handles terms.
There are two ways to initiate a custom sort:
- On the Home tab, click Sort in the Editing group and click Custom Sort.
- On the Data tab, click Sort in the Sort & Filter group.
Now, let’s take on a contrived example—I’m doing so because it is so unique, and many of you will have peculiar sorting requirements that make no sense to anyone else but you. Figure A shows a simple data set of regions. You could sort alphabetically, both ascending and descending. But let’s suppose, instead, that you want a specific sort order: Southwest, Central, Northwest. Neither an ascending nor descending sort will work. Southwest, Central, and Northwest are the sort terms.
To create this sort, do the following:
- Click anywhere within the data set.
- Click the Data tab and then click Sort in the Sort & Filter group. (This route requires one less click than the Home tab.)
- In the resulting dialog, choose Region from the Sort by dropdown because you want to sort by the Region values.
- Leave Sort On with Cell Values, the default.
- From the Order dropdown, choose Custom Sort, which will open a new dialog, where you can create the custom sort (list).
- In the List Entries control, enter Southwest, Central, Northwest—the sort terms in the order by which you want them sorted (Figure A).
- Click Add to move the new list to the Custom Lists control on the left (Figure B).
- Click OK to return to the Sort dialog, which will now display the new list in the Order control (Figure C).
- Click OK to sort the data set.
As you can see in Figure D, the data set now sorts by the custom list. Southwest sorts first, followed by Central, and then Northwest. Custom sorts don’t always make sense, but it’s great to know how this works.
To reuse the sort, click Sort and choose Custom Sort from the Order dropdown. Choose the list in the Custom Lists control and click OK.
You might be wondering if a data set can have more than one custom sort. Yes, it can. Let’s create a second custom sort that sorts by the Personnel values in the following order: Rosa, June, James, Martha, Luke, Mark. To do so, repeat the instructions above until step 3 and choose Personnel from the Sort By dropdown. At step 6, enter Rosa, June, James, Martha, Luke, Mark, as shown in Figure E. Click Add, and then click OK twice. Figure F shows the results. You can add as many custom sorts as you need.
Admittedly, the examples are a bit contrived and might not make sense—unless you’re the person asked to sort records this way. Then, knowing how to use this feature will make you look great!
Excel supports a second custom support: sorting by multiple columns. In a future article, I’ll show you how to use this same feature to sort by multiple columns.
Microsoft Weekly Newsletter
Be your company’s Microsoft insider by reading these Windows and Office tips, tricks, and cheat sheets.
This guide will show you how to alphabetize in Excel using the Sort and Filter functions Functions List of the most important Excel functions for financial analysts. This cheat sheet covers 100s of functions that are critical to know as an Excel analyst to organize your data from A to Z. This feature is particularly useful for large datasets where it would take a very long time to alphabetize information in Excel manually.
Before you go on to learn more about Alphabetizing in Excel, use the form below to download CFI’s Excel Shortcuts Cheat Sheet.
Keyboard Shortcuts Sheet
Steps on How to Alphabetize in Excel
Follow the steps below to sort your data from A to Z (or in reverse, from Z to A). It’s important to point out that the Sort function can also be used or organize data from the largest to smallest and smallest to largest. We will outline the steps using two different methods: Sort and Filter.
Method #1 – Sort
To alphabetize in Excel using Sort, select the data, go to the Data Ribbon, click Sort, then select the column you want to alphabetize by.
Select the data you want to alphabetize with your cursor. You can select just one column, or multiple columns if you want to include other information. In the screenshot below, you can see how two columns are selected. This means Excel will match the data in the second column to the alphabetical order of data in the first column.
Once the information is highlighted, access the Data Ribbon and select the Sort function. A dialogue box will then appear, and you’ll be prompted to choose which column you want to use to sort the data by. In our example, we chose ‘City.’ Then decide if you want to alphabetize from A to Z or reverse-alphabetize from Z to A.
Press OK and view your results, as shown below. If you wish to undo the sorting, you can Undo the action by pressing Ctrl + Z.
Method #2 – Filter
Now we will show you how to alphabetize in Excel using the Filter function. This method should be used if you plan on repeating the action multiple times, as you will have a lasting option to keep resorting or unsorting the information.
Select the data you want to be in alphabetical order, including any additional columns that you want to be included. In our case, this is both City and Ranking.
After the information is selected, access the Data Ribbon and then click Filter. This will automatically add a small drop-down arrow to the top of each column you selected. Click on the arrow at the top of the column you want to alphabetize, then select either A to Z or Z to A.
You can now sort any column in the table by clicking the down arrow and alphabetizing it.
Thank you for reading CFI’s guide to how to alphabetize in Excel. To learn more about how to become a world-class financial analyst, check out the additional CFI resources below:
- Dynamic Financial Analysis Dynamic Financial Analysis This guide will teach you how to perform dynamic financial analysis in Excel using advanced formulas and functions. INDEX, MATCH, and INDEX MATCH MATCH Functions, Combining CELL, COUNTA, MID and OFFSET in a Formula. When used, these Excel functions make your financial statement analysis more dynamic
- Excel Modeling Best Practices Excel Modeling Best Practices The following excel modeling best practices allow the user to provide the cleanest and most user-friendly modeling experience. Microsoft Excel is an extremely robust tool. Learning to become an Excel power user is almost mandatory for those in the fields of investment banking, corporate finance, and private equity.
- Excel Shortcuts for PC and Mac Excel Shortcuts PC Mac Excel Shortcuts – List of the most important & common MS Excel shortcuts for PC & Mac users, finance, accounting professions. Keyboard shortcuts speed up your modeling skills and save time. Learn editing, formatting, navigation, ribbon, paste special, data manipulation, formula and cell editing, and other shortucts
- Free Excel Crash Course Excel Fundamentals – Formulas for Finance Are you looking for an Excel Crash Course? Get Free Excel Training for a career in corporate finance and investment banking from the Corporate Finance Institute.
Free Excel Tutorial
To master the art of Excel, check out CFI’s FREE Excel Crash Course Excel Fundamentals – Formulas for Finance Are you looking for an Excel Crash Course? Get Free Excel Training for a career in corporate finance and investment banking from the Corporate Finance Institute. , which teaches you how to become an Excel power user. Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.
Launch CFI’s Free Excel Course now Excel Fundamentals – Formulas for Finance Are you looking for an Excel Crash Course? Get Free Excel Training for a career in corporate finance and investment banking from the Corporate Finance Institute.
Microsoft Excel is a computer program that enables users to quickly organize and simplify data with spreadsheets, workbooks, graphs or charts. Many companies want their employees to know how to use Excel for sorting data, especially in administrative jobs or positions that involve reading and filing large amounts of detailed materials. Sorting data could mean arranging prices in ascending value, alphabetizing a list of names or ordering a list of dates that appears out of sequence.
The most important benefit to learning how to sort in Excel is the time you save by using it to perform actions automatically. In this article, we explain the steps for sorting data in Excel.
How to sort in Excel
You can sort data in Excel in a few different ways, such as by cell color or numerical value. Here are some general steps for sorting data in Excel:
1. Highlight the data items you want to sort
Click and drag your cursor to select all the cells you want to sort. You might choose to highlight single or multiple rows or columns, or both rows and columns, depending on how you want to reorganize your data.
2. Open the “Data” menu
Choose the tab in the navigation bar at the top of the page—known as the ribbon—that reads "Data" to reveal the options for manipulating data in the spreadsheet. The ribbon also includes options for “File,” "Home," "Insert'' and "Draw."
3. Choose “Sort & Filter”
Navigate to the section in the “Data” menu that reads "Sort & Filter" to view the sorting and filtering options. The “Sort” button allows options for organizing that include:
Sort by: This option allows you to choose which column you want to use as the anchor for your sorting. Any other highlighted data will sort according to the rules you establish for that column.
Sort on: Use this dropdown menu to decide how the cells will be sorted. It lets users pick between aspects like cell value, cell color, font color or conditional formatting icon.
Order: This dropdown menu lets you tell Excel how you want the data to be arranged, such as alphabetically from A to Z, reverse-alphabetically from Z to A or in a custom list, such as the days of the week. If your list contains numbers instead of letters, the order menu accounts for numerical order and offers the choice of "smallest to largest" or "largest to smallest."
Headers: The top right corner of the sorting window allows users to check a box to confirm that their list has headers, or cells at the top of each list they want to keep as they are. If your list doesn't use headers, you can leave this box unchecked.
Options: The top of the sorting window includes a button labeled "Options" which can change the "Sort by" option to rows instead of columns. To do so, click on "Options" and choose "Sort left to right" instead of "Sort top to bottom."
4. Customize your sorting options
Decide how you want your list to be sorted or the order in which you want your data to be displayed. You can use any combination of the options in the sorting window listed above to customize how your data is shown.
5. Confirm by hitting "OK"
Finalize your sorting choices by clicking the button at the bottom of the sorting window that says "OK." The window then disappears, and your data reorganizes itself according to the options you have selected in the sorting menu.
Example of how to sort in Excel
One example of sorting in Excel is alphabetizing list items. Here's how to alphabetize a list in Excel:
1. Highlight the items you want to sort
Click and drag your cursor to select all the cells you want to sort.
2. Open the “Data” menu
Choose the tab in the ribbon at the top of the page that reads "Data" to open options for manipulating the information in the spreadsheet.
3. Click the “Sort” button
Open the “Sort” window in the "Sort & Filter" section of the “Data” tab. This window contains the different dropdown menu choices for sorting data.
4. Choose "Sort A to Z"
Select "Sort A to Z" in the “Order” dropdown. This will sort whatever text is within your highlighted cells into alphabetical order.
5. Confirm by hitting "OK"
Click "OK" to confirm your sorting choices. Once you do, your list items should be arranged in alphabetical order.
Please note that the company mentioned in this article is not affiliated with Indeed.
The Excel SORT function sorts the contents of a range or array in ascending or descending order. Values can be sorted by one or more columns. SORT returns a dynamic array of results.
- array – Range or array to sort.
- sort_index – [optional] Column index to use for sorting. Default is 1.
- sort_order – [optional] 1 = Ascending, -1 = Descending. Default is ascending order.
- by_col – [optional] TRUE = sort by column. FALSE = sort by row. Default is FALSE.
The SORT function sorts the contents of a range or array in ascending or descending order with a formula. The result from SORT is a dynamic array of values that will “spill” onto the worksheet into a range. If values in the source data change, the result from SORT will update automatically.
The SORT function takes four arguments: array, sort_index, sort_order, and by_col. The first argument, array, is the range or array to be sorted. This is the only required argument. By default, the SORT function will sort values in ascending order using the first column in array. Use the optional arguments sort_index and sort_order to control which column to sort by, and the order to sort by (ascending or descending). Sort_index should be a number corresponding to the column (or row) used for sorting. For example, to sort by the third column in a range of data, use 3 for sort_index. The optional sort_order argument determines sort direction. Use 1 for ascending order and -1 for descending order. By default, the SORT function will sort data vertically by rows. To sort a range horizontally by columns, set the fourth argument, by_col, to TRUE.
To sort a range by the first column in ascending order:
To sort a range by the first column in descending order:
To sort a range by the second column in descending order:
To sort a range horizontally (by column) using values in row 1 in descending order, set the fourth argument, by_col, to TRUE or 1:
Example – sort by score
In the example shown above, data includes names in column B and scores in column C. In cell E5, the SORT function is used to sort the data by score in descending order:
The SORT function extracts all values, sorted in descending order by score, and results “spill” into the range E5:F14. To sort by score in ascending order, omit sort_order or to 1 like this:
Sort by more than one level
Unlike the SORTBY function, the SORT function does not provide a way to sort data by more than one level. However, by using array constants for sort_index and sort_order, it can be done. This video explains how.