Lesson Blueprint - Excel Section 4 (2nd half)



|Lesson Blueprint |

|Marquee 2010 |

|Application: Excel 2010 |

|Lesson: 20 |

|Working with Multiple Worksheets, Tables, and Other File Formats |

|Activities |

|4.5 Using Page Break Preview |

|4.6 Formatting Data as a Table; Applying Table Design Options |

|4.7 Sorting a Table by Single and Multiple Criteria |

|4.8 Filtering a Table |

|4.9 Inserting, Editing, Deleting, and Printing Comments |

|4.10 Creating a Workbook from a Template |

|4.11 Opening and Saving a Workbook in a Different File Format |

|4.12 Creating a PDF/XPS Copy of a Worksheet |

|Text Page Numbers: 131-150 |

|Learning Objectives |

|- Use Page Break Preview to manipulate page breaks |

|- Format data as a table |

|- Apply table design options |

|- Insert rows and columns into a table |

|- Add a total row to a table |

|- Sort and filter a table by single and multiple criteria |

|- Insert, edit, delete, and print comments |

|- Create a new workbook using a template |

|- Open and save a workbook in a different file format |

|- Create a PDF/XPS copy of a worksheet |

|END-OF-SECTION EXERCISES |

|Knowledge Check |

|Page 152, 15 short answer questions |

|Skills Review |

|Begins on page 153 |

|Review 1 Copy, rename, delete, and insert a worksheet; change the color of worksheet tabs; create a formula with a 3-D reference; |

|insert a date using the DATE function; create a formula that performs a date calculation; create a formula using the IF function; |

|merge and center titles; print multiple worksheets |

|Review 2 Format data in a worksheet as a table; filter and sort data in a table; add comments to a worksheet; print worksheet and |

|all comments |

|Review 3 Create a workbook using a template |

| |

|Skills Assessment |

|Begins on page 155 |

|Assessment 1 Insert a worksheet to serve as a summary sheet; link cells across worksheets; format titles; group worksheets; change |

|page orientation; change margin in worksheet; print grouped worksheets |

|Assessment 2 Format data as a table; filter and sort data; change scaling option of worksheet to fit on one page; print worksheet |

|Assessment 3 Add comments to a worksheet; print worksheet including comments |

|Assessment 4 Open a workbook saved in Excel 2003 format; format columns; format data as a table; format titles; scale and print |

|worksheet; save in Excel 2010 format |

|Assessment 5 Search Microsoft Office Online for information on file formats supported by Excel 2010; display the information in |

|formatted tables in a worksheet |

|Assessment 6 Use Internet to find information on Smartphones; create worksheet analyzing three Smartphones; organize information in|

|a table;Add clipart or other enhancements to improve worksheet’s appearance; print worksheet in landscape orientation, on one page;|

|print worksheet including comments. |

| |

| |

|Marquee Challenge |

|Pages 127-128 |

|Challenge 1 Create a new worksheet from a template and populate with data from Figure 4.3 and format as shown; insert a logo from a|

|file; delete rows; save, print, and close worksheet |

|Challenge 2 Use Help to find information on importing a text file; open text files using the Text Import Wizard; modify worksheet |

|as shown in Figure 4.4; save the worksheet as an Excel workbook |

|ESTIMATED TIMES |

|Lecture/Demonstration: 1 hour - flexible |

|Section tutorials: 1 – 1½ hours (homework time needed will vary depending on length of lecture/demonstration) |

|End-of-Section Exercises: 2 – 2½ hours |

|Student file(s) needed for entire section |

|Activities: WBQtrlySales.xlsx |

|WBCatering.xlsx |

|WBSchedule-Feb14.xls |

|WBInvestment.xlsx |

| |

| |

|Skills Review: WBPayroll.xlsx |

|WBInventory.xlsx |

| |

|Skills Assessment: NPCInternGrades.xlsx |

|PTMarqueeSch.xlsx |

|PTRentalCost.xls |

| |

|Marquee Challenge: TWBLogo.jpg |

