Microsoft Office Tutorials and References
In Depth Information
Thus, by viewing the cumulative percentages, you can determine, for example, what percent-
ages of the total defects are due to the three worst parts. In this way, Kemp can isolate the
problem filler heads and report how much they contribute to the total defects.
Kemp has a worksheet listing the number of defects per filler head from a recent shift. The
data is already sorted going from the filler head with the most defects to the one with the
fewest. The cumulative percent values have also been calculated already. Kemp wants you
to create a Pareto chart based on this data. To complete this task:
1. Open the Powder1 workbook located in the Tutorial.04\Cases folder included with
your Data Files, and then save the file as Powder2 to the same folder.
2. Enter your name and the current date in the Documentation sheet, and then switch to
the Quality Control Data worksheet.
3. Select the range A5:C29, and then start the Chart Wizard.
4. Use the Chart Wizard to create a custom chart, selecting the Line – Column on
2 Axes option in the Custom Types list box. Specify “Filler Head Under Fills” as the
chart title. Specify “Filler Head” as the x-axis title, “Count of Under Fills” as the y-axis
title, and “Cumulative Percentage” as the second y-axis title. Do not include a leg-
end. Place the chart on a chart sheet named “Pareto Chart.”
5. Change the alignment of the x-axis labels to an angle of 90 degrees.
6. Change the alignment of the second y-axis title to –90 degrees.
7. Change the scale of the second y-axis so that the values range from 0 to 1.0.
8. Select the data series that displays the number of defects for each filler head, and add
data labels that display the number of defects above each column. Do not display
labels above the lines that represent the cumulative percentages. ( Hint : Use the Data
Labels tab in the Format Data Series dialog box.)
9. From the Format Data Series dialog box for the chart’s columns, use the Options tab
to reduce the gap separating the columns to 0 pixels.
10. Change the fill color of the chart columns and the plot area to white.
11. Examine the Pareto chart, and determine approximately what percentage of the total
number of defects can be attributed to the three worst filler heads.
12. Add a header to the Pareto Chart sheet that displays the name of the worksheet in
the center and your name and the date on the right of the page, and then print the
13. Save your changes to the workbook, and then close it.
Case Problem 3
Data File needed for this Case Problem: Oil1.xls
Hardin Financial Kurt Lee is a financial analyst for Hardin Financial. As part of his job,
he likes to store stock market activity in Excel workbooks. One of his workbooks contains
the recent stock market activity of Mitchell Oil. He would like your help in creating a
chart displaying the stock values. The chart should display the stock’s opening, high, low,
and closing values for each day of the past few weeks, in addition to the number of shares
traded. The volume of shares traded should be expressed in terms of millions of shares.
Go beyond what you’ve
learned in the tutorial
by exploring how to use
Excel to chart stock
To complete this task:
1. Open the Oil1 workbook located in the Tutorial.04\Cases folder included with your
Data Files, and then save the workbook as Oil2 in the same folder. Enter your name
and the date in the Documentation sheet and then switch to the Stock Values sheet.