Sorting in Excel 2007 by ert634


									                                           Sorting in Excel 2007

A Bit of Basic Information
• New in 2007: don’t have to select ALL data, can just select one cell in a table if want to sort on only one
  column (column where cell selected is default for sorting)
• Won’t work properly if there are blank rows/columns in between – will only sort up to the blank.
• Use column width/row height & cell margins if want to have space between.
• Default sort is by column, but can set to sort by row

First Things First: Check Formatting
• make sure there are no extra spaces in the cells -- before, after or between items
• make sure data in the column you want to sort is in the correct format
• Select a column (or a cell in a table column) containing the data you want to sort
        Home tab Number group Dialog Box Launcher next to Number Category box choose
        appropriate format
               For text: make sure all are formatted as text (including numbers)
               If includes numbers as both numbers and text, numbers are sorted (and appear in results) before text
               If want case-sensitive: Data tab Sort & Filter group Sort Options Case Sensitive OK
        If sorting numbers, make sure all are formatted as numbers
               esp. check, eg, negative numbers imported from some accounting systems; a number entered with a
               leading ' (apostrophe)
        If sorting by dates/times, make sure are formatted as dates or times
               must be stored as a date or time serial number to sort correctly
               if Excel cannot recognize a value as a date or time, will store as text.
               Hint: dates that are formatted as text default to left-aligned instead of right-aligned in a cell

Commonly Used Sort Methods

Sorting by Columns:
• Select a row (or a cell in a table) containing the data you want to sort
• Data tab Sort & Filter group
         for text: click either “Sort A to Z” OR “Sort Z to A”
         for numbers: click either “Sort Smallest to Largest” OR “Sort Largest to Smallest”
        for dates or times: click either “Sort Oldest to Newest” OR “Sort Newest to Oldest”

Sorting by Rows:
• Select a row (or a cell in a table) containing the data you want to sort
• Data tab Sort & Filter group Sort (opens dialog box) Options (opens dialog box) Orientation Sort
  left to right OK Row Sort by select row to sort Sort On: Values Order: select appropriate order

After Changing or Adding Any Data
• need to sort again to incorporate new info
• applies to all forms of sorting
• click cell in range or table Data tab Sort & Filter group        Reapply

                                                    Page 1 of 4 
Advanced Sorting

Sort by Cell Color, Font Color, or Icon
• Used if you have manually or conditionally formatted by these criteria
• There is no default cell color, font color, or icon sort order. You must define the order that you want for each
  sort operation.
• To sort:
        Select a column (or a cell in a table column) containing the data you want to sort
        Data tab Sort & Filter group Sort (opens dialog box) Column/Row Sort By: column/row want
        to sort Sort On: select Cell Color OR Font Color OR Cell Icon Order: select color or icon, then
        indicate position (On Top/On Bottom OR Left/Right) OK

Sort by a Custom List
• You can use a custom list to sort in a user-defined order.
        custom list must be based on a value (text, number, and date or time).
        cannot create a custom list based on a format (cell color, font color, and icon).
        maximum length 255 characters, and the first character must not begin with a number
• To create a custom list:
        Type into a range of cells: values that want to sort by, in order want them, from top to bottom.
             example: A1 High A2 Medium A3 Low
        Select the range that you just typed.
        Microsoft Office Button      Excel Options Popular category Top options for working with Excel
           Edit Custom Lists
        In Custom Lists dialog box Import OK (twice)
• To use your custom list:
        Select a column (or a cell in a table) containing the data you want to sort
        Data tab Sort & Filter group Sort (opens dialog box) Column Sort by OR Then by: select
        column want to sort Order: Custom List (opens dialog box) select list OK

Sort by More Than One Column or Row
• Data that you want to group by value in one column or row, then sort within that group
• up to 64 columns
        example: can sort by department, then by name
        should have column headings.
• To sort:
        Select a range of (or a cell in a table with) at least two columns containing the data you want to sort
        Data tab Sort & Filter group Sort (opens dialog box) Under Column Sort by select first
        column want to sort Sort On: select the type of sort Order: select how to sort
        click Add Level and repeat steps for Column, Sort By and Sort On
        repeat for each additional criterion
• Can modify sort criteria and order once entered
        Must keep at least one entry in the list.
        To copy a column to sort by: select entry for the column Copy Level
        To delete a column to sort by: select the entry for the column Delete Level.
        To change the sort order: select entry want to move         Up Arrow OR Down Arrow
        Entries higher in the list are sorted before entries lower in the list.

                                                    Page 2 of 4 
Sort by a Partial Value
• Sorting is based on the entire value in a column.
• to sort by part of a value in a column, need to split the column so that the value you want to sort by is in its own
• examples: part number code (789-WDG-34), last name (Carol Philips), or first name (Philips, Carol
• To do that, use the Convert Text to Columns Wizard
        Data tab Data Tools group Text to Columns follow on-screen instructions

Potential Causes of Unexpected Sort Results:
• Values returned by a formula have changed: make sure that you reapply the sort or do the sort again to get up-
  to-date results
• Hidden columns/rows are not moved when you sort: before sorting, it's a good idea to unhide the hidden
  columns and rows
• Sort orders vary by locale setting: make sure that you have the proper Regional Settings in your computer’s
  Control Panel
• Column headings should be in only one row: if you need multiple line labels, wrap the text within the cell
• Header Row needs to be turned on/off: usually best to have a header row to make it easier to understand the
        May sometimes be necessary to turn on/off header row to exclude the first row (column heading) OR
        include the first row (not a column heading) for proper sorting
        Home tab Editing group Sort & Filter Custom Sort select OR clear “My Data Has Headers”

• Allows to show only data meeting specified criteria
• Data to be filtered must include a header
• Can sort filtered data
• To create a filter:
        Select the header row of the data you want to filter
        Data tab Sort & Filter group Filter click drop down arrow in header Text Filters or Number
        Filters (depends on data) set criteria
        Data not meeting the selected criteria will be hidden
              To make visible again, click again on the drop down arrow and select Clear Filter
              To sort filtered data, click again on any drop down arrow in header row and select sort criteria

                                                     Page 3 of 4 
    Pivot Tables
•   Used for summarizing , analyzing and comparing large amounts of data
•   Allows for filtering, sorting, grouping, and conditionally formatting data subsets
•   Creating requires that you: define source data, specify a location for the table, lay out fields
          data should be in list format
               column labels in the first row, with data appropriate to headings in rows below
          no blank rows or columns within the data of interest
          column labels are used as default field names
•    To create a pivot table from data in an existing Excel worksheet:
          Select a cell within the data range Insert tab Tables group click PivotTable select type of data
          to use confirm or select data Next select where table should appear (recommended: New Sheet)
          Note: if/when data source changes, will need to update the Pivot Table
               Options tab Refresh each time open the table OR
               set for automatic updating: PivotTable Tools menu PivotTable group Options Refresh
               When Opening

Last update: June 2010 

                                                 Page 4 of 4 

To top