VIEWS: 10 PAGES: 4 POSTED ON: 5/31/2011
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 twice. 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 column. • 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 data. 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” Filtering • 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) OK 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
"Sorting in Excel 2007"