6
6Microsoft Office 2007 - Illustrated Creating a Custom Filter Perform complex filters using options in the Custom AutoFilter dialog boxPerform complex filters using options in the Custom AutoFilter dialog box Filter data based on two entries in a single columnFilter data based on two entries in a single column Use comparison operators, like “greater than” and “less than”Use comparison operators, like “greater than” and “less than”

9
9Microsoft Office 2007 - Illustrated Creating a Custom Filter (cont.) “And” and “Or” logical conditions“And” and “Or” logical conditions Narrow a search using either the And or Or buttons in the Custom AutoFilter dialog boxNarrow a search using either the And or Or buttons in the Custom AutoFilter dialog box Referred to as logical conditionsReferred to as logical conditions

10
10Microsoft Office 2007 - Illustrated Filtering a Table with Advanced Filter Search for data that matches complicated criteria in more than one column using the Advanced Filter commandSearch for data that matches complicated criteria in more than one column using the Advanced Filter command Use “And” and “Or” conditionsUse “And” and “Or” conditions Define a criteria rangeDefine a criteria range A criteria range is a cell range containing one row of labels and at least one row underneath the row of labels that contains the criteria you want to matchA criteria range is a cell range containing one row of labels and at least one row underneath the row of labels that contains the criteria you want to match

12
12Microsoft Office 2007 - Illustrated Extracting Table Data When you extract data, you place a copy of a filtered Table in a range you specify in the Advanced Filter dialog boxWhen you extract data, you place a copy of a filtered Table in a range you specify in the Advanced Filter dialog box Prevents accidental clearing of the filter or the loss of complied recordsPrevents accidental clearing of the filter or the loss of complied records

14
14Microsoft Office 2007 - Illustrated Extracting Table Data (cont.) Understanding the criteria range and the copy-to location.Understanding the criteria range and the copy-to location. When you define the criteria range and the copy-to location, Excel automatically creates the names Criteria and Extract for these ranges in the worksheetWhen you define the criteria range and the copy-to location, Excel automatically creates the names Criteria and Extract for these ranges in the worksheet The criteria range includes the fields names and any criteria rows underneath themThe criteria range includes the fields names and any criteria rows underneath them The extract range includes just the field names above the extracted TableThe extract range includes just the field names above the extracted Table

15
15Microsoft Office 2007 - Illustrated Look Up Values in a Table The Excel VLOOKUP function helps you locate values in a TableThe Excel VLOOKUP function helps you locate values in a Table VLOOKUP searches vertically down the leftmost column of a Table then reads across the row to find the value in the column you specifyVLOOKUP searches vertically down the leftmost column of a Table then reads across the row to find the value in the column you specify Like looking up a number in a phone bookLike looking up a number in a phone book

16
16Microsoft Office 2007 - Illustrated Look Up Values in a Table (cont.) Location of value you want to search for Name of range to search Number of column to search Finds exact match

17
17Microsoft Office 2007 - Illustrated Look Up Values in a Table (cont.) Using the HLOOKUP and MATCH functionsUsing the HLOOKUP and MATCH functions The VLOOKUP function is useful when your data is arranged vertically, in columnsThe VLOOKUP function is useful when your data is arranged vertically, in columns The HLOOKUP function is useful when your data is arranged horizontally, in rowsThe HLOOKUP function is useful when your data is arranged horizontally, in rows Use the Match function when you want the position of an item in a rangeUse the Match function when you want the position of an item in a range

18
18Microsoft Office 2007 - Illustrated Summarizing Table Data Database functions allow you to summarize Table data in a variety of waysDatabase functions allow you to summarize Table data in a variety of ways Count, average, or total values in a field for only those records that meet specified criteriaCount, average, or total values in a field for only those records that meet specified criteria

19
19Microsoft Office 2007 - Illustrated Summarizing Table Data Name of database function Name of range the function will use Column label the function will use Range that contains the Table criteria DSUM(Table, K1, H40:H41)

21
21Microsoft Office 2007 - Illustrated Validating Table Data The Data Validation feature allows you to specify what data is valid for a range of cellsThe Data Validation feature allows you to specify what data is valid for a range of cells Restrict data to whole numbers, decimal numbers, or textRestrict data to whole numbers, decimal numbers, or text Specify a Table of acceptable entriesSpecify a Table of acceptable entries Set limits on entriesSet limits on entries Excel prevents users from entering any data which is considered invalidExcel prevents users from entering any data which is considered invalid

24
24Microsoft Office 2007 - Illustrated Creating Subtotals The Excel subtotals feature provides an easy way to group and summarize data in a TableThe Excel subtotals feature provides an easy way to group and summarize data in a Table Create a subtotal with the SUM functionCreate a subtotal with the SUM function Also use the COUNT, AVERAGE, MAX, and MIN functionsAlso use the COUNT, AVERAGE, MAX, and MIN functions

27
27Microsoft Office 2007 - Illustrated Summary Use Filter to retrieve recordsUse Filter to retrieve records Use custom filters to add logical conditions to a filterUse custom filters to add logical conditions to a filter Use Advanced filters to search for data in more than one columnUse Advanced filters to search for data in more than one column Create subtotalsCreate subtotals Use LookUp functions to find values in a TableUse LookUp functions to find values in a Table Use data validation to specify Table entriesUse data validation to specify Table entries