Create Histogram Chart in Excel

Introduction to Histogram Charts

A histogram is a graphical representation that organizes a group of data points into specified ranges. It is a type of bar plot where the bar width represents the range of values, known as bins or classes, and the height corresponds to the frequency or density of data points within each bin. Histograms are particularly useful for understanding the distribution of data, including identifying patterns such as skewness, outliers, and the overall shape of the data distribution.

Creating a Histogram in Excel

Excel provides a straightforward method to create a histogram using its built-in tools. Here’s a step-by-step guide to create a histogram in Excel:

To begin, ensure your data is organized in a single column. This column will be the basis for your histogram.

Method 1: Using the Histogram Tool (Excel 2016 and Later)

• Go to the Data tab on the Ribbon.
• Click on Data Analysis in the Analysis group. If you don’t see this option, you might need to activate the Analysis ToolPak add-in.
• In the Data Analysis dialog box, select Histogram and click OK.
• In the Histogram dialog box, select the Input Range (your data column) and choose a Bin Range if you have predefined bins. Otherwise, Excel will automatically create bins for you.
• Check the box for Chart Output to create a histogram chart.
• Click OK, and Excel will create a histogram chart and a table with the frequency distribution.

Method 2: Using the FREQUENCY Function and Charting (All Excel Versions)

If you’re using an older version of Excel or prefer a more manual approach, you can create a histogram by using the FREQUENCY function and then charting the results.

• Set up your bins by deciding on the range of values you want to group your data into. For example, if your data ranges from 1 to 100, you might decide on bins of 0-20, 21-40, etc.
• Create a column next to your data with the upper limit of each bin (e.g., 20, 40, 60, etc.).
• Use the FREQUENCY function to calculate how many data points fall into each bin. The formula for the FREQUENCY function is FREQUENCY(data_array, bins_array), where data_array is the range of your data, and bins_array is the range of your bin upper limits.
• Select the cell where you want to display the frequency for the first bin and enter the FREQUENCY formula. Press Ctrl+Shift+Enter to enter the formula as an array formula if you’re using Excel versions prior to Excel 365.
• Copy the formula down for all bins.
• Select the bins and their corresponding frequencies, go to the Insert tab, and choose a column chart to create your histogram.

Customizing Your Histogram

Once you have created your histogram, you can customize it further to better suit your needs. This includes:

• Changing Bin Sizes: You can adjust the bin sizes in the Histogram tool or manually by changing the FREQUENCY function’s bins array.
• Editing Chart Appearance: Use the tools in the Chart Design and Chart Format tabs to change colors, add titles, and modify the chart’s layout.
• Adding Data Labels: Right-click on the bars in the chart, select Format Data Point, and then check Value in the Format Data Point pane to display the frequency above each bar.

Bin Range	Frequency
0-20	10
21-40	20
41-60	15
61-80	8
81-100	5

💡 Note: Always ensure that your data is clean and appropriately prepared before creating a histogram. This includes checking for and handling missing values and outliers that could skew the interpretation of your histogram.

In summary, creating a histogram in Excel is a straightforward process that can be accomplished using the built-in Histogram tool for newer versions or the FREQUENCY function combined with charting for all versions. Customization options allow you to tailor your histogram to effectively communicate the insights gleaned from your data.