Sorting Data in Microsoft Excel 2016
- Using the Sort Dialog Box
- Doing Quick Sorts
- Performing Custom Sorts
- Rearranging Columns
- Fixing Sort Problems
You’ll often need to sort your data in Excel, whether it be numerically, alphabetically, by color, or by icon. In this chapter from My Excel 2016, you’ll learn the various ways of sorting data, allowing you to view data from least to greatest, greatest to least, and even by color.
In this chapter, you’ll learn the various ways of sorting data, allowing you to view data from least to greatest, greatest to least, and even by color. You’ll also learn how to do the following:
- Sorting data with one click
- Sorting using a custom, non-alphabetical order
- Sorting by color or icon
- Rearranging columns with a few clicks of the mouse and keyboard
You’ll often need to sort your data, whether it be numerically, alphabetically, by color, or by icon. You aren’t limited to sorting the rows—columns can also be sorted.
Sorting data allows you to change how you view it. For example, if your dataset has a date column, you can view the oldest data at the top, or you can view the newest data at the top. You can also sort the data so like values, such as product names, are grouped together. You can even combine sorts so that you not only view the products grouped together, but in date order from oldest to newest.
Using the Sort Dialog Box
The Sort dialog box provides the most versatile way of sorting your data because it allows you to specify how you want the data sorted. When you use the dialog box, Excel applies each sort in the order it appears in the list.
Sort by Values
The Sort dialog box makes it easy to sort by multiple columns. A different sort method can be applied for each level. The sorts are done in the order they appear in the list.
- Select a cell in the dataset. Excel will use this cell to determine the location and size of the dataset.
On the Data tab, select Sort.
- If the data has a header row, but Excel doesn’t recognize it, select the My Data Has Headers check box.
- From the Sort By drop-down, select the first column header by which to sort.
- From the Sort On drop-down, select Values.
From the Order drop-down, select the order by which the column’s data should be sorted. Choose A to Z to sort in alphabetical order; choose Z to A to sort in the opposite order. If the data is numerical, the drop-down options will change to Smallest to Largest and Largest to Smallest.
- Select Add Level to add another sort rule.
- From the Then By drop-down, select the second column header by which to sort.
- From the Sort On drop-down, select Values.
- From the Order drop-down, select the order by which the column’s data should be sorted.
- If you realize a field is in the wrong position, use the up or down arrow at the top of the dialog box to move the field to the correct location.
Click OK to sort the data.
Sort by Color or Icon
Excel can also sort data by fill color, font color, or an icon set from conditional formatting.
- Select a cell in the dataset. Excel will use this cell to determine the location and size of the dataset.
On the Data tab, select Sort.
- If the data has a header row, but Excel doesn’t recognize it, select the My Data Has Headers check box.
- From the Sort By drop-down, select the column header by which to sort.
- From the Sort On drop-down, select Cell Color to sort by the cell’s fill color. You can also choose Font Color to sort by the value’s color or Cell Icon to sort by conditional formatting icons.
From the first Order drop-down, select the color by which the column’s data should be sorted. If sorting by icon, you’ll have a choice of icons.
- From the second Order drop-down, select whether the color should be sorted to the top or bottom of the data. If you select multiple colors to sort at the top of the data, the colors will still appear in the order chosen.
Click OK to sort the data.