5.4 Chapter Scored

Roofing Companies

Download Data File:  SC5 Data

  1. Open the data file SC5 Data and save the file to your computer as SC5 Roofing Companies.
  2. Delete Columns D & E
  3. Expand Column A to a 40 pt. width
  4. Format Columns B to a 15 pt. width.
  5. Format Column C to a 18 pt. width
  6. Create a Table by clicking anywhere in the worksheet, then click on the Insert tab. then Table. Make sure to check the box “My table has headers” and then click OK.
  7. Choose Blue, Table Style Medium 2
  8. Select “Remove Duplicates” within Table Design ribbon.
  9. When the menu box opens, Unselect all Columns except Company.
    • Confirm you receive a text ox that says, “52 duplicate values found and removed; 87 unique values remain.” Hit OK.
  10. Copy/Paste this worksheet to create a new worksheet; rename this newly created worksheet as State Missing.
  11. Filter column E to display just “Blank” and hit OK. This will show you only companies that do not have a state listed with their information.
  12. Copy/Paste A.S.R. worksheet to create a new worksheet; rename this newly created worksheet as Last Name Missing.
  13. Filter column C to display just “Blank” and hit OK. This will show you only companies that do not have a a Last Name entered with their information.
  14. Copy/Paste A.S.R. worksheet to create a new worksheet; rename this newly created worksheet as OR Roofing Companies with Tag
  15. Format Column D Best Fit.
  16. Center the text for A1:F1 so that column headings are centered over each column.
  17. Format Column F to 87 pts.
  18. Use Filter by Text, then use a “custom filter” to display/filter companies that contain companies that are ‘Minority Business Enterprise (MBE)’ or ‘Women Business Enterprise (WBE)’.  Hint:  you need to select “contain” for both rows in the custom filter.
  19. Filter companies only located in Oregon.
  20. Sort by Last Name, A to Z.
  21. Save the file; submit as directed.

License

Icon for the Creative Commons Attribution 4.0 International License

Excel for Contractors Copyright © by Barbara Lave; Nick Bredleau; Hallie Puncochar; Julie Romey; Mary Schatz; and Art Schneider is licensed under a Creative Commons Attribution 4.0 International License, except where otherwise noted.

Share This Book