Compute Your Grades with Excel
Overview: This spreadsheet activity uses the AVERAGE function to compute student grades. You will
also learn how to hide columns so that you can graph data in non-adjacent columns.
1. Open a new spreadsheet file.
2. Set up your spreadsheet file like this.
3. Right-align the words in cells B1, C1, D1, E1 and F1.
4. Mike Artley’s Final Grade
A. Click cell F2.
B. Type =AVERAGE(B2:E2). Press <Enter>.
5. Fill the function down to cell F6.
6. Class Average for Test 1
A. Click cell B7.
B. Type =AVERAGE(B2:B6). Press <Enter>.
7. Fill the function to the right through cell F7.
8. Create a Chart Using Non-Adjacent Spreadsheet Column Contents
There are times in which you might want to create a chart that includes information from one
column and another column that is not next to it. An example would be a chart of class averages
that includes student names, where the names and averages are not in adjacent columns. If the
names were positioned in the column adjacent to the averages, then you could select the data in
both columns and proceed by clicking the Chart Wizard button. When the columns are not
adjacent to each other, then an additional step needs to be applied.
A. Select cells A2 through A6.
B. Press the <CTRL> key (Command key on a Macintosh) and select cells E2 through E6. The
result should look like this.
By holding the <CTRL> key, you can select as many columns as you wish, regardless of
location.
#3469 Step-by-Step Spreadsheet Activities for Microsoft Excel ©Teacher Created Resources, Inc.
Compute Your Grades with Excel (cont.)
C. Now click the Chart Wizard button from the Standard Toolbar and decide which type of
graph you wish to produce. A pie chart is probably not the best representation. Why?
D. Produce the chart.
E. Resize and move the chart so it does not cover any of the chart data.
9. Finishing
A. In the footer, type your chart’s name, press <Enter>, and type your name.
B. Go to FILE and select Save. Name the file.
C. Print the spreadsheet in portrait format with the answers showing.
D. Then print the spreadsheet in portrait format with the formulas showing.
E. Go to FILE and select Exit.
Time to Review and Extend
Answer the following questions.
1. Why will the AVERAGE function work to accurately compute student grades
in this spreadsheet? ____________________________________________
_____________________________________________________________
2. Why was it necessary to hold the <CTRL> key when selecting data for the
chart?
_____________________________________________________________
_____________________________________________________________
3. Instead of using the <CTRL> key (or Command key on a Macintosh), there
is another way to hide columns. Complete the following steps using SS Act
23.
A. Select cells A1 through F6.
B. Go to EDIT and select Copy.
C. Click the Sheet 2 tab near the bottom of the screen.
©Teacher Created Resources, Inc. #3469 Step-by-Step Spreadsheet Activities for Microsoft Excel
Time to Review and Extend (cont.)
D. Click cell A1.
E. Go to EDIT and select Paste. After the cell contents are pasted, you
may need to adjust column width.
F. Click column labels B through E.
G. Go to FORMAT and choose Column, Hide.
H. Now select A2 through F6. Even though columns B through E are still part
of your spreadsheet, they are hidden from view and therefore are not
selected.
I. Now make a column graph of the data and place it below cell C7.
J. Click column A and drag to column F. This also selects everything in
between.
K. Go to FORMAT, select Column, Unhide.
L. What happened to the graph after unhiding all of the columns? ________
__________________________________________________________
M. Why is it better to use <CTRL> (Command on a Macintosh) instead of the
Hide option when selecting non-adjacent columns?_______________
__________________________________________________________
N. Save the file. This file now contains Sheet 1 and Sheet 2.
#3469 Step-by-Step Spreadsheet Activities for Microsoft Excel ©Teacher Created Resources, Inc.
Compute Your Grades with Excel
Answer Key
8C) It is not meaningful to compare the students’ averages to the whole. Column charts or line charts
would be better visual representations of the data.
Review 1) Because all of the tests equal 100 points. If that was not the case, the AVERAGE function
could not be used to compute the percent. With this data, the points for each of the tests are the
same as the percentages for each of the tests.
Review 2) Without pressing <CTRL>, the two non-adjacent columns could not have been selected.
Review 3L) It added extra bars to represent the unhidden rows.
Review 3M) You can still see all the content.
©Teacher Created Resources, Inc. #3469 Step-by-Step Spreadsheet Activities for Microsoft Excel
