Search

In Stock

succeeding in business with microsoft excel 2013 1st Debra Gross sm

Instant delivery only

  • ISBN-10 ‏ : ‎ 1285099141
  • ISBN-13 ‏ : ‎ 978-1285099149

Original price was: $75.00.Current price is: $28.00.

SKU:tb1002044

succeeding in business with microsoft excel 2013 1st Debra Gross sm

Succeeding in Business with
Microsoft Excel 2013
Chapter 7: Organizing Data for Effective Analysis
A Guide to this Instructor’s Manual:
We have designed this Instructor’s Manual to supplement and enhance your teaching experience through classroom activities and a cohesive chapter summary.

This document is organized chronologically, using the same headings in black that you see in the textbook. Under each heading you will find (in order): Lecture Notes that summarize the section, Figures and Boxes found in the section (if any), Teacher Tips, Classroom Activities, and Lab Activities. Pay special attention to teaching tips and activities geared towards quizzing your students, enhancing their critical thinking skills, and encouraging experimentation within the software.

In addition to this Instructor’s Manual, our Instructor’s Resources CD also contains PowerPoint Presentations, Test Banks, and other supplements to aid in your teaching experience.

For your students:
Our latest online feature, CourseCasts, is a library of weekly podcasts designed to keep your students up to date with the latest in technology news. Direct your students to http://coursecasts.course.com, where they can download the most recent CourseCast onto their MP3 player. Ken Baldauf, host of CourseCasts, is a faculty member of the Florida State University Computer Science Department where he is responsible for teaching technology classes to thousands of FSU students each year. Ken is an expert in the latest technology and sorts through and aggregates the most pertinent news and information for CourseCasts so your students can spend their time enjoying technology rather than trying to figure it out. Open or close your lecture with a discussion based on the latest CourseCast.

Table of Contents
Learning Objectives
2
EX 428: Working with Text Data
2
EX 430: Combining Text Using the CONCATENATE Function
3
EX 431: Extracting Characters from a Text String
3
EX 438: Converting Text into Columns of Data
4
EX 441: Analyzing Data by Creating Subtotals
5
EX 443: Creating and Working with an Excel Table
5
EX 450: Importing Data from a Database into Excel
6
EX 456: Making Calculations with Date and Time Data
7
EX 460: Analyzing Data Using a PivotTable Report
7
EX 470: Evaluating Data Using a PivotChart Report
8
EX 473: Understanding Markup Languages and Excel
8
EX 476: Analyzing XML Data with Excel
9
EX 476: Importing XML Data as an XML Table
9
EX 479: Adding an XML Map to a Workbook
9
EX 481: Exporting XML Data
10
End of Chapter Material
10
Glossary of Key Terms
11

Learning Objectives
Students will have mastered the material in Chapter 7 when they can:

Level 1
• Import text data into a worksheet
• Concatenate values and extract characters from a text string
• Convert text into columns of data
• Analyze data by creating subtotals
• Create, sort, and filter an Excel table

Level 2
• Import data stored in a database into Excel
• Use dates and times in calculations
• Analyze data using a PivotTable report
• Create a PivotChart report

Level 3
• Understand markup languages and XML
• Import XML data into Excel as an XML table
• Add an XML map to a workbook
• Analyze XML data with Excel
• Export XML data from Excel into an XML document

EX 428: Working with Text Data
LECTURE NOTES
• Introduce the terms string, comma-delimited file, and comma-separated values (CSV).
• Use Figures 7.1 and 7.2 to discuss the data Barbara received from the retailers.

FIGURES
• Figure 7.1, Figure 7.2

BOXES
1. How To: Import Delimited Data. Discuss the steps involved in importing a text file of delimitated data. (EX 429)

CLASSROOM ACTIVITIES
1. Assign a Project: Ask students to complete questions 1 and 2 from the Conceptual Review.
2. Quick Quiz:
1. True or False: The comma-delimited file is the same format as a file with comma-separated values. (Answer: True)
2. True or False: Only CSV files may be imported into an Excel worksheet. (Answer: False )

EX 430: Combining Text Using the CONCATENATE Function
LECTURE NOTES
• Introduce the CONCATENATE function (see Figure 7.3).

