How to create a histogram chart by category using frequencies in Excel

Excel 365
There are several ways to create a histogram chart in Excel. By the moment, Excel uses two different algorithms to calculate the data for a histogram chart:
  • For the histogram with By category option for bins, Excel calculates the data bin height as SUM of data points within the bin range,
  • For the histogram with any other option for bins, Excel calculates the data bin height as COUNT of data points within the bin range.

Thus, for web analysis or any other reporting, when it is necessary to consolidate several disparate reports with pre-calculated frequencies and bins, becomes useful a histogram chart by categories:

Histogram chart by categories using frequencies in Excel 2016

In this example, the histogram chart shows the distribution of ages of users on our Web site for some period of time.

To create a histogram chart using partially calculated frequencies in Excel 2016, do the following:

   1.   Prepare the data for the chart.

For example, the summary table with calculated data for different countries using additional filters looks like:

The data for histogram chart in Excel 2016

Note: To display the intervals on the horizontal axis in the correct order, the charts need the data to be sorted. See Using a custom sort order for more details.

   2.   Create a histogram chart:

   2.1.   Select the prepared data (in this example, B1:C859 or all columns B:C).

   2.2.   On the Insert tab, in the Charts group, click the Statistic button:

The Insert Statistic Chart button in Excel 2016

From the Statistic dropdown list, select Histogram:

The Histogram Chart in Excel 2016

The labels for the x-axis are invalid so far:

The Histogram chart in Excel 2016

   3.   Format the histogram chart:

   3.1.   Right-click on the horizontal axis and choose Format Axis... in the popup menu:

Format Axes in popup Excel 2016

   3.2.   In the Format Axis pane, on the Axis Options tab, under Bins, select the By Category option:

Format Axis for Histogram chart in Excel 2016

Excel changes the histogram chart:

The Histogram chart in Excel 2016

Make any other adjustment you desire.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.