Microsoft Office Tutorials and References
In Depth Information
Practice the skills
you learned in
the tutorial using
the same case
Data File needed for the Review Assignments: Crockett.xlsx
Ajita has to create a report on the investment portfolio for Brian and Tammy Crockett.
She wants to add charts that display where the couple’s money is currently being
invested and how their portfolio has performed in recent years. She’s already entered
the data. You will complete the report by adding the charts and a decorative cover sheet.
Complete the following:
1. Open the Crockett workbook located in the Excel4\Review folder included
with your Data Files, and then save the workbook as Crockett Portfolio . In the
Documentation sheet, enter your name in cell B3 and the date in cell B4.
2. In the Composition worksheet, select the range A3:B8, and then insert the ﬁ rst
2-D pie chart. Move and resize the embedded pie chart to cover the range D1:G9 in
the Portfolio Report worksheet.
3. Move the legend to the left side of the chart area. Change the chart title to Investment
Categories and set its font size to 11 points. Change the ﬁ ll color of the Cash slice
to yellow. Add data labels that show the percentage of each pie slice to two decimal
places outside of the pie chart, and then set the font size of the labels to 8 points.
4. Change the pie chart to a 3-D pie chart and set the 3-D rotation of the x-axis to 230°
and the y-axis to 40°.
5. In the Sectors worksheet, select the range A3:D15, and then insert a 2-D clustered
column chart. Move and resize the embedded chart to cover the range A12:D25 in
the Portfolio Report worksheet.
6. Change the font size of the axis labels and legend to 8 points. Insert the chart title
Sector Percentages above the plot and set its font size to 11 points. Change the
format of the percentages in the vertical axis to display no decimal places. Overlay
the legend at the top of the chart, and then change its ﬁ ll color to white and insert a
solid border around the legend.
7. Change the chart type of the S&P 500 series to a line chart, remove the line
connecting the markers in the chart, and then change the marker type to a solid horizontal
line of size 10.
8. Change the ﬁ ll color of the columns for the Portfolio data series to the theme color
Purple, Accent 4, Lighter 60%. Set the gap width of the columns in the Portfolio data
series to 30%.
9. In the Portfolio History worksheet, select the range A3:B127, and then insert a line
chart. Move and resize the chart to cover the range D29:G37 in the Portfolio Report
worksheet. ( Hint : Scroll down the Portfolio Report worksheet to locate the
10. Remove the chart legend. Set the font size of the chart title to 11 points, and then set
the font size of the axis labels to 8 points.
11. Set the major tick mark interval for the category axis at two years and the minor tick
mark interval at one year. Use a custom format that displays the category axis date
values as four-digit year values. Insert vertical gridlines for the minor tick marks.
12. Change the scale of the value axis to range from $200,000 to $350,000. Add major
tick marks every $50,000. Add minor tick marks every $25,000. Insert horizontal
gridlines for every minor tick mark.
13. In cell B32, insert a line sparkline for the data range C4:C127 of the Portfolio History
worksheet to describe the growth of the portfolio over consecutive three-month
periods. Mark the high point and the low point with green and red markers, respectively.
14. In cell B37, insert a line sparkline for the data range D4:D127 of the Portfolio
worksheet to show the growth of the portfolio over consecutive one-year periods. Add
high/low data markers and an axis to this sparkline to match the ﬁ rst sparkline.