• Worksheet database (list) - organized collection of data
• example: phone book, grade books, list of company employees
• field - single item of data
• field names can be column titles
• record - group of related field names
• row in a worksheet

• 1. Add, Delete records
• 2. Change values of fields
• 3. Sort records
• 4. Subtotal for numeric fields
• 5. Display records on comparison criteria
• 6. Analyze data using database functions
• 7. Summarize information using pivot

• Pivot table - gives the ability to summarize data in database and then rotate the table’s row and column titles to show different views of the summarized data

• Involves entering field names in a row in worksheet and changing column widths so data will fit in columns.

• Eliminates any confusion
• the range assigned the name Database includes column titles, and one blank row below column titles
• Blank row is for expansion
• As records added using data form, Excel automatically expands the named range Database to include last record

• Data form - is an Excel dialog box that lists the field names in the database and provides boxes in which to enter field values
• considered to be most accurate and reliable method of data entry, since it automatically extends the range of the name Database to include any new records

• One database per worksheet
• Put blank row between database and rest of worksheet
• No worksheet entries in same rows as database
• Define name Database as database range
• maximum of 256 fields and 65,536 records in database

• Place column titles (field names) in 1st row of database
• No blank rows or dashes to separate column titles from data
• Apply different format to column titles and data (only necessary if database range not named Database)
• Column titles can be up to 32,767 characters in length

• Ascending - lowest to highest
• Sort Ascending button (Standard Toolbar)
• Descending - highest to lowest
• Sort Descending button (Standard Toolbar)
• Sort command on Data menu
• use sort buttons to sort a single field, use Sort command to sort on multiple fields
• sort key - field selected to sort records

• Excel requires that you sort database on the field for which you want subtotals (done 1st)
• Subtotals command on Data menu
• control field - field used to group the data into categories. (determines when subtotals calculate)
• Remove Subtotals - click Undo button, click Remove All button in Subtotal Dialog box

• Comparison criteria - one or more conditions that include the field names and entries in the boxes on data form

• no blank characters appear between relational operators
• case sensitive - Excel considers uppercase and lowercase in comparison criterion to be the same

• Question Mark (?) - replaces a single wildcard character

• Asterisk (*) - replaces a group of wildcard characters

• Computed criteria - using formula in comparison criteria

• Alternative to using data form that meet comparison criteria
• AutoFilter - displays all records that meet certain criteria as a subset of database
• data form displays only one record at a time
• point to Filter on Data menu, click AutoFilter
• Filter criterion - item selected from list

• If AutoFilter active - drop-down arrows used to establish filter and row headings of selected records in blue
• select cell within database prior to invoking AutoFilter if you have multiple lists
• if you select certain field names, Excel assigns arrows only to selected field names

• Remove filter criteria for single field, select the All option from list for that field
• Plan to have Excel determine automatic subtotals for filtered database, apply AutoFilter 1st and then apply Subtotals because Excel does not recalculate after selecting the filter criteria

• Criteria range - can be set up on worksheet and used to manipulate records that pass comparison criteria
• Alternative to using a data form or AutoFilter

• Copy database fields to criteria range to names spelled correctly
• Criteria Range is independent of criteria set up on data form
• Do not begin test for equality involving text with an = sign (M not =M for male)
• If you include a bank row in criteria range all records will pass test
• Can print criteria range by entering name Criteria in Print Area box

• Advanced Filter command similar to AutoFilter command, except it does not filter records based on Comparison criteria selected from a list.
• The Advanced Filter command instead uses the comparison criteria set up in a criteria range.

• Advanced Filter - command displays a subset of database like AutoFilter command.
• Primary difference - Advanced Filter command can create more complex comparison criteria, because criteria range can be as many rows long as necessary, allowing for many sets of comparison criteria,

• Select Copy to another location button in Action area of Advanced Filter dialog box
• Excel copies records meeting comparison criteria to another part of worksheet
• Extract range - location where records are copied
• can manipulate and print them as group

• Only some of the fields in the database need to be used to set up an extract range
• can be in any order
• typing field names not recommended
• Excel clears all cells below field names in extract range (if comparison criteria changed, excel clears extracted records before it copies a new set of records)

• Define Extract Range as Single Row - any number of records can be extracted from database
• Define Fixed-Size Extract Range - only fixed number of rows can catch the subset of data
• if more records extracted than rows available, Excel displays message extract range full

• OR - criteria range contains two or more entries under same field name, records pass test if either one is true
• AND - must duplicate the field name in criteria range to force both conditions to be true to pass test
• requires you to delete current name Criteria using Name command and must redefine it using the duplicate field name

• When comparison criteria under different field names are in same row, then records pass test only if they pass all comparison criteria
• If comparison criteria for field names are in different rows, then records must pass only one of the tests

• HLOOKUP - used table direction is horizontal across worksheet
• VLOOKUP - table direction is vertical or down worksheet
• most often used

• VLOOKUP function searches leftmost column of table (table arguments) for particular value (search argument), returns value from specified column (table values)
• general form:

• Table arguments must be in ascending order

• Microsoft Map - mapping feature in Excel showing the relationship between numbers and geographic regions
• Can embed a data map of any location in world in worksheet
• can be formatted (add labels, text, and pins)
• Map Button on Standard toolbar, after selecting range on cells that include state or country names

• Pivot table - gives ability to summarize data in database, then rotate rows and column titles to get different views of summarized data
• PivotTable command on Data menu starts PivotTable Wizard
• Does not modify database in any way (generates information on new worksheet)

• End
• Excel
• Project 6

Return to Elective Courses