Sorting can be done in Excel from the ribbon or the Sort dialog.
The Sort A-Z and Sort Z-A buttons do their sorting based only on the first column in the selection.
The Sort button opens the Sort dialog, which has options for how to sort and allows up to 64 levels in a single sort. For example, you could sort on Last Name, First Name, and then Middle Name. Or you could sort on region, month, and sales amount.
If your sort does not work as expected, check the Sort dialog. Make sure you selected the correct order for the columns to sort on and also the correct sorting order (A to Z, Z to A, etc.)
You may be surprised at how combinations of letters, numbers, and symbols
are sorted. For example, in ID numbers like A10, A11, A100, A120, A#10,
A#11, A#100 and A#120, Excel treats the numbers as text. Excel sorts text by
looking at the characters from left to right. The first and second
characters from the left in this example are A and # or A and 1.
Sorting on the third character from the left puts 0 before 1 and 1
before 2. That sounds good... until you look at the results of the sorting:
Most of us would usually put these in order A10, A#10, A11, A#11, A100, A#100, A120, A#120. We would use alphabetic order for letters and number order for the number part, and just ignore the # entirely. Computers don't do that!
Apostrophes (') and hyphens (-) are usually ignored. They are not in the Combination list above. The only exception: If two text strings are the same except for a hyphen, the text with the hyphen is sorted last. So, 'Peter Pan syndrome' comes before 'Peter-Pan syndrome'.
Experiment: Sorting mixed text
Copy the values in the table at the left and paste into a blank Excel sheet in column A and again in column C. Make a guess as to how Excel would sort these values. Select the values in column C and click the Sort A-Z button. How good was your guess??
How would you sort these mixed values by hand? What 'rules' would you use?
Warning: Check the top cell. Was it included in the
sort? Excel's default is to assume that the top cell is a heading and leave
it in place. To change this behavior, open the Sort dialog and uncheck the box for 'My data has headers'.
|What you will learn:||to copy a whole sheet
to sort on the first column
to sort on multiple columns
to sort with a custom list
Start with: trips20-Firstname-Lastname.xlsx (saved in previous lesson)
You have decided to look at the subtotals for each of the three travel agents who handled the special offers.
The data on the sheet Specials is currently arranged by Trip. You could pick out the trips for each agent yourself and add them up or use a formula to add just those cells. You could easily overlook one though, even in a list as short as this one. If you added more trips later, your formula would be out-of-date.
Excel's Subtotal command will be very useful in this situation. But, if you subtotal right now, you will get subtotals anywhere the trip changes in the first column. To get subtotals for travel agents, you must rearrange the data so that the rows for each travel agent are grouped together. The data will be easier to read if Travel Agent is the first column.
Select carefully: Before you sort, be sure that you selected all the data that goes together on each row.
Save As trips21-Firstname-Lastname.xlsx to your
Class disk in the excel project4 folder.
If the folder does not exist, then create it.
The table will read better if its first column is the one you sort on. You will need to move the Travel Agent column to the far left.
Next you will to sort the table to get each travel agent's data together.
A natural sorting method would be to use the Sort dialog and choose to sort A to Z for Travel Agent, then Trip, and then Customer. But the trips would be in alphabetical order instead of the Custom List order. The Sort dialog can handle this!
If you do not include the header row in your selection, the column labels do not show in the drop lists, just column headers - A, B, C...