Jan's Illustrated Computer Literacy 101 Logo:Jegsworks Jan's Illustrated Computer Literacy 101

Note: This site will be taken down July 1, 2024.

Home > Jan's CompLit 101 > Working with Numbers > Formulas > Subtotals > Sort
Icon: Arrow - Previous pagePrevious    NextIcon: Arrow - Next page

Jan's Working with Numbers

    Formulas: Subtotals: Sort

Sorting can be done in Excel from the ribbon or the Sort dialog.

Ribbon: Data > Sort & Filter > Sorting buttons (Excel 2010)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.

Dialog: Sort - two levels (Excel 2010)

TipCheck 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

Icon: Experiment 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'.
 

Icon Step-by-Step

Step-by-Step: Sort

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

Start with: Icon: Class disk trips20-Lastname-Firstname.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.

WarningSelect carefully: Before you sort, be sure that you selected all the data that goes together on each row.

  1. Icon: Class diskOpen  trips20-Lastname-Firstname.xlsx on your Class disk in the excel project3 folder.

  2. Icon: Class diskSave 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. Right Click Menu: Move or Copy (Excel 2010)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. Dialog: Move or Copy (Excel 2010)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. Sheet tabs: Special(2) (Excel 2010)Click on OK.
    A new sheet named Specials(2) appears. It contains a copy of the data on the sheet Specials.
  7. Sheet tabs: Agent Totals (Excel 2010)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. Icon: Class diskSave.
    [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. Message: You cannot change part of a merged cell. (Excel 2010)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. Travel Agent columns moved leftSelect 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. Icon: Class diskSave.
    [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. Dialog: Sort > Travel Agent A-Z, Trip custom list, Customer A-Z (Excel 2010)In the Name Box, type A4:F23 to select that range.
  2. From the ribbon on the Data tab, click the Sort buttonButton: Sort (Excel 2010).
    The Sort dialog opens.
  3. If necessary, check the box My data has headers.

    TipIf 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. Dialog: Custom Lists > Tahiti, New Zealand, World, Other (Excel 2010)For the first sort, select Travel Agent, Values, A to Z.
  5. Click on Add Level to add another sorting level.
  6. Select Trip, Values, and Custom List.
    The Custom Lists dialog opens.
  7. Click on the custom list you created earlier: Tahiti, New Zealand, World, Other.
  8. Data sorted on Travel Agetn (A-Z), then Trips (Custom list), then Customer (A-Z)Click on OK to close the Custom Lists dialog.
  9. Click on OK again to close the Sort dialog.
  10. Icon: Class diskSave.
    [trips21-Lastname-Firstname.xlsx]