LTRC Studio - Microsoft Excel Tutorial

Spreadsheet/Graphing tutorials from NC State's LabWrite

You may want to print this page out to follow as you work in Excel.

Spreadsheets are especially useful for organizing numeric data because you can use them to sort, calculate, and graph. They can be used by teachers as part of lessons (especially in cases where data can be manipulated). Teachers can make spreadsheets to store grade information about their students. Some of the spreadsheet packages you might encounter include Microsoft Works, Microsoft Excel, AppleWorks, and StarOffice Calc.

Make a spreadsheet for class grades

Under the File Menu, choose New - Workbook. A blank spreadsheet will appear. In cell A1, type a title for your spreadsheet (e.g., "Grable Science Period 1 Grades"). In the next row, type a label for each column: Last name, First name, Test 1, Test 2, Test 3, Average.
Save the new workbook under File, Save As with your last name in the title (grablesci.xls).
From the Window menu, choose your database file. Drag the curser to highlight all the names in your "Last name" column. You should see all the last names become highlighted.
From the Edit menu, Copy. From Window, choose your spreadsheet. Click once in the cell below the label Last Name. From Edit, Paste. Repeat this process to add the First Names.
Save your file.
Spreadsheets have cells that are identified with a letter and a number, corresponding to the column and row. Each cell can hold information in different forms, such as numbers, text, or equations. Spreadsheets are useful because you can easily sort information, perform repetitive calculations on large sets of numbers, and use the Chart option to show data graphically.

Sorting

Next we will sort the names alphabetically. Click in the first last name, holding the mouse button down (on mine, it is Hayworth).
Drag the mouse down and diagonally until the entire last and first names are highlighted (not the labels).
From the Data menu, choose Sort. A dialogue box will appear. Check that the correct Order and Direction radio buttons are clicked. Desi Arnaz should now be the first student.
What if you waited to sort until after you entered all the grades? It is important in spreadsheets to always select all the cells that need to be moved when you sort. Grades would need to be moved with the student they belong to. Remember not to DO anything until you see if the sort is correct. If it's not right, you can always choose Edit, Undo Sort and then try again.

Calculating

Now give all your students 3 different test grades. Use your imagination to make these random like a real class would be. As you type each grade, notice that you can use the Tab key to jump to the right or use the Return or Enter key to jump down one cell. Notice that your typing for each cell appears in the Formula Bar.
First, let's calculate the test average for each student. Click in the top cell under Average. From the Insert menu, choose Function. The Function dialogue box will appear. In the right hand column, click AVERAGE. Click OK.

The Average dialogue box appears. You can move the box by clicking in the gray area; drag the box down so you can see your test scores.
Using the curser, highlight the three scores to average; they will be enclosed in a moving box. Click OK. Your score will appear in the average column.
Note: The formula is written in the Formula Bar, with the letters and numbers (i.e. C5) representing the column and row. You can also average the scores by clicking on the “=” sign by the formula bar, typing in AVERAGE and manually adding the columns, so that row 5, columns C, D, and E is represented as (C5:E5).
Spreadsheets need a special formatting for formulas in order to do the operations in the order you want. You can always liberally use parentheses. Read the Excel Help on the subject of About Number Formats.
Now use your mouse to highlight row F, which is the row of averages. Under Format, choose Cells. In the dialogue box, be sure the “number” tab is active. Under Category in the left hand column, choose Number. On the right, several options will display. Set the decimal places to 2 and click OK. Your averages should have two decimals now.

Next, at the bottom of each column of tests, find the class average for each test. Don't forget to Save. This is a good practice for teachers. Sometimes you may need to rethink the test you gave depending on how the class performs.
There might be occasions when you would need a constant value to add or multiply with a set of numbers. Use the Excel Help to learn About Cell and Range References.

Make a Chart of the Results

Use the mouse to highlight the labels and data from First Name to Test 3 for the whole class. Under Insert, choose Chart. To begin, choose the Bar Chart. A chart option menu will pop up. Note that by clicking and holding the “Sample” tab, you can see what your chart will look like. Pick the chart you like best, and click Next. Highlight the selector next to “columns;” click Next. In this dialogue box, you can change the title of your chart and legend placement. Click Next. Create the chart in the spreadsheet by clicking Finish.
You can move this around on your spreadsheet by dragging from its middle. You can change its size by grabbing the square handles on the corners and dragging. You will probably need to resize so you can read the students' names.
You may want to change the colors in the legend. Double-click on the legend color, and a Format Legend dialogue box will appear; change the colors.
Right click in the body of the chart to change the chart type. Try some of the other chart options such as Scatter and Line. Determine the chart that best illustrates each student's performance in the class. Teachers working with data often use bar, line, and pie charts. Note: you can change the source of your data from you spreadsheet by right clicking, also. To do this, select "Source Data..." from the right click menu. Use your curser to choose the columns and rows to represent. This allows you to display the results of one test, two tests or the average. Hold down the control key to select multiple rows.
Move the chart below the spreadsheet section and size so that it is readable. Under File, choose Print Preview. This is the way to see how your work will print. You may need to resize the chart again to fit it on the first page. When you select print preview, be sure your spreadsheet is active, not just your chart.
Use the Options menu to further clean up your spreadsheet and chart for printing. Click somewhere out of the chart on the spreadsheet. Click in cell A1 and drag diagonally until all the spreadsheet and chart are highlighted. From the File menu, choose Print Area and click set print area
Under View, choose Header/Footer. Excel allows you to choose standard footers, or you can create your own footer by clicking on Custom Footer. Type in information about your printout. Mine says "Dr. Grable, Science One Grades, Fall 2002". Save before printing and emailing to turn in.

Items for your Technology Portfolio
You can now print your class spreadsheet with calculated averages and a chart of the class performance for your portfolio.

Check your knowledge
Define cell, row, column, spreadsheet, formula bar. What are the advantages of using spreadsheet software? What is the preferred way to write a formula for calculating averages of grades? What is the purpose of Set Print Range? What are the most commonly used charts? How do you make an absolute reference in a formula? What is the order of operations? What is repetitive information that is printed at the bottom of each page of a document?

Advanced Options:

Filtering:
Filtering allows you to view only the records you wish to see. By filtering the data, you can print and change cells very quickly and easily. To filter, chose the column you wish to filter; highlight the column by clicking on it. Click on the Data menu at the top of the screen; click filter, then AutoFilter. Notice that an arrow appears in the cell at the top of the column.

Clicking on this arrow will give you several pre-determinted options for filtering your data, or allow you to customize the options. For example, to see each and every student who earned an average score of 85.33, you can simply click “85.33” in the filter table.

You can also customize your options for filtering the data. Let’s say you wanted a list of all the students whose average score was above 90. In the filter dropdown, click “customize” and complete the custom autofilter screen. In the first dropdown blank, click “is greater than or equal to” and then type 90 in the second blank. Click OK.

Only the students who scored above 90 show up on your table. Note that you can use the and/or feature to further customize your entries.

To turn the autofilter feature off, click the data menu again, click filter and un-check autofilter. All your records will return to the original.

Page maintained by Learning Technologies Resource Center