Skip to main content

Create a Histogram Chart in Excel

A histogram chart is a kind of column chart, which can represent the distribution of numerical or categorical data approximately. The below histogram chart shows the distribution of scores for a subject. Through the chart, you can easily figure out how many students fall into the scores “48 - 60”, and the number of students that have scored between 60 and 78, and so on. In this tutorial, we are going to show you how to create a histogram chart in Excel step by step.

Create a histogram chart in Excel 2019 and the earlier versions

Supposing you want to create a histogram chart based on the scores data as the below screenshot shown, please do as follows to get it down.

Note: Methods in this section can apply in not only Excel 2019, but in earlier Excel versions (Excel 2016, 2013, and 2010).

Create a histogram chart in Excel 2019 and the earlier versions with formula

1. Firstly, you need to divide the range of scores into a series of bins (intervals) based on your needs. In this case, I separate the scores into 4 bins.

Tips: Normally, the bins are specified as consecutive, non-overlapping intervals of the variable.

2. Calculate how many scores fall into each bin.

Select a blank cell to output the result for the first bin, copy the below formula into it and then press the Enter key.

=COUNTIFS($B$2:$B$28,">=48",$B$2:$B$28,"<60")

Note: In the formula, $B$2:$B$28 is the scores range, 48 and 60 are the range of values for the first bin.

3. Apply the same COUNTIF function (change the range of values for different bins) to get the frequencies for the other bins.

4. Select the Bins and Frequency data (include headers), click Insert > Insert Column or Bar Chart > Clustered Column.

Now a histogram chart is created as the below screenshot shown.

5. Right click the data series and select Format Data Series from the right-clicking menu.

6. In the Format Data Series pane, change the Gap Width to 0% under the Series Options tab.

7. Keep staying in the Format Data Series pane, and go ahead to do as follows.

7.1) Click the Fill & Line tab;
7.2) Expand the Border section and choose the Solid line option;
7.3) Specify a border color;
7.4) Enlarge the border width. See screenshot:

8. Go back to the chart and change the chart title based on your needs.

Now the histogram chart is finished as the below screenshot shown.

Create a histogram chart in Excel 2019 and the earlier versions with Analysis ToolPak

Here recommend the Analysis ToolPak to create a histogram chart in Excel. This method can also apply in Excel 2019, 2016, 2013 and 2010.

1. Firstly, you need to create a helper column, and specify the bin ranges based on the original data range.

Note: As the below screenshot shown, the first bin 48 indicates that you will figure out the number of students that have scored less than 48, the second bin 60 will figure out how many students that have scored between 49 and 60, and so on.

2. Click File > Options to open the Excel Options window.

3. In the Excel Options window, click Add-ins in the left pane, make sure the Excel Add-ins option is selected in the Manager drop-down list, and then click the Go button.

4. In the Add-ins dialog box, check the Analysis ToolPak – VBA box and then click the OK button.

Now an Analysis group is added under the Data tab in Excel.

5. Go to the Data tab, click Data Analysis in the Analysis group.

6. In the Data Analysis dialog box, select Histogram in the Analysis Tools section, and then click OK.

7. In the popped out Histogram dialog box, please configure as follows.

1) Select the original score range in the Input Range box;
2) Select the bin range you created in step 1 in the Bin Range box;
3) Select the Output Range option, specify a cell to place the output range;
4) Check the Chart Output box;
5) Click the OK button. See screenshot:

Then a new range and a histogram chart are created as the below screenshot shown.

8. Go ahead to adjust your bins properly and change the other elements of the chart.

8.1) In this case, I adjust the bins to display as intervals directly. You just need to manually change the cell values in the Bin column and they will be synchronized to the horizontal axis of the chart.
8.2) Remove the Secondary Axis from the chart (just select it and press the Delete key);
8.3) Remove the space between the bars. Enable the Format Data Series pane, and then change the Gap Width to 0% under the Series Options tab;
8.4) Add borders to the bars.

Finally, histogram chart is displayed as follows.

Create a histogram chart in Excel 2019 and 2016 with build-in feature

If you are using Excel 2019 or 2016, you can apply the build-in Histogram chart type to easily create a histogram chart in Excel. Please do as follows.

1. Select the whole table range and then click Insert > Insert Statistic Chart > Histogram.

The histogram chart is created as the below screenshot shown.

Note: The number of bins and the bin widths are calculated automatically based on the selected scores.

Tips:

If you want to configure the histogram chart, please right click the horizontal axis and select Format Axis from the right-clicking menu.

In the opening pane on the right side of Excel, there are several options you should know and adjust by yourself:

1. By Category: This option is often used for text categories. The histogram will group the same categories and sum the corresponding values in the value axis;
2. Bin width: You can adjust the bin width by selecting this option and entering a new width into the textbox;
3. Number of bins: You can specify how many bins you want by selecting this option and entering a certain number into the textbox;
4. Overflow bin: If you want to create a bin for all values above a certain value, please select this Overflow bin check box and enter the certain value into the textbox;
5. Underflow bin: If you want to create a bin for all values below or equal to a certain value, please select this Underflow bin check box and enter the certain value into the textbox;

Easily create a histogram chart with an amazing feature

The Normal Distribution / Bell Curve utility of Kutools for Excel can help you easily create a histogram chart in Excel with several clicks only as the below demo shown.
Download and try it now! 30-day free trail

Download the sample file

Video: Create histogram chart in Excel

The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...

Description

Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • One second to switch between dozens of open documents!
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations