You work for an independent automobile rating company that provides statistics to consumers. A group of testers recently evaluated four 2012 mid-sized passenger car models and provided the data to you in a worksheet. You need to transform the data into meaningful charts that you will include on your company's Web site. Refer to figure 3.54 as you complete the exercise (see attachment).
a. Open e03m1autos and save it as 03m1autos_RobinSquires.
b. Select the rand A3:F8, and then create a stacked column chart. You realize that the data in this chart is not cumulative and that you included the overall ratings by category. You also notice that the chart is located to the side of the data. Make the following changes:
• Change the chart to a clustered column chart.
• Remove the overall category from the data source.
• Position the chart starting in cell A11, and then increase the chart width through cell G27.
c. Make the following design changes:
• Select Layout 1 as the chart layout size.
• Edit the chart title to be 2012 Mid-Sized Car Ratings by Category.
• Apply the Red, Accent 2, Darker 25% font color to the chart title.
• Apply the Style 12 chart style.
d. Create a screen print of the file at this point. Save the image to a MS Word document with the file name, Lesson 6_Car_RobinSquires.docx
• Select the range A4:A7, press and hold Ctrl, and then select the range G4:G7-the models and overall ratings.
• Create a clustered bar chart.
• Move the chart to its own sheet named Overall Ratings.
e. Make the following changes to the bar chart (see figure 3.54/attached):
• Remove the legend.
• Add the title 2012 Mid-Sized Car Overall Ratings above the chart.
• Apply the Style 2 chart style, if necessary.
• Add data labels in the Outside End position, and the bold the data labels. Apply the Fog gradient fill color to the data labels.
• Select the data series, and then apply a picture fill, searching for clip art of a blue automobile. Select the option to stack the images instead of stretching one image per data marker.
f. Insert a footer with Robin Squires and ROB2209353 on the left side, the sheet name code in the center, and the file name code on the right side on all worksheets.
g. Adjust the page setup option to fit the worksheet to one page with 0.5" left and right margins.
h. Save and close the workbook *Note: When completed there should be two attachments: 6_Car_RobinSquires.docx and e03m1autos_RobinSquires.xlsx
A solution-only Excel spreadsheet with Word document containing the required plots.