Microsoft Excel – Lesson 2

/ Excel, Excel Resources, Office / By

Activity Overview

The following activity illustrates how spreadsheets can be used to compute number totals, averages, maximums (highest), and minimums and sort totals.

New Skill

In this activity, you will practice how to:

  1. Add numbers in a spreadsheet using a formula.
  2. Use the AutoFill feature to complete formulas.
  3. Display formulas in a spreadsheet.
  4. Use the Average, Maximum, and Minimum functions.
  5. Sort data in descending order and scale a spreadsheet to fit to one page.
  6. Format cells as numbers with 0 decimal places.

Lesson

  1. Open the SAT SCORES.xls file.
  2. Bold cell A5.
  3. Bold rows 7 and 8. Select the ENTIRE row.
  4. Underline row 8.
  5. Format the width of columns A and B to 20.0 and left align if needed.
  6. Format the width of columns C – F to 12.0 and center align. Select the ENTIRE column.
  7. In cell F9 enter a formula to add the Critical Reading, Math, and Writing scores for Lisa Hom.
  8. Use the AutoFill copy feature to quickly calculate the totals for the remaining cells in column F. When finished each student should have a total of their Critical Reading, Math, and Writing scores.
  9. Select cells A9 – F42. Do a Custom Sort to alphabetize the students by their last names in Ascending order (A-Z). Use the “LAST” column to Sort by.
  10. Compute the AVERAGE, MAXIMUM, and MINIMUM formulas for column C, CRITICAL READING as follows:
    a. Enter a formula to find the average score of the students on the Critical Reading portion of the test.
    – Click in cell C46 and click the down arrow next to the AutoSum button.
    – Select AVERAGE.
    – Drag to select C9:C42 so that the formula indicates =AVERAGE(C9:C42) and press Enter.
    b. AutoFill copy the AVERAGE formula in cell C46 across to columns D, E, and F.
    c. Enter a formula to find the highest score received on the Critical Reading portion of the test.
    – Click in cell C47 and click the down arrow next to the AutoSum button.
    – Select MAXIMUM.
    – Drag to select C9:C42 so that the formula indicates =MAXIMUM(C9:C42) and press Enter.
    d. AutoFill copy the MAXIMUM formula in cell C47 across to columns D, E, and F.
    e. Enter a formula to find the lowest score received on the Critical Reading portion of the test.
    – Click in cell C48 and click the down arrow next to the AutoSum button.
    – Select MINIMUM.
    – Drag to select C9:C42 so that the formula indicates =MINIMUM(C9:C42) and press Enter.
    f. AutoFill copy the MINIMUM formula in cell C48 across to columns D, E, and F.
  11. Format cells C46 – F48 as numbers displaying 0 decimal places.
  12. Select cells A9 – F42. Do a Custom Sort to arrange the students from highest total score to lowest total score. Use the “TOTAL” column to sort by Largest to Smallest. Note: The student with the highest TOTAL score will appear on top and the student with the lowest TOTAL score will appear at the bottom.
  13. Display formulas in your spreadsheet to check for accuracy. Then restore your spreadsheet to show your totals again.
  14. Change page setup settings as follows:
    a. Scale to Fit to One Page.
    b. Set the page to Print Gridlines.
    c. Horizontally and Vertically center.
    d. Insert a Custom Header using Left section: Your Name, Center section: SAT, and Right section: Today’s Date.
  15. Rename Sheet 1 to SAT and change the Tab Color to Orange. Delete Sheet 2 and Sheet 3.
  16. Print Preview your spreadsheet to make sure it fits on one page and to view your header.
  17. Save your document.
  18. Optional: print a copy for review.
Scroll to Top