Creating a Basic Grade Book in Microsoft Excel

Technology Training for Faculty

Creating a Basic Grade Book With Microsoft Excel

Creating a Basic Grade Book in Microsoft Excel Objectives: Part I – The Basics of Microsoft Office Excel • Introduction • Starting Microsoft Office Excel • File Terminology • Common Window Elements of Microsoft Office Programs • Excel-Specific Window Elements • Current Worksheet Navigation • Toolbars Part II – Creating and Formatting a Basic Grade Book • Column Titles • Student Names • Student Numbers • Student Emails • Student Phone Numbers • Saving • Total Points • Percentages • Grades • Basic Formatting Part III – Using Your Basic Grade Book • Sorting • Worksheets • Adding / Deleting Columns • Adding / Deleting Rows • View / Track Formulas • Freeze Panes • Header / Footer • Alt + Enter • Comments • Spellchecking • Print Preview • Page Set Up • Printing Part IV – Grade Book Templates and Protection • Deleting Information to Make a Template • Locking Cells • Protecting a Worksheet • Protecting a Workbook • Saving as a Template • Opening and Saving From a Template • Unprotecting a Worksheet and Workbook • Grade Book Password

Creating a Basic Grade Book in Microsoft Excel Part I – Introduction to Microsoft Excel Introduction Microsoft Excel is a versatile and fun program to use. From a general standpoint, Excel is called a spreadsheet program specifically designed to display, organize, and interpret numerical data. You can also format this data to make it visually appealing, perform calculations, and create charts and graphs from your data. Excel is most commonly used for financial tracking and reporting, but it is also very commonly used by instructors to create and use a grade book.

Starting Microsoft Office Excel 1. 2. 3. 4.

Click Start. Click All Programs. Click Microsoft Office. Click Microsoft Office Excel 2003.

Creating a Basic Grade Book in Microsoft Excel When Excel opens on your computer it might look like one of the following screen shots below. The screen shot on the left shows the Excel window that opens with a blank workbook ready to work with. The screen shot on the right shows the Excel window without a workbook.

If Excel looks like the figure on the right, you will need to open a blank workbook. Click the “New Blank Document” button. The button is located in the upper-left side of your screen.

Click this button to open a new blank workbook.

File Terminology The files you create in Excel are called workbooks and can contain one or more different spreadsheets. A spreadsheet is the data entered and used as a tool for analyzing and reporting information. The spreadsheets in an Excel workbook are called worksheets. By default, Excel starts with three blank worksheets and named Sheet1, Sheet2, and Sheet3.

Creating a Basic Grade Book in Microsoft Excel Common Window Elements of Microsoft Office Programs If you’ve worked with other Microsoft Office Programs, you will notice that Excel shares a lot of the same toolbars, buttons, and other features with the other Microsoft Office programs. The most common window elements that are displayed in every program and their descriptions are shown in the screen shots below.

Creating a Basic Grade Book in Microsoft Excel Excel-Specific Window Elements Microsoft Excel also introduces window components that are unique to Excel or add unique buttons or menu options. The most important elements and terminology are shown in the screen shots below.

Creating a Basic Grade Book in Microsoft Excel Current Worksheet Navigation Now that we are familiar with the basic layout of an Excel window, we can try moving around within the workbook. In the screen shot below are the most common keyboard navigation shortcuts.

You can also use the vertical and horizontal scroll bars to move within a worksheet. You can scroll a worksheet with the scroll bars in two ways. First, you can click either the up/down or right/left arrows with your mouse to move in increments around the worksheet. Or, you can click the scroll bar itself and while holding the mouse button down, move your mouse up/down or right/left.

Vertical and Horizontal Scroll Bars

Creating a Basic Grade Book in Microsoft Excel Toolbars The final major components of the Excel window are the toolbars. There are two main toolbars: the Standard Toolbar and the Formatting Toolbar. The standard toolbar consists of frequently used “standard” functions, such as opening a file, creating a new file, saving, printing, etc. The formatting toolbar consists of frequently used “formatting” functions, such as changing text font or size, bolding, and adding color. Depending on the computer you use, the toolbars you see when you first get into Excel could vary. In addition, the toolbars could be displayed on one row, rather than each on its own separate row. To show or hide a toolbar: 1. Click View on the menu bar. 2. Click Toolbars. 3. A list of all toolbars is listed in the toolbar menu. The toolbars that are currently being displayed have a checkmark to the left of the toolbar name. 4. If you are missing either the standard or the formatting toolbar (or both) click the first toolbar you wish to view. 5. The toolbar menu will close and the toolbar you selected is now visible. 6. If you were missing both toolbars, you will need to repeat this process to set the other toolbar to visible.

Creating a Basic Grade Book in Microsoft Excel Sometimes your toolbars will be visible, but are both on one row like in the screen shot below. When your toolbars are on one row, not all of the buttons available on that toolbar will be visible. It is best to place your toolbars on to two rows.

Standard Toolbar

Standard Toolbar Options Button

