How to create a histogram chart by categories in Excel

Excel 2016
Histograms show the distribution of numeric data, and there are several different ways how to create a histogram chart. Distributions of non-numeric data, e.g., ordered categorical data, look similar to Excel histograms. However, you cannot use Excel histogram tools and need to reorder the categories and compute frequencies to build such charts.
Histogram chart by categories in Excel 365

In this example, the histogram chart shows the distribution of birthdays by month for the more than 3000 employees of a company. Months are just one example of ordered categorical data. Others are categories of size (small, medium, large), weight (light, normal, heavy), etc.

To create a histogram chart of non-numeric data in Excel for Microsoft 365, do the following:

   1.   Prepare the data for the chart:

   1.1.   Add the new data for the chart:

The data for simple histogram chart in Excel 365


  • Column Month repeats the data from the column Birthday and has the format "mmm" to show just a month name:

    = TEXT (<birthday>, "mmm")

  • Column Number has the number 1 (one person has this date of birthday).

   1.2.   Sort the additional data for the chart:

Note: This step is optional. It is needed to see the months in the chart in sorted order. Skip this step if you have non-numeric categories that do not require sorting.

To sort the data derived from the other data, you can:

  • Select and sort all the data, including the cells from which the formula calculates,
  • Select only additional data, copy it to the Clipboard (by clicking Ctrl+C), and then paste it using the Paste Special options, see Paste results into cells without formulas.

   1.2.1.   Select the data (in this example, D2:D3041 or select the column).

   1.2.2.   On the Home tab, in the Editing group, click the Sort & Filter button and then select Custom Sort...:

Custom Sort in Excel 365 menu

   1.2.3.   In the Sort dialog box:

  • In the Sort by list, choose the column for sorting,
  • In the Order list, choose the sorting method (see Using a custom sort order),
  • Make sure that the option My data has headers is selected, if you selected the columns with respective headers:
Sort dialog box in Excel 365

   2.   Create a histogram chart:

   2.1.   Select the prepared data (in this example, D2:E3041).

   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

Excel creates the incomprehensible histogram chart from the data:

The incomprehensible Histogram chart in Excel 365

   3.   Format the histogram chart axes:

   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 adjustments you find appropriate.

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.