Join Dennis Taylor for an in-depth discussion in this video Rearranging columns with Sort, part of Excel 2011 for the Mac: Managing and Analyzing Data.
- In this workbook we're looking at a worksheet called column sort. Although rarely used, the time may come when you need to sort data by rearranging the order of columns. And in this particular list here you could imagine wanting to take the columns as we read them from left to right and making them alphabetical. So in the left-hand column we might want to see Albuquerque and then Boston and then Chicago and so on as we look at the data. Atlanta in there too of course. So, a little bit different as we approach this though, we can't just casually click inside the data anywhere.
We must highlight the data specifically. If we're gonna be rearranging these columns left to right you could imagine not wanting to change column A at all. We simply want to highlight this data here. All the column are data. Now we can do this one of two ways: highlight the data as I showed you right here. Or since we know, or we would know that there's no data below this we could simply drag across the column letters B through M. Either way, after highlighting the data, then we go to the Data tab, the drop arrow to the right of sort, choose Custom Sort.
And what's going to be different here is that we first go to Options. Notice before we do that we see the word Column up here. We usually sort on the basis of what's in a column. Let's click Options. And then we see the choice, and it might not ever have occurred to you before the need arose, sort left to right instead of top to bottom. Then OK. That word Column in the background has been replaced by Row. So we want to sort this data by Values, in the order A to Z alphabetically, but that now means left to right.
On the basis of what's in Row one in this case. Of course at other times it could be on the basis of other rows but not here. And I think nearly all the time if you see this example it's likely to be row one here. So we're about to move those columns left and right. And we're gonna see Albuquerque over there in column B. The first number under Albuquerque is easy to remember it's all twos. So we expect to see that number there. Of course everything that's currently in column M will eventually be over in column B as we complete the sort. And then we'll see Atlanta and Boston and Chicago and so on.
Click OK. And there it is. There's the Albuquerque data with the all twos. Atlanta, Boston, Chicago. We moved the columns left and right and all the numbers below each of the headings has been moved with it. So sorting left to right, column sort, unusual but yet needed at times and easy to get to.
- Multiple-key sorting
- Sorting columns left-to-right
- Filtering columns
- Creating a top-ten list with values or percentages
- Using Advanced Filter for complex filters
- Setting up subtotals
- Using the Remove Duplicates command
- Using SUMIF, COUNTIF, and related functions
- Working with the database functions, such as DSUM and DMAX