|WEUSDistributors.txt |

|WECdnDistributors.txt |

|WELogo.jpg |

|Equipment Check |

|In Activity 4.6 students use the Time Card template. In advance of class check the computers in the lab to ensure the template is |

|installed. |

|PowerPoint Presentation Available: Marquee10_Presentation_ExcelS4.ppt, Activities 4.5–4.10 |

|LECTURE/DEMOSTRATION NOTES |

|In this lesson students learn a myriad of special Excel features. As students are wrapping up the last Excel section, these |

|features are intended to introduce students to other methods with which productivity in Excel is enhanced. |

| |

|Lesson Lead-In Discussion |

|In this lesson students learn a variety of features, such as filtering and sorting lists and inserting comments, that are used less|

|often when working with worksheets. Begin the lesson by mentioning that several topics will be covered within a short time. |

|Students should focus on the merits of each feature and how it could be used to increase their productivity when working with |

|Excel. |

| |

|If you have access to a computer connected to a video display projector, or are teaching students in a computer lab, demonstrate |

|the following tasks. If time is constrained due to previous discussion, focus the demonstration on the tasks preceded by an |

|asterisk (*): |

|- Have on disk or other storage medium the data files WBInvestment.xlsx and WBInventory.xlsx. |

|* Open WBInventory.xlsx and select the range A2:O52. |

|* Click the Format as Table button in the Styles group in the Home tab, and then choose Table Style Medium 6 from the drop-down |

|gallery. |

|* The Format as Table dialog box will have the A2:O52 range in the text box (with absolute cell references) and a check in the My |

|table has headers option. Click the OK button. |

|* Add data in row 53: Wild Rice, 25 lb bag, quantity of 1 for each month. Point out that because the data was entered in the row |

|immediately below the table, Excel has automatically expanded the table, including the column total in column O. |

|- With any cell in the table the active cell, click the Table Tools Design tab. |

|- Click the Total Row check box in the Table Style Options group. Note that Excel automatically adds a Total Row to the table, and |

|sums the values in column O. The value (1,045) will match the existing total row. |

|- Click the Undo button to remove the duplicate total row. |

|* Click any cell in the in column A in the table range. |

|* Click the Sort & Filter button (Editing group in the Home tab), and then click Sort Z to A in the drop-down list to sort the |

|table from largest to smallest value. |

|* Show how a table can also be sorted by clicking the filter arrows in the top row of the table. Click the filter arrow for column |

|A and then click Sort A to Z in the drop-down list. |

|* Sort by multiple criteria using the Sort & Filter button. Click the button and then choose Custom sort at the drop-down list. At |

|the Sort dialog box, add a level so that the table is sort first by Item and then by Unit, both from A to Z. Point out the |

|up-pointing arrows in the filter button for these two columns. |

|* Filter the table to show only those items that are purchased in bags. Begin by clicking the filter arrow in the Unit column. |

|Point out that Excel searches the field and displays each unique value in the drop-down list. |

|* Unselect every item except those with values of 10 lb bag, 25 lb bag or 50 lb bag. Click OK. |

|- Point out the filter symbol (funnel) that now appears in the filter arrow for this column. |

|* Further filter the table by clicking the filter button in column A and unselecting one or more values. |

|* Clear the filters at the filter buttons in columns A and B. |

|* Open WBInvestment.xlsx and leave WBInventory.xlsx open in the background. |

|* Insert a comment in cell B7 (for example, Based on interest rates posted on bank Web site). To do this, right-click the cell and |

|then click Insert Comment from the shortcut menu. |

|- Activate another cell in the worksheet and then hover over the comment indicator in B7 to redisplay the comment text. |

|- Edit the comment text by right-clicking B7 and choosing Edit Comment at the shortcut menu. |

|- Point out and explain the buttons in the Comments group (Review tab). |

|- By default, comments do not print. Mention that the Page Setup dialog box (in the Sheet tab) contains a Comments section in which|

|you can choose to print the comments as they are displayed in the worksheet or at the end of the sheet. |

