The Histogram chart helps us to analyse the distribution of the data, where we have many data points that can be grouped into categories, or “buckets”. We begin by looking at the standard Excel Histogram chart, which is available in Excel 2016 onwards.
For my illustration, let’s consider some ‘Age’ data:
We select our data, and go to the Insert tab, where we select the ‘Recommended Charts’ option:
Even though it is a logical chart for our data, the Histogram is not suggested:
To find the chart we want, we need to select the ‘All Charts’ tab:
Having located the Histogram chart, we click ‘OK’ to select it:
This gives us a basic chart, but there are some things we would like to do to improve it. We can select the chart and use the Format tab to format the chart elements:
We would like to add shadow and rounded corners to the chart area, so we select the Chart Area in the dropdown, and choose ‘Format Selection’ to access the ‘Format Chart Area’ pane:
In the Effects options, we can choose shading:
However, in the Border options, there is no ‘Rounded Corners’ option at the bottom of the pane:
There is another feature which is not available to us on this chart. When we create chart titles, we often use an excel formula to determine the title so that it is dynamic. However, when we try and access the Formula bar for this title, we are not permitted to type anything:
Whilst we are able to create a standard Excel Histogram chart, we are unable to access many of the features that we have come to expect when creating charts.
There is also a practical problem. The chart above is using the data in cells F38:G64. However, if we hide the data:
We have a polar bear in a snowstorm. We have covered this issue before and we solved it using the ‘Hidden and Empty Cells’ settings on the ‘Select Data’ dialog. We can access this by selecting the chart and right-clicking:
However, for the standard Histogram, the ‘Hidden and Empty Cells’ option is greyed out:
The final drawback is that we cannot make the standard Histogram chart dynamic. We can change the number of bins by selecting the horizontal axis and right-clicking to access the ‘Format Axis’ pane:
In the ‘Axis Options section on the ‘Axis Options’ tab, we can change the ‘Bin’ from Automatic to ‘Number of Bins’. Here, we have changed the ‘Number of Bins’ to six [6]:
However, we cannot put a cell reference in the ‘Number of Bins’, so it can only be changed manually.
We may take a different approach. Instead of using the standard Histogram chart provided by Excel, we can use a Clustered Column chart. We start with the data as before:
