# Jan's Working with Numbers

## Formulas: Subtotals: Sort

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.

Check the Sort dialog if you get unexpected results.
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.)

## Sort Order

• Numbers: normal numerical order - 1, 2, 3, 4, ...10, 11, 12...20, 21, 22....
• Text: normal alphabetical order - a, b, c, ....
• Combination of text and numbers:
(space) ! " # \$ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = >
0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

The order is symbols then numbers then letters.

You may be surprised at how combinations of letters, numbers, and symbols are sorted. For example, in ID numbers with both letters and 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. In this example, the first and second characters from the left 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:
A#10
A#100
A#11
A#120
A10
A100
A11
A120

Most of us would probably 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'.

 A#1 a#10 A#100 a#11 a#120 a10 A100 a11 a120 b\$12 B\$10 b%12 b%8 14ab 160ab 21x 210c

Experiment: Sorting mixed text

• Copy the values in the list at the left (which is in a table) 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?
• Add more values to the column and use different symbols, numbers, and letters.
• Practice until you get a better feel for how Excel sorts.

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'.

## Step-by-Step: Sort

 What you will learn: to copy a whole sheet to move a column to use Sort dialog - multiple columns and a custom list

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.

1. Open  trips20-Lastname-Firstname.xlsx on your Class disk in the excel project3 folder.
2. Save As  trips21-Lastname-Firstname.xlsx  to your Class disk in the excel project4 folder.
If the folder does not exist, then create it.

3. Change the header for each sheet to read Excel Project 4.
[Hint: Select all the sheet tabs at once and edit the header. Do not forget to ungroup the sheets!]

### Copy: Sheet

1. Select the sheet Specials.
2. Right click on the sheet tab and from the context menu select  Move or Copy…
The Move or Copy dialog appears.

3. Leave in the To book text box trips21-Lastname-Firstname.xlsx .
4. Select in the Before sheet text box Tahiti-linked.
5. Check Create a copy.

6. Click on OK.
A new sheet named Specials(2) appears. It contains a copy of the data on the sheet Specials.
7. Rename the new sheet  Agents Totals .
(Hint: Double-click the tab and type. Press ENTER.)
8. Delete Rows 28 through 38 on the new sheet since they don't relate to the agents.
9. Save.
[trips21-Lastname-Firstname.xlsx

### Move: Travel Agent Column

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.

1. Select column B, the Travel Agent's column.
2. Right drag and drop at the left of column A.
3. From the context menu select  Shift Right and Move .
Whoops! A message appears. By selecting the whole column, you also caught the merged title cells. You will have to try this another way.
4. Click on OK to close the message.
5. Select cells B4:B23, the cells in the Travel Agent column that actually have data.
6. Right drag the selection and drop when the dotted outline surrounds the cells in column A.
7. From the context menu select Shift Right and Move.
Success!
The Travel Agent data moved to column A and the Trip data shifted over to column B.
8. AutoFit columns A and B.
9. Save.
[trips21-Lastname-Firstname.xlsx]

### Sort: Dialog - on Travel Agent

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!

1. In the Name Box, type A4:F23 to select that range.
2. From the ribbon on the Data tab, click the Sort button.
The Sort dialog opens.
3. If necessary, check the box My data has headers.

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...

4. For the first sort, select Travel Agent, Values, A to Z.