How to Make a Histogram in Microsoft Excel
Wondering how to make a histogram in Excel to display your frequency data? We’ll walk you through making and customizing a histogram.
If you need to create a chart that displays frequency distributions, you’ll want to make a histogram. This type of chart can show how often various data occurs and can help you analyze process changes. A histogram is often used as a tool in quality control but can be beneficial for many industries.
Here, we’ll explain how to create a histogram in Microsoft Excel. And to make your histogram more useful or attractive, we’ll also show you how to customize it.
Create a Histogram in Excel
Inserting a chart in Excel is actually quite easy. Assuming you have your data ready to go, select it. Then, head to the Insert tab, click the Insert Statistic Chart arrow, and pick Histogram (on the left).
The chart will pop into your spreadsheet. It contains default styling, and the bins are automatically configured per your data. But this is where customizing your chart comes into play.
Customize a Histogram in Excel
There are two main parts to customizing your histogram in Excel, usage and appearance. Starting with the most important first, let’s look at configuring the bins for your chart to make it more beneficial.
Configure a Histogram
The columns that you see in your histogram are called Bins. These are used to show the data distribution – how often the values apply to the ranges. The bins are the same widths, but the heights equal the number of data points for each bin.
To open the formatting options for the bins, right-click the horizontal axis on the chart and select Format Axis or double-click the axis.
When the sidebar opens, expand Axis Options. You can then adjust the following configurations:
By Category: If your data uses text instead of numbers, you can select this option. The histogram then groups the categories and sums the values.
Automatic: This is the default setting for histograms in Excel, which automatically calculates and assigns the number of bins.
Bin width: You can change how wide the bins (columns) appear by entering a decimal number in this section.
Number of bins: Here, you can enter the exact number of bins you want to use if different than the Automatic setting’s outcome.
Overflow bin: Use this bin for values above a decimal number you enter in the box on the right.
Underflow bin: Use this bin for values below or equal to a decimal number you enter in the box on the right.
Below the Axis Options in the sidebar, you can also expand Tick Marks and Number to adjust the points on the chart, category, and format code.
Adjust the Appearance of a Histogram
If you’d like to change the appearance of the histogram, the chart elements, and the title, you have plenty of format options.
You can add or remove chart elements or change the style on the Chart Design tab. But you can also select a new layout, adjust your data selection, or move the chart to another location.
In the Format Chart Area sidebar, you can choose the fill and line colors and styles, apply a shadow or 3-D format, and enter an exact size for the chart or lock it. To open the sidebar, right-click the chart and pick Format Chart or double-click the chart.
In Excel on Windows, you can display Chart Elements and Chart Styles buttons on the right simply by selecting the chart.
- Chart Elements: Check and uncheck those elements you want to display on the chart. Select an item to choose its position on the chart.
- Chart Styles: Use the Style and Color tabs to give your chart a different look.
Other Chart Options
Here are a few additional options you can customize for your histogram in Excel.
- Move the chart: Click and drag the chart where you want it on your sheet.
- Resize the chart: Click a corner or edge and then drag in or out to resize it.
- Enter a chart title: Click the Chart Title text box on the chart and enter a title.
Show Your Frequency Data in a Histogram in Excel
Because it’s so simple to create a histogram in Excel, consider it for your frequency data. The customization options are plentiful, and your audience might appreciate the helpful visual!
For more, take a look at how to make a pie chart in Excel for other types of data or how to create a Gantt chart for your next project.