Microsoft Office Tutorials and References
In Depth Information
11. Go to the Sightings History worksheet, and then insert orange data bars in the
range B4:D13. Insert blue data bars in the range E4:E13. Note that the meaning of
the lengths of the two sets of data bars is different because they are applied to
12. In cell B2, insert a sparkline for the data in the range B4:B13 (do not include the
total in cell B14). Repeat to insert a sparkline in each cell in the range C2:E2.
13. Save and close your workbook, and then submit the ﬁ nished workbook to your
instructor, either in printed or electronic form, as requested.
Case Problem 3
to use Excel
to chart stock
Data File needed for this Case Problem: Mitchell.xlsx
Hardin Financial Kurt Lee is a ﬁ nancial analyst for Hardin Financial, a consulting ﬁ rm
in Owatonna, Minnesota. As part of his job, he records stock market activity in Excel
workbooks. One workbook contains the recent stock market activity of Mitchell Oil. He
wants your help in creating a chart displaying the stock values. The chart should display
the stock’s opening, high, low, and closing values, and the number of shares traded for
each day of the past few weeks. The volume of shares traded should be expressed in
terms of millions of shares. Complete the following:
1. Open the Mitchell workbook located in the Excel4\Case3 folder included with your
Data Files, and then save the workbook as Mitchell Oil . In the Documentation sheet,
enter your name in cell B3 and the date in cell B4.
2. In the Stock Values worksheet, select the range A3:F33, and then insert a
VolumeOpen-High-Low-Close stock chart. ( Hint : Click the Other Charts button in the Charts
group on the Insert tab to locate the stock charts.) Move the embedded chart to the
chart sheet named Stock History .
3. Insert the chart title Mitchell Oil above the chart area, and then set the font size of
the chart title to 18 points. Remove the chart legend.
4. Add the title Date to the primary horizontal axis, and then set its font size to
14 points. Add the title Shares Traded to the primary vertical axis, set its font size to
14 points, and then rotate the title 270°.
5. Add the title Stock Value to the secondary vertical axis, set its font size to 14 points,
and rotate the title 90°. ( Hint : Open the Format Axis Title dialog box for the
secondary vertical axis and use the Text direction button found in the Alignment category.)
6. Set the font size of all axis values to 12 points.
7. Display the horizontal and vertical gridlines using a dashed line style. Set the interval
between major tick marks on the primary horizontal axis to 7 days.
8. For the primary vertical axis, display the values in units of one million, change the
number format to two decimal place accuracy, and then set the maximum value of
the axis scale to 2,000,000.
9. For the secondary vertical axis, set the minimum value of the scale to 10.
10. Decrease the gap width between the columns in the plot to 30% and change the ﬁ ll
color to light blue.
11. Change the ﬁ ll color of the plot area to light yellow.
12. In a stock market chart, the daily chart values will show either an increase or a
decrease from the previous day. Increases are shown with an up bar displayed in
white and decreases are shown with a down bar displayed in black. Select the data
series for the up bars and change their ﬁ ll colors to light green. Select the data series
for the down bars and change their ﬁ ll colors to red.