5.4 Chapter Practice

Tables For A Tourism Company

Download Data File: PR5 Data

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.

 1.  Open the data file PR5 Data and save the file to your computer as PR5 Canyon Trails.

 2.  Click Sheet 1.  Choose cell B3.

 3.  From the Home tab, choose Format as Table. Choose the Orange, Table Style Medium 3.

 4.  In J4, calculate Total Cost (number of Guests *Per Person Cost). Note Excel will add the formula to the entire column. (If prompted, choose to overwrite the formula to the cells below.)

 5.  Format Columns I and J with Accounting format, no decimal places.

6.  Center all headings in Row 3.

 7.  Adjust column widths within the table so that all the headings are completely visible.

 8.  Rename Sheet 1 Current Tours. Sort this sheet alphabetically (A to Z) by Last Name.

9.  Make a copy of the Current Tours sheet and rename it Tours by Canyon. One way to make a copy of a worksheet is to right-click on the worksheet tab ( Mac Users: Ctrl+click) and select Move or Copy.  Be sure to check the Create a Copy box. Place the Tours by Canyon sheet to the right of the Current Tours sheet.

10.  Sort the Tours by Canyon sheet by Tour Canyon, Home Country, and then Last Name all in Ascending order (A to Z).

11.  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.

12.  Filter the US Guests sheet to display customers who live in the United States. Sort the filtered data alphabetically (A to Z) by Tour State. Add a Total Row that sums the Guests and Total Cost columns.

13.  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.

14.  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 L3. Resize the slicer so all buttons display. Format the slicer to match the table.

15.  Using the slicer, filter the data to display customers from Germany and the United Kingdom.

16.  Sort the filtered data by the Home Country, and Last Name fields displaying both in Ascending order (A to Z).

17.  Click the Advanced Filter sheet. Using the Advanced Filter option, filter the Current Tours table based on the criteria given.  Determine how many guests from Canada are taking tours in Arizona and Utah between the costs indicated in the criteria table. Place the results in A10.

18.  Turn the results into a table. Format the table to match the criteria area.  Turn on the total row and show the Sum of the Total Cost column.

19.  Select the Current Tours sheet. Click in the table area and insert a PivotTable as a new sheet. Name the sheet ToursPT. Run a report to show the Total Cost per Home Country, for each available Tour States. Format the numbers in currency format, zero decimal places. Choose a PivotStyle format to match the current orange theme.

20.  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. Go to the Table Tools and turn off the Banded Rows.

21.  Subtotal the data by State, summing the Total Cost column. (Note: Remember to follow the four rules of subtotaling!)

22.  After you subtotal, turn on filters and filter out 3-day tours in the table.

23.  On each worksheet, make the following print setup changes:

a) Add a footer with the current date, worksheet name, and your name.

b) Change to Landscape Orientation

c) Set the scaling to Fit All Columns on One Page

d) 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.

24.  Check the spelling on all of the worksheets and make any necessary changes. Save the PR5 Canyon Trails workbook.  Submit the PR5 Canyon Trails workbook as directed by your instructor.