add-in combo box date excel 2000 excel 2002 excel 97 excel XP format formatting formula grouping loan - amortization macros subtotal sum excel tutorial workbook worksheet vba
One of the most common operations in working with data in an Excel workbook is Sorting data. Read Chapter 15, Data and the guidelines for sorting data carefully. Understanding them is important, both to prevent problems and to get the most out of the options available.
Guidelines to consider before sorting data
© Selecting cells
Do not select a column or data range in a data list on a sheet. Select only a single cell. Click the Sort icon to automatically sort the entire list. The data will be sorted according to the field of the cell selected.
© Formulas
Be careful when sorting data if there are formulas in the cells. Sorting data when cells are linked to other rows or cells in other sheets could distort the calculations.
Be meticulous when sorting a list with formulas that have Names (which are defined with absolute references by default) or with formulas that have absolute references.
© Insert a sequence column to restore original order
Insert an additional column into the data list with ascending numbers, 1,2,3,…(do not use a formula) before sorting the data.
If a list includes a column with consecutive dates, use this column as the first sorting column.
Performing a Simple Sort
1. Select Cell E5 (see figure below).
2. Click the Sort Ascending icon on the Standard toolbar.
The result – a table sorted in ascending order (alphabetically), according to the Market field.
Tip – Save space on the toolbar by removing one icon
By pressing Shift+Sort Ascending icon, the direction of the sort (AZ) is changed to descending (ZA).
Guidelines for Sorting Data
Excel sorts data according to a defined order.
© Values – Numerical values, including date and time, are sorted from the lowest (negative) to the highest (positive). Excel does not consider the format of the cell, only its contents. Date and time receive numeric values when data is sorted. (See Chapter 4, Date and Time).
© Text – Text is sorted first by symbols such as *, (,), $; then by ASCII characters; and finally, by letters of the alphabet. From the Data menu, select Sort, Options, and select the Case Sensitive box. Text with upper-case letters is sorted before text with lower-case letters.
© Logical values – False is sorted before True.
© Errors – Errors in cells that were created during calculation of formulas do not undergo internal sorting. They will appear next to last.
© Empty cells – Empty cells are always sorted last.
Sort Descending changes the sort order from the last to the first, except for empty cells, which are always last.
Tip – Deleting empty rows
When importing text files into Excel, use Sort to delete empty rows, delimiting characters and errors, which are all automatically placed at the end of a sorted list.
Sorting a Large Number of Fields
Select a cell in the data list, and from the Data menu, select Sort. The Sort dialog box includes sorting options of up to three fields. To sort more than three fields in a sheet, begin the sort with the last three fields in order of importance, and continue in ascending order until you arrive at the most important field.
Sorting by Column
The default setting for sorting in ascending or descending order is by row. Occasionally you will want to sort by column.
Sorting by column
1. From the Data menu, select Sort, Options.
2. Select the Sort left to right option.
3. Click OK.
In the Sort by option of the Sort dialog box, select the number of the row by which the columns will be sorted. In the figure above (with data), the second row is selected.
Example:
The list includes salary data and will be sorted according to the employees’ salaries, from the highest to the lowest. The structure of the list is such that the first row has the employee name and the second row has the gross salary. In the Sort by option, select the second row.
Result:
The columns in the table are sorted from the highest salary to the lowest.
Sorting by Custom Lists
As mentioned above, sorting by row is the default setting, and sorting by column is performed alphabetically, in either ascending or descending order. Occasionally, you will want to sort data according to different criteria, for example, by a list of months – January, February, March, etc., or according to a list of employees in some order other than alphabetical. A list of this kind is saved in Custom Lists.
The Market field (see page 256) contains four items – Western Europe, USA, Asia, Africa. The data will be sorted as follows – Asia, Africa, Western Europe, USA (not alphabetically).
Step 1 – saving a custom list
1. In a new sheet, in Cells A1:A4, enter the following items in this order – Asia, Africa, Western Europe, USA.
2. Select Cells A1:A4.
3. From the Tools menu, select Options, and then select the Custom Lists tab. Check the Import box to make sure that Cells A1:A4 match your selection.
4. Click Import.
5. Click OK.
Step 2 – sorting by a custom list
1. Select one of the cells in the list.
2. From the Data menu, select Sort, and then Options.
3. In the First key sort order drop-down list, select the list you saved in Step 1.
4. Click OK.
5. In the Sort dialog box, select Sort by, and then select Market.
6. Click OK.
Inserting a Line between Groups of Data after Sorting
Insert a colored line to separate between one sorting group and another. To create separating lines between customer names, use Conditional Formatting.
Transferring the line between customer groups
1. Select the data list without the column headings row.
2. A shortcut for quick selection is to select Cell A2 and press Ctrl+Shift+Down Arrow. While holding down Ctrl+Shift, press Right Arrow.
From the Format menu, select Conditional Formatting.
3. In the Condition 1 drop-down list, select Formula Is.
4. In the formula box, enter the formula =$D2<>$D3. Be sure to enter the formula with absolute reference for the column and relative reference for the row.
5. In the Conditional Formatting dialog box, click the Format button, and then select the Border tab. Select underline and the color red.
6. Click OK twice.
Explanation
When the formula is calculated, the text cells in Column D are compared. If the name of the customer changes, a red separating line is created for all the cells of the row.