Formatting Toolbar

Formatting Toolbar Options Button

To Put Your Toolbars on Two Rows 1. Click either one of the Toolbar Options Button. 2. At the bottom of the toolbar options window, click “Show Buttons on Two Rows.” 3. The toolbar options menu will close and your formatting and standard toolbars will be visible on two separate rows.

Creating a Basic Grade Book in Microsoft Excel Part II – Creating and Formatting a Basic Grade Book When creating a grade book in Excel, it is best to first type the information you want in the worksheet and then proceed to formatting it to make it visually appealing and useful. Column Titles We will start by entering our grade book column titles. If you make a mistake while typing you cannot use the backspace key at this point to change your text. Click anywhere in the Excel window to deselect the cell. Click the cell that you made the mistake in and press F2 which will put you into cell edit mode. At this point you can use the backspace key to correct your mistake. Once you have finished your corrections, press tab to proceed to the next cell. You will notice that as you type in a cell and proceed to the next cell, that not all of your text is visible and seems to be “cut-off.” For now, ignore the text appearance, as we will correct this when we get to the formatting section.

1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14.

Click cell A1 and type Student Name and press tab to go to cell B1. In cell B1 type Student Number and press tab to go to cell C1. In cell C1 type Phone Number and press tab to go to cell D1. In cell D1 type Email and press tab to go to cell E1. In cell E1 type Total Points and press tab to go to cell F1. In cell F1 type Percentage and press tab to go to cell G1. In cell G1 type Grade and press tab to go to cell H1. In cell H1 type Intro Test and press tab to go to cell I1. In cell I1 type Assignment #1 and press tab to go to cell J1. In cell J1 type Assignment #2 and press tab to go to cell K1. In cell K1 type Group Project and press tab to go to cell L1. In cell L1 type Midterm and press tab to go to cell M1. In cell M1 type Assignment #3 and press tab to go to cell N1. In cell N1 type Final and press tab.

Once you have completed the preceding steps, your grade book should look similar to the screen shot below.

Creating a Basic Grade Book in Microsoft Excel Student Names 1. Click cell A2 and type Abell, Jennea and press enter to go to cell A3. 2. In cell A3 type Newman, Jim and press enter to go to cell A4. 3. In cell A4 type Collins, Brian and press enter to go to cell A5. 4. In cell A5 type Foret, Asya and press enter to go to cell A6. 5. In cell A6 type Naidoo, Roweena and press enter.

Student Number 1. Click Cell B2 and type S01234567 and press enter to go to cell B3. 2. In cell B3 type S12345678 and press enter to go to cell B4. 3. In cell B4 type S23456789 and press enter to go to cell B5. 4. In cell B5 type S34567890 and press enter to go to cell B6. 5. In cell B6 type S45678901 and press enter.

Creating a Basic Grade Book in Microsoft Excel Student Email You will notice that once you type the email address for each student and press enter, the email is automatically recognized as an email address and changes to a hyperlink. For now, we will leave them as hyperlinks. 1. 2. 3. 4. 5.

Click cell C2 and type [email protected] and press enter to go to cell C3. In cell C3 type [email protected] and press enter to go to cell C4. In cell C4 type [email protected] and press enter to go to cell C5. In cell C5 type [email protected] and press enter to go to cell C6. In cell C6 type [email protected] and press enter.

Student Phone Numbers 1. Click cell D2 and type (303) 123-4567 and press enter to go to cell D3. 2. In cell D3 type (303) 234-5678 and press enter to go to cell D4. 3. In cell D4 type (303) 345-6789 and press enter to go to cell D5. 4. In cell D5 type (303) 456-7890 and press enter to go to cell D6. 5. In cell D6 type (303) 567-8901 and press enter.

Creating a Basic Grade Book in Microsoft Excel Saving This would be a good time to save our file. The first time you save a file, you need to specify a location to save it and give it a file name. Each subsequent save, will save future changes over the same file. If you make changes and want to keep your original, you would need to do another save as. 1. Click File on the menu bar. 2. Click Save As.

Once you click “Save As” the Save As Dialog Box will appear. Here you can enter where you want to save the file and what you want to name it. We will save the file onto the floppy disks that I have provided and we will name the file Sample Grade Book. 1. 2. 3. 4.

Click the Save In drop-down list arrow. Click 3½ Floppy (A:). In the File Name box type Sample Grade Book. Click the Save Button.

Save In Drop-Down List Arrow Floppy Disk Drive

File Name Box

Creating a Basic Grade Book in Microsoft Excel

Our Sample Grade Book file has now been saved successfully. The new filename will appear on the title bar of the Excel Window.

New File Name

