With the increased likelihood that you will be asked to analyze and use student data, this month we will be exploring various ways to use MS Excel to import, collect, and analyze student scores. Excel is a very flexible and readily available tool for most of today's computer users, even the Mac. Accountants, businesses, and educators have used Excel to organize, analyze, and display data in spreadsheets for years.

My first experience with a spreadsheet was with Lotus 1 2 3, when I created a spreadsheet for calculating body fat content in an emersion tank during my graduate work, and have used a spreadsheet for a class grade book for years.

We will first be looking at importing and exporting data from a database to and from MS Excel. We will then look at the use of Excel to collect student data, such as: fitness scores, daily attendance, motor assessment, and grading. We then explore the use of Excel on Windows Mobile Devices or Pocket PCs.

Next we will look at how to summarize student data using formulas. Lastly, we will learn how to create charts and graphs in MS Excel. Hopefully this will help some of you explore the use of Excel as a teaching tool.

I will finish this technology section by providing information about my Christmas "wish list."

Scott Tomassetti
Technology Section Editor

To begin using MS Excel in your Physical Education classes you need to learn to import student information into Excel from a Database. This will save you hours of typing, as well as finding and fixing spelling errors.

Most school systems in the U.S. now have database programs that collect student information. Our school uses a program called SASI. Within SASI (Most database programs have the ability to export to Excel through a .csv file) is the ability to export student information in various formats, like colon delimited values, or comma separating values. When exported by your school's executive assistant or secretary, the file will have a .csv extension (like "classList.csv").

It is important to define for your secretary the fields that she/he will need to query from the database. It is always better to have too much information rather than too little. Each field becomes a column header in Excel. I usually ask for: StudentID, StudentFirstName, StudentLastName, BirthDate (which usually needs converting), Teacher, Grade, Class, Gender, Phone, and ParentName. Your 'important' field names will probably be named differently.

PICTURE    

To import this file into Excel you should open Excel, then open the file by using the file type drop down list and select all file types or Text files, and then open the .csv file. A dialog wizard will pop up, and ask you to define the file. You selected either colon separated values or comma separated values. Then the wizard will ask a few more questions and then place the information into Excel.

When finished, go to the file menu and click "Save As." In the "Save As" dialog box, make sure you select an Excel file type. This will ensure that you can use formulas and Excel formatting later. Once you have it saved, create the appropriate column headers, and sort your file as you wish using the column headers and the tools menu. I separate each class into separate worksheets, and rename each worksheet by the classroom number or designated class name. This makes it easier later to find a class when using a Pocket PC device.

One of the best resources exists right within Excel itself; HELP. Using the help system is how I learned many of these techniques, especially those involving any formulas or data.

	Top 10 Tips to Save Time in Excel 2003 (Level 200) - A video to help you learn valuable time saving tips in Excel.
	Excel online Office Assistance - The place to find everything that has anything to do with MS Excel.
	The Crabby Office Lady - A great resource of information on all Office products.
	BONUS: Understanding Internet Explorer 7

USING EXCEL TO COLLECT STUDENT DATA
(Word document - printable form of this tutorial)

Freezing Panes
Freezing panes is a must when using Excel for attendance, assessment, and grading. I always select the columns I always what to see, like the students first name, last name, and class, then freeze the pane right after so that I can move the rest of the worksheet and still see the those columns. To freeze the pane, highlight the whole column right after the column you want to keep. Then go to the Windows menu, and click on Freeze Pane. There you have it, a worksheet that scrolls.

PICTURE          

Selecting the whole worksheet to Sort Data
Use the upper left hand border box to select the whole worksheet prior to sorting by columns. This way you can sort all the data. I usually sort each class by Gender, LastName, and FirstName columns. This makes it easier to interpret data later, and is important for analyzing physical fitness scores. See a picture.

Using Excel on a Windows Mobile Device or Pocket PC

Since Excel is native on Pocket PCs, it is great to use for collecting student assessment data. I most frequently use it when I cannot carry my Laptop, or am away from my computer on a cart. It makes it easier to circulate through the class, and assessing each skill, movement pattern or routine.

