Sorting Data in Microsoft Excel

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.

  1. Select a cell in the dataset. Excel will use this cell to determine the location and size of the dataset.
  2. On the Data tab, select Sort.
  3. If the data has a header row, but Excel doesn’t recognize it, select the My Data Has Headers check box.
  4. From the Sort By drop-down, select the first column header by which to sort.
  5. From the Sort On drop-down, select Values.
  6. 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.