Histograms are used to illustrate the distribution of data by showing the frequency with which points of a certain value occur in that data set. This is usually achieved by counting the points that fall into a “bin”. For example, in the chart below, 1367 points summarised into 14 separate bins.

Basic Histogram

It is sometimes useful to plot the cumulative total of points as well, so that, e.g., you can see how many points there are in total. QuickChart provides a simple option to include the cumulative total on the secondary axis.

Histogram with Cumulative Total

To make the chart, simply select the column of data that you wish to analyse and press the “Histogram” button on the Excel ribbon toolbar (in the Distribution Charts group).

Select the Range to be Analysed in the Histogram

There are other variants of the Histogram available, which can be selected in the Settings menu. For example, depending on the context, you might prefer to see a continuous empirical distribution.

Empirical Distribution

This can be especially effective if you plot the theorised distribution on the same chart:

Empirical Continuous Distribution with Theorised Distribution Comparison

(Add the comparison distribution using the usual Select Data / Add Series functionality in Excel.)

Alternatively, you might know that the samples are drawn from a discrete distribution.

Empirical Discrete Distribution

To configure the type of Histogram, open the “Settings” dialog box in the Histogram dropdown menu on the ribbon toolbar (See Settings below).

Histograms created with a single click are not “live”, however, because the actual data being charted is a set of summary statistics and not the raw points themselves. This means that it won’t update with changing data. However, to achieve this, you can use the “=QC.HISTOGRAM(…)” formula.

“=QC.HISTOGRAM” Formula

This formula takes four parameters:

  • The source data (in a single column, with optional header);
  • The type of Histogram – “1” for the standard type, “2” for an empirical continuous distribution and “3” for an empirical discrete distribution;
  • The required number of bins – any integer or “0” to allow the formula to pick a suitable number automatically;
  • “TRUE” for bins to be denominated with integer spacing or “FALSE” to not apply this restriction.

Like most of the other QuickChart formulae, this is an array formula, so you must use “Ctrl-Shift-Enter” when you complete it, and select the full range required (although the most recent versions of Excel provide much better support for array formulae). More details on array formulae here.

Once you’ve entered the formula, just select a cell in the formula range and press the “Histogram” button on the ribbon (as you do to make the single-click version) or right-click and the “Make Histogram” option should be at the top of the context menu.

Full examples of the histograms and formulae are in the example workbook provided with the QuickChart download.

Settings

Histogram Settings Dialog Box
  • Chart Type – Choose whether the chart you want to create is a histogram, a continuous distribution or a discrete distribution as described above.
  • Auto – let QuickChart decide how many bins would suit the data you are charting
  • Integer Bins – Rather than have bins cover a range like 23.45 – 27.1 it might be preferable to configure that bin to cover the range 23 – 27.
  • Show Cumulative – Whether or not to show the cumulative series on the chart
  • Outline Bins – When drawing the histogram, this adds a thin outline to each bin which can make reading the data clearer.