Show the Distribution of Data: Histogram and Pareto Charts

Show the Distribution of Data: Histogram and Pareto Charts

Create a Histogram or Pareto chart to show the distribution of data. 

When to use

We use a Histogram chart to show how numerical data is distributed such as exam scores across subjects. Pareto charts are used in similar situations to Histogram charts but they include a line to highlight the cumulative percentage of the total. 

Instructions

A histogram chart in Excel is a graphical representation of the distribution of numerical data. It displays the frequency of data points within specified ranges (bins) and helps to visualize the shape and spread of the data distribution. Each bin represents a range of data, and the height of each bar indicates the number of data points within that range.

Create a Histogram Chart

Let's create a Histogram chart to show the distribution of ages across employees at a company. 

• Select the data. 
• From the Insert tab, click the diagonal arrow in the corner of the Charts group. 
• Click All Charts. 
• Choose Histogram from the list. 

The Histogram chart will look at the data and group it into bins. We can easily see that the most common age range for employees is 32-46. 

This can be valuable information if we are looking to diversify the age demographic across the company. 

Format the Histogram Chart

We can format the histogram chart in the usual way by changing the chart style or color palette. 

Change the Chart Colors

If we select a palette from the Change Colors drop-down list, all of the bars in the chart will change to the same color. If we want each bar to be a different color we need to click twice on the bar to select it and then go to the Format tab, Shape Fill. 

Change the Bin Size

The bins are created automatically by Excel, however we can customize these. 

• Click on the horizontal axis and press CTRL+1. 
• Expand Axis Options. 
• Change Bin width to '10'. This means each bin will span a 10-year age range. 

Alternatively, we can specify the number of bins instead. 

• Select Number of bins. 
• Change the value to '7'. 

We now have 7 bins instead of 5. 

Format Values

Sometimes when we do this, we'll end up with some very long numbers which make the chart look messy and hard to read. We can change this by formatting the numbers. 

• Click on the horizontal axis. 
• Press CTRL+1. 
• Expand the Number group. 
• Change the category to Number. 
• Change the decimal places to 0. 

Add a Chart Title

• Double-click in the chart title text box. 
• Type 'Age Distribution of Employees'. 
• Press CTRL+B to make the heading bold. 

Create a Pareto Chart

A Pareto Chart is very similar to a Histogram except the bins are sorted from largest to smallest and a line is added to show the cumulative percentage of the total. 

• Click on the Histogram chart. 
• From the Chart Design tab, in the Type group, click Change Chart Type. 
• Click on Histogram and choose Pareto. 
• Click OK. 

Hints & tips

• To format one bar only, click twice on the bar to select it.