Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2021 Beginners
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Data Analytics in Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2021
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • Power BI
        • Power BI
        • Power BI Intermediate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2021 Beginners
        • Project for the Web
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Alteryx
        • Alteryx Advanced
        • Introduction to Alteryx
      • Power BI
        • Power BI Intermediate
        • Power BI
      • Qlik Sense
        • Qlik Sense Advanced
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Get Started
Home > Microsoft Excel > How to Make a Histogram in Excel?—2 Best Methods

How to Make a Histogram in Excel?—2 Best Methods

Note: This tutorial on how to make a histogram in Excel is suitable for Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, and Office 365 users. 

In this guide, I will show you how to create a histogram in Excel, the easy way. Histograms add a power punch to your reports. They are powerful data visualization tools that help you gain insights and tell a story based on your data. 

I’ll cover :

  • Excel Histogram Overview
  • How to Make a Histogram in Excel?
    • How to Make a Histogram in Excel Using Data Analysis Tool?
    • How to Make a Histogram in Excel Using the Histogram Chart Tool?
  • FAQs

Find the sample Excel Sheet used in this guide, below. You can use it to follow along with me.

Related: 

How To Find Duplicates In Excel? The Best Guide

Excel Goal Seek—the Easiest Guide (3 Examples)

Create A Pivot Table In Excel—the Easiest Guide

Excel Histogram Overview

A histogram is a column chart that displays the frequency of a variable in a given range. It is very similar to bar charts, except that it groups data together in user-specified intervals called bins. 

This is very helpful for easy data interpretation. 

Let’s say, you are a teacher who just finished evaluating your students’ performance. Now, you want to find how many students have failed the test and how many students have performed well.

For this, you create three intervals or bins, namely, poor performance, average performance and good performance. Using an Excel Histogram, you can plot the number of students in these three intervals easily. 

How to Make a Histogram in Excel?

There are two easy ways to add an Excel Histogram. If you are using Excel versions 2013 or earlier, you can add them using the Data Analysis Tool.

In later versions of Excel, there is a ready-made Histogram Chart tool available in the Charts section. I’ll explain both these methods using simple examples. 

How to Make a Histogram in Excel Using Data Analysis Tool? 

To add Excel Histogram using the Data Analysis tool pack, follow these steps.

  1. Get your data ready. I have the list of students along with their marks in this example.
  1. Enter the upper limits of each bin (interval) in a separate column, anywhere in the sheet.

I have entered the upper limits in column D, starting from D2. I have specified 3 upper limits, which means I want to have three bins.

Upper limits for bins for Excel Histogram
How to Make a Histogram in Excel? – Enter the Upper Limits of each bin (interval) in any adjacent column
  1. Click on the Data Analysis button under the Data tab. 
  1. In the Data Analysis menu, select Histogram and click OK. 
  1. Select the Input Range for the Histogram. In my example, it is going to be B2:B21. 
Excel Histogram using Data Analysis tool
Enter the Input Range, Bin Range, and Output Range in the Histogram dialogue box. Also, make sure to check the Chart Output tickbox to display the histogram.
  1. Select the Bin Range for the Histogram. In this example, it is D2:D4.
  1. Select a suitable Output Range for the Histogram. I have selected cell F2. 
  1. Check the Chart Output tick box and click OK. 
Excel Histogram Result
Click OK. The histogram along with the frequency distribution table will now appear in the specified Output Range.
  1. To change the gap width between bins, right-click on any one of the bars and click on the Format Data Series button. Also, remove unwanted intrevals by unchecking them in the Values filter as shown below.
Values Filter Excel Histogram
Click on the Values filter on the right-hand side of the chart to remove unwanted bins or intervals.
How to Make a Histogram in Excel
Change the gap between the bins by setting a suitable Gap Width in the Format Data Series pane

Also Read: 

How To Use Excel Countifs: The Best Guide

Excel Conditional Formatting -the Best Guide (Bonus Video)

The Best Excel Project Management Template In 2021

How to Make a Histogram in Excel Using the Histogram Chart Tool?

Let’s see how to make a frequency Histogram in Excel using the Histogram Chart Tool:

  1. Select the Data Range. I have selected the range B2:B21 in this example. 
  1. Locate and click on the Histogram option under the Charts group in the Insert tab. 
Excel Histogram using Charts
Click on the Histogram button under the Charts group in the Insert tab after selecting your Data Range
  1. After Excel plots the Histogram, you can format it if needed. To format, right-click on the Horizontal axis and then on the Format Axis option.
Format Axis Excel Histogram
Right Click on the Horizontal Axis and click on the Format Axis option to change the number of bins and bin size
  1. Change the Number of Bins and the Bin Width in the Format Axis pane. 
Excel Histogram Gap Width
Set the Number of Bins, Underflow Bin, and Overflow Bin to properly plot the Histogram. Right-Click on the bins to access the Format Data Series pane to change the Gap Width between the bins.

Specify the number of bins, the range of overflow and underflow bins here to make Excel plot your histogram properly. 

You can change the gap between the bins by right-clicking on the bins and changing the Gap Width in the Format Data Series pane. 

Suggested Reads: 

Create An Excel Dashboard In 5 Minutes – The Best Guide

Dynamic Dropdown Lists In Excel – Top Data Validation Guide

Predict Future Values Using Excel Forecast Sheet – The Best Guide

FAQs

What is a histogram chart used for in Excel?

A histogram displays the frequency of a variable in certain intervals (bins) in the form of a column chart. It simplifies data visualisation to a great extent. 

Is a histogram suitable for continuous data?

Histograms are suitable for plotting both continuous and discrete data, as long as they need to be measured on an interval scale. 

What is the difference between a histogram vs bar graph?

Though histograms and bar graphs may look very similar, they are very different from each other. 
Histograms present the frequency distribution of a quantitative variable in a single chart. Bar graphs on the other hand compare different categorical variables in a single chart. 

Let’s Wrap Up 

In this guide, I have walked you through how to make a histogram in Excel. We also saw, how to change the bin width and range of a histogram. If you have any doubts regarding histograms or any other Excel feature, please let us know in the comments below. 

If you need more high-quality Excel guides, please check out our free Excel resources centre.

Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 100+ IT training courses. Click here for advance Excel courses with in-depth training modules.

Adam Lacey

Adam Lacey is an Excel enthusiast and online learning expert. He combines these two passions at Simon Sez IT where he wears a number of different hats. When Adam isn't fretting about site traffic or Pivot Tables, you'll find him on the tennis court or in the kitchen cooking up a storm.

Most Popular Posts

  • Kanban vs Scrum: Project Management Methodologies and Their Differences [2022]
  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15

Similar Posts

What-If Analysis in Excel – Recorded Excel Live Class

Getting to Grips with PivotTables in Excel (1-Hour Recorded Webinar)

How to Update Your Company Information In QuickBooks 2019

Using Organization Data, Pictures, and More on Layouts in Visio 2010

How to Organize Photos Using an Album in Photoshop Elements 2019

Using SkyDrive in Windows 8

Course Categories

  • Adobe
  • Data Analysis
  • QuickBooks
  • Microsoft
  • Web Development
  • Work Productivity

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebookLinkedIn
© 2023 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
  • Sitemap
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)