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.
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.
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.
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.
Subscribe to the Developer Insider Newsletter
From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays