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 2016

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 2016, 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 2016

Where:

  • 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 cell without formulas.

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

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

The Custom Sort button in Excel 2016

   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 2016

   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 2016

From the Insert Statistic Chart dropdown list, select Histogram:

The Histogram Chart in Excel 2016

Excel creates the incomprehensible histogram chart from the data:

The incomprehensible Histogram chart in Excel 2016

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

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