plot with 2 different scales in Excel
Q100. In Excel, how can I plot 2 series on the same chart with different scales / measurement unit for Values (Ex: Pareto chart)?
One set of values are numbers and another set of values to be charted are percentages. For example, pareto charts for manufacturing defects of mobile phones in week 39. In Excel, how do I include them in a single chart?
To do it in Excel, here is the answer:
a) To make a Pareto chart, first arrange the data in descending order of defects. To arrange the data as required, select the Table with data and click on "Sort" under "Data".
In the dialog box that appears, set "Sort by" to "No. of Defects" and Sort On to "Values" and Order to "Largest to Smallest".
b) Add 2 columns "Cum Defects" (Cum - Cumulative) and "Cum Defects %" and enter formulas as shown - 1). Enter =SUM(C6:C11) in cell C12 and =C6 in cell D6. Then enter =C7 + D6 in cell D7 and copy formula all the way down.
Finally enter =D6/$C$12 in cell E6 and copy formula all the way down.
c) Click on any cell in Table. Click on "Column" under "Insert", click first chart under "2-D Column".
d) A chart would be automatically created as shown below.
e) A chart would be automatically created as shown below.
f) Select "Cum Defects" under "Legend Entries (Series)" and then click on "Remove". Click OK.
g) Click on chart, and under "Chart Tools", under "Layout", click on drop down list under "Current Selection". Select "Series Cum Defects %". The series would be selected in chart.
h) Click on "Change Chart Type" under "Chart Tools", under "Design".
i) In the "Change Chart Type" dialog box, click on "Line with Markers".