|* Delete the comment in cell B7 by right-clicking the cell and choosing Delete Comment at the shortcut menu. |

|* Close WBInvestment.xlsx without saving. |

|* Create a new workbook from the Time Card template (under Installed Templates in the New Workbook dialog box). |

|* When the template displays show students how to add information to the cells using an example similar to the one shown below. |

|Point out that Total Hours updates as you enter values in the days of the week. |

| |

|[pic] |

| |

|* Close the timecard workbook without saving. |

| |

|* With WBInventory.xlsx as the active workbook, explain to students that they may need to share workbooks with colleagues who are |

|using a previous version of Excel, and therefore will not be able to open a workbook saved in Excel 2010 format. |

|* Save WBInventory.xlsx in the previous file format by clicking the Office button, pointing to Save As, and then clicking |

|Excel97-2003 Workbook. Keep the same name for the workbook, but point out that the Save As dialog box shows the file with the old |

|.xls extension. |

|- Explain the meaning behind the message in the Microsoft Office Excel Compatibility Checker dialog box – that earlier versions of |

|Excel did not contain all the formatting options in Excel 2010, and therefore some of the formatting will be lost or degraded when |

|saving to the older format. Click Continue. |

|* Reopen WBInventory.xlsx (Note: the Excel 2010 version) and save as a text file with comma-separated values. Click the Save As |

|button and then choose CSV in the Save as type drop-down list. |

|- Explain the warning regarding multi-sheet workbooks – only the active sheet will be saved. Click OK and explain the next warning |

|message regarding features and formatting that will be lost. Click Yes. |

|- Explain a comma delimited file is usually used to transfer data from Excel to another application such as a non-Microsoft |

|database. In csv files, columns of data are separated by commas and a carriage return is inserted at the end of each row. All |

|formulas and formatting features are stripped from the file. |

|* Close WBInventory.csv without saving. |

| |

|Wrap-Up Class Discussion Topics |

|Depending on time available consider the following topics: |

| |

|In this lesson students sorted and filtered an inventory list. Excel includes several features on the Data tab that allow Excel to |

|be used in a manner similar to a database. Show the Data menu and point out features such as Subtotals and Validation that are |

|features you would expect to find in a database application. |

| |

|Completing the four sections in Excel is a good introduction to the capability of the application. Students should be encouraged to|

|continue their exploration of the program on their own. Mention some of the advanced features that might be of interest including: |

|- Conditional formatting |

|- PivotTables and PivotCharts |

|- Formula auditing |

|- Track changes |

|- Worksheet protection |

|Remind students to use the Help feature and Microsoft Office Online to find information on these topics or any other feature that |

|is of interest. |

|Teaching Hints |

|Pages 134-135: Point out to students that formatting data in a worksheet as a table has the advantage of built-in filtering arrows |

|as well as access to many different predesigned table styles. |

| |

|Pages 136-139: Stress that sorting or filtering does not change data the in a worksheet. It only allows users to view the data |

|differently. Filtering is a very valuable tool when you need to view specific data from a large worksheet. Sorting simply lets |

|users view data in an order that is specific to the users need at a given time |

| |

|Page 142-143: Templates are used in Excel for the same reason as they are used in Word: they are preformatted documents. In Excel, |

|students will find that formulas and functions are built into a template, which may allow them to create documents beyond their |

|achieved level of creating and using formulas and functions. Some students may find it easier to click in the cells in which they |

|will enter information instead of navigating through the template with the Tab key. |

|Extra Tips for Students |

|Many of the table sorting and filtering tasks described in this section can be performed with the filter arrows or with the Sort & |

|Filter button. |

|Possible Work for Advanced Students |

|When working in the Sales Invoice template in Activity 4.9, students work with the Time Card template. Encourage students to |

|explore the other templates that are installed and those available at Microsoft Office Online. |

|Instructor’s Notes |

| |

| |

| |

| |

| |

| |

| |

| |

-----------------------

NOTE: Add the text circled in red.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download