FIGURES
• Figure 7.3

CLASSROOM ACTIVITIES
1. Quick Quiz:
1. The ____ function combines the values in a range of cells into one text item in a new cell. (Answer: CONCATENATE)
2. True or False: The ____ is called the concatenation operator and can be used instead of the concatenate function. (Answer: False)

EX 431: Extracting Characters from a Text String
LECTURE NOTES
• Introduce the RIGHT function and LEFT function (see Figure 7.4).
• Discuss the removal of spaces from a text string.
• Introduce the TRIM function (see Figure 7.5).
• Introduce the FIND function and SEARCH function.
• Introduce the #VALUE! error message (see Figure 7.6).
• Use Figure 7.7 to illustrate the Sort dialog box.
• Use Figure 7.8 to discuss sorting data in ascending order.
• Use Figure 7.9 to illustrate comma-delimited data.
• Use Table 7.1 to discuss common functions used to manipulate data in Excel.

FIGURES
• Figure 7.4, Figure 7.5, Figure 7.6, Figure 7.7, Figure 7.8, Figure 7.9

TABLES
• Table 7.1

BOXES
1. Best Practice: Preserving Data Before Making Changes. Discuss the importance of making backups as you make changes. (EX 438)

CLASSROOM ACTIVITIES
1. Assign a Project: Ask students to complete questions 3 and 4 from the Conceptual Review.
2. Quick Quiz:
1. The ____________ function removes extra spaces. (Answer: TRIM)
2. The ____________ function returns the last character or characters in a text string based on the number of characters specified. (Answer: RIGHT)

LAB ACTIVITIES
1. Ask students to copy the data shown below to an Excel worksheet. The goal is to create a list of comma-separated values from the data in column A by concatenating the company name, street address, city, state/zip code, and phone number. Separate the name, street address, city, state/zip code, and phone number information with commas. Trim each text string in the concatenated value to remove all spaces except for spaces between words.

Show the formula you would use in cell B1 and the results.
A B
1 Uptown Sportswear
2 12 Lake Drive
3 Madison, WI 53703
4 608-255-2079
5 LaFayette Sports
6 1513 LaFayette Street
7 Middleton, WI 53517
8 608-881-2525

Formula in B1:
=CONCATENATE(TRIM(A1),”,”,TRIM(A2),”,”,TRIM(A3),”,”,TRIM(A4))
Results in B1:
Uptown Sportswear,12 Lake Drive,Madison, WI 53703,608-255-2079

EX 438: Converting Text into Columns of Data
LECTURE NOTES
• Use Figures 7.10 and 7.11 to illustrate the Convert Text to Columns Wizard.
• Use Figure 7.12 to illustrate the Sort dialog box.
• Use Figure 7.13 to illustrate the sorted data.

FIGURES
• Figure 7.10, Figure 7.11, Figure 7.12, Figure 7.13

BOXES
1. How To: Use the Convert Text to Columns Wizard to Parse Data. Discuss the steps involved in converting a column of data from text to columns. (EX 438)

CLASSROOM ACTIVITIES
1. Quick Quiz:
1. True or False: Only CSV data can be parsed using the wizard. (Answer: False)
2. True or False: Fixed-width data may be parsed by the position of the data in the string. (Answer: True)

EX 441: Analyzing Data by Creating Subtotals
LECTURE NOTES
• Introduce the Subtotal tool (see Figures 7.14 and 7.15).

FIGURES
• Figure 7.14, Figure 7.15

BOXES
How To: Use the Subtotal Command. Discuss the steps involved in creating subtotals in a column of data. (EX 441)

CLASSROOM ACTIVITIES
1. Assign a Project: Ask students to complete questions 3 through 5 from the Conceptual Review.
2. Quick Quiz:
1. The ___________ creates summary reports that quickly organize data into categories with subtotal calculations, and lets you collapse and expand the level of detail in the report. (Answer: Subtotal tool)
2. True or False: One limitation of the Subtotal tool is that it works only with one category and one subtotal calculation at a time. (Answer: True)
3. Critical Thinking: How can you determine whether to use Microsoft Excel or Access to work with numeric and nonnumeric data? Can you develop some guidelines that will help you choose the appropriate application?

