Excel Histogram Chart
(the little chart that DOES)
Histogram charts are among the simplest variety of charts to create while packing a knowledge punch like no other chart.
This type of chart is great for creating collected summaries of data.
When you don’t need to see each data point, but rather a grouped presentation of the data, the Histogram chart is a fantastic way of aggregating data into ranges without the need to create any additional calculations in your data.
Imagine taking a dataset like the following…
…and charting the yearly salaries for all employees. You are likely to end up with something that looks like the following.
Although this chart does display the information and given enough time you could decipher relevant information about the salaries, the time it would take is likely more than you care to invest.
Close, but Not Quite There
The result is technically a Histogram chart, but it doesn’t really tell the story in the way we need.
We will improve the chart with the following modifications:
Setting the Bin Width
The groupings of data points are known as “bins”. We can define the bin logic in two ways: either by the width of the bins (i.e. size) or the number of bins.
NOTE: “Automatic” is another option, but it rarely proves useful.
To decide on the strategy and set the parameters, double-click the values along the X-Axis (or single-click the values and press CTRL-1) to reveal the Format Axis panel.
If we set the Bin Width to 40,000, the result is 6 bins ranging from approximately 21K to 261K in increments of 40K.
Not great but getting better.
Creating an Overflow Bin
The purpose of the Overflow bin is to capture all values over a certain range.
For our chart, we will define the Overflow bin to hold all values over 200K.
Creating an Underflow Bin
The purpose of the Underflow bin is to capture all values below a certain range.
For our chart, we will define the Underflow bin to hold all values under 30K.
Bonus Final Touch
With number ranges as large as we’re working with, we spend a large amount of real estate currency on the excessive zeroes in the X-Axis labels.
To make more efficient use of the X-Axis label space, double-click the X-Axis to open the Format Axis panel.
Open the Number category. In the Format Code field, enter the following code and press the Add button.
The result is as follows.
The way this works is that if you place a single comma after the zero instead of a period (decimal point), the number will be rounded to the nearest thousand. If you added 2 commas, the number would be rounded to the nearest million; 3 commas would round to the nearest billion, etc.
The “K” is simply a tacked-on suffix used to help the reading understand the true value of the axis.
Interpreting Excel Bin Labels
Excel uses standard statistical notation on labels for the bin ranges on the horizontal axis.
Numbers that are preceded or followed by parenthesis are excluded from the bin, while those adjacent to a bracket are included.
For example, the label “[10, 20]” denotes that both 10 and 20 are within the bin range. The label “(20, 40]” shows that 20 is not within the bin range but 40 is.
In English, if you have the value “20”, the value would be included in the first bin “[10, 20]”, but would not be included in the second bin “(20, 30]”. The second bin effectively starts at a fraction above “20”.
Feel free to Download the Workbook HERE.
Excel ESSENTIALS for the REAL World (The Complete Excel Course)
From Excel Beginner to Professional
Learn Excel from Scratch
OR Improve Your Excel Skills to Become More Confident
Check out our best-selling course