How to create a histogram chart by categories in Excel
![Histogram chart by categories in Excel 2016 Histogram chart by categories in Excel 2016](/images/tips/620/1_2016.png)
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 The data for simple histogram chart in Excel 2016](/images/tips/620/2.png)
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 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...:
![The Custom Sort button in Excel 2016 The Custom Sort button in Excel 2016](/images/tips/excel_button2016/custom_sort-tab.png)
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 Sort dialog box in Excel 2016](/images/tips/620/3_2016.png)
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 the Statistic button:
![The Insert Statistic Chart button in Excel 2016 The Insert Statistic Chart button in Excel 2016](/images/tips/excel_button2016/statistic_chart.png)
From the Statistic dropdown list, select Histogram:
![The Histogram Chart in Excel 2016 The Histogram Chart in Excel 2016](/images/tips/excel_button2016/histogram_chart.png)
Excel creates the incomprehensible histogram chart from the data:
![The incomprehensible Histogram chart in Excel 2016 The incomprehensible Histogram chart in Excel 2016](/images/tips/620/4.png)
3. Format the histogram chart axes:
3.1. Right-click on the horizontal axis and choose Format Axis... in the popup menu:
![Format Axes in popup Excel 2016 Format Axes in popup Excel 2016](/images/tips/excel_button2016/format_axis.png)
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 Format Axis for Histogram chart in Excel 2016](/images/tips/620/5_2016.png)
Excel changes the histogram chart:
![The Histogram chart in Excel 2016 The Histogram chart in Excel 2016](/images/tips/620/6.png)
Make any other adjustments you find appropriate.