1
Start Excel. Download and open the file namedexploring_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% fillcolor.
3
On the DC worksheet, merge and center the title in the rangeA1: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 LineSparklines in the range G5:G10.
6
On the DC worksheet, select the range G5:G10, display the highpoint sparkline marker, and change the color of the high pointmarkers to Dark Blue.
7
On the DC worksheet, select the range G5:G10, apply Same for AllSparklines for both the vertical axis minimum and maximumvalues.
8
On the DC worksheet, select the ranges A4:A10 and C4:F10 andcreate a clustered bar chart. Apply the Color 16 chart color. Applythe gradient fill to the plot area. Do not change the defaultgradient options.
Note, depending on the version of Office used, the chart colormay be named Monochromatic Palette 12.
9
Position the top-left corner of the chart in cell A13. Changethe 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, andapply Table Style Medium 6.
13
On the Places worksheet, sort the data by City in alphabeticalorder and then within City, sort by Sightseeing Locations inalphabetical order.
14
On the Places worksheet, add a total row to display the averageof the Time Needed column. Apply Number format with zero decimalplaces to the total.
15
On the Places worksheet, select the values in the Time Neededcolumn and apply conditional formatting to highlight cellscontaining values greater than 60 with Green Fill with Dark GreenText.
16
On the Places worksheet, apply a filter to display only feesthat are less than or equal to $10.
17
On the Cities worksheet, click cell F4 and enter a formula thatwill subtract the Departure Date (B1) from the Return Date (B2) andthen 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 locatedin the Rental Car? Column for a city. If the city contains No,display the value in cell F2. If the city contains Yes, display thevalue in the Rental Car Total (F4). Copy the function from cell E13and use the Paste Formulas option to copy the function to the rangeE14:E18 without removing the border in cell E18.
19
On the Cities worksheet, click cell F13. The lodging is based ona multiplier by City Type. Some cities are more expensive thanothers. Insert a VLOOKUP function that looks up the City Type(B13), compares it to the City/COL range (A7:B10), and returns theCOL percentage. Then multiply the result of the lookup function bythe Total Base Lodging (B5) to get the estimated lodging for thefirst city. Copy the function from cell F13 and use the PasteFormulas option to copy the function to the range F14:F18 withoutremoving the border in cell F18.
20
On the Cities worksheet, click cell H13 and enter the functionthat calculates the total costs for the first city. Copy thefunction in cell H13 and use the Paste Formulas option to copy thefunction to the range H14:H18 without removing the border in cellH18.
21
On the Cities worksheet, select the range E14:H18 and applyComma Style with zero decimal places. Select the range E13:H13 andapply Accounting Number format with zero decimal places.
22
On the Cities worksheet, in cell I2, enter a function that willcalculate the average total cost per city. In cell I3, enter afunction that will identify the lowest total cost. In cell I4 entera function that will return the highest total cost.
23
On the Cities worksheet, select Landscape orientation, set a1-inch top margin, and center the worksheet data horizontally onthe page.
24
Ensure that the worksheets are correctly named and placed in thefollowing order in the workbook: DC, Places, Cities. Save theworkbook. Close the workbook and then exit Excel. Submit theworkbook as directed.
