If your Excel worksheet has become quite large, using the Sort dialogue box to sort on multiple columns can make it easier to find the data you need. The Sort dialogue box lets you tell Excel what column to sort on next if two cells in the main sort column contain the same value or data.
Sort by Columns
Although the most common sort is by rows (which is the topic of this post), you can also sort by columns. In the Sort dialogue box, click the Options button, choose Sort Left to Right, and click OK.
Sort Multiple Columns
To sort multiple columns by rows in Excel,
- Select or click in the list of data you want to sort.
You can select only a single column of data if you want to sort that column independently of the rest of the data. However, in most cases you are likely to sort the entire list.
- You can access Sort function through Home tab, in Editing section, find Sort & Filter. An alternative is to go to Data tab, in Sort & Filter section, select the Sort button. Keyboard shortcut Alt+D+S.
Sort function in Data tab.
Sort function in Home tab.
- The Sort dialogue box appears. If your data includes column headings, make sure the My Data Has Headers option is checked. Excel usually detects the correct setting automatically
- From the Sort By drop-down list, select the column by which you want to sort. This drop-down list includes the column headings for each column in the list.
- From the Sort On drop-down list, choose Values.
Notice that you also can perform the sort on Cell Color, Font Color, or Conditional Formatting Icon. Typically, you will perform a sort based on values (rather than formatting).
- From the Order drop-down list, select how you want to sort the data.
The options that appear in this list change based on the contents of the sort column. Choose A to Z or Z to A to sort text values, Smallest to Largest or Largest to Smallest to sort numeric data, or Oldest to Newest or Newest to Oldest to sort by dates.
- Click the Add Level button.
Additional drop-down list boxes appear for the secondary sort column. This is the column Excel will sort by if two or more items are identical in the first Sort By option.
- Repeat steps 3 through 5 for the new sorting level.
After you specify the options for the secondary sort column, you can add more sort columns as needed. Use the Move Up and Move Down buttons if you decide to change the order of the sort columns.
- If you need to differentiate case in sort, you can press Options and check “Case sensitive”. You can use Sort Options dialogue to sort left to right instead.
- Click OK.
The dialogue box closes and Excel performs the sorting process.