create a bar chart to illustrate annual visitors at each memorial.

Exp19 Excel AppCapstone IntroAssessment Travel

Project Description:

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

 

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 DC worksheet,   select the range G5:G10, display the high point sparkline marker, and change   the color of the high point markers to Dark Blue.

 

7

#On the DC worksheet,   select the range G5:G10, apply Same for All Sparklines for both the vertical   axis minimum and maximum values.

 

8

#On the DC worksheet,   select the ranges A4:A10 and C4:F10 and create a clustered bar chart. Apply   the Color 16 chart color. Apply the gradient fill to the plot area. Do not   change the default gradient options.
Note, depending on the version of Office used, the chart color may be named   Monochromatic Palette 12.

 

9

Position the   top-left corner of the chart in cell A13. Change the chart height to 6 inches   and the chart width to 7 inches.

 

10

#Change the chart   title to Annual Visitors. Apply Blue,   Accent 5, Darker 25% font color to the chart title and category axis labels.   Change the value axis display units to Millions.

 

11

#Apply data labels to   the outside end of the 2015 data series. Apply Number format with 1 decimal place to the data labels.

 

12

#Click the Places   sheet tab, convert the data to a table, and apply Table Style Medium 6.

 

13

#On the Places   worksheet, sort the data by City in alphabetical order and then within City,   sort by Sightseeing Locations in alphabetical order.

 

14

#On the Places   worksheet, add a total row to display the average of the Time Needed column.   Apply Number format with zero decimal places to the total.

 

15

#On the Places   worksheet, select the values in the Time Needed column and apply conditional   formatting to highlight cells containing values greater than 60 with Green Fill with Dark Green Text.

 

16

#On the Places   worksheet, apply a filter to display only fees that are less than or equal   to $10.

 

17

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

 

18

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

 

19

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

 

20

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

 

21

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

 

22

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

 

23

#On the Cities   worksheet, select Landscape orientation, set a 1-inch top margin, and center   the worksheet data horizontally on the page.

 

24

#Ensure that the   worksheets are correctly named and placed in the following order in the   workbook: DC, Places, Cities. Save the workbook. Close the workbook and then   exit Excel. Submit the workbook as directed.