Please help Im stuck.

View Help Tell me what you want to do Home Insert Design Layout References Mailings Review Shelly Cashman Excel 2016 | Module 6: SAM Project 1b Go Adventure Tours CREATING. SORTING, AND QUERYING A TABLE sam TTING STARTED Open the file SC_EX16 6b FirstLastName_1.xlsx, available for download from the SAM website. Save the file as SC_EX16_6b_FirstLastName_2.xlsx by changing the "1" to a "2". If you do not see the xlsx file extension in the Save As dialog box, do not type it. The program will add the file With the file SC EX16_6b_FirstLastName, 2.xlsx still open, ensure that your first and last name is displayed in cel D If cell Bo does not display your name, delete the file and download a new copy from the SAM website. ROJECT STEP 1. Kris Hayden works at Go Adventure Tours. She wants to summarize tour information about Go's tours into tables to he Switch to the 2018 Tours worksheet. Select the range A1:H12 and format the range as a table (with headers), using th Use Scheduled Tours as the name of the table. 2. Kris decides to add a new tour to the 2018 schedule. Switch to the Air Info worksheet. Enter a new record into the end of the Air_Info table (in row 12) as shown in Table 1 able 1: New Air_Info Record Depart Date Number of Days Hiking Patagonia 12/5/2018 3. Sort the Air Info table from smallest to largest by the price field values. 4. On the Alr Info worksheet, Kris wants to display only tours that have air included Use an advanced filter to copy all the records for the tours that include air from the the Air_Info table into a new rang- a. Enter Yes into cell G16 to set up the value to filter on in the criteria range. b. In the Advanced Filter, use the range AT:H12 as the List range, the range A15:16 as the Criteria Range, and co 5. Switch to the 7-Day Tours worksheet. Apply Wrap Text Formatting to the range E1:1. 6. Remove the duplicate record in the Seven_Days table 7. Seven-day tours are the most popular tours sold by Ge and Kris wants to highlight these tours in the table Filter the table to display in those rerned with a Number of Days value of 9 werdie Type here to search Tell me File Home Insert Design Layout References Mailings Review View Help 3. Sort the Air_Info table from smallest to largest by the Price field values. 4. On the Air Info worksheet, Kris wants to display only tours that have alr included. Use an advanced filter to copy all the records for the tours that include air from the the Air a. Enter Yes into cell G16 to set up the value to filter on in the criteria range. b. In the Advanced Filter, use the range A1:H12 as the List range, the range A15:H16 a 5. Switch to the 7-Day Tours worksheet. Apply Wrap Text formatting to the range E1:1. Remove the duplicate record in the Seven Days table. 7. Seven-day tours are the most popular tours sold by Go and Kris wants to highlight these tour Filter the table to display only those records with a Number of Days value of 7. 8. Kris wishes to show her sales associate a snap-shot of the number of seats reserved and seat Switch to the Subtotals worksheet. Convert the Subtotals table in the range A1:24) into ar 9. After converting the table to a range, insert subtotals into the range A1:H24 using the followin a. Subtotals should be inserted at each change in the Tour value. b. C. The subtotals should use the Sum function. Subtotals should be added to the Seats Reserved and Seats Available values only. d. The subtotals should replace current subtotals and include a summary below the data. 10. Kris wishes to summarize the number of tours offered for certain locations and the deposits col Switch to the Tour Info worksheet and complete the following steps to identify how many seats a. In cell C2, enter a formula using the COUNTIF function that counts the number of Corfu ! b. In cell C3, enter a formula using the COUNTIF function that counts the number of Nepal c. In cell C4, enter a formula using the COUNTIF function that counts the number of Hiking 11. Complete the following steps to determine the total deposits received for each tour a. In cell D2, enter a formula using the SUMIF function that totals the amounts in the Depo Tour Info[Deposits Received] as the sum_range argument in your formula b. In cell D3, enter a formula using the SUMIF function that totals the amounts in the Depos Tour Info[Deposits Received as the sum_range argument in your formula. C. In cell 04, enter a formula using the SUMIF function that totals the amounts in the Depos Tour Info[Deposits Received] as the sum_range argument in your formula. 12. In the range 08:029 (the Seats Reserved column), create a new Icon Set conditional formatting Use the 3 Traffic lights (Unrimmed) icon set in your conditional formatting rule. a. Display the green, circular icon in all cells with a Number type value greater than or equi b. Display the yellow, circular icon in all cells with a Number type value less than 12 and grea c. Display the red, circular icon in all cells with a Number type value less than 6. 13. Apply a custom sort on multiple fields to the Tour Info table, so that the table is first sorted by th 14. Add a Total Row to the Tour Info table. (Hint: The total row should appear in row 30 with a total Using the total row, display the SUM of the Seats Reserved in cell D30. 1509 words Type here to search o i na OO View Help Tell me what you want to do Home Insert Design Layout References Mailings Review Shelly Cashman Excel 2016 | Module 6: SAM Project 1b Go Adventure Tours CREATING. SORTING, AND QUERYING A TABLE sam TTING STARTED Open the file SC_EX16 6b FirstLastName_1.xlsx, available for download from the SAM website. Save the file as SC_EX16_6b_FirstLastName_2.xlsx by changing the "1" to a "2". If you do not see the xlsx file extension in the Save As dialog box, do not type it. The program will add the file With the file SC EX16_6b_FirstLastName, 2.xlsx still open, ensure that your first and last name is displayed in cel D If cell Bo does not display your name, delete the file and download a new copy from the SAM website. ROJECT STEP 1. Kris Hayden works at Go Adventure Tours. She wants to summarize tour information about Go's tours into tables to he Switch to the 2018 Tours worksheet. Select the range A1:H12 and format the range as a table (with headers), using th Use Scheduled Tours as the name of the table. 2. Kris decides to add a new tour to the 2018 schedule. Switch to the Air Info worksheet. Enter a new record into the end of the Air_Info table (in row 12) as shown in Table 1 able 1: New Air_Info Record Depart Date Number of Days Hiking Patagonia 12/5/2018 3. Sort the Air Info table from smallest to largest by the price field values. 4. On the Alr Info worksheet, Kris wants to display only tours that have air included Use an advanced filter to copy all the records for the tours that include air from the the Air_Info table into a new rang- a. Enter Yes into cell G16 to set up the value to filter on in the criteria range. b. In the Advanced Filter, use the range AT:H12 as the List range, the range A15:16 as the Criteria Range, and co 5. Switch to the 7-Day Tours worksheet. Apply Wrap Text Formatting to the range E1:1. 6. Remove the duplicate record in the Seven_Days table 7. Seven-day tours are the most popular tours sold by Ge and Kris wants to highlight these tours in the table Filter the table to display in those rerned with a Number of Days value of 9 werdie Type here to search Tell me File Home Insert Design Layout References Mailings Review View Help 3. Sort the Air_Info table from smallest to largest by the Price field values. 4. On the Air Info worksheet, Kris wants to display only tours that have alr included. Use an advanced filter to copy all the records for the tours that include air from the the Air a. Enter Yes into cell G16 to set up the value to filter on in the criteria range. b. In the Advanced Filter, use the range A1:H12 as the List range, the range A15:H16 a 5. Switch to the 7-Day Tours worksheet. Apply Wrap Text formatting to the range E1:1. Remove the duplicate record in the Seven Days table. 7. Seven-day tours are the most popular tours sold by Go and Kris wants to highlight these tour Filter the table to display only those records with a Number of Days value of 7. 8. Kris wishes to show her sales associate a snap-shot of the number of seats reserved and seat Switch to the Subtotals worksheet. Convert the Subtotals table in the range A1:24) into ar 9. After converting the table to a range, insert subtotals into the range A1:H24 using the followin a. Subtotals should be inserted at each change in the Tour value. b. C. The subtotals should use the Sum function. Subtotals should be added to the Seats Reserved and Seats Available values only. d. The subtotals should replace current subtotals and include a summary below the data. 10. Kris wishes to summarize the number of tours offered for certain locations and the deposits col Switch to the Tour Info worksheet and complete the following steps to identify how many seats a. In cell C2, enter a formula using the COUNTIF function that counts the number of Corfu ! b. In cell C3, enter a formula using the COUNTIF function that counts the number of Nepal c. In cell C4, enter a formula using the COUNTIF function that counts the number of Hiking 11. Complete the following steps to determine the total deposits received for each tour a. In cell D2, enter a formula using the SUMIF function that totals the amounts in the Depo Tour Info[Deposits Received] as the sum_range argument in your formula b. In cell D3, enter a formula using the SUMIF function that totals the amounts in the Depos Tour Info[Deposits Received as the sum_range argument in your formula. C. In cell 04, enter a formula using the SUMIF function that totals the amounts in the Depos Tour Info[Deposits Received] as the sum_range argument in your formula. 12. In the range 08:029 (the Seats Reserved column), create a new Icon Set conditional formatting Use the 3 Traffic lights (Unrimmed) icon set in your conditional formatting rule. a. Display the green, circular icon in all cells with a Number type value greater than or equi b. Display the yellow, circular icon in all cells with a Number type value less than 12 and grea c. Display the red, circular icon in all cells with a Number type value less than 6. 13. Apply a custom sort on multiple fields to the Tour Info table, so that the table is first sorted by th 14. Add a Total Row to the Tour Info table. (Hint: The total row should appear in row 30 with a total Using the total row, display the SUM of the Seats Reserved in cell D30. 1509 words Type here to search o i na OO