Docstoc

Advanced Excel

Document Sample
Advanced Excel Powered By Docstoc
					Lesson Plan Template
Lesson Plan Submitted by: Sharon Byler; sbyler@tiu11.org Lesson Plan Title:
Advanced Excel for eData

Learning Level: Experienced Excel User Pre-Instruction or Prerequisite Knowledge/Skills
Participants should have attended “Intermediate Excel using eData” training Participants should be familiar with Microsoft Office Participants should have prior experience with edata One three-hour session with ample time to practice new skills learned Computer lab

Lesson Duration

Learning Location Materials

Personal computer for each participant with Microsoft Excel installed Internet connection with web browser on each machine Edata templates and exported edata tables on CD as a backup Instructor computer with projection system (not required) At the end of the training, each participant will be able to:  Change Browser settings on their machine to best facilitate using the edata system  Create a folder (C:/data) on machine and save workbooks to folder  Download edata templates from the ABLENet web site  Export edata tables from the edata test site  Perform Excel functions as directed by the instructor Following: 1) The participant(s) will complete the Excel worksheet assignments (as assigned/viewed by the instructor) with 90% accuracy 2) The participant will complete a pre and post self evaluation and will show an increase in reported skill level in 90% of the areas

Learning Objectives

Procedure

Evaluation

Page 1

Advanced Excel using edata Following are Review Activities from the Intermediate Excel Lessons Browser/Monitor Settings
1) Start-Settings-Control Panel  Display o Settings Tab o Change Screen Area to 1024 x 768 2) Internet Explorer  Tools Menu-Internet Options o General Tab - Temporary Internet Files [Settings] - Select “Every Visit to Page” o IE 5.0 Security Tab - [Custom Level] - Enable Cookies o IE 6.0 Privacy Tab - [Advanced] - First Party = accept - Third Party = accept - Select “always allow session cookies”

Create Folder for edata Usage
1) Double click “MY Computer” on Desktop screen 2) Double click [C:] Icon  File Menu o New-Folder - Type: data

Downloading Excel edata Templates
1) Open Web Browser  Navigate to ABLENet website http://www.lhup.edu/ablenet/ o Follow Links - Edata – edata Templates o Double Click “Excel Template” link - Follow download program directions - Place files (edata.xls and edata_formula.xls) into C:/data folder 2) Open C:/data folder and review .xls files  Remind participants that these files are ONLY samples of edata

Page 2

Exporting Data from edata Test Site
Access edata test site at http://www.edatatest.ed.state.pa.us Supply Username and Password as supplied by instructor 1) Select Program Year (2002-2003) 2) Run Export – Adult table  File Menu – Save As o Save In - C:/data o File Name - Adult o Save As Type – Excel Workbook 3) Run Export – Adult Goals  File Menu – Save As o Save In - C:/data o File Name – Adult Goals o Save As Type – Excel Workbook Merging Multiple Workbooks Into One Workbook 1) Open both Excel files from the C:/data folder 2) On the Adult Goals Worksheet  Right Click on Tab Name o Select Move or Copy  To Book: o Adult.xls  Before Sheet: o Move to end 3) Export/merge additional tables as time permits 4) Perform previous Excel functions on Exported/Merged spreadsheets as time permits

Page 3

Manipulating edata with Excel-Advanced Functions
Combining Sort, Subtotals, and Filters Open Edata Adult Goals Worksheet 1) Sort Goals Ascending 2) Apply Subtotal to Goals  Data Menu – Subtotals o At each change in – Goal o Use Function – Count o Add Subtotal to – Goal 3) Search for goals set after 3/1/02  Data Menu – Filter – Auto Filter o Set Date Column (Column D) o Custom Filter - Set date: is greater than or equal to - 3/01/02 o Discuss Results - Includes subtotal  Continue with filters as suggested by participants that may be relevant in actual edata use 4) Remove all Subtotals and Filters Advanced Formulas for edata Find Average, Medium, Mode Instructional Hours 1) Open Adult Workbook file from C:/data folder  Adult Worksheet 2) Average Instructional Hours  Scroll to Instructional Hours Column (Column AK) o Click into empty cell at bottom of data in Column AK o Insert Menu – Function or Use Insert Function Toolbar Button o Function Name: Average - Verify all cells containing data in Column AK are being selected 3) Medium Instructional Hours  Scroll to Instructional Hours Column (Column AK) o Click into empty cell at bottom of data in Column AK o Insert Menu – Function or Use Insert Function Toolbar Button o Function Name: Medium - Verify all cells containing data in Column AK are being selected 4) Mode of Instructional Hours  Scroll to Instructional Hours Column (Column AK) o Click into empty cell at bottom of data in Column AK o Insert Menu – Function or Use Insert Function Toolbar Button o Function Name: Medium - Verify all cells containing data in Column AK are being selected

Page 4

VLOOKUP Formula Attach student name (Adult Worksheet) to Goal (Adult Goals Worksheet) 1) Open Adult Worksheet and Adult Goals Worksheet  Merge into same workbook  On the Adult Goals Worksheet o Right Click on Tab Name o Select Move or Copy - To Book: Adult.xls - Before Sheet: Move to end 2) Adult Worksheet: Name Range of cells containing data  Select row headings 2 through 12  Insert Menu – Name – Define o Names in Workbook - Adult 3) Adult Goals Worksheet  Insert column between Goal ID and Goal o Select column heading C o Insert Menu – Column - New column C is inserted  Name new column o “Student Name” o Move to next cell down  Adult Goals Worksheet: Insert VLOOKUP Function o Lookup value: (Adult ID column reference) - A2 o Table array: (range of cells named “Adult” from the Adult Worksheet) - Adult o Column Index: (column in adult worksheet where formula should look for the value to return) - 9 o Range Lookup: (finds a logical value to match) - False  Use Auto Complete to duplicate formula in C3:C28 Converting edata to Charts/Graphs Compare number of different goals Use Adult Goal Worksheet 1) Need to sort before subtotaling (may need to Auto Fit Column C)  Click into the Goal Column (Column C)  Sort Ascending 2) Data Menu – Subtotal  At each change in – Goal  Use function – Count  Add subtotal to – Goal 3) Look at “Level Buttons” on left side  Grand totals, Subtotals, Show all levels  Click to subtotal to level 2 Page 5

4) Insert Menu – Chart or Insert Chart Toolbar Button  Step 1 Chart Type o Pie  Step 2 Chart Source Data o Only rows containing subtotals-NOT Grand Total  Step 3 Chart Options o Title, Legend, data Labels as time permits  Step 4 Chart Location o As a new Sheet

Page 6


				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:5
posted:12/20/2009
language:English
pages:6
xl771209 xl771209
About