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?

excel plot with 2 different scales

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".

excel excel plot with 2 different scales

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.

excel excel excel plot with 2 different scales

c) Click on any cell in Table. Click on "Column" under "Insert", click first chart under "2-D Column".

excel excel excel excel plot with 2 different scales

d) A chart would be automatically created as shown below.

excel excel excel excel excel plot with 2 different scales

e) A chart would be automatically created as shown below.

excel excel excel excel excel excel plot with 2 different scales

f) Select "Cum Defects" under "Legend Entries (Series)" and then click on "Remove". Click OK.

excel excel excel excel excel excel excel plot with 2 different scales

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.

excel excel excel excel excel excel excel excel plot with 2 different scales

h) Click on "Change Chart Type" under "Chart Tools", under "Design".

excel excel excel excel excel excel excel excel excel plot with 2 different scales

i) In the "Change Chart Type" dialog box, click on "Line with Markers".

excel excel excel excel excel excel excel excel excel excel plot with 2 different scales

 

You can find similar Excel Questions and Answer hereunder

1) Can I apply formatting to a number concatenated with a Text?

2) I have 2 sets of lists from 2 different reports - how can I select the cells with differences?

3) How can I enter information in multiple cells simultaneously?

4) I have data arranged in rows of information. Is there any way I can enter new data through a form instead of entering it directly in SpreadSheet?

5) How do i apply a formula to an entire column in Excel

6) I have several pictures that are misaligned - manually aligning them is very laborious. Is there an automatic alignment option?

7) How can I get row count of filtered data?

8) In my organization different users have different versions of Excel - how do I check compatibility issues with my WorkBook across versions?

9) How can I set up ListBox using VBA to allow users to select multiple values?

10) Is there a way to easily shade alternate rows in a data range for improving readability?

11) How can I find the number of working days between 2 dates given a holidays list?

12) How can I set the Source Data of charts using VBA?

13) I have angle values in radians. In Excel, how can I convert them to degrees?

14) I have 2 sets of lists from 2 different reports - how can I select the cells with differences?

15) How can I fill a series of data automatically?