Project 5B Key Points

Use Advanced Sort Techniques

  1. Sorting on Multiple Columns - A quick way to select a large range of cells is to enter the range in the Name Box and hit enter.
    A database is an organized collection of rows related to a specific topic; each table row forms a record and each column forms a field. A query restricts the selection of records through the use of criteria conditions

    Once the range is selected, you can create the table: on the Insert tab, in the Tables group, by clicking Table and make sure the My table has headers check box is selected.

    On the Data tab, in the Sort & Filter group, click Sort and then in the Sort dialog box you can specify the column to sort by, what to sort by, and whether it should be in ascending or descending order. To sort by a secondary criterion, in the upper level corner of the Sort dialog box click Add Level, and then click what column to sort by, what to sort (e.g. Values), and whether the sort should be ascending or descending. You can add additional levels to the sort.
  2. Sorting by Using a Custom List

    You can use a custom list to sort in an order than you define. You can access a predefined custom list or create a new one by clicking the Order arrow in the sort dialog box and then choose Custom List. Excel has predefined custom lists for day-of-the-week and month-of-the year. To create a new list, make sure NEW LIST is selected. Under List Entries, enter the entries, one to a line, in the order you want the sort to use.

    When you create a custom list, it remains available for all workbooks on the computer on which you created the list. (pp. 342-343)

Use Customs and Advanced Filters

Use a custom filter to apply complex criteria to a single column. Use an advanced filter to specify three or more criteria for a particular column, to apply complex criteria to two or more columns, or to specify computed criteria. You can also use an advanced filter for extracting - copying the selected rows to another part of the worksheet.
  1. Filtering by Format and Value Using AutoFilter

    There are three types of filters than you can create with AutoFilter. You can filter by one or more values, you can filter by a format, or you can filter by criteria. Each of these filter types is mutually exclusive for the column.

    In an Excel table, filter arrows are automatically add in the header row of the table. A filter arrow, when clicked, displays the AutoFilter menu. In the menu, if you choose Filter by Color, you can then choose whether you want to Filter by Cell Color or Filter by Font Color and in either case you can select the color you want to filter by. One you make a selection, in you point to the filter arrow at the top, you will see a ScreenTip that indicates what you are filtering by, e.g. Course Name: Equals a Yellow Cell Color" where Course Name is the text at the top of the column. A small funnel displays to the right of the arrow indicating that a filter is applied. The number of records satisfying the filter will be displayed in the status bar at the bottom left. If you want to remove the filter, click the filter arrow at the top of the column and then click Clear Filter from [the name at the top of the column].

    To filter by value, you can right click over a cell in the desired column that contains the desired value. After the right click, point to Filter and then click Filter by Selected Cell's Value.
  2. Filtering by Custom Criteria Using AutoFilter

    By using a custom filter, you can apply complex criteria to a single column. For example, you can compare two values by using comparison operators such as Equals (=), Greater Than (>), or Less Than (<). To do this, click the filter arrow at the top of the column where you want to apply the filter (in this case "Enrolled", point to Number Filters, and then choose the comparison operation you want to use. At the bottom of the list of comparison operations, you can click Custom Filter to create a compound condition using either "and" or "or"

    To clear all filters, on the Data tab, in the Sort & Filter group, click Clear.

    In the previous case, we clicked on a filter arrow at the top of a column that contained numbers, so the choice Number Filters appeared in the dropdown. If you click on a filter arrow at the top of a column that contains text, the choice Text Filters will instead appear and one of the choices then will be "Contains". You again can apply "and" or "or" conditions for multiple comparisons.
  3. Using the Name Manager and Filtering by Using Advanced Criteria

    Use an advanced filter when you want to specify three or more criteria for a particular column, to apply complex criteria to two or more columns, or to specify computed criteria. When you sue the Advanced Filter command, you type the criteria on the worksheet above the range you want to filter.

    The first step in filtering by using advanced criteria is to create a criteria range - an area on the worksheet where you define the criteria for the filter. Typically the criteria range is placed above the data. It includes a row for the column headings which must match the column headings in the data range exactly. The criteria range must also include at least one row for the criteria. Separate the criteria range from the data by a blank row.

    If you name the range of cells containing the criteria range using the name Criteria (which is a predefined name recognized by Excel), the reference to the range will automatically appear as the Criteria range in the Advanced Filter dialog box. Excel also automatically assigned defined tables with names.

    In the Advanced Filter dialog box, for Action, the choices are "Filter the list, in-place" and "Copy to another location".

    You can use an asterisk (*) as a wildcard. Placing the * before text means to match any value ending in the text. Placing the * at the end would match any value beginning with the specified text. While an asterisk can substitute for one or more characters, a question mark (?) represents a single character.
  4. Naming Ranges and Extracting Filtered Rows

    If you extract - pull out the results of a filter to another area, the location where you copy the records is the Extract area and is commonly placed below the table of data. If you name the Extract area with the name Extract, Excel will automatically display in the Copy to box.

Subtotal, Outline and Group a List of Data

  1. Subtotaling, Outlining and Grouping a List of Data

    You can group and summarize a list - a series of rows that contain related data - by adding subtotals. The first step is to sort the data by the field for which you want to create a subtotal.

    With the range selected of the data you have sorted, on the Data tab, in the Outline group, click Subtotal. In the Subtotal dialog box, you use the At each change in box to specify when you do the operation specified in the Use function box (in this case Sum), and then in the Add subtotal to list, you specify what you want to add (in this case Annual Salary)

    Along the left edge of your workbook, locate the outline. The outline bar enables you to show and hide levels of detail with a single mouse click. At the top of the outline area, you can click buttons. Clicking a given button will hide all details of levels greater than that button (e.g. clicking the level 2 button will hide all Level 3 and 4 details.

    To remove the subtotals, in the subtotal dialog box, click the remove all button at the lower left.

revised 11/1/21. Comments to Bill Pegram, wpegram@nvcc.edu