Total Points Since this grade book is designed on a points-based system, we will be entering points received from each student for each assignment in cells H2 through N6. We could manually add up all their points to determine their total points, and thus their grade; however, Excel has an automated tool that will do that calculation for you and you can use this tool in three different ways. First you need to determine which cells you want to add together. For our first student, Jennea, we want to add cells H2 through N2 together to get her total points for the class. 1. Click cell E2 and type =SUM(H2:N2) – but do not press any other key when you have finished. This is called the SUM Function and is designed to add cells together. The equal sign (=) tells Excel that we are entering a formula or a function. SUM tells Excel that we want to use the SUM function. In parentheses ( ) we need to put the cells that we want to add. Since we want to add cells H2 through N2, we type the beginning and ending cells with a colon (:) between them. The colon essentially is the word through. So (H2:N2) says cells H2 through N2. As we type our formula, Excel places a colored border around the cells we want to add. We can use this visual aid to help determine if we are adding the cells that we want to. By default, the border color is blue, but you may have a different border color.

Creating a Basic Grade Book in Microsoft Excel

Cell Range Border

1. Now press enter. The number zero shows up in the cell E2. 1. Click cell E2 again. In the cell we see the number zero, but in the formula bar we are able to see the formula we entered and are able to edit it if needed.

Formula we entered in cell E2

Result of calculation

Creating a Basic Grade Book in Microsoft Excel Jennea has zero total points because there are no points entered yet for her assignments. As you add point scores, the Total Points cell will be updated automatically. The method we just demonstrated is completed by manually typing in the entire formula. Another method we can use is to type the beginning of the formula and use the mouse to select the range of cells that we want to add. 1. 2. 3. 4. 5.