The trick to using the PDA is freezing the right panes and reducing the size of the columns to fit the PDA screen. I usually reduce the Last Name column to see only the first letter and freeze the pane after the First Name column. Freezing the pane is done as shown above. Reducing column width is pretty straight forward. You click on the right hand edge of the column header row (see a picture), then drag until it only shows the first digit or letter. Results look like this: picture.

To organize your classes, you have two options. Option 1: Save each class as a separate file with various semesters or grading period on a separate worksheet. Option 2: Save each class as a separate worksheet with the same workbook or file. Option 2 is the method I tend to use most often, and feel comfortable with. It saves space, it is easier to find, and easy to navigate to different worksheets. Use the Insert Menu and Choose Worksheet to create worksheets within the workbook for each class. They will show in a tab at the bottom of the worksheet.

PICTURE

It is also easier to move student from class to class when needed. Once saved in your shared PDA folder, then open and use it on your PDA. (The above scenario is very similar when using a palm powered device with Pocket Excel).

Summarizing Data using the Formula Bar
To average or sum data in a worksheet, you use the formula bar. Select the formula you want to use; the cells you want to calculate, and hit enter.

PICTURE

Creating Charts and Graphs in Excel
To create a graph or chart in Excel select the data, header, and row cells you want to include in your graph. Then click the Graph Tab in the menu bar. The wizard will guide you through your choices, and then you have the graph of your choice.

PICTURE

Click next after selecting your preferred graph type. Choose row or column data.

PICTURE

Then click next. Fill in the Chart header, X axis label, and Y axis label and click next.

PICTURE     

Click Next. Choose whether you are going to place the graph on the current worksheet or another worksheet. I prefer to place my graphs on another worksheet.

PICTURE --- And click Finished…

Graphs are great for teacher portfolios, displaying data so students can better understand where they fall in the grading spectrum, or for use in reports home (picture).

pelinks4u does not endorse any of the products listed below. They are listed only for your personal information and inspection.

	a-ape software is an MS Excel workbook containing separate worksheets for more than 20 of the most commonly used adapted physical education assessment tools. Dan Cariaga, an adapted physical educator, developed a-ape to make motor assessment accurate and efficient for the IEP process. Find out more.
	Dartfish is the complete solution to easily integrate video in PE lessons. See case studies and testimonials.
	Interact™ Secondary Physical Education is a broad and in-depth interactive series that covers a number of areas including fitness, training, fatigue, the heart, performance, and injury. This all-encompassing course is a valuable and engaging asset for teachers and students. This looks worth checking into!
	Birchfield - Instant Lessons are precisely that - ready-made interactive lessons, games and quizzes, worksheets and an assessment tool. No preparation is needed. Just click and teach! Find out more, and be sure to check out the physical education page.
	PE Manager software is a comprehensive data collection and grading tool designed specifically for PE.
	Companion Pocket PC - Enables you to collect fitness data in the field. Sync data with the TriFIT and web site, simplify class management, including attendance and performance records, and develop your own custom Rubrics.

Department of Education Promotes Physical Fitness through Handheld Technology Project

If you're getting freezing winter weather, why not diversify and do your workouts in the pool? Stay warm, and still stay fit. Check out these products.

Exer-Swim ProTrainer can supply hours of swim time for endurance swimming. Manufactured with durable fiberglass composite housing, Exer-Swim ProTrainer features pull-trued fiber fins pitched for efficient & minimum turbulence water flow. At 24 inches wide, it will fit in any lane and its transportable ability allows you to move to different lanes in minutes, Rechargeable batteries are fully charged in 2 hours and supply hours of use. Find out more.

AquaJog.com - Fun, Effective Water Exercise Equipment. With winter cold weather here, why not have some fitness fun at the local pool? Jogging in the swimming pool' looks like it could be fun!

Virus blamed for obesity epidemic - Well, if you can't eat right and exercise, why not say your excessive weight is caused from a virus, huh?

Test for Obesity Virus - If you test positive for the virus, know what the recommendations is? The standard treatments are eating a healthier diet with lots of vegetables and fruits, reducing calorie intake, increasing physical activity to obtain 30-90 min of vigorous activity (such as walking) at least 5 days per week (now this is all good, but read the rest), and in cases where it is indicated, starting drugs that may lower body weight or prevent weight gain. For people who qualify, obesity surgery may cause dramatic weight losses. Who's giving this advice, huh? Read more.