Instructor Reference Card [CF_NUM]
Excel Chapter 5 | Data to Information
Concepts–At a Glance Summary
§ KEY CONCEPTS (blue)–Most important concepts in this chapter
§ TIPS (red)–Useful shortcuts and information for more productive use of Word
§ STICKY POINTS (green)–Areas that might cause difficulty for students
A poorly designed table may result in flawed analysis.
Guidelines for construction of a data table:
The top row should contain the field names
Field names should be short, descriptive, and easy to remember
Formatting the field names makes them easy for users to identify
Each column must contain the same type of information for each row in the table
Separate the table from other elements of the spreadsheet
IMPORTING is the process of inserting data from another application.
STICKY POINTS–Importing does not maintain a connection to the original data. Use the Data Connection Wizard to create the connection.
Text file data is most often imported into an Excel worksheet using a DELIMINATOR, which separates one column from another.
The most common delimiters in a text file are commas and tabs
STICKY POINTS–If the file is a .csv file, Excel automatically converts the file and opens it. If the file is a .txt file, the Text Import Wizard will prompt you for information about the external data and then do the conversion.
Access databases may be imported into Excel in three ways:
As a table
As a PivotTable Report
As a PivotChart and Pivot Table Report
TIP–You can export the data in an Excel worksheet into a CSV or text file, or an XML document.
CONDITIONAL FORMATTING applies specific formats to cells that contain particular values. Use to highlight interesting cells or ranges of cells.
DATA BARS vary in length based on value relative to other values.
COLOR SCALES format cells with colors based on the relative value of a cell compared to other cells.
ICON SETS classify values into categories and display icons by category.
Apply conditional formatting using either QUICK FORMATTING or ADVANCED FORMATTING
Quick Formatting uses predefined formatting options from the Conditional Formatting drop-down list.
With advanced formatting, create a formatting rule.
Data becomes more useful and important when it is organized or sorted.
Sort by color or cell attribute
Filter records based on conditional formatting or cell attributes
Use predefined number filters
Create custom filters
A CRITERIA RANGE is an area separate from the data table and specifies the conditions used to filter the table.
Use with the Advanced Filter
Must be at least two rows deep and one column wide
First row contains the field names as they appear in the table; subsequent rows contain the conditions
Multiple values in the same row creates an AND condition
Values entered in multiple rows creates an OR condition
STICKY POINT–All conditions must be met in an AND condition.
STICKY POINT–At least one condition must be met in an OR condition.
STICKY POINT–Field names in the first row must match the field names in the table exactly.
A RELATIONAL OPERATOR is a symbol that is used to compare cell contents to another cell or value.
TIP–Remind students these are the same as the logical operators used in functions in Chapter 2
STICKY POINT–Use equal (=) and unequal (>) symbols to select records with empty and nonempty fields, respectively.
A RANGE NAME is a word or string of characters that represents a cell, a range of cells, or a constant value. A range name:
Can be up to 255 characters long, with a mixture of upper or lowercase letters, numbers, periods, and underscores
Must begin with a letter or underscore
Cannot have blank spaces or special characters
Should not look like a cell address
Can be used in any formula or function instead of cell addresses
Must be unique within a workbook
TIP–Ranges automatically adjust for insertions or deletions within a range.
TIP–Range names used in formulas are absolute references.
TIP–Excel can display an alphabetical list of the range names.
The DATABASE FUNCTIONS analyze data for selected records in a table. Database functions have three arguments:
Database range – the entire table
Field – the column in the database that contains the values operated on by the function
Criteria – defines the conditions to be met by the function
Commonly used database functions include:
DSUM–Calculates total of values in a field that meets specified condition(s)
DAVERAGE–Determines mathematical average of values in a field that meets specified condition(s)
DMAX–Identifies largest value in a field that meets specified condition(s)
DMIN–Identifies smallest value in a field that meets specified condition(s)
DCOUNT—Counts number of records for a field that meets specified condition(s)
DCOUNTA–Counts number of records that contain values (nonblank) in a field that meets specified conditions
A PIVOTTABLE is a way to quickly summarize long lists of data by categories.
Enables you to arrange data dynamically as your needs require
° Expand and collapse levels of data to facilitate focusing.
° Pivot, or move fields to different areas, to see different views of data.
A PIVOT CHART is an interactive graphical representation of the data in a PivotTable.
STICKY POINT–Deleting the PivotChart does not delete the associated PivotTable.
AREA OF A PIVOTTABLE REPORT
Area / DescriptionValues / Displays summary numeric data
Row Labels / Displays fields on the left side of the report to organize data into categories
Column Labels / Displays fields as columns at the top of the report to organize data into categories
Report Filter / Filters the entire report based on the selected item in the report filter
TIP–A page field adds a third dimension to a pivot table
TIP–You can create a calculated field, a user-defined field that does not exist in the original data source
Use the PivotTable Tools Design tab to format a PivotTable.
Sorting and filtering the PivotTable makes the data more manageable and easier to analyze.
STICKY POINT–Excel does not update PivotTables and PivotCharts automatically.
CONNECTIONS -- Practical Projects for the Student Excel User
· Creation of a data table that will be used for complex data analysis (Designing Tables Based on Data Table Theory)
· Import Employee data created as a text file (Importing Data from Text Files and Other Sources)
· Highlight in yellow the Employee salaries that are greater than $60,000 (Applying Conditional Formatting)
· Sort Employees by last name and select Employees with 5 years of service (Applying Advanced Filtering and Sorting Methods)
· Create a group of Employees as one entity such as the Management Department (Creating and Using Range Names)
· Average the salaries of all Employees (Using Database Functions)
· Show a graphical representation of Employee data (Creating and Deleting PivotTables and PivotCharts)
· Reload the PivotTable to effect enhancements to its information value (Formatting, Sorting, Filtering, Subtotaling and Refreshing a PivotTable)
Case Study Lecture Demonstration Document
Legal Specialties
CONNECTIONS -- Practical Applications to the Real World
In the following Case Study Demonstration Document, students learn about turning data into information. In the future, your students may need to:
Track patients in a long term care facility by primary medical condition
Follow the breeding lines of purebred dogs
Maintain a national list of organ donors
Track and sort customers by purchase and price points
1. Open the Student File: chap5_case_law.
2. Save the file as chap5_case_law_solution.
3. Sort the List
STEPS:
To sort a list by three levels:
· Click anywhere in the list of lawyers, click the Data tab, and click Sort in the Sort and Filter group to open the Sort dialog box.
· Click the Sort by drop-down arrow and click Last Name.
· Click the Add Level button.
· Click the Then by drop-down arrow and click First Name.
· Click the Add Level button.
· Click the Then by drop-down arrow and click City.
· Click OK to sort the list by last and first name and then by city.
4. Create a Pivot Table
STEPS:
To create a Pivot Table:
· Click anywhere in the list of lawyers, click the Insert tab, and click PivotTable in the Tables group to open the Create PivotTable dialog box.
· Verify that the Select a table or range is selected and that the Table/Range is Lawyers! $A$5:$G$87. Verify that New Worksheet is selected and click OK.
· Drag the Specialty field to the Row Labels area.
· Drag the City field to the Column Labels area
· Drag the Gender field to the Report Filter area.
· Drag the Last Name field to the Values area.
· Double-click the Sheet1 tab (the worksheet that contains the PivotTable), type PivotTable as the new name, and press Enter.
NOTE: The Pivot Table shows the number of lawyers in each specialty by the city where they are located. The filter is by gender but is set to both M and F, but could show only one or the other by selecting one from the drop down box. The Pivot Table counts the number of names in each category.
5. Create a Pivot Chart
STEPS:
To create a pivot chart:
· Click anywhere in the PivotTable, click the Options tab, and click PivotChart in the Tools group.
· Click Stacked Column, the first chart in the first row, in the Insert Chart dialog box, and click OK
To move and enhance the pivot chart:
· Click Move Chart on the Location group on the Design tab to open the Move Chart dialog box.
· Click new sheet, type PivotChart in the text box, and click OK.
· Close the PivotTable Field list pane if necessary and close the PivotChart Filter pane.
· Click Chart Title in the Labels group on the Layout tab and select Above Chart.
· Type Additional Corporate Lawyers are needed!
· Click Data Table in the Labels group on the Layout tab and select Show Data Table.
· Close the Pivot Table Field List.
6. Use Conditional Formatting
STEPS:
To format information based on a condition:
· Click the Lawyers tab, and select the cells A6:G87.
· Click the Home tab and click Conditional Formatting in the Styles group.
· Point to Highlight Cells Rules and select Less than to open the Less Than dialog box.
· Type 1/1/1995 in the Format cells that are Less than text box, click the with drop-down arrow, click Green Fill with Dark Green Text, and then click OK.
NOTE: The most experienced lawyers, who passed the bar before 1995, display highlighted in green.
7. Use Database Functions
STEPS:
To use database functions:
· Click cell F5 to make it the active cell. Click the Home tab, and click Copy in the Clipboard group.
· Move to cell D91 and click Paste in the Clipboard group.
· Select A5:G5, and click Copy. Move to A95 and click Paste.
· Move to D92 and type >1/1/2004.
· Click in anywhere in the list of lawyers, click the Data tab, and then click Advanced in the Sort and Filter group to open the Advanced Filter dialog box.
· Click Copy to another Location in the Action section; type or select the cells A5:G87 in the List Range box; type or select the cells D91:D92 in the Criteria Range box; type or select the cells A95:G105 in the Copy to box, and click OK.
· Click anywhere in the list of copied lawyers, and click Sort in the Sort and Filter group.
· Click Delete Level three times to remove any previous sort criteria in the Sort dialog box. Click Add Level, click the Sort by drop-down arrow, and click Date Passed Bar.
· Click the Order drop-down arrow, click Newest to Oldest, and click OK.
NOTE: The latest lawyer to pass the bar, who would be the least experienced lawyer, is listed at the top of the list.
· Click in cell D92 and type <1/1/1963.
· Click anywhere in the list of lawyers, click Advanced in the Sort and Filter group to open the Advanced Filter dialog box.
· Click Copy to Another Location in the Action section; check to be sure that the List Range contains the cells A5:G87; check to be sure that the Criteria Range box contains the cells D91:D92; check to be sure the Copy to box contains the cells A95:G105, and click OK.
NOTE: The first lawyer to pass the bar, who would be the most experienced lawyer, is listed.
8. Enhance the Worksheet
STEPS:
To make changes to all three worksheets:
· Select the range A91:G105, click the Home tab, and then click Delete in the Cells group.
· Click the Lawyers tab, press and hold Ctrl and click the PivotTable tab and then PivotChart tab.
· Click Margins in the Page Setup group on the Page Layout tab, select Custom Margins, and click the Horizontally check box in the Center on page section.
· Click the Header/Footer tab, click Custom Footer, and type Your Name in the Left section, click in the Center section, click Insert Page Number, and then type Your Instructor’s Name in the Right section.
· Click the Sheet tab; click the Rows to Repeat at Top and type A5:G5.
· Click the Page tab and click Landscape, and then click OK.
· Click Print Preview to verify that the PivotTable and the Sales Data will print correctly.
CAUTION: Instructing all students to print the same large file at the same time can cause chaos in a lab, so you might want them simply to view the results in Print Preview and skip printing or print at a later time.