How to Make a Histogram in Excel: 3 Easy Methods

A histogram is a vital tool for analyzing and visualizing the distribution of data, allowing users to easily identify patterns, trends, and outliers. Commonly used in statistics and data-driven decision-making, histograms help present data frequency in a structured and readable format. Excel provides multiple ways to create histograms, catering to different versions and user preferences. In this guide, you’ll learn how to make a histogram in Excel using three methods: the built-in Histogram Chart for Excel 2016 and later, the Data Analysis ToolPak for earlier versions, and creating a Frequency Chart in Excel using formulas.

Disclaimer: Always double-check your data for accuracy to ensure precise and reliable results.

Method 1: Using the Built-in Histogram Chart (Excel 2016 and Later)

For Excel 2016 and later versions, a Histogram Chart is available as a built-in feature. Follow the below step by step process to make a histogram chart in excel:

Step 1: Prepare Your Data

Organize your data in a single column. For example: Here we have Test scores of a Student:

Step 2: Insert a Histogram

• Select the data range (e.g., A2:A8).
• Go to the Insert tab.
• In the Charts group, click Insert Statistic Chart > Histogram.

Step 3: Customize the Bins

• Right-click on the X-axis (horizontal axis) and select Format Axis.
• Adjust the Bin Width, Number of Bins, or add Overflow/Underflow Bins.

Step 4: Format the Chart

• Add Data Labels by right-clicking the bars > Add Data Labels.
• Modify colors, chart title, and gridlines for better clarity.

Method 2: Using the Data Analysis ToolPak (Excel 2013 and Earlier)

In older versions of Excel, or when using advanced statistical tools, you can use the Data Analysis ToolPak to create a Histogram.

Step 1: Enable the ToolPak Add-in

• Go to the File tab > Options > Add-Ins.
• Select Excel Add-Ins and click Go.
• Check the box for Analysis ToolPak and click OK.

Step 2: Prepare Your Data and Bins

• Enter your data in one column.
• In a second column, create bins (interval ranges to group your data).

Step 3: Run the Histogram Tool

• Go to the Data tab > Data Analysis.
• Select Histogram and click OK.

In the Histogram dialog box:

• Set Input Range (data).
• Set Bin Range (intervals).
• Select Output Range or a new worksheet.
• Check Chart Output to generate the chart.

Click OK to generate the Histogram and chart.

Method 3: Create a Histogram in Excel with FREQUENCY Formula

You can make an Excel histogram easily using the FREQUENCY or COUNTIFS function. It automatically updates when your data changes. Just put your data in one column and bin numbers in another. Then, use a formula to count values in each bin and create the histogram from that summary data.

Step 1: Prepare Data and Bins

Enter your data in one column and create bins in another column.

Step 2: Use the FREQUENCY Function

In an empty column, use the FREQUENCY formula:

=FREQUENCY(data_range, bin_range)

Press Ctrl + Shift + Enter to generate an array of frequencies.

Step 3: Insert a Column Chart

• Select the bin values and frequencies.
• Go to the Insert tab > Choose Column Chart.

Step 4: Customize the Chart

Add axis titles, labels, and adjust the bin width as needed.

Also Read:

• How to Create a Bar Chart in Excel
• How to Use Pivot Tables for Data Analysis in Excel
• How to Create a Line Chart in Excel

Conclusion

Histograms in Excel are an excellent way to explore and present data distributions effectively. By leveraging the built-in Histogram Chart, the Data Analysis ToolPak, or the FREQUENCY formula, you can create visually engaging charts tailored to your dataset. Whether you’re working with the latest Excel features or relying on earlier versions, this guide equips you with the tools to represent your data with precision and clarity.

jaygala260

Follow

Improve

Article Tags :

• Excel
• ExcelGuide