Note: the Long Version of These Directions with Explanations About Logic Statements Are

Interactive Spreadsheets Exercise for Mac OSX

Directions: Follow this tutorial to create an interactive for first grade that will serve as a drill and practice for compound words. Use the same technique when you create your own design. NOTE: If you computer asks you IF you are managing a list…..say NO!!!!

1. Open Excel. It should default to a worksheet.

2. Format columns A, B, and E to width of 20 (pixels). (Hold down the key and click each column to select them separately.) NOTE: If the columns are in inches, format to 1.5 inches.

3. Format columns C and D to width of 10 (pixels). NOTE: If the columns are in inches, format to .75 inches.

4. In cell A1 type Words (this column will be used by the teacher to enter words for combining to make a compound word).

5. In cell B1 type Compound Word (this column is where the student will enter the compound word).

6. In cell C1 type Right? (This column will provide positive or negative feedback to the student).

7. In cell D1 type Points (this column will display points earned for the student’s entry).

8. In cell E1 type Expected (this column is used by the teacher to enter the correct, expected student response; you will later hide this column from the student).

9. Set the alignment for row 1 to center alignment and choose green for the font color of the words in row 1.

10. Save your workbook as compound.xls. Your work should look like figure 1.

figure 1

11. In cell A2 type fire + place.

12. In cell B2 and E2 type fireplace (fireplace in cell B2 is used for testing purposes only; this cell will be blank when you are finished). Your worksheet should look like the one in figure 2.

figure 2

13. In cell C2, type this logic statement:

=IF(B2="","",IF(B2=E2,"Good","Uh-Oh")) Type the quotes with no spaces between them. This logic statement says if B2 is blank ("") then leave C2 blank (""), otherwise if B2=E2, display Good, otherwise display Uh-Oh. Test your formula by typing both correct and incorrect responses into B2. Be sure to leave the cell blank when you are finished. These statements are called nested IF statements because they contain more than one IF statement. Seven “IF statements” is the maximum number that can be nested.

figure 3

Using this same procedure, add one more compound word (of your choice) to the spreadsheet. Be sure to save frequently. Copy the formulas by grabbing the lower right corner of cell C2 and dragging it down to Cell C3.

14. Test the spreadsheet to make sure the logic statements are working correctly for Right? (the feedback column).

15. Now, it is time to enter points into the Points column. This also uses a logic statement. If the student entered response in the B column is equal to the teacher entered expected response in the E column, the student should receive 10 points if this worksheet is based on 100 points. The number of points should not appear until the student has actually entered a response. The formula is: =IF(B2="","",IF(B2=E2,10,0)). Notice there are no quotes around the numbers. This is because we want to treat these numbers as numbers and not as text.

figure 4

16. Test the spreadsheet to make sure the logic statements for the Points column are working correctly.

17. Add a final row that will give the total number of points earned for this worksheet. The formula to be entered in D4 is: =Sum(D2:D3). If you add additional questions, simply make sure the total includes ALL of the points scored, (e.g. in D12, =Sum(D2:D11) for ten questions).

figure 5

18. Double-click on the Sheet1 tab at the bottom of the worksheet and change the name of the tab to something descriptive.

Now, you are ready for the final formatting of your spreadsheet

Final Formatting for Interactive Spreadsheets Created in Excel

· Locking/Unlocking cells and protecting worksheets: Important!!! Do not protect the worksheet until all formatting has been completed.

· By default, all cells in the spreadsheet are locked when a worksheet is protected. To prevent students from making changes to your interactive spreadsheet, the worksheet needs to be protected. Before protecting the worksheet, however, the cells where students are expected to put their answers must be unlocked.

· To do this, select the cells you want to unlock, choose Format > Cells > Protection > deselect locked.

figure 6

Hiding columns you don’t want students to see.

· Hide the expected answer column by choosing Format > Column > Hide.

· Any other columns you need to hide can be hidden in the same manner.

· If you need to see the column again, select the columns on both sides of the “expected answer column” and the choose Format>Column>Unhide and the hidden column should reappear.

Surround the spreadsheet with white space (empty space).

· Insert a column before column A by highlighting column A (put mouse on A and click),

· Choose Insert > Column.

· Insert several rows above row 1 by highlighting row 1 (put mouse on 1 and click), choose Insert > Row.

Writing directions for students.

· Merge several horizontal cells together by highlighting the cells and then choose Format > Cells > Alignment: place a check mark in Wrap Text and Merge Cells.

· Type directions for your students into the merged cells.

· Insert rows above and below the directions to create a pleasing appearance for the worksheet.

· Inserting a cell(s) for student’s name: Label this cell with Name:

· Inserting a cell(s) for date. Label this cell with Date:

Making the spreadsheet easier for students to read.

· Students may have difficulty tracking across the rows. To improve the ease of reading, change the color of each row by selecting the cells in a row (not the whole row), then

· choose Format > Cells > Patterns. Choose a pastel color to shade the cells.

· Color every other row to make the information easier to read.

To add footer information

· Footer information is only seen when in print preview mode or when worksheet is printed.

· Choose File > Page Setup > Header/Footer > Custom Footer.

· Type: Created by (your name) in the left section.

· Type grade spreadsheet was created for in center section.

· Paste state standard in right section.

Last minute formatting

· To make the spreadsheet look like an ‘unlined piece of paper’.

· Choose Excel > Preferences > View. Deselect Formula Bar, Status Bar, Page breaks, Formulas, Gridlines, Row & column headers as pictured in figure 7.

figure 7

Deselecting Toolbars

Additional ‘cleaning up’ can be accomplished by choosing View > Toolbars > Deselect all selected toolbars (toolbars are deselected one at a time).

Selecting Cell

Leave the mouse in a cell where you want the student to enter information.

Protect Worksheet

· Protect worksheet to prevent any changes. Choose Tools > Protection > Protect sheet.

· No changes can be made once the sheet is protected (default selections work fine for our purpose).

· To make changes choose: Tools > Protection > Unprotect sheet.

· Do not use a password. If you forget your password, there is no backdoor to unprotect the sheet.

Using spreadsheets to create interactive practice exercises or assessments is one more way that all teachers can use this tool to get students working with the computer in learning.

1/13/09