You are considering several cities for a vacation. In particular, you are interested in Washington DC, Philadelphia, and Boston. You will format a list of memorials in DC, add Spark lines to compare the number of visitors over a 15-year period, and create a bar chart to illustrate annual visitors at each memorial. In addition, you will create a table of sightseeing locations, sort and filter the data, apply conditional formatting, and add a total row to display average time needed to spend at each memorial. Finally, you will complete a worksheet by adding formulas to compare estimated major expenses for each city.i am posting the links for 2 files, one contains instructions and other contains the file these are the links for the files required.

https://drive.google.com/file/d/1edd95uW0g1kmh9p9rGVI03tjy6xLBswm/view?usp=sharing

https://drive.google.com/file/d/1WP0H71WGoDEZdJGSSU06tNpDzK0tzQKP/view?usp=sharing

1. Start Excel. Download and open the file named exploring_ecap_grader_a1.xlsx.

2. On the DC worksheet, select the range A4:G4, wrap the text, apply Center alignment, and apply Blue, Accent 5, Lighter 60% fill color.

3. On the DC worksheet, merge and center the title in the range A1:G1. Apply Accent5 cell style and bold to the title.

4. On the DC worksheet, change the width of column A to 34.

5. On the DC worksheet, select the range C5:F10 and insert Line Sparklines in the range G5:G10.

6. On the Cities worksheet, click cell F4 and enter a formula that will subtract the Departure Date (B1) from the Return Date (B2) and then multiply the result by the Rental Car per Day value (F3).

7. On the Cities worksheet, click cell E13. Depending on the city, you will either take a shuttle to/from the airport or rent a car. Insert an IF function that compares to see if Yes or No is located in the Rental Car? Column for a city. If the city contains No, display the value in cell F2. If the city contains Yes, display the value in the Rental Car Total (F4). Copy the function from cell E13 and use the Paste Formulas option to copy the function to the range E14:E18 without removing the border in cell E18.

8. On the Cities worksheet, click cell F13. The lodging is based on a multiplier by City Type. Some cities are more expensive than others. Insert a VLOOKUP function that looks up the City Type (B13), compares it to the City/COL range (A7:B10), and returns the COL percentage. Then multiply the result of the lookup function by the Total Base Lodging (B5) to get the estimated lodging for the first city. Copy the function from cell F13 and use the Paste Formulas option to copy the function to the range F14:F18 without removing the border in cell F18.

9. On the Cities worksheet, click cell H13 and enter the function that calculates the total costs for the first city. Copy the function in cell H13 and use the Paste Formulas option to copy the function to the range H14:H18 without removing the border in cell H18.

10. On the Cities worksheet, select the range E14:H18 and apply Comma Style with zero decimal places. Select the range E13:H13 and apply Accounting Number format with zero decimal places.

11. On the Cities worksheet, in cell I2, enter a function that will calculate the average total cost per city. In cell I3, enter a function that will identify the lowest total cost. In cell I4 enter a function that will return the highest total cost.