Software

How to sort a single column in Excel without disrupting the rest of the spreadsheet

We tend to view spreadsheet data as a whole, and that's as it should be. Seldom does a single column of values mean much to us without some related data to define it. Together, seemingly useless values become information. In fact, that's the definition of data and information. Information is an aggregate — a collection of data that's greater than the sum of its parts.

Occasionally, the big picture gets in the way. That happens when you need to view a subset of the data. Actually, viewing that subset isn't the problem, but manipulating it can be. For instance, suppose you want to sort a unique list of cities, countries, ZIP codes, and so on. A simple change of perspective is all you really need.

Copy the single column or subset to an out of the way spot so you can sort it separately from the source spreadsheet. To do this, select the column you want to sort by clicking the column's header cell.

jan2008blog5fig1r.jpg

Next, choose Filter from the Data menu and then select Advanced Filter. In the Advanced Filter dialog box, click the Copy To Another Location option. If you want a unique list, be sure to click the Unique Records Only option. Then, enter an out-of-the-way cell in the Copy To option. Or click that option and then click a cell.

jan2008blog5fig2r.jpg

Click OK to copy the contents of the column. Now, you can sort the copied list by clicking any cell in it and choosing Sort from the Data menu. By default, Excel selects the Header Row option. Be sure to update this option accordingly.

jan2008blog5fig3r.jpg

Then, click OK. The result is a sorted list. Of course, this list isn't dynamic. As you add records to the spreadsheet, you'll have to re-create the copied list.

jan2008blog5fig4r.jpg

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox