FIMC-VI 2011-2012 Webinar Series

1. FIMC-VI 2011-2012 Webinar Series Excel Spreadsheets for the Teacher of the Visually Impaired

2. Goals for Excel Webinar • Introduce Teachers of the Visually Impaired (TVIs) to use of spreadsheets for management of student information • Creating an Excel Spreadsheet • Data entry tips • Data management • “Without data all anyone has is an opinion.” W. Edward Deming

3. But why should I use Excel?? I like digging through mounds of paper and files!

4. “Big Picture” Purpose of Data • Organization, sharing, and ease of access • FIMC-VI annual student registration • Identify areas of strengths and areas of concern • Making decisions based on data (reality) and not perceptions (opinions)

5. Data Relationships District Database Reports, Data Analysis, Caseload Management

6. When do you use a spreadsheet? • More than 10 items of data • Complex data – several entries for same topic (IEP Dates) • Data changes frequently (IEP Dates) • Need to sort or manipulate data (IEP Dates)

7. QPVI Master List Questionnaire

8. QPVI Master List Questionnaire

9. Workbook • Allows you to control student information • Combine information from different sources • Retrieve information • Sort by dates, names, locations, grade levels, eye conditions, etc. • Analyze • Summarize • Report

10. Terms and Definitions • Workbook – An Excel file with one or more worksheets. (Think of a notebook with tabs) • Fields – Types of data/information • Columns - vertical • Rows - horizontal • Cells – where columns and rows intersect

11. Terms and Definitions • Sort – rearranging the information/ data in a new format. You can sort alphabetically A to Z or Z to A, numerically, or date range. Example sorting the IEP dates from oldest to newest • Filter – not showing some of the information in the spreadsheet. For example the supervisor can filter on the TVIs name and only see those students. Or, filter on the blanks for FVA and see how many FVAs are missing.

12. Blank Excel Workbook This is referred to as the Workbook or Spreadsheet COLUMNS are labeled A, B, C, and ROWS are labeled 1, 2, 3 ACTIVE CELL has dark black box around it – A1 Tabs for “Worksheets”

13. When Designing Your Workbook • PLAN, PLAN, PLAN • Think big picture of current and future use • One piece of data per cell • Separate first name, last name • no “lists” • Consistent labels and data entry to allow combining data • Work from computer not paper version • One central place for data collection and sharing

14. Let’s see a sample Excel Workbook

15. Sample Excel Worksheet

16. Column Headings for Your Worksheet • Teacher Last Name • Teacher First Name • Student Last Name • Student First Name • Date of Birth • FIMC-VI Number • Grade • IEP Date • 3-year Re-Eval Date (previous) • 3-year Re-Eval Projected Date K. Direct or Consult • IEP Vision Minutes Per Week • Primary Disability • Secondary Disability • Eye Condition per Medical Exam • Etiology (per medical info) • Date of Eye Medical Exam • Date of Functional Vision Assessment • Date of Leaning Media Assessment • O&M Minutes per Week

17. Opening a new Excel Workbook Demonstration

18. To Open Excel • Click Start • Select Programs / Microsoft Office • Click Microsoft Excel OR… • Click Start • Click New Office Document • Click Blank Workbook • Click OK

19. To Enter Column Headings • Double click in cell 1A • Type: Teacher Last Name • Tab or double click in cell 1B • Type: Teacher First Name • Tab, arrow key over, or click in the next cell to enter the column headings (through 1T) (Use column headings from Slide 12. Non TVIs, adjust headings to your needs.)

20. Editing and Making Corrections • While working in the cell, backspace to erase a character. • To change data in a specific cell, double click in the cell, then adjust as needed (backspace, type over, highlight to select, etc.). • Typically BOLD the first row headings to keep those distinct.

21. Tips for Working in Excel • Be precise! Every space, keystroke, letter, or number, means something in Excel. • You have to click out of a cell before you can make changes or navigate. • Set the workbook to full screen. You can shrink the worksheet if needed.

22. I get these funny symbols when I try to put in the FIMC-VI number. What causes that? Resizing the columns

23. Resizing the Columns • Text that is too long for one cell will stretch over the next cell • Numbers will display as pound signs (#####) if the value is more than 11 characters. You must reset the column width. • Place your mouse on the column border between the letters A and B. • You will see the cursor change to “crosshairs.” • Click and drag to widen or shrink the ENTIRE column as needed.

24. Automatic Settings for Resizing Columns • Click on the row or column heading (letter or number) to highlight the area • In the toolbar under Home tab/ Cells / Format (little down arrow next to format) • Auto Fit Row Height • Auto Fit Column Width

25. My text runs over in the next column or I can’t see it all in the cell. How do I fix that? WRAP TEXT

26. Wrap Text in Columns or Rows For most text based cells (Names, eye conditions, etc.) you will need to set the column or row to automatically WRAP TEXT. • Click on the column heading (A, B, C) until the down arrow appears and the entire column is highlighted • In the toolbar under the Home tab /Alignment, click on the small down arrow, and in the Text Control Box, select WRAP TEXT.

27. Set Data Types for Cells • Default setting is General • Changing the setting keeps the data consistent • Highlight the Column you want to change • Toolbar/Home Tab/Number • Select TEXT for Name, Eye Condition, etc. • Select SHORT DATE for dates • Select NUMBERS and change decimal point to zero for FIMC-VI number, etc.

28. You’ve created your headings, formatted the cells, and now you are ready to enter data/student information. Data Entry

29. To Enter Student Information in the Rows • Double click in the cell (2A) • Type the data that goes in that column • Tab, arrow key, or use the mouse to move to the next cell in that ROW • Continue entering data specific for that student

30. Tips for Managing Text in Cells If one cell has extensive text, such as eye conditions or a long last name, you can shrink the text in that one cell to better fit. • Double click in the cell to select it • From the toolbar / Font section, select a smaller font or font size

31. Aligning Text or Numbers in Cells • By default text aligns to the left of the cell and values (numbers) are aligned to the right. • To change the alignment of a column • Click on the column heading • When the column is highlighted, • In the Toolbar in the Paragraph section, select the alignment you want: Left, Center, Right, or Justified

32. Tips for Managing Text in Cells To mark data that you want to change or edit later, you can highlight that data • Click on the cell that contains data • From the toolbar in the Font section, select the “paint can,” then select the color • To remove the highlight, select the down arrow next to the paint can and select NO FILL

33. Saving Your Data • File / Save As/ Excel Workbook • For this assignment please use the following naming pattern: Webinar-LastName–Month12 Webinar-Ratzlaff-Jan12 • Typically I recommend, Name –VI Master List-Year

34. Explore the tabs on the Toolbar

35. Toolbar-Home Tab – Font • Font Section • Type of font • Size • Bold, Italic • Borders • Highlight • Color of text

36. Toolbar -Home Tab -Alignment • Alignment Section • Text alignment in cell – top, middle, bottom • Text alignment in cell –right, center, left • Wrap text

37. Toolbar -Home Tab -Number • Number Section – type of data in the cell. • For Master List we use • General – no specific format • Number – general display of numbers • Date – use short date for consistency • Text

38. Toolbar -Home Tab –Styles and Cells • Styles Section • Use this for color coding the rows/columns • Editing Section • Insert cells, rows, columns • Delete cells, rows, columns • Cut and paste columns or rows to move data • Format – Row Height / Column Height

39. Toolbar -Home Tab -Editing • This is where the magic happens! • Sort A to Z • Sort Z to A • Filter • Sort allows you to rearrange data in number, date or alphabetical order • Filter allows you to filter data to view what you want to see or print

40. Sorting Student Information Alphabetical, Numbers, Dates • Highlight the Column you want to sort • Toolbar – Home Tab – Editing – Sort and Filter • Small down arrow • Select sorting function you want • SORT WARNING – EXPAND THE SELECTION - OK

41. Excel 2007 made filtering much easier. If you are working with an older version, stay online and I will walk you through that. Filtering the Data in Excel 2007

42. Filter by Student Last Name In Toolbar/ Editing Section / Sort and Filter click on the small down arrow and select FILTER. Small down arrows appear in each column. Click on the small down arrow next to Student Last Name and the box appears with all the student last names. Uncheck the ones you do not want to see or clear all and then select the ones you want. Click OK

43. Filtered Data – Don’t panic if you can’t see all your data. Two Clues: Numbers are out of sequence Teeny tiny funnel next to Student Last Name means the data is filtered on that source

44. Navigating the Worksheet • When working in the far right columns (past Q or R) you can no longer see the first columns on the left. • When working past rows 8 or 9, you can no longer see the headings at the top. • Need to use FREEZE PANES to simplify your work.

45. Toolbar -View Tab -Freeze Panes • Click in the cell that is in the column to the right and the row below the data to be frozen • For our purposes that is Column D Row 2 • Toolbar –View Tab click on small down arrow by Freeze Pane, then Freeze Pane • Now the headings and the student’s name is visible as you enter data and scroll right.

46. Homework for Next Time • Create an Excel Worksheet with the 20 data headings on slide #16 (or similar headings) • Insert the names of at least 5 students and complete the data • Use the following standards for entering data:

47. Homework -Data Entry • If you do not have the source data, leave the cell blank • If the data is not appropriate for that student (Acuities for a student with no light perception) mark the field NA • Use the DOE Disability Categories for Primary and Secondary Disability

48. Homework -Disability Categories • Specific Learning Disability • Intellectual Disability • Emotional /Behavioral Disability • Deaf or Hard-of-Hearing • Visually Impaired • Dual Sensory Impaired • Orthopedic Impairment • Other Health Impaired • Traumatic Brain Injury • Speech Impairment • Language Impairment • Autism Spectrum Disorder • Developmentally Delayed

49. Homework -Submission • For all student information use first name, last initial when sending to me • Name your file using the following pattern: Webinar-Last Name-2012 (example Webinar-Ratzlaff-2012) • Attach it to an email with the subject line: Excel Webinar Homework 1 • Due Date: February 13, 2012

50. Next Time – February 20, 2012 • Discuss spreadsheets that were submitted • How to set-up the spreadsheet for printing • Merging the spreadsheet for reports or mailing lists