How to create a comparative histogram chart in Excel

Excel 2016
Excel 2016 offers several new pre-defined charts, including the histogram chart. This pre-defined chart makes possible to create different types of histogram charts with little effort.

But, if you want to compare two distributions or to create a smoothed distribution curve, standard bar or line charts become more practical:

Comparative Histogram chart in Excel 2016

In this example, the histogram chart shows the distribution of the daily revenue for two months.

To create a histogram chart like the one above, do the following:

   1.   Add the new data for the chart:

Take the data of the daily revenue for two months:

The data for histogram chart in Excel 2016

Create a new data set for the histograms:

New data for histogram chart in Excel 2016

Where:

  • Column Bins contains the array with the upper bounds for each interval ("bin") of grouped values. Thus, all values greater than the upper bound of the previous interval and less or equal to the upper bound of the current interval fall into the current bin. All values greater than the last value of upper bounds array fall into the last bin.

    Note: You can define bins with different widths.

  • Column Labels contains the array of labels for the horizontal axis.
  • Column Aug created using the formula:

    {= FREQUENCY (<data>, <bins>)}

    The FREQUENCY function returns a frequency distribution table; a table containing the number of elements from the <data> array included in each interval of the <bins> array.

    This function returns multiple values and should be entered as an array formula using Ctrl+Enter.

    Note: If you don’t familiar with array functions, you can use the following function to do the same calculations as the FREQUENCY function does:

    = COUNTIF (<data>, <criteria>)

    See more about function COUNTIF.

    So, for this example:

      D6: = COUNTIF (B2:AF2, "<25")

      D7: = COUNTIF (B2:AF2, ">25") – COUNTIF (B2:AF2, ">50")

      D8: = COUNTIF (B2:AF2, ">50") – COUNTIF (B2:AF2, ">75")

      etc.

   2.   Create a histogram chart:

   2.1.   Select the prepared data (in this example, C5:E16).

   2.2.   On the Insert tab, in the Charts group, click on the Insert Line or Area Chart button:

The Insert Line or Area Chart button in Excel 2016

Select Line with Markers:

The Line with Markers in Excel 2016

Excel creates a nice distribution chart for the data:

The new chart in Excel 2016

Make any other adjustment you desire.

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.