5.4 Chapter Practice

Tables for a Tourism Company

Download Data File: PR5 Data

Canyon Trails Tour Company workbook with 5 worksheets (L-R): Current Tours, Tours by Canyon, US Guests, European Guests, and open to 5-7 Day Tours by State. A1:F1 range merged into one cell for title in large, orange font. G1:J1 contains a color photo of canyon. Row 2 has no data. A3:J3 Column titles in succession: First Name, Last Name, Guests, Average Age, Home Country, Tour Canyon, Tour State, Days, Per Person Cost, Total Cost. A4:I53 data entered.
Figure 5.30 Completed Exercise

Travel and tour companies need to keep track of client data, as well as, travel/tour options and tour guides. Keeping up-to-date, accurate records is essential to their bottom line. To run a tour company, employees must be able to manipulate their data quickly and easily. This exercise illustrates how to use the skills presented in this chapter to generate the data needed on a daily basis by a tourism company. See Figure 5.30 above.

  1. Open the data file PR5 Data and save the file to your computer as PR5 Canyon Trails.
  2. In Column J, calculate Total Cost (number of Guests *Per Person Cost). Copy the formula down the column.
  3. Format Columns I and J with Currency and no decimal places.
  4. Center all headings in Row 3.
  5. Click in cell A3. Insert a table with headers for the range A3:J53.
  6. Adjust column widths within the table so that all the headings are completely visible.
  7. Rename Sheet 1 Current Tours. Sort this sheet alphabetically (A to Z) by Last Name.
  8. Make a copy of the Current Tours sheet and rename it Tours by Canyon.  Place the Tours by Canyon sheet to the right of the Current Tours sheet. Sort this sheet by Tour Canyon (A to Z), then Home Country (A to Z), and then Last Name (A to Z).
  9. Make another copy of the Current Tours sheet and rename it US Guests. Place the US Guests sheet to the right of the Tours by Canyon sheet.  Filter this sheet so that only guests with a Home Country of the United States show. Sort the filtered data alphabetically (A to Z) by Tour State. Add a Total Row that sums the Guests and Total Cost columns.
  10. Make another copy of the Current Tours sheet and rename it European Guests.  Place the European Guests sheet to the right of the US Guests sheet. Hide the Average Age column.
  11. Insert a slicer in the European Guests sheet for Home Country. Move the top left corner of the slicer to the top left-hand corner of cell K3. Change the width of the entire slicer to 1.65”.
  12. Select both Germany and the United Kingdom on the slicer. Sort the filtered sheet by Home Country (A to Z) and then Last Name (A to Z).
  13. Make one more copy of the Current Tours sheet and rename it Tours by State. Place the Tours by State sheet to the right of the European Guests sheet. Subtotal the sheet by State, summing the Total Cost column.
  14. Change the name of the Tours by State sheet to 5-7 Day Tours by State. Filter out 3 day tours in the table.
  15. On each worksheet, make the following print setup changes:
    1. Add a footer with the worksheet name in the center.
    2. Change to Landscape Orientation
    3. Set the scaling to Fit All Columns on One Page
  16. For any worksheets that print on more than one page, add Print Titles to repeat the first three rows at the top of each page.
  17. Save the PR5 Canyon Trails workbook.
  18. Make sure your sheets are in the following order from left to right: Current Tours, Tours by Canyon, US Guests, European Guests, and 5-7 Day Tours by State.
  19. Compare your work with the self-check answer key (found in the Course Files link) and then submit the PR5 Canyon Trails workbook as directed by your instructor.

Attribution

“5.4 Chapter Practice” by Diane Shingledecker, Portland Community College is licensed under CC BY 4.0

“Canyon Trails Data File” by Matt Goff is licensed under CC BY 3.0

License

Icon for the Creative Commons Attribution 4.0 International License

Beginning Excel, First Edition Copyright © by Noreen Brown, Barbara Lave, Julie Romey, Mary Schatz, Diane Shingledecker is licensed under a Creative Commons Attribution 4.0 International License, except where otherwise noted.