How to Create A Pareto Chart 

by | May 3, 2023 | Excel

Do you want to put a Pare­to chart in Excel? I will show you how.

How to Cre­ate A Pare­to Chart 

If you want to put a chart on which you have columns that rep­re­sent income and a line that rep­re­sents the cumu­la­tive per­cent income, fol­low me. 

From Excel 2016, you can sim­ply go to the Insert tab and choose the Pare­to chart from the His­togram com­mand (Fig. 1)

Inserting a Pareto chart
Fig. 1 Insert­ing a Pare­to chart

And you have the Pare­to chart ready. How­ev­er, the charts from Excel 2016 have some draw­backs. The line isn’t an actu­al series of the chart. It means that we can­not add data labels there. That’s why I don’t pre­fer using this type of chart. What I pre­fer is the ear­li­er ver­sion that gives me more free­dom con­cern­ing val­ue chang­ing, although it requires more cal­cu­la­tions. We have to cal­cu­late the cumu­la­tive per­cent income on our own (Fig. 2)

=SUM(B$2:$B2)/SUM($B$2:$B$9)

Cumulative percent calculations
Fig. 2 Cumu­la­tive per­cent calculations

Now, we can insert a sim­ple col­umn chart. Since the income is enor­mous­ly big­ger that the per­cent­age, we aren’t able to see the income columns at all, but we want to select them (Fig. 3). How can we do it?

No % Income columns
Fig. 3 No % Income columns

We can select our chart and go to the For­mat tab. On the left we can see all ele­ments of the chart. We are inter­est­ed in the Series “% Income” option (Fig. 4)

Series "% Income"
Fig. 4 Series “% Income”

We can see now that the series is select­ed on the chart. Let’s press Ctrl + 1 and go to For­mat Data Series, Series Options, Plot Series On, and select the Sec­ondary Axis option. Now we have % income on a dif­fer­ent axis (Fig. 5)

Secondary Axis options
Fig. 5 Sec­ondary Axis options

Now, we can change the chart type into a chart that will rep­re­sent income bet­ter. We have to click on the chart ele­ment once, then go to the Insert tab and the Line chart with Mark­ers option (Fig. 6)

Line Chart with Markers
Fig. 6 Line Chart with Markers

Since we are cre­at­ing a Pare­to chart our­selves, the val­ues aren’t sort­ed and we have to do it man­u­al­ly. We just select one cell in our data, then go to the Data tab and choose the from Z to A option (Fig. 7)

Data sorting
Fig. 7 Data sorting

Now the data on the chart is nice­ly sort­ed and it looks more like a Pare­to chart. We still have to add mod­i­fi­ca­tions to make it a real Pare­to chart. First of all, let’s select a col­umn and press Ctrl + 1. On the right, we have the Gap Width option. Let’s slide it to 0%. Now, let’s go to the sec­ondary axis. It goes up to 120%, how­ev­er our max­i­mum is 100% (Fig. 8)

Gap Width modification
Fig. 8 Gap Width modification

We have to select the axis, press Ctrl + 1, go to Axis Options and write 1 in the Max­i­mum bar, which equals 100%. We also change our Major Units to 0.2 which means that there will be less per­cent num­bers showed on the axis (Fig. 9)

Less percent numbers
Fig. 9 Less per­cent numbers 

We still don’t need the 0 after dot in our per­cent­ages, so let’s go to Num­bers and change the For­mat Code from 0.0% to 0% and click Add (Fig. 10)

Format Code change
Fig. 10 For­mat Code change

Now, let’s go to the Income For­mat Axis. Press the axis and Ctrl + 1. Let’s change the Major from 100000 to 200000. This way the chart will show less num­bers (Fig. 11)

Less numbers
Fig. 11 Less numbers

What I care about the most right now are data labels for the line. Let’s click once on the line, click on the plus sign and we have Data Labels option. Let’s place them above our line (Fig. 12)

Data Labels
Fig. 12 Data Labels

We still need to set a prop­er title. Let’s just write Pare­to. After imple­ment­ing the most impor­tant changes, the Pare­to chart looks like that (Fig. 13)

Finished Pareto chart
Fig. 13 Fin­ished Pare­to chart

https://www.youtube.com/watch?v=wL2yeXCeL2Y

Chart Secondary Axis

by | May 1, 2023 | Excel

Today, we are going to talk about putting two dif­fer­ent series into one chart.

Sec­ondary Axis on Excel Chart Tem­per­a­ture and rainfall

If you have two series that dif­fer from each oth­er, e.g. they have dif­fer­ent units or one of them is much big­ger than the oth­er, then you should use the Sec­ondary Axis on you chart. How can you do it? From Excel 2013 the task is sim­ple as Microsoft insert­ed the Com­bo chart. Our exam­ple has sim­ple data, so we just have to click on one cell, then go to the Insert tab, where we can find a Com­bo chart with the Sec­ondary Axis (Fig. 1)

Combo chart with secondary axis
Fig. 1 Com­bo chart with sec­ondary axis

I can see that not every­thing is as I want­ed, that’s why I’m going to make some changes. I just select the chart and go to the Chart Design tab, where I can find the Change Chart Type option. In the win­dow that appeared, we change the type of each series. The rain­fall is on the sec­ondary axis, which is good, how­ev­er, I pre­fer the rain­fall to be pre­sent­ed as a col­umn chart, and I will put the tem­per­a­ture into a line chart with mark­ers (Fig. 2)

Column chart and line chart with markers
Fig. 2 Col­umn chart and line chart with markers

After press­ing OK, we can see a fin­ished chart with two val­ues (Fig. 3)

A finished chart with two values
Fig. 3 A fin­ished chart with two values

But, how can we do it in Excel from before 2013? Let’s insert a sim­ple col­umn chart by going to the Insert tab, then choos­ing the prop­er col­umn chart (Fig. 4)

Column chart
Fig. 4 Col­umn chart

As our val­ues dif­fer much in size, where the rain­fall is sig­nif­i­cant­ly big­ger than the tem­per­a­ture, I would like to have the rain­fall series on a sec­ondary axis. I have to select the hole series by click­ing once on the series ele­ment, then press Ctrl + 1, find the series option, go to Plot Series On, and choose Sec­ondary Axis (Fig. 5)

Series options
Fig. 5 Series options

And, just like that we have a dif­fer­ent axis for rain­fall, and a dif­fer­ent axis for tem­per­a­ture. There is still one thing we need to change, which is the type of tem­per­a­ture series, because now one series is behind the oth­er and we don’t know how high some columns are. Let’s click one time on the series and go to the Insert tab, where we can choose a new chart type. Let’s choose the Line with Mark­ers option (Fig. 6)

Line chart with markers
Fig. 6 Line chart with markers

Just like that, I have a chart with a sec­ondary axis and dif­fer­ent chart types. Let’s change the name of the chart into Tem­per­a­ture and rain. The chart is fin­ished (Fig. 7)

Finished chart
Fig. 7 Fin­ished chart

https://www.youtube.com/watch?v=L7K2_jejIHg