EX 443: Creating and Working with an Excel Table
LECTURE NOTES
• Introduce the terms flat-file database and Excel table.
• Discuss the advantages of an Excel table.
• Use Figure 7.16 to illustrate the Create Table dialog box.
• Use Figure 7.17 to illustrate an Excel table.
• Introduce the concept of filtering an Excel table (see Figure 7.18).
• Use Table 7.2 to discuss the options available for filtering data.
• Discuss how to perform calculations on filtered data (see Figure 7.19).
• Explain how to add data to an Excel table.
• Discuss how to remove an Excel table definition.

FIGURES
• Figure 7.16, Figure 7.17, Figure 7.18, Figure 7.19

TABLES
• Table 7.2

BOXES
1. Best Practice: Deciding When to Use an Excel Table. Discuss the difference between an Excel table and a database. (EX 444)
2. How To: Create an Excel Table. Discuss the steps involved in creating an Excel table. (EX 445)
3. Steps To Success: Level 1: Ask students to complete the assignments. (EX 449)

CLASSROOM ACTIVITIES
1. Assign a Project: Ask students to complete questions 6 through 8 from the Conceptual Review.
2. Quick Quiz:
1. A(n) ____________ is a range of cells that you formalize as a single unit of data. (Answer: Excel table)
2. True or False: When data is formatted as an Excel table, you can insert a new row into the table by selecting the last cell in the last row of the existing data, and then pressing the Tab key to create a new row. (Answer: True)
3. Critical Thinking: What advantages do Excel lists offer that other types of worksheet data do not?
EX 450: Importing Data from a Database into Excel
LECTURE NOTES
• Introduce the following terms: database, table, field, record, and database management system (DBMS).
• Introduce students to the concept of importing an Access table into Excel (see Figure 7.20).
• Use Figure 7.21 to illustrate the Import Data dialog box.
• Introduce Microsoft Query and the Query Wizard (see Figures 7.22 and 7.23).
• Use Figure 7.24 to illustrate the Query Wizard – Filter Data dialog box.

FIGURES
• Figure 7.20, Figure 7.21, Figure 7.22, Figure 7.23, Figure 7.24

BOXES
1. Best Practice: Combining Spreadsheets and Databases. Discuss the advantages of importing information from a database table. (EX 451)
2. How To: Import Data Stored in a Database Table into Excel. Discuss the steps involved in importing data stored in a database table into Excel. (EX 453)
3. How To: Use the Query Wizard to Import Data from Access. Discuss the steps involved in using the Query Wizard to import data from Access. (EX 454)

TEACHER TIP
Note that databases have the most advantages over spreadsheets when data is organized into a field/record structure and there is a large quantity of data.

CLASSROOM ACTIVITIES
1. Assign a Project: Ask students to complete questions 9 and 10 from the Conceptual Review.
2. Class Discussion: Have students discuss when they would choose to use a database over a spreadsheet.
EX 456: Making Calculations with Date and Time Data
LECTURE NOTES
• Introduce the TODAY function (see Figure 7.25).
• Introduce the YEARFRAC function (see Figure 7.26).
• Use Table 7.3 to discuss commonly used date and time functions.

FIGURES
• Figure 7.25, Figure 7.26

TABLES
• Table 7.3

BOXES
1. Best Practice: Formatting Cells with Date and Time Calculations. Discuss how you would format cells in Excel. (EX 457)

CLASSROOM ACTIVITIES
1. Assign a Project: Ask students to complete questions 11 and 12 from the Conceptual Review.
2. Quick Quiz:
1. True or False: The syntax for the TODAY function is: TODAY(). (Answer: False)
2. True or False: The syntax for the YEARFRAC function is as follows: =YEARFRAC(start_date,end_date,[basis]). (Answer: True)
EX 460: Analyzing Data Using a PivotTable Report
LECTURE NOTES
• Introduce the term PivotTable report.
• Use Figure 7.27 to illustrate the Create PivotTable dialog box.
• Use Figure 7.28 to illustrate the PivotTable layout.
• Use Figure 7.29 to illustrate the Recommended PivotTables dialog box.
• Use Figure 7.30 to illustrate a PivotTable report.
• Use Figure 7.31 to illustrate the Value Field Settings dialog box.
• Use Figures 7.32 and 7.33 to illustrate the PivotTable report modified to show the number of orders by state.
• Show students how to add to the Report Filter area (see Figure 7.34).
• Use Figures 7.35 and 7.36 to illustrate how to use slicers.
• Use Figures 7.37 and 7.38 to illustrate the Grouping dialog box.
• Use Figure 7.39 to illustrate how to drill down into data.

