Do you want to put a Pareto chart in Excel? I will show you how.
How to Create A Pareto Chart
If you want to put a chart on which you have columns that represent income and a line that represents the cumulative percent income, follow me.
From Excel 2016, you can simply go to the Insert tab and choose the Pareto chart from the Histogram command (Fig. 1)
Fig. 1 Inserting a Pareto chart
And you have the Pareto chart ready. However, the charts from Excel 2016 have some drawbacks. The line isn’t an actual series of the chart. It means that we cannot add data labels there. That’s why I don’t prefer using this type of chart. What I prefer is the earlier version that gives me more freedom concerning value changing, although it requires more calculations. We have to calculate the cumulative percent income on our own (Fig. 2)
=SUM(B$2:$B2)/SUM($B$2:$B$9)
Fig. 2 Cumulative percent calculations
Now, we can insert a simple column chart. Since the income is enormously bigger that the percentage, we aren’t able to see the income columns at all, but we want to select them (Fig. 3). How can we do it?
Fig. 3 No % Income columns
We can select our chart and go to the Format tab. On the left we can see all elements of the chart. We are interested in the Series “% Income” option (Fig. 4)
Fig. 4 Series “% Income”
We can see now that the series is selected on the chart. Let’s press Ctrl + 1 and go to Format Data Series, Series Options, Plot Series On, and select the Secondary Axis option. Now we have % income on a different axis (Fig. 5)
Fig. 5 Secondary Axis options
Now, we can change the chart type into a chart that will represent income better. We have to click on the chart element once, then go to the Insert tab and the Line chart with Markers option (Fig. 6)
Fig. 6 Line Chart with Markers
Since we are creating a Pareto chart ourselves, the values aren’t sorted and we have to do it manually. We just select one cell in our data, then go to the Data tab and choose the from Z to A option (Fig. 7)
Fig. 7 Data sorting
Now the data on the chart is nicely sorted and it looks more like a Pareto chart. We still have to add modifications to make it a real Pareto chart. First of all, let’s select a column 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 secondary axis. It goes up to 120%, however our maximum is 100% (Fig. 8)
Fig. 8 Gap Width modification
We have to select the axis, press Ctrl + 1, go to Axis Options and write 1 in the Maximum bar, which equals 100%. We also change our Major Units to 0.2 which means that there will be less percent numbers showed on the axis (Fig. 9)
Fig. 9 Less percent numbers
We still don’t need the 0 after dot in our percentages, so let’s go to Numbers and change the Format Code from 0.0% to 0% and click Add (Fig. 10)
Fig. 10 Format Code change
Now, let’s go to the Income Format Axis. Press the axis and Ctrl + 1. Let’s change the Major from 100000 to 200000. This way the chart will show less numbers (Fig. 11)
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)
Fig. 12 Data Labels
We still need to set a proper title. Let’s just write Pareto. After implementing the most important changes, the Pareto chart looks like that (Fig. 13)
Sometimes, we need to insert a population pyramid in Excel or a chart comparing two values.
Population Pyramid Chart
One part of the chart is on the left, while the other is on the right. In order to create something like population pyramid we have to insert a proper chart. I’m using data from Poland as I’m an Excel lover from Poland. We have to have one part of negative values, which will go to the left side of the chart. If you want to do it, you have to write ‑1 in one cell, then copy it and select all values where we want to change the sign, then use the Paste Special option (or use the Alt + Ctrl + V shortcut). In the Paste Special window, select the Values radio button and the Multiply radio button in the Operation section. It means that we will be multiplying ‑1 by the selected values (Fig. 1)
Fig. 1 Paste Special window
Now, we have negatives and positives, which is the left and the right side of our chart. We can insert the chart. Let’s select one cell and go to the Insert tab and find the Clustered Bar option (Fig. 2)
Fig. 2 Clustered Bar option
The chart that appeared isn’t good enough for us. The first modification we want to implement is changing the title. Let’s write ‘Population Pyramid’ (Fig. 3)
Fig. 3 Chart Title change
I also want to modify bars. Let’s click on them, then press Ctrl +1. Format Data Series window will appear where we have to select 100% in the Series Overlap section and set the Gap Width to 10% (Fig. 4)
Fig. 4 Series Overlap and Gap Width options
Now, the bars are larger. The next thing is the Vertical Axis. Click Ctrl + 1 shortcut and go to the Labels tab. Set the Label Position to Low which, in our case, is the left side. In the Specify interval unit let’s leave 1 (Fig. 5)
Fig. 5 Label Position and Specify Interval Unit options
Now, the chart looks much better, but I don’t like the vertical lines here. Let’s click it and press the Delete key (Fig. 6)
Fig. 6 Vertical lines not needed
The next thing we want to change is the horizontal axis where we have person values. It means that we don’t need any negative values. We have to select it, press Ctrl + 1, then in the Format Axis window, we need to go to the Numbers bar. In the Format Code bar, we need to repeat the percent code for the negative values. Let’s add a semicolon, then write 0.00% without any minus sign. Then, we click on the Add button (Fig. 7)
Fig. 7 Format Code bar
Now, we have positive values on the left and on the right side of the chart. What I’m still missing is Data Labels. We have to press the plus sign and select the Data Labels checkbox (Fig. 8)
Fig. 8 Adding Data Labels
We can see that the label font is too big. Let’s make it smaller by selecting the labels and changing the font size on the ribbon (Fig. 9)
Fig. 9 Changing the font size
There is still one more thing we need to change. There are minus signs on the left side of the chart. So, let’s press the Ctrl + 1 shortcut and go to the Format Data Labels window. We have to open the Number tab and write the percentage once again. We separate the percentages with a semicolon, so that the percentage from the left side corresponds to the left part of the chart, and the right corresponds to the right part. Then, we press Add (Fig. 10)
Fig. 10 Two percentages
In the end, I want to change the position of the legend. Let’s select the legend and press Ctrl + 1. We can see many options concerning the position of the legend (Fig. 11)
Fig. 11 Legend Positions
However, I want to change the position manually. Let’s drag it to the right and change its size. We can also change the size of the bars. There are many more modifications that you can implement. My Population Pyramid is finished and looks as follows (Fig. 12)
