Microsoft Excel – Lesson 5

/ Excel, Excel Resources, Office / By

Activity Overview

The following activity illustrates how spreadsheets can be used to calculate a company’s purchase discounts and sales tax.

New Skill

In this activity, you will practice how to:

  1. Format cells for percentages, comma, and decimal places.
  2. Insert and format Word Art.

Lesson

  1. Open the ACCOUNTS PAYABLE.xls file.
  2. Select all text and change the font to Comic Sans MS and 14 font size.
  3. Format the width of column A to 36.0.
  4. Format the width of column B – G to 17.0.
  5. Right align column B and columns D – G.
  6. Center align column C.
  7. Adjust the height of row 1 to 69.0.
  8. Bold rows 2 – 7.
  9. Compute the formulas for the first company, Baby Phat, as follows:
    a. PURCHASE DISCOUNT = AMOUNT OWED multiplied by % DISCOUNT
    b. SUBTOTAL = AMOUNT OWED minus PURCHASE DISCOUNT
    c. SALES TAX = SUBTOTAL multiplied by 6%
    d. AMOUNT OWED = SUBTOTAL plus SALES TAX
  10. Use the AutoFill copy feature to copy the formulas down for the remaining companies.
  11. Enter formulas to calculate the TOTALS, AVERAGE, MAXIMUM, and MINIMUM for column B and D – G.
  12. Format cells B9 – B39 as comma displaying 2 decimal places.
  13. Format cells C9 – C34 as percentages displaying 0 decimal places.
  14. Format cells D9 – G39 as comma displaying 2 decimal places.
  15. Merge and center cells A2:G2.
    Merge and center cells A3:G3.
    Merge and center cells A4:G4.
  16. Insert the title CUSTOM FRAGRANCES in WordArt above the address.
    a. Select the WordArt title CUSTOM FRAGRANCES and change the font to Jokerman and the font size to 36.
    b. Move and drag the WordArt title CUSTOM FRAGRANCES so it is positioned in row 1 and is centered between columns A – G.
    c. Format the WordArt as you desire changing the style, fill, outline, and effects of the WordArt.
  17. Display formulas in your spreadsheet to check for accuracy. Then restore your spreadsheet to show your totals again.
  18. Change page setup settings as follows:
    a. Scale to fit to one page.
    b. Center the spreadsheet vertically and horizontally.
  19. Insert a Custom Header using Left section: Your Name, Center section: ACCOUNTS PAYABLE, and Right section: Today’s Date.
  20. Insert a Custom Footer using Center Section: PAGE number
  21. Print Preview your spreadsheet to view your header and make sure it fits on one page.
  22. Save your document.
  23. Optional: Print a copy for review.
Scroll to Top