FIGURES
• Figure 7.27, Figure 7.28, Figure 7.29, Figure 7.30, Figure 7.31, Figure 7.32, Figure 7.33, Figure 7.34, Figure 7.35, Figure 7.36, Figure 7.37, Figure 7.38, Figure 7.39

BOXES
1. Best Practice: Deciding When to Use a PivotTable Report. Discuss the purpose of PivotTables. (EX 461)
2. How To: Create an Empty PivotTable Report. Discuss the steps involved in creating an empty PivotTable report. (EX 461)
3. How To: Add a Slicer to a PivotTable Report. Discuss the steps involved in using slicers to filter PivotTable data. (EX 467)
4. How To: Group Data in a PivotTable Report. Discuss the steps involved in grouping data in a PivotTable report. (EX 469)

TEACHER TIP
Note that, unlike any other table, you can change the data contained in a PivotTable by simply dragging a column to a new location in the PivotTable.

CLASSROOM ACTIVITIES
1. Assign a Project: Ask students to complete questions 13 and 14 from the Conceptual Review.
2. Class Discussion: Ask students to analyze the data in Barbara’s PivotTable report.
EX 470: Evaluating Data Using a PivotChart Report
LECTURE NOTES
• Use Figure 7.40 to introduce the concept of a PivotChart report.
• Use Figure 7.41 as an example of a PivotTable

FIGURES
• Figure 7.40, Figure 7.41

BOXES
1. How To: Create a PivotChart Report Using a PivotTable Report. Discuss the steps involved in creating a PivotChart report using a PivotTable report. (EX 472)
2. Steps To Success: Level 2: Ask students to complete the assignment. (EX 472)

CLASSROOM ACTIVITIES
1. Assign a Project: Ask students to complete the case problem for Level 1.
2. Quick Quiz:
1. The ____ field in a PivotChart functions like the Values area in a PivotTable report, in that it is the field in the data source that includes the data to summarize. (Answer: Values)
2. The ____ field in a PivotChart provides the series of data for the chart. (Answer: Legend)
EX 473: Understanding Markup Languages and Excel
LECTURE NOTES
• Explain that a markup language uses a set of tags to distinguish different elements in a document, and uses attributes to define those elements further.
• Introduce the following terms: markup language, Hypertext Markup Language (HTML), Standard Generalized Markup Language (SGML), document type definition (DTD), Extensible Markup Language (XML), and World Wide Web Consortium (W3C).
• Introduce the class to XML documents.
• Define the term metadata.

BOXES
1. Best Practice: Using XML Data in Excel. Discuss the use of XML data in Excel. (EX 476)

TEACHER TIP
Note that XML was designed to combine the markup power of SGML with HTML’s ease of use.

CLASSROOM ACTIVITIES
1. Class Discussion: Discuss the advantages of using XML in Excel.
2. Quick Quiz:
1. A(n) ____ is the link between the content in a document and the instructions for formatting that content. (Answer: markup language)
2. True or False: HTML is defined as an SGML document type. (Answer: True)
3. The ____ was created in 1994 with the goal of leading the World Wide Web to its full potential. (Answer: World Wide Web Consortium [W3C])
EX 476: Analyzing XML Data with Excel
LECTURE NOTES
• Define the term “well-formed.”
EX 476: Importing XML Data as an XML Table
LECTURE NOTES
• Show students how to import XML data as an XML table.
• Use Figure 7.42 to illustrate the XML document that Barbara created.
• Introduce the term root element.
• Introduce the terms schema, XML table, and XML map (see Figure 7.43).

FIGURES
• Figure 7.42, Figure 7.43

