We will add some formatting to our worksheet to improve the appearance. There are a number of places to make changes. We would like to change the font size on the title. I'll click in Cell A1 and change this font to a 16 point font, and we will choose a color for the font. We will make that blue. For the "Grade Book" subtitle, we will use a 14 point font--it might look better if we go down to a 12, add bold, and also assign blue. We would like for "Fall 2007" to be the same as the previous subtitle. A good way to copy formatting is to select the cell where the formatting is assigned, click on the Format Painter, then click in the cell where you wish to copy the formatting. We will also change the formatting on some of the cells where test scores were calculated or assignment scores were calculated. Select the cells for the formatting to be changed. There is an option to convert that to percent style. Click on the percent style button. There is another column or two where calculations were made and they are displaying a lot of decimal places, so we will convert these. Select this series, and notice we are just using the percent style button to convert those. The letter grades would look better centered. We will select that range of cells and click the center button. The title line on the student grade book would be nice set in bold. We can do that by selecting the row and clicking Bold. Let's also place a border around the grade book. I'll select Cell A11, hold the Shift key, and select the last cell, which is M21. Click on the drop-down button for the borders. We have a number of choices here. I want to put a thick box border around the outside with no lines in between. We have a number of choices here. That will place a border around the roll book. Between student names, it is sometimes difficult to follow a line. I'm going to hold the Control key and click on every other line, and we want to select some shading. Click on Format, Cells, and choose Patterns. Select the green--we need a light color so the text will show through--and click OK. That improves the appearance of that area. I believe I had this cell selected, so I am going to undo that. Let's go back and make some changes. It is important to learn how to make changes as much as it is to make the initial assignments to fonts, etc. On this particular cell, I had selected blue, and blue and green don't look just wonderful together. I think I will go back to black text on this; likewise, to these two cells. I can select both of them and select black. The title should be centered over the worksheet area. I'm going to select all the way to the last column that is used, and that would be Column M. We have a merge and center button. When I click on Merge and Center, it places the title in the center. I think I will do likewise for the "Grade Book" subtitle and the second subtitle, "Fall 2007." We now have our grade book formatted to look a little nicer.
Using the information from the grade book worksheet, we will create a simple pie chart showing the grade distribution. In order to do that, I need to know the number of A, B, C, D, F grades. I wrote a simple function, which is beyond the scope of this training, but I will explain what the parts of the function mean. The = sign starts a function or formula. The function name is COUNTIF. The M12:M21 refers to the cells that are being counted, and the "A" says if there is an A there, tell me how many. You will notice in this row, it counted A's, as I move down, the function counts the number of B's, C's, D's, and F's. Select the data and click on Chart Wizard. I will select a pie chart. There are various types of pie charts. I will select the first one. Press and hold to view a sample, and click Next. I want to use columns because this is the way I want the chart to display. Click Next. For data labels, I want to add the category name--that would be the A, B, C, D, F grades--and percentages to show the percentage of each grade. The legend is displayed at the right, and I'll leave it there. We can change the location by choosing another option. For the title, key "Grade Distribution." Click Next. I'll create this as a new sheet. It will create a new worksheet that will display at the lower part of the screen once it is created. It is called Chart2, I'll rename it Chart 1, and click Finish. This is our chart. I'm going to move the legend over a little bit. This is the title. We can view it by zooming in and choosing a higher magnification and maybe see it a little better. Here's Chart 1 and the chart that we have created to show grade distribution.
On a large worksheet, the columns may scroll out of view. To keep them in view, you can freeze or lock them. In this case, we need the students' names next to columns so we can either enter grades or view the grades. To freeze a pane, select the cell at the point the panes are to be frozen. I'll select one cell just to the right of the area I want frozen. Click on Window, Freeze Panes. You will see the cross bars here. I have my student names in Columns C and D as I scroll, F moves over and E does not display. Notice how G, the Assignment Average moves over. I am able to view scores, test scores, the final average, and letter grade by scrolling. Once I am finished using the freeze, to turn it off, click Window, Unfreeze Panes.
There are two ways to sort lists in Excel. One way is to use the Sort Ascending and Sort Descending buttons. You will notice that the students' last names are not in alphabetical order. Click any one cell within the list and then click Sort Ascending. That arranges them in alphabetical order by last name. Excel automatically keeps row information together when you sort a column. All of the information for any one student is rearranged when the names are alphabetized. Just as Sort Ascending arranged them alphabetically from A to Z, we can choose Sort Descending by clicking the Z to A button. I'll undo this last sort. To sort with multiple criteria, we can select a cell in the list and then choose Data, Sort. The reason we would want to use multiple criteria is that "Miller, Nicole" and "Miller, James" were not alphabetized with a single sort. We can do a primary sort on the last name and a secondary sort on the first name. Click on Data, Sort. The last name would be the primary sort and that would be ascending. We'll choose first name as the secondary sort, and choose ascending. You can also sort with another criterion. Header row is selected to identify the fact that there is a header row, and it will not be sorted. Click OK. Now the students' last names are in alphabetical order as well as James' and Nicole's first names.
Excel has an AutoFilter feature that allows you to display only the data that meet your criteria. To turn on the AutoFilter, click any cell within the list. Click Data and choose Filter and AutoFilter. A drop-down list will appear at the top of each column. Look at the "Letter Grade" column. I will drop the list down, and we will choose "C." I should have students listed who have a letter grade of "C." To return to the full list, click on Data, Filter, Show All. Notice that AutoFilter is still on. Let's do one other filtered search. We will use the Assignment 2 column. I would like to display all students who made 80 or above on Assignment 2. Click the drop-down list, choose Custom. I would like to have everyone whose score is "greater than or equal to" "80." Click OK, and now under Assignment 2, you will see only students listed who have 80 or above on the assignment.
We will add a header to the worksheet. This will allow us to repeat the same information at the top of every printed page, and that is referred to as a header. You can also create footers which would repeat information at the bottom of every printed page. It is similar to numbering pages when you are keying a report. Click on View, choose Header or Footer, and we will select Custom Header. There are three sections, and then there are a number of buttons that we can use to insert information. In the left section, let's insert the file name. Clicking on the icon that looks like Excel will insert the file name. The code displays: &[File], and it is left aligned. In the center section, by default, it is going to be centered. The clock will insert the time. I'll insert a comma and a space, and then insert the date--these are pages from a calendar--and it inserts the date. I could on the right section insert a page number. There are two ways to do page numbers. The number icon inserts a page number code. This one would indicate number of pages. So in order to do this, I will insert &[Page #] of &[Pages #]. This will give me the page number and total number of pages. We'll look at that after it has been inserted. I'll scroll a little bit to the left so the last code will display. And we will click OK. The page setup page displays. Let's click Print Preview so we can see how it looks. Zooming in gives us a little larger view. This is the file name. You will notice that the time and date displays, and at the right margin, this is Page 1 of 3. When the worksheet prints, the header will be printed at the top of every page. The same process would be used if you wanted to insert a footer. Click on the Custom Footer button and follow the same procedure.
We are now ready to print the worksheet. We have a choice of either printing the worksheet or the entire workbook. Step 1 is to mark the print area. Select Cell A1, hold the Shift key, and scroll to the last cell. We need to go down to Row 30 and over to Column M. Click on File, Print Area, Set Print Area. That area has now been marked to print. Choose File, Page Setup. There are four tabs. The first one is page. Choose either portrait or landscape. Since this a rather wide grade book I'm going to choose landscape. We have choices of adjusting to the normal size. This can be reduced or increased. I'm going to leave it at 100 percent so the print is not extremely small. It is possible to fit the entire worksheet onto a page. Click "Fit to." You can choose the number of pages and how tall it should be. I'll go back to adjust to 100 percent. The paper size is letter size, and we cannot make further adjustments there. The margins are preset. I'm going to accept those. I do want to center it horizontally on the page, and I'll select to center vertically on the page. The header/footer information was entered previously. The file name, time, date, and number of pages is already set. On the sheet tab, we have options of adding rows or columns. I'm going to delete the content here, but if we wanted to include student names at the left of every page, we can do that by clicking the icon and selecting those columns. Gridlines will place lines around each cell. Row and column headings will print letters at the tops of columns and numbers to the left of rows. We can preview what our worksheet will look like. Click Next to see the second page. I'll close the print setup, and now we are ready to send it to the printer. Choose File, Print. Make sure you have selected a printer that is active. For "print what," you have a choice of printing a selection--which is selected material, the active sheets, or the entire workbook. The entire workbook would include the active sheet and also the chart. Click OK.