Tips on Sorting Data in Excel 2007

Written by: John Garger • Edited by: Bill Fulks
Updated Sep 7, 2010 • Related Guides: Microsoft | Excel | Excel Spreadsheet

An overlooked function on the Excel 2007 Ribbon is the sort command. Learn how to do simple sorting to manager large spreadsheet files.

Excel Sorting

Basic spreadsheet use in Excel 2007 requires you to know how to format data, write formulas, and manipulate the cells to display the information you need. However, many Excel 2007 users stop there and do not explore some of the more powerful yet simple to use functions located on the Office 2007 Ribbon.

Sorting is the process of listing data in a more convenient format. For example, you may wish to sort data according to last name in the case of addresses, or you may want to sort the data by sales receipts from highest to lowest. You can even sort your data by date to show your expenses for your small business. However, there is one caveat to sorting data in Excel 2007. Read on to learn how to use the sort function without running into problems.

How to Use the Sort Function in Excel 2007

click to enlarge

Suppose you have a long list of addresses that you typed into Excel and you want to sort the information by last name so you can locate certain addresses more quickly. To sort by last name, select the column that contains the last names by clicking on the column header that contains that information. Then click on the DATA tab in the Excel 2007 Ribbon and select the SORT button (See Figure 1).

Immediately a window pops up warning you that you have data next to the cell that you intend to sort (see Figure 2). This is the one caveat when sorting in Excel 2007. Data in the rows of a spreadsheet are not considered to be related in any way. You see, spreadsheets are like two-dimensional databases. They are used for calculations and data manipulation, but data in Excel 2007 are not considered to be “cases” of related data. If you were to sort the column without selecting the data next to it, only the column would sort and the last names would no longer match up to their appropriate first names, addresses, cities, and zip codes. In the window that pops up, click EXPAND THE SELECTION and then click OK.

click to enlarge

Another window opens and now you can select exactly how you want your data to sort (see Figure 3). Click on the arrow next to the SORT BY drop-down box and select the data by which you want to sort. In this example choose LAST NAME. Notice that in the ORDER drop-down box, A to Z is the default sort method. Click OK and your data fields are now sorted by last name. Notice also that the rest of the information correctly lines up with the last names. This is because we chose the entire selection of data and not just the one column.

click to enlarge

This simple example should get you on your way to doing more complex sorting. You can even sort by cell color in case you have tagged cells with a certain kind of data or even by font color if you use black for positive values and red for negative values. Play around with the sort function and you will realize just how powerful this function is in Excel 2007.

Conclusion

Sorting data in Excel 2007 is a powerful function that can reduce the time you spend working within a spreadsheet. By sorting information in a spreadsheet, you can find data much more quickly and spend less time pointing and clicking to find what you need. More advanced sorting functions in Excel can even allow you to do some basic queries that eliminate the need for more complex programs such as Access 2007, an accessible but more complicated database. Used as a two-dimensional database, many people find that Excel does all they need it to do for storing and representing information in useful ways.