BOXES
1. How To: Import an XML Document as an XML Table. Discuss the steps involved in importing an XML document as an XML table. (EX 478)
EX 479: Adding an XML Map to a Workbook
LECTURE NOTES
• Show students how to add an XML map to a workbook (see Figures 7.44 and 7.45).
• Use Figure 7.46 to illustrate how to add XML data to an XML table.

FIGURES
• Figure 7.44, Figure 7.45, Figure 7.46

BOXES
1. How To: Display the DEVELOPER Tab. Discuss the steps involved in displaying the DEVELOPER tab. (EX 480)
2. How To: Add an XML Map to a Workbook. Discuss how to add an XML map to a workbook. (EX 480)

CLASSROOM ACTIVITIES
1. Assign a Project: Ask students to complete the case problem for Level 2.
2. Quick Quiz:
1. True or False: An XML document can have only one root element. (Answer: True)
2. A(n) ________ is a set of validation rules for an XML document. (Answer: schema)

EX 481: Exporting XML Data
LECTURE NOTES
• Use Figure 7.47 to illustrate Barbara’s XML document.
• Explain to students how Barbara converts the list of sports apparel retailers stored in an Excel worksheet into an XML table (see Figure 7.48).

FIGURES
• Figure 7.47, Figure 7.48

BOXES
1. How To: Export an XML Table to an XML Document. Discuss the steps involved in exporting an XML table to an XML document. (EX 482)
2. How To: Map All Elements from an Existing XML Map to a Worksheet. Discuss the steps involved in mapping elements from an XML map to a worksheet. (EX 483)
3. Steps To Success: Level 3: Ask students to complete the assignments. (EX 484)

CLASSROOM ACTIVITIES
1.
2. Assign a Project: Ask students to complete questions 16 through 19 from the Conceptual Review.
3. Quick Quiz:
1. True or False: XML documents are organized in a running tabular form with each element occupying one line. (Answer: True)
2. Excel table data is organized in rows and columns. Each ____ defines a record, and each column defines a field within a group of records. (Answer: row)
4. Critical Thinking: The inclusion of XML data in Microsoft Excel 2013 is a major enhancement. What else can you find out about XML that makes its inclusion so significant?

LAB ACTIVITIES
1. Assign a Project: Ask students to complete the case problem for Level 3.
End of Chapter Material
• Chapter Summary
• The Conceptual Review is a series of questions designed to reinforce concepts learned in the chapter.
• Case Problems contain three scenarios that allow students to explore the topics in the chapter. Each of the three case studies contains an assignment for students.

Glossary of Key Terms

• AutoFilter (EX 446)
• Comma-delimited file (EX 429)
• Comma-separated values (EX 429)
• Concatenate (EX 429)
• CONCATENATE function (EX 430)
• CSV (EX 429)
• Database (EX 450)
• Database management system (EX 451)
• DBMS (EX 451)
• Document type definition (EX 474)
• DTD (EX 474)
• Excel table (EX 444)
• Extensible Markup Language (EX 474)
• Field (EX 451)
• FIND function (EX 434)
• Flat-file database (EX 444)
• Header row (EX 440)
• HTML (EX 474)
• Hypertext Markup Language (EX 474)
• LEFT function (EX 432)
• Markup language (EX 474)
• Metadata (EX 475)
• Microsoft Query (EX 454)
• Modules (EX 476)
• Normalization (EX 451)
• Parse (EX 438)
• PivotChart report (EX 470)
• PivotTable report (EX 460)
• Query Wizard (EX 454)
• Record (EX 451)
• Relational database management systems (EX 451)
• RIGHT function (EX 431)
• Root element (EX 477)
• Schema (EX 477)
• SEARCH function (EX 434)
• SGML (EX 474)
• Slicer (EX 467)
• Standard Generalized Markup Language (EX 474)
• String (EX 428)
• Subtotal command (EX 441)
• TODAY function (EX 457)
• TRIM function (EX 432)
• XML (EX 474)
• XML documents (EX 475)
• XML map (EX 478)
• XML table (EX 477)
• YEARFRAC function (EX 458)

Top of Document

Reviews

There are no reviews yet.

Write a review

Your email address will not be published. Required fields are marked *

Back to Top
Product has been added to your cart