How to Create Histogram in Excel (Step By Step Guide)

Download ebook

Excel is a powerful tool that, if used properly, can be of great help. It offers a variety of tools and functions that are useful for one and all. Thus, this tool should be used to its full potential and enhance your spreadsheet game by learning its functions. One such function is to create a histogram in Excel.

What is Histogram

Histograms are commonly used graphs to depict the frequency distribution of the data. These charts are similar to bar graphs and are used to analyze and interpret data. They are useful in producing data distributions and charts. 

How to create a Histogram in Excel.

There are 2 major methods in which you can create a histogram in your spreadsheet. 

Chart options in Excel

You can use the in-built option to make a histogram in Excel 

Steps 

1. Select the range of your data cells 
2. Go to the insert tab and then go to charts 
3. Select the histogram icon
4. Click on histogram 
5. You will get your histogram in Excel 

Also Read: How to Insert a Checkbox in Excel: A Step-by-Step Guide

You can Customize your Histogram by Doing a Few Things:

1. Category: you can use this option when you have text categories. It is helpful when you want to know the total or count of the categories and there are repetitions in them.  For example: if you are given sales data for various items and you want to know the total sales of each of these items, you can sort them by category option.
2. Automatic: This automatically creates a bin range for your data, for example, in our data, excel found the need for only 4 bins. However, you can change this by: 

To change the bins and size range of the data 

• Select the horizontal axis ( X-axis) and right-click on it 
• Select the format axis 
• Click on the bin size and change the width accordingly 
• To change the size range click on overflow bin and bin underflow and change according to the data 

3. Bin Width: by using this can define how big the bin should be by Specifying Bin width in a histogram in Excel. 

4. Number of Bins: you can also specify how many bins you want. Excel will automatically create a chart with the specified number of bins. For example: if I type 7 bins here, it will create a chart as shown. At a given point, you can either specify the Bin Width or the Number of Bins (not both). Specifying the Number of Bins in the histogram chart

5. Overflow Bin: If you want all the numbers in the Histogram chart that are greater than a specific value combined, use this bin. For instance, I can put 75 as the Overflow Bin value to find out how many students have scored higher than 75. 

6. Underflow Bin: Like the Overflow Bin, for example, If I enter 4 as the value and see a chart like the one below I want to see how many students have scored less than 40.

Data Analysis Tool Pack 

To make a histogram in Excel using the data analysis tool pack, you need first to activate the tool pack. 

Activate your tool pack by following these steps

• Go to the home tab 
• Click on more and then options 
•  Scroll down to the add-ins 
• Select analysis tool pack 
• Unclick tool pack VBA 
• Restart your Excel 

To Draw a Histogram in Excel using the Data Analysis Tool Pack 

• Create bins for your data 
• Go to the data tab and click on data analysis 
• Scroll down to select the histogram in the dialogue box 
• Insert the input range, output range, and bin range in the dialogue box 
• Select chart output 
• Click enter
• Excel will create your bin range and frequency and will also produce a histogram.

Key points to keep in mind while making a histogram in Excel using the analysis tool pack 

• The bin values should include all the values below it. For example in this case, under 45, there are 3 students in it. 
• Excel created the last bin -more to include the data since our last bin is 90. This means there are 2 more students under it. 
• This last bin will be created even after we add our last bin as 100 
• This method results in a histogram chart that remains constant. The reason is that when Excel forms and pastes the frequency distribution as values, any alterations made to the original data won’t be reflected in the chart automatically. To observe any changes, you’ll find it necessary to recreate the histogram chart from scratch.
• You can always change the formatting of the regular chart according to your preference and usage 
• The chart can only be deleted by manually deleting the bins, you cannot use ctrl+z to reverse it. 
• If you make a histogram without setting any bin range, Excel will still generate the histogram. It will automatically establish six bins with equal spacing and utilize this information to construct the histogram in Excel.

Recommended courses 

Being good at Excel is a must for work. It makes you stand out when applying for jobs. So, it’s smart to get better at Excel, especially the advanced features. This skill is essential and can give you an edge in your professional life.

We recommend Henry Harvin’s Advanced Excel course, a course that will provide you with an in-depth understanding of advanced Excel tools and how to use Excel to its full potential.

The total program fee will be around ₹ 7500. the learning period will of around 24 hours which will equip you with all the advanced formulas, data analysis, data visualization, and how to perform them as well. it will also teach how to create pivot tables, dashboards, and much more. It will be a live online classroom experience and provide you with brush-up training sessions along with it. The course also provides a wide exposure and will include about 11+ projects during the time span.

Conclusion 

This blog provided a detailed description of how to create a histogram in Excel and methods to customize it. These are the 2 methods by which you can make a histogram. These are used to analyze and interpret data in an easy way. The method chosen is according to the user’s preference and usage, and both of them create a similar output. 

Recommended Reads:

• 15 Free Online Excel Courses for Beginners
• Benefits of Taking an Advanced Excel Course
• 15 Best Advanced Excel Courses in Delhi
• How to Use Concatenate in Excel?
• Excel VBA-All You Need To Know
• How Long Does It Take To Learn Advanced Excel in 2024?

FAQs 

Q1 What is the difference between a histogram and a bar graph 

Ans: A histogram is a graphical representation of quantitative data, whereas a bar graph is a graphical representation of categorical data 

Q2 How many ways are there to create a histogram in Excel 

Ans: There are primarily two methods to create a histogram in Excel, which have been listed in the above blog 

Q3 How to get the Data analysis tool pack in your Excel 

Ans: Open files<more<options<add ins, select data analysis tool pack, and select go. Restart Excel and you will have your data analysis tool pack added to the data tab.

Q4 What do advanced Excel courses teach? 

Ans: The Advanced Excel course teaches how to efficiently make use of Excel functions to make your work easier and enhance your skills for technical work 

Q5 How much time it takes to learn this skill 

Ans: Usually, these courses take up to one month to complete. However, they need regular practice and brushing up to make use of all the functions properly. 

Download ebook