Click cell E3. In cell E3 type =SUM(. Click cell H3 and hold down the mouse button. Without releasing the mouse button, drag your mouse to the right to also select cells I3 through N3. Once all the cells are selected you can release the mouse button.

Excel displays a colored, lined, and animated border around the cells we want to add. 1. Press enter. The formula produced the total points this far for this student (zero).

The last way to use the sum function is by using the AutoSum button which is located on the standard toolbar. While in most cases the AutoSum button is the easiest way to enter a sum formula, it depends on how your worksheet is designed. You must always pay close attention to the formula that the AutoSum button enters.

Creating a Basic Grade Book in Microsoft Excel 1. In cell E3 click the AutoSum button on the standard toolbar.

As we can see, there is a blue, lined, and animated border around the cells that are going to be added and the formula has been filled in for us. Unfortunately, it is adding the two cells above this one (cells E2 and E3), which is not what we want. If we had placed our “Total Points” column after all of the assignments instead of in front of them, the AutoSum function would have added the cells we wanted. When you are creating your own grade book, you can place your Total Points, Percentage, and Grade columns anywhere on the worksheet to fit your specific needs. If this were the correct formula we wanted, we could just press enter. Since it is not what we want, we will use our mouse as we did in above in cell E3 and drag it to select the cells we really want. 1. 2. 3. 4.

Click cell H4 and hold down the mouse button While holding down the mouse button drag to select all the cells through N4. Release the mouse button. Now, the cells we want to add are selected. Press enter.

Creating a Basic Grade Book in Microsoft Excel

We will use the copy / paste function to fill in the formulas for the remaining two students. As with any formulas, but especially when copying and pasting, it is always a good idea to check the formulas you have pasted, to make sure they are calculating the cells you intended. Sometimes, Excel thinks for you and calculates cell you do not want included.

Copy Button

1. 2. 3. 4.

Paste Button

Click cell E4. Click copy on the standard toolbar. Click cell E5. Click paste on the standard toolbar. The formula is pasted into cell E5 and is adding cells H5 through N5. 5. Click cell E6. 6. Click the paste button again. The formula is pasted into cell E6 and is adding cells H6 through N6.

Creating a Basic Grade Book in Microsoft Excel Percentages For this class, each assignment and test is worth 50 points so there are 350 total possible points for the class. We can enter the formula that calculates the percentage in three ways. 1. Click cell F2. 2. Type =E2/350 and press enter to go cell F3. 3. In cell F3 type the equal sign (=). a. Click cell E3. b. Type /350. c. Press enter. 4. Click cell F3 and click the copy button. 5. Click cell F4 and click the paste button. 6. Click cell F5 and click the paste button. 7. Click cell F6 and click the paste button.

Grades This column will be filled out once a student completes all of their assignments or when the semester ends. There is a complex formula that can be used to automatically calculate a letter grade based on total points; however, that is covered in more advanced workshops. For now, we will use this column to manually enter a student’s grade.

Creating a Basic Grade Book in Microsoft Excel Basic Formatting Now that we have all of our student information and formulas entered we can begin to format the worksheet to make it visually appealing. Resizing Columns The first item we need to tackle is being able to see all the data we’ve typed in each cell. We can do this in a few different ways, but the easiest way is to select all the columns and have Excel format them to their “best fit.” 1. Click the Column Header for column A to select the column. 2. Position your mouse on the Column separation line between columns A Column Header and B until your mouse turns into a Separator Line black type cross with right and left arrows. 3. Double-click the mouse on the separator line. 4. The column has now been formatted to fit the longest name. To format the rest of the columns we could repeat this process for each column or we can resize them all at once. 1. 2. 3. 4. 5.

Click the column header for column B While holding down the mouse button, drag to select columns C through N. Release the mouse button (Your grade book should now look like the screen shot below). Position your mouse on the separator between columns N and O. Double-click the mouse on the column separator line. All the columns have now been resized to their best fit.

Creating a Basic Grade Book in Microsoft Excel Text Formatting Next we will format the grade book to make the column titles more prevalent. 1. 2. 3. 4.

Click the row heading for row 1. Click B on the formatting toolbar. This will bold the text. Click U on the formatting toolbar. This will underline the text. Click the Center Alignment button on the formatting toolbar. This will center the text.

If text in any of the columns is not entirely visible, ignore it for now. We will resize all columns to their best fit after we complete our formatting. Bold, Underline, and Center Buttons Row Header for row 1

Changing Font Type and Size

–

Undo / Redo

Undo Button Redo Button Font Type

Font Size

Creating a Basic Grade Book in Microsoft Excel To further format our grade book column titles, we will change the font type and size. 1. Click the row header for row one again. 2. Click the drop down list arrow of the font box. 3. Click the font Arial Rounded MT Bold to change it. 4. Click the drop down list arrow for font size box. 5. Click 12 to change it. After looking at the larger size, the font might be too big. We could change the size again or we could undo our last action. 1. Click the undo button to undo the last action (changing the font size). The previous size is too small. We could change the size to 12, or we could redo what we just undid. 1. Click the redo button to redo the last action. 2. Click anywhere on the worksheet to deselect the row. The rest of the text is easy to see so we won’t change the font type or size. Removing Hyperlinks We decide that we don’t want the student email address to be hyperlinks because every time we click on it Excel will try and open outlook. To prevent this, we will remove the hyperlinks. 1. Right click cell B2. 2. Click Remove Hyperlink.

3. Repeat this process for all the student email addresses. 4. Once you have finished your grade book should look like the screen shot below.

Creating a Basic Grade Book in Microsoft Excel

Percentages Since we want the Percentage column to display as a percent, we need to change the format of the column. We cannot just type in a percent sign because we typed a formula into the cells and not a number. Instead, we will utilize Excel’s number formatting feature. 1. Click the column header for column F. 2. Click Format on the menu bar. 3. Click Cells. 4. The format cells dialog box opens. 5. Click the Number tab if necessary. 6. Click Percentage in the category list box. The percentage format options display to the right. 7. Click the up or down arrow in the decimal places box to change the format to display 2 decimal places. 8. Press OK. Now all the cells in column F are set to display a percent sign and two decimal places.

Number Tab Decimal Places Box Percentage Category

Creating a Basic Grade Book in Microsoft Excel Number Format In this class, it is possible for students to receive partial points, so we will need to set the Total Points column and all the assignment columns to display a number with two decimal places. 1. Click the column header for column E. 2. Click Format on the menu bar. 3. Click Cells. 4. Click the Number tab if necessary. 5. Click Number in the category list box. 6. Change the Decimal Places box to 2. 7. Press OK. 8. Click the column header for column H and hold down the mouse button. 9. Drag to select columns I through N and release the mouse button. 10. Click Format on the menu bar. 11. Click Cells. 12. Click the Number tab if necessary. 13. Change the Decimal Places box to 2. 14. Press OK. Now all the cells in column E and columns H through N are set to display a numerical value and two decimal places.

Now that we have completed our formatting changes, we can resize all the columns again to best fit. 1. Click Column A. 2. While holding the mouse drag to select all the grade book columns. 3. Place your mouse over a column separator and double-click. 4. All the columns have now been resized and we can no longer see all the columns. 5. Click File on the menu bar, then click save, to save the latest changes.

Creating a Basic Grade Book in Microsoft Excel GRIDLINES / BORDERS Gridlines are the separator lines we see between each column and each row. These do not print, unless we tell them to. Printing Gridlines 1. Click File on the menu bar. 2. Click Page Set Up. 3. Click the Sheet Tab. 4. Click the Gridlines checkbox under the Print Area. 5. Click ok. This will make sure that the gridlines will print when we print. Sheet Tab

Gridlines Box

Creating a Basic Grade Book in Microsoft Excel

Part III – Using your Basic Grade Book We have now created and formatted a basic grade book. The grade book is functional as it stands. This section covers additional concepts that are often used after the initial grade book has been created. Sorting Excel allows you to sort your worksheet by almost anything you want to – off of one column or multiple columns. In our grade book, we are going to sort our list alphabetically by the student’s last name. 1. Click the Select All Button 2. Click Data on the menu bar 3. Click Sort Sorting 4. The Sort Dialog Box opens.

Select All Button

1. Make sure that the Header Row radio button is selected (see screen shot below.) This lets Excel know that our first row consists of column titles and is not data to be sorted. 2. Click the Sort By drop down list arrow and select Student Name. 3. Make sure the Ascending radio button is selected. 4. Since some students may have the same last and first name, we will also sort by student number. 5. Click the drop down list arrow for the first Then By box and select Student Number. 6. Make sure the Ascending radio button is selected. 7. Click OK. Our grade book is now sorted first by Student Name, then by Student Number.

Creating a Basic Grade Book in Microsoft Excel

Sort By Box Ascending Buttons Then By Box

Header Row Button

Worksheets Depending on your course, you may wish to create additional worksheets to track varying information. You may want a worksheet to track attendance or attendance points, track extra credit activities, or track makeup work assignments. While we will not be using multiple worksheets in the basic grade book, we will demonstrate what you can do with them. Renaming a Worksheet 1. Right click the Sheet1 tab. 2. Click Rename from the shortcut menu. 3. Sheet1 is highlighted in black, meaning you can type right over the current text. Right Click Here 4. Type Gradebook. 5. Press enter. 6. We have now changed the name of the worksheet. 7. We realize that our worksheet name should be two words. 8. Double click the Gradebook tab. 9. Type Grade Book. 10. Press enter. If we were using multiple worksheets we could rename each of them with their intended purpose. If we needed more worksheets, we could add them.

Creating a Basic Grade Book in Microsoft Excel

Inserting a Worksheet 1. 2. 3. 4.

Click the Shhet2 tab to make it the active worksheet. Click Insert on the menu bar. Click worksheet. A new worksheet is entered between Grade Book and Sheet2 and is automatically named Sheet4 (depending on your computer, the next sheet number may differ).

Moving a Worksheet 1. 2. 3. 4.

Click the Sheet4 tab and hold down the mouse button. While holding the mouse, drag your mouse to the left. Your mouse will turn into a white arrow with an icon that looks like a piece of paper. As you move your mouse, there is a black arrow at the top left corner of the worksheet. This is to alert you to the current location of the moving worksheet. 5. Move your mouse until the black arrow is on the left side of the Grade Book worksheet and release the mouse. 6. The worksheet has moved to its new location at the beginning of the workbook.

This indicates where your worksheet will be placed

Deleting worksheets 1. 2. 3. 4. 5. 6. 7.

Right click Sheet4. Click Delete from the shortcut menu. Right click Sheet2. Click Delete from the shortcut menu. Right click Sheet3. Click delete from the shortcut menu We now have only our Grade Book worksheet in our workbook.

Creating a Basic Grade Book in Microsoft Excel Adding / Deleting Columns Oftentimes it will be necessary to add or delete a column, especially when we are working with a template, which will be discussed in the next section. Deleting a Column We decide that we are not going to use the group project this semester and decide to delete it from the grade book. 1. Right click the column header for column K. 2. Click Delete on the shortcut menu. 3. The Group Assignment column has now been deleted and the next column – Mid-Term Test – which was previously column L is now column K. Adding a Column We realize that we wanted to include an Assignment #4 so we will need to add additional columns. 1. Right click the column header for column M – Final Test. 2. Click Insert on the shortcut menu. 3. This will insert a new column between Final Test and Assignment #2. 4. The column becomes column M and Final Test become column N again. 5. Click cell M1 and type Assignment #3 and press tab twice. Notice how our row formatting applies to the text we just typed. Adding / Deleting Rows Oftentimes it will be necessary to add or delete a row, especially when we receive new students or lose students initial students throughout the semester. Adding a Row We have a new student and we need to add him to our grade book. We could add him at the bottom and then re-sort the grade book to put them back in alphabetical order, or we could insert a row and enter his information there. 1. The name of our new student is Matthew Gorman. 2. Right click the row header for row 5. 3. Click Insert on the shortcut menu. 4. This will insert a new row between Asya and Roweena. 5. The row becomes row 5 and Roweena becomes row 6. 6. Click cell A5 and type, Gorman, Matthew and press tab to move to cell B5. 7. In cell B5 type S90123456 and press tab to move to cell C5. 8. In cell C5 type [email protected] and press tab to move to cell D5. 9. In cell D5 type (303) 555-4201 and press tab to go to cell Ae5. 10. In cell E5 type =SUM(H5:N5) and press tab to go to cell F5. 11. In cell F5 type =E5/350 and press tab. 12. We have now entered the information for our new student. When we add a new row, it takes the text formatting of the row above it. Since the formatting for Asya is what we want, we do not need to make any changes. However, if we inserted the row at the beginning of the

Creating a Basic Grade Book in Microsoft Excel student names, the new row would be formatted like our grade book column titles, and we would have to change it to the correct formatting. Deleting a Row Asya Foret has decided to drop the class, so we need to delete her from the grade book. 1. Right click the row header for row 4. 2. Click Delete on the shortcut menu. 3. Asya has now been deleted and the next row – Matthew Gorman, which was previously row 5 is now row 4. After you have added and deleted columns and rows, your grade book should look similar to the screen shot below.

View / Track Formulas Now that we have added and deleted assignments and have added and deleted students, we need to reevaluate our formulas. Excel does not always adjust the formula when adding or deleting cells in a formula to accommodate the new information, so we always want to make sure it is still adding what we want it to after making these changes. When adding new rows, the formulas are not always inserted for the new row, so we need to make sure that the new student is formatted correctly. To view the formulas we could click on each cell and view it there or we can view all the formulas all at once. 1. To view all the formulas hold down the Ctrl key and click the ~ mark (usually on the left side of the keyboard.) 2. Release both buttons. 3. Our worksheet has enlarged and if we scroll to see the Total Points column, we will see the formulas, rather than the numerical values. 4. Review all formulas in the Total Points and Percentages columns and revise or correct if needed.

Freeze Panes If we had a lot of students and/or a lot of assignments, as we most likely would with our real courses, we would have to scroll around to look at all the information, and some of the information would not be visible as we are scrolling. Excel has a feature that lets us freeze rows or columns so that they stay in place no matter how far away we scroll. In order to freeze an area, you want to click in the cell below the row or rows you want to freeze and to the right of the column you want to freeze. Freezing a Column 1. Click cell B1. 2. Click Window on the menu bar.

Creating a Basic Grade Book in Microsoft Excel 3. Click Freeze Panes. 4. Notice that the column separator line between columns A and B is now a dark black line. Scroll to the right. Notice how the student names stay in place. Now try scrolling down. Notice how the title row eventually disappears.

Unfreezing a Column 1. Click cell B1. 2. Click Window on the menu bar. 3. Click Unfreeze Panes. 4. The black separator line has returned to normal the student names will disappear if we scroll.

Freezing a Row 1. Click cell A2. 2. Click Window on the menu bar.

Creating a Basic Grade Book in Microsoft Excel 3. Click Freeze Panes. 4. Now scroll down and notice how the title row stays put. Unfreezing a Row 1. Click cell A2. 2. Click Unfreeze Panes. Freezing a Row and a Column 1. Click cell B2. 2. We are now below the row we want to freeze and to the right of the column we want to freeze. 3. Click Window on the menu bar. 4. Click Freeze Panes. 5. Now when we scroll to the right the student name is frozen in place and when we scroll down the title rows stay. Notice that the black line is now between rows 1 and 2 and between columns A and B. This divides our frozen columns from our unfrozen columns. Header / Footer Custom Header 1. Click View on the menu bar 2. Click Header and Footer 3. The Header / Footer dialog box opens 4. Click Custom Header. 5. The Custom Header dialog box opens. 6. Click in the box that says Left Section and type the name of our course Excel101 7. Click in the box that says Center Section and type the semester of the course Fall 2006 8. Click in the box that says Right Section. 9. In the row above the section boxes, click the 2nd button and press the spacebar. This is a formula to insert the current page. 10. Type of then press the spacebar. 11. Click the 3rd button. This is a formula that calculates the total number of pages. 12. Click OK and you have returned to the Header / Footer dialog box.

Custom Footer 1. Click Custom Footer. 2. The Custom Footer dialog box opens.

Creating a Basic Grade Book in Microsoft Excel 3. 4. 5. 6.

Click the Left Section. Type your first and last name. Click the Center Section Click the 7th button. This will insert the name of your file. If you wanted to show the file path (i.e. what folders and drives it is located, you could click the 6th button.) 7. Click the Right Section 8. Click the 4th button. This will insert the date. If you also wanted to include the time, you could click the 5th button. 9. Click OK. 10. We have returned to the header and footer dialog box. 11. Click OK to accept the custom header and footer and return to the worksheet.

Alt + Enter Sometimes, we don’t want all of our columns to extend far enough for the largest text. In some cases, this can make our columns extremely long and will result in a lot more scrolling. We can fix this issue with manually putting text on several lines. We want to change all of our grade book titles that are longer than one word, to have each word on a separate line. 1. Click cell A1 2. Hit the F2 button on your keyboard to go into edit cell mode 3. Use the left arrow button on your keyboard and place the insertion point just to the left of the N in Name. 4. Press and hold the Alt button on your keyboard and press enter. The words are now on two lines. 5. Repeat this process in cell B1, E1, F1, H1, K1, and M1 6. Once you have put the words on two lines, resize all the columns to best fit.

Creating a Basic Grade Book in Microsoft Excel Comments One last major component instructors frequently use with their grade book is comments. Comments can be used for a variety of purposes. I use them as quick notes for students or assignments. Adding a comment We will add a comment that Jennea prefers to be contacted via email, rather than phone. 1. Click cell A2. 2. Click Insert on the menu bar 3. Click Comment. 4. The comments box will appear and is ready for you to enter text. Type Prefers to be contacted via email. 5. Click anywhere on the worksheet to deselect it. 6. The comments box disappears, but now there is a red triangle in the top right corner of the cell. This tells you that this cell has a comment attached to it. 7. Position your mouse over the triangle. The comments box will appear and display your note.

Comment Marker

Show / Hide a Comment You can choose to show or hide your comment. Currently, the comment is hidden. Once you move your mouse, the comment box will go away. 1. Right click cell A2. 2. Click Show/Hide Comments. 3. The comments box will appear and will not go away. Moving a Comment Sometimes, we want comments to show up, but it is in the way of other information, so we need to move the comment box. 1. Click the border of the comments box and hold down the mouse button. 2. Drag your mouse down a couple rows underneath the last student. 3. Release the mouse button. 4. A line is drawn from the comment to cell A2 as you moved the mouse.

Creating a Basic Grade Book in Microsoft Excel Editing a Comment Sometimes, you need to make changes to a comment or add to it. 1. Click cell A2 2. Click Edit Comment. 3. Type: Don’t call after 8:00 PM and don‘t call before 8:00 AM as she has many children who will be asleep. 4. Click anywhere in the spreadsheet to deselect the comments box. Resizing a Comment The comments box is still visible, but we have typed so much text that we cannot see all of it. We can resize the comments box to fit our Not all text is visible needs. 1. Click the comments box to select it. 2. When you select the comments box, a black border surrounds it and resizing handles appear in the middle of all the sides and at each corner.

Resizing Handle to Use

3. Click the middle circle on the bottom line and hold down the mouse button. 4. While holding down, drag your mouse down until you can see all of the text. We could have also widened the comment to see the text. Deleting a Comment Jennea has let us know that now we can call her and that we don’t have to always use email, so we can delete the comment. 1. Right click cell A2 2. Click Delete Comment on the shortcut menu. 3. The comment box is gone and also the red triangle in the corner of the cell. Spellchecking 1. Click the Spelling and Grammar button on the standard toolbar. 2. Excel will lead you through its proposed misspellings. 3. If the word is spelled correctly you can click Ignore which will ignore it once. If you click ignore all, it will ignore all instances of the word for the rest of the spell check.

Spelling and Grammar Checker

If it is spelled correctly used frequently, you can click add it to dictionary, and it will be seen as a correctly spelled word for the duration of the spell check and future spell checks. Excel offers suggestions for each word it believes is misspelled. If it is misspelled, you can click one of the suggestions from the list and click replace. Spell check will insert the correctly spelled word. You can also click in the text above and change it manually. Spell check will also check for grammatical errors and repeated words. 4. Navigate through the spell check until it is done. 5. When it is finished it will say spell check complete. Click ok.

Creating a Basic Grade Book in Microsoft Excel Print Preview Now that we have finished preparing our grade book, we need to see what it will look like when we print it. 1. Click the Print Preview button on the standard toolbar. 2. This will take you to the print preview screen. 3. As we can see, our file is not all on one page, this can cause confusion and inconvenience if we were to print it this way. 4. Click Close on the Print Preview screen to return to the grade book. Print Preview Button

After clicking print preview, we now see dotted lines between some columns and rows on our worksheet. This lets us know the edges of the page and what will appear on a different page. Our goal is to set the grade book to print on one page. We could decrease our column widths to accomplish this, but most likely this would not be enough.

Page Divider Line

Creating a Basic Grade Book in Microsoft Excel Page Setup We will use page setup to help us make our grade book more printable. 1. Click File on the menu bar 2. Click Page Set Up. 3. This takes us to the page setup dialog box. 4. Click the Page Tab if necessary. Changing Page Orientation First we are going to change the orientation of our grade book. Currently, it will be printed in portrait style. We want to print the grade book wider so we will want to print in landscape style. 1. Under the orientation section, click the landscape radio button to select it.

Changing Margins 1. We also want to change the margins to give us some more room. We do not need to create extra room form top to bottom so we will leave those margins alone. If you had more students, where one or two are on a second page, you could adjust the top and bottom margins accordingly to fit the extra information. Instead we are changing the right and left margins. 2. Click the Margins tab 3. Click the down or up arrows for the left margin box until you get to 0.1 then press tab. This will take you to the right margin box. 4. Type 0.1 and press tab. 5. The margins have been changed. 6. Click OK. 7. Each computer is different in its smallest allowable margins. When you press OK, you may see a warning that your text is outside the printable area. 8. Click Fix and it will adjust the margins according to your specific computer settings. 9. Now we will click print preview again to see where we are now.

Creating a Basic Grade Book in Microsoft Excel

Set to Print on One Page Even after our changes, it is still going to print on one page. However, we can manually tell Excel how many pages to print it on. 1. Click file on the menu bar. 2. Click Page Set Up. 3. Click the Page tab. 4. Under the Scaling area, click the Fit To radio button to select it. 5. Make sure it says 1 page wide by 1 page tall. This will ensure that everything is squeezed onto one page. 6. Click OK.

Creating a Basic Grade Book in Microsoft Excel

Now, when we clock Print Preview, we will see that our grade book will print on one page. In your real courses you will have many more assignments and tests then we have demonstrated here. Instructors frequently abbreviate their assignment and test names and make the columns wide enough for just the points. This will help in the ability to get a large amount of columns to all fit on one page. Printing Now that we have set up our grade book to print as we want it to, we can print it. 1. Click File on the menu bar 2. Click Print. This takes us to the print dialog box. 3. From here we can determine where we want it to print, what we want to print, and how many copies. For now, we will print to the default printer and we will print the default amount of one copy. 4. Click print. 5. A dialogue box may appear. Click Print or OK on the dialog box and your grade book should start printing within a few seconds.

Creating a Basic Grade Book in Microsoft Excel Part IV – Templates and Protection Now that we have created and formatted our grade book, we could easily repeat this whole process again for each of our courses, or we could make this file a template. A template is a file that is designed to contain all the formatting and printing options we desire, but with no data. For each course we would open our template and save it with a new name, keeping the original version intact and unchanged. Deleting Information to Make a Template Before saving our grade book as a template, we need to delete the all of the student information (name, number, email, and phone) but we want to leave the formulas intact. 1. Click to select cell A2 and hold down the mouse button. 2. While holding the button drag the mouse to the right to select the information in columns B, C, and D and all the student rows. 3. When all of them are selected release the mouse button. 4. Click the delete key on your keyboard. 5. All of the student information is not gone. Locking Cells It is also important to protect your template and your grade book. You protect the template so that you don’t accidentally change it and you protect your grade book so that it can’t be accessed by anyone but you. First we need to make sure all the cells are locked and cannot be changed. 1. Click the Select All button 2. Click Format on the menu bar 3. Click the Protection tab. 4. Make sure Locked Cells is selected. 5. Click OK. If we had more than one worksheet we wanted to protect, we would need to repeat the steps for locking cells on each additional worksheet. Protecting the Worksheet The locked cells will not take effect until we have first protected the worksheet. 1. Click Tools on the menu bar 2. Click Protection 3. Click Protect Sheet. 4. The protect sheet dialog box will appear. 5. Make sure that all the boxes are unchecked. 6. Since our grade book template does not contain confidential information, there is no need to create a password, so we will leave this one blank. 7. Click OK. 8. We have returned to the grade book. If we had more than one worksheet we wanted to protect, we would need to repeat the sheet protection steps for each additional worksheet.

Creating a Basic Grade Book in Microsoft Excel Protecting a Workbook Protecting the sheet prevents someone from editing or changing what is in the worksheet, but not from renaming the sheet or moving it or inserting or deleting worksheets. 1. Click Tools on the menu bar 2. Click Protection 3. Click Protect Workbook. 4. Make sure that structure is selected and window is not selected. 5. Again, we will not use a password. 6. Click OK. Our grade book sheet and workbook is now protected. Saving as a Template 1. Click File on the menu bar 2. Click Save As. 3. The Save As dialog box opens. Underneath the file name is where we can tell Excel what we want to save this file as. 4. Click the drop down list arrow for the Save as Type box and select Template. 5. Change the File Name to Grade Book Template. 6. Excel wants to save the template with the other Microsoft office templates that come with the program. However, we want to save our template to our floppy disks. 7. Click the Save In list arrow as before and select the 3½ Floppy as our saving place. 8. Click Save. Our template has now been saved. NEED TO ADD A SCREEN SHOT HERE FROM ANOTHER COMPUTER – I DON’T HAVE A FLOPPY DRIVE SO IT WON’T SHOW UP. Now try changing or adding information. It will not let you. Close the file without saving. Opening and Saving from A Template Now that we have our template, you can open it any time and created unlimited additional grade books with the same formatting. 1. Click File on the menu bar 2. Click Open 3. Select your template and the Grade Book Template will open. 4. Click File on the menu bar 5. Click Save As 6. Select the floppy drive and put in a file name of Real Grade Book 7. Click save Unprotecting a Worksheet and Workbook Our worksheet and workbook are still protected so we need to take out the protection to add our course information. 1. Click Tools on the menu bar 2. Click Unprotect Sheet. 3. Since we specified no password, it will automatically unprotect the sheet. 4. Click Tools on the menu bar again 5. Click Unprotect Workbook. 6. Since we specified no password, it will automatically unprotect the sheet.

Creating a Basic Grade Book in Microsoft Excel Grade Book Password Now we can protect our file against anyone seeing the information. Since our main goal is that no one but us gets in, we will add a password to the workbook and a different password to modify it. If we protected the worksheet and the workbook, we also would not be able to edit it without unprotecting them. As opposed to our template, anyone can get in and see what we have, even through it is protected. If you don’t want others to get into your template, you can repeat the steps below to password the template as well. 1. Click Tools on the menu bar 2. Click Options 3. Click the Security tab 4. Click in the box next to Password to Open. 5. Enter a unique identifiable preferably alphanumeric password. You will enter this password anytime you open the file. Make sure you remember what it is, because there is no way to retrieve your password if you forget it. 6. For now type cca in all lower case. 7. You can also add a password that must be entered before any changes can be made. Type cca2 in the password to modify box. 8. Click ok. The next time the file is open, it will ask you for a password to open it and a password to modify it. When you are creating your own grade books, you may decide to only have an open password.