You can sort by several columns by adding levels of sorting criteria. For example, you could sort a sales report by region, then by date, and then by salesperson. Each sort level is represented by a single row in the Sort dialog box. Click a cell in one of the columns that you want to sort. On the Data tab, select Sort. If your data has a header row, select the My list has headers check box.
But if the data does not have a header row, clear the My list has headers check box. In the row next to Sort by, under Column, click the blank space, and then click the column that you want to sort.
Under Sort On in the same row, click Values, and then on the shortcut menu, click the criteria that you want. You can also choose to sort based on cell or font color, or on the icon in a cell. Under Order in the same row, click A to Z, and then on the shortcut menu, click the criteria that you want. Note: This procedure can't be performed in a table.
To sort multiple columns by weekday, month, or another custom list, use the previous 'Sort a list by two or three columns' procedure to sort each column separately. Sort dates or times Select a column of dates or times in a range of cells or in a table. To initiate the sort in Excel 2010 and 2007, click anywhere inside the data range and then click the Data tab. Click Sort in the Sort & Filter group. In Excel 2003, choose Sort from the Data menu.
To remove table formatting so that you can sort by rows, on the Table tab, select Convert to Range. Click a cell in the row that you want to sort. On the Data tab, select Sort. If your data has a header row, select the My list has headers check box. But if the data does not have a header row, clear the My list has headers check box.
Click Options. Under Orientation, click Sort left to right, and then click OK.
In the first row, under Row, click the blank space next to Then by, and then on the shortcut menu, click the row that you want to sort next. Under Sort On in the same row, click Values, and then on the shortcut menu, click the criteria that you want. Under Order in the same row, click A to Z, and then on the shortcut menu, click the criteria that you want. Excel includes custom lists that you can sort by: days of the week and months of the year. In addition, you can create your own custom lists, for example, grade levels in a school, such as Freshman, Sophomore, Junior, and Senior.
Once your custom list is created, to use it, in the Sort box, under Order, select Custom List. On the Excel menu, click Preferences, and then under Formulas and Lists, click Custom Lists. Type the values for your list in the order that you want them sorted, with a comma between each value. When you are finished, click Add, and then close the Custom Lists box. Excel includes custom lists that you can sort by: days of the week and months of the year. In addition, the previous procedure explains how you can create your own custom lists, for example, grade levels in a school, such as Freshman, Sophomore, Junior, and Senior. Click a cell in one of the columns that you want to sort.
On the Data tab, select Sort. If the data has a header row, select the My list has headers check box. But if the data does not have a header row, clear the My list has headers check box. Under Order, click Custom List. Select the list that you want to sort by, and then click OK. If there are cells formatted by cell color or font color in the column that you are sorting, you can sort by these colors. You can also sort by an icon set that was created by using a conditional format.
Because there is no default sort order for cell color, font color, or icons, you must define your own order for each sort operation. Click a cell in one of the columns that you want to sort. On the Data tab, select Sort. If the data has a header row, select the My list has headers check box. But if the data does not have a header row, clear the My list has headers check box. Under Column, click the blank space next to Then by, and then on the shortcut menu, click the column that you want to sort.
Under Sort On in the same row, click Values, and then on the shortcut menu, click Cell Color, Font Color, or Cell Icon. Under Color/Icon, select the color or icon. Under Order, choose whether the selected color or icon should be at the top or bottom of the list. For each additional column you want to sort by, click Add Level.
Then fill in the Column, Sort On, Order, and Color/Icon columns for the new row. You can sort by several columns by adding levels of sorting criteria.
For example, you could sort a sales report by region, then by date, and then by salesperson. Each sort level is represented by a single row in the Sort dialog box. Click a cell in one of the columns that you want to sort.
On the Data tab, under Sort & Filter, click the arrow next to Sort, and then click Custom Sort. Click Add Level. If the table has a header row, select the My list has headers check box. But if the table does not have a header row, clear the My list has headers check box. Under Column, click the blank space next to Then by, and then on the shortcut menu, click the column that you want to sort. Under Sort On in the same row, click Values, and then on the shortcut menu, click the criteria that you want. Under Order in the same row, click A to Z, and then on the shortcut menu, click the criteria that you want.
If you selected Font Color, Cell Color, or Cell Icon in step 5, then under Color/Icon, click the row, and then on the shortcut menu, click the criteria that you want. For each column that you want to sort by, repeat steps 3 through 6. Click a cell in the row that you want to sort. On the Data tab, under Sort & Filter, click the arrow next to Sort, and then click Custom Sort.
Click Options. Under Orientation, click Sort left to right, and then click OK. Click Add Level. Under Row, click the blank space next to Then by, and then on the shortcut menu, click the row that you want to sort next. Under Sort On in the same row, click Values, and then on the shortcut menu, click the criteria that you want.
Under Order in the same row, click A to Z, and then on the shortcut menu, click the criteria that you want. For each row that you want to sort by, repeat steps 5 through 8. Excel includes custom lists that you can sort by: days of the week and months of the year. In addition, the previous procedure explains how you can create your own custom lists, for example, grade levels in a school, such as Freshman, Sophomore, Junior, and Senior. Click a cell in one of the columns that you want to sort. On the Data tab, under Sort & Filter, click the arrow next to Sort, and then click Custom Sort.
Click Add Level. If the table has a header row, select the My list has headers check box. But if the table does not have a header row, clear the My list has headers check box. Under Order, click the current sort order, such as A to Z, and then click Custom List.
Select the list that you want to sort by, and then click OK. If there are cells formatted by cell color or font color in the column that you are sorting, you can sort by these colors. You can also sort by an icon set that was created by using a conditional format. Because there is no default sort order for cell color, font color, or icons, you must define your own order for each sort operation. Click a cell in one of the columns that you want to sort. On the Data tab, under Sort & Filter, click the arrow next to Sort, and then click Custom Sort.
Click Add Level. If the table has a header row, select the My list has headers check box. But if the table does not have a header row, clear the My list has headers check box. Under Column, click the blank space next to Then by, and then on the shortcut menu, click the column that you want to sort. Under Sort On in the same row, click Values, and then on the shortcut menu, click Cell Color, Font Color, or Icon Color. For each column that you want to sort by, repeat steps 3 through 5.
Important: To complete this procedure, you must first turn on Chinese language features. For more information, see. Click a cell in one of the columns that you want to sort. On the Data tab, under Sort & Filter, click the arrow next to Sort, and then click Custom Sort.
To add another sorting criteria, click Add Level. If the table has a header row, select the My list has headers check box. But if the table does not have a header row, clear the My list has headers check box. To change the sort order, under Order, click the current sort order, such as A to Z, and then click Custom List. Click Options, and then do one or more of the following: To Do this Sort top to bottom Under Orientation, click Sort top to bottom.
Sort left to right Under Orientation, click Sort left to right. Sort by stroke ordering Under Method, click Stroke Ordering. Sort by syllabary ordering Under Method, click Syllabary Ordering.
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 formula keying off the Type data and referencing a new table created (on Sheet 1) with the sort order I wanted, as depicted below.
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 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. Post author 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.