Discussion
The Sort options available from the Filter buttons in the Header Row of a table only let you sort the table rows using the entries in a single column of the table. However, when a column contains many entries that are the same, such as a Region column, you
Page 26 Villanova UNIT Training©
may want to add a second level of sorting to organize the rows within each Region.
By using the Sort dialog box, you can add as many levels of sorting as you need.
You can use the entries in any column to sort table data. Text columns let you sort the data from A to Z or Z to A. Number columns let you sort from Smallest to Largest or Largest to Smallest. Date columns and Time columns let you sort from Oldest to Newest or Newest to Oldest. You can also sort by a custom list (such as month names, which do not produce the result you need when sorted alphabetically) or by the cell format (such as the Cell Color, Font Color, or Icon Set applied to cells by conditional formatting).
Sorting data by multiple levels
To reverse a sort, you can use the Undo feature immediately following the sort operation. Procedures
1. Select any cell in the table.
2. Select the Data tab.
3. Select in the Sort & Filter group.
Villanova UNIT Training© Page 27 4. Select the Column Sort by list.
5. Select the name of the column you want to use for the first level of sorting.
6. Select the Sort On list.
7. Select the desired option.
8. Select the Order list.
9. Select the desired option.
10. Select .
11. Select the Column Then by list.
12. Select the name of the column you want to use for the second level of sorting.
13. Select the Sort On list.
14. Select the desired option.
15. Select the Order list.
16. Select the desired option.
17. Add additional levels of sorting as required.
18. Select OK.
Step-by-Step
Sorting data by multiple levels.
Steps Practice Data
1. Select any cell in the table.
The cell is selected.
Click cell G10
2. Select the Data tab.
The Data tab is displayed.
Click Data
3. Select the Sort button in the Sort &
Filter group.
The Sort dialog box opens. Click
Page 28 Villanova UNIT Training©
Steps Practice Data
4. Select the Column Sort by list.
A list of table column names is displayed.
Click Sort by
5. Select the name of the column you want to use for the first level of sorting.
The column name appears in the Sort by box.
Click Region
6. Select the Sort On list.
A list of options is displayed. Click Sort On 7. Select the desired option.
The selected option appears in the Sort On box.
Click Values, if necessary
8. Select the Order list.
A list of options is displayed. Click Order 9. Select the desired option.
The selected option appears in the Order box.
Click A to Z, if necessary
10. Select the Add Level button.
A new row of options is displayed. Click 11. Select the Column Then by list.
A list of table column names is displayed.
Click Then by
12. Select the name of the column you want to use for the second level of sorting.
The column name appears in the Then by box.
Click Year
13. Select the Sort On list.
A list of options is displayed. Click Sort On 14. Select the desired option.
The selected option appears in the Sort On box.
Click Values, if necessary
15. Select the Order list.
A list of options is displayed. Click Order 16. Select the desired option.
The selected option appears in the Order box.
Click Smallest to Largest, if necessary
Villanova UNIT Training© Page 29
Steps Practice Data
17. Add additional levels of sorting as required.
The additional sort levels are displayed in the Sort dialog box.
Follow the instructions shown below the table before continuing on to the next step
18. Select OK.
The Sort dialog box closes and the table data is sorted.
Click
Click the Add Level button. Select the Column Then By list and click Month. Select the Sort On list and click Values, if necessary. Select the Order list and click Custom List. In the Custom Lists dialog box, select the Jan, Feb, Mar, Apr, May, Jun option from the Custom lists box, then click OK.
Return to the table and continue on to the next step (step 18).
Practice the Concept: Click the Sort button to reopen the Sort dialog box. Add another sort level to sort by Product values in A to Z order. Notice that the new level is added just after the first level. Use the Move Down button at the top of the Sort dialog box to move the new sort level to the bottom of the list of levels. Click OK to apply the new sort level. Notice that where there is more than one row within a Region with the same Month and Year (such as the Dec 2006 rows in the Northeast region), they are now sorted by Product.
Use the Undo button on the Quick Access Toolbar to undo all sorting and return the table to its unsorted state.