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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related searches
- section 4.2 overview of photosynthesis
- section 4 2 overview of photosynthesis
- cellular energy section 4 1 answer
- 14th amendment section 4 explained
- section 4 of 14th amendment
- section 4 of the 14th amendment
- article 1 section 4 us constitution summary
- section 4 4 overview of cellular respiration
- night section 4 quiz
- cpa section 4 notes
- 14th amendment section 4 meaning
- 25th amendment section 4 explained