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)
Today, we are going to talk about putting two different series into one chart.
Secondary Axis on Excel Chart Temperature and rainfall
If you have two series that differ from each other, e.g. they have different units or one of them is much bigger than the other, then you should use the Secondary Axis on you chart. How can you do it? From Excel 2013 the task is simple as Microsoft inserted the Combo chart. Our example has simple data, so we just have to click on one cell, then go to the Insert tab, where we can find a Combo chart with the Secondary Axis (Fig. 1)
Fig. 1 Combo chart with secondary axis
I can see that not everything is as I wanted, 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 window that appeared, we change the type of each series. The rainfall is on the secondary axis, which is good, however, I prefer the rainfall to be presented as a column chart, and I will put the temperature into a line chart with markers (Fig. 2)
Fig. 2 Column chart and line chart with markers
After pressing OK, we can see a finished chart with two values (Fig. 3)
Fig. 3 A finished chart with two values
But, how can we do it in Excel from before 2013? Let’s insert a simple column chart by going to the Insert tab, then choosing the proper column chart (Fig. 4)
Fig. 4 Column chart
As our values differ much in size, where the rainfall is significantly bigger than the temperature, I would like to have the rainfall series on a secondary axis. I have to select the hole series by clicking once on the series element, then press Ctrl + 1, find the series option, go to Plot Series On, and choose Secondary Axis (Fig. 5)
Fig. 5 Series options
And, just like that we have a different axis for rainfall, and a different axis for temperature. There is still one thing we need to change, which is the type of temperature series, because now one series is behind the other 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 Markers option (Fig. 6)
Fig. 6 Line chart with markers
Just like that, I have a chart with a secondary axis and different chart types. Let’s change the name of the chart into Temperature and rain. The chart is finished (Fig. 7)
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)
Sometimes, you need to create a running total in pivot tables. How to do it properly?
Running Total in Pivot Table
Let’s start with creating a pivot table. Select one cell from our data and go to the Insert tab and select the Pivot Table command. A Pivot table from table or range window will appear, where we have our range. We need to select the Existing worksheet radio button and write the location, which, in our case, will be cell F1. Let’s click the OK button (Fig. 1).
Fig. 1 Creating a pivot table
And there we have it. In our pivot table we need to drag the Date to the Rows labels.
Fig. 2 Dragging the date
Now, depending on the Excel version, we have an option of Group by dates. I’m going to group our date by months and years (Fig. 3).
Fig. 3 Grouping
We have our table. Now, I want to add Income so let’s press the Income checkbox (Fig. 4).
Fig. 4 Income checkbox
Now, I want to have the income and the running total for the income, so let’s drag the Income two times to the Values area. We can see that we have Sum of Income and Sum of Income 2, where we actually want to have our running total (Fig. 5).
Fig. 5 Income and Income 2
Let’s right click any value from Income 2, then select the Show Values as and then the Running Total In option (Fig. 6).
Fig. 6 Selecting the Running Total option
Now, we have to decide whether we want to base our running total on Date or Years Field. Let’s take Date (Fig. 7).
Fig. 7 Date Field
Now, we can see that in the Income 2 column we have bigger numbers. Let’s format them by pressing any cell in the column and choosing the Number Format option (Fig. 8).
Fig. 8 Number Format option
In the Format Cell window, let’s choose the Currency category, in Decimal places let’s write 0 and press the OK button. Let’s do the same in the Sum of Income column (Fig. 9).
Fig. 9 Format cell window
Now, we can see that we are working with money. In each month we have bigger and bigger numbers which means that we have the running total in this column. We can change the name of Sum of Income 2 into Running Total. Since we are using the Date Field as our reference point, we have the running total in 2020. In 2021 Excel counts from the start, which means that we have the running total only for 2021. The same is with 2022. If we change the Date field into the Years field in the Show Values as (Running Total) window, we will have the same value in the first year and in the Sum of Income column (Fig. 10).
Fig. 10 Years
However, in the next year, we have the the sum from January 2021 and January 2020. In February, we have the sum from February 2021 and 2020. In 2022, we have sums from three Februaries (Fig. 11).
Fig. 11 Sums from three Ferbuaries
Let’s drag Manager into the columns header (Fig. 12).
Fig. 12 Dragging Manager into headers
By doing so, we can show value from the Manager’s perspective, which means a horizontal view.
Fig. 13 Horizontal view
However, this type of data isn’t a proper one to show it this way, so let’s get back by pressing Ctrl + Z two times and see that we can also create a percent running total by going to the % Running Total In option and choose the Date Field (Fig. 14).
Fig. 14 Percentage Running Total
Now, we can see the values as percentage (Fig. 15).
If we want to show our data trend, we can use line charts, however they are usually large. If we want something simple and small, like a chart in a cell, we can use Sparklines.
Sparklines Chart in cell
All we have to do is to select one empty cell, then go to the Insert tab, then go to Sparklines and select the Line Sparkline option. What we will get is a Create Sparkline window, where we have to select a a proper range and check if location is good.(Fig. 1).
Fig. 1 Creating a sparkline
After pressing OK and dragging it down, we have trends for other countries (Fig. 2).
Fig. 2 Ready trends
If we want to add some markers, we have to open the Marker Color bar and choose proper options (Fig. 3).
Fig. 3 Adding markers
After adding markers, sparlines look as follows. If we want to make them bigger, we have two options. We can simply enlarge the cells or we can merge a few cells using the Merge & Center option from the Insert tab (Fig. 4).
