Plot with 2 different scales in Excel

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 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 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 plot with 2 different scales

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

excel plot with 2 different scales

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

excel plot with 2 different scales

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

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 plot with 2 different scales

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

excel plot with 2 different scales

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

excel plot with 2 different scales

 

You can find similar Excel Questions and Answer hereunder

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

2) 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?

3) How to use data table function in Excel. It helps you go through various scenario when seeking a goal

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

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

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

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

8) Tables in Excel VBA. How you can create table in VBA and work with them with macros

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

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

 

Here the previous and next chapter