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

Excel 2016
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 365

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 for Microsoft 365, 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 365

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 on the Insert Statistic Chart button:

The Insert Statistic Chart button in Excel 365

From the Insert Statistic Chart dropdown list, select Histogram:

The Histogram Chart in Excel 365

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

The Histogram chart in Excel 365

   3.   Format the histogram chart:

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

Format Axis in popup menu Excel 365

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

Format Axis for Histogram chart in Excel 365

Excel changes the histogram chart:

The Histogram chart in Excel 365

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.