Saturday, August 13, 2022

How To Draw Histogram In Excel

You May Be Interested

Make A Histogram Based On The Summary Data

How to Make a Histogram in Excel 2016

Now that you have a list of frequency distributions computed with either FREQUENCY or COUNTIFS function, create a usual bar chart – select the frequencies, switch to the Insert tab and click the 2-D Column chart in the Charts group:

The bar graph will be immediately inserted in your sheet:

Generally speaking, you already have a histogram for your input data, though it definitely requires a few improvements. Most importantly, to make your Excel histogram easy to understand, you need to replace the default labels of the horizontal axis represented by serial numbers with your bin numbers or ranges.

The easiest way is to type the ranges in a column left to the column with the Frequency formula, select both columns – Ranges and Frequencies – and then create a bar chart. The ranges will be automatically used for the X axis labels, as shown in the below screenshot:

Tip.‘1-5bin numbers‘5’10

If there is no way you can type the desired histogram labels on your sheet, then you can enter them directly on the chart, independently of the worksheet data. The final part of this tutorial explains how to do this, and shows a couple of other improvements that can be made to your Excel histogram.

How To Create A Simple Histogram Chart In Excel

365histogram chartdifferent typesof histogram charts in Excel

The chart above shows the distribution of employees ages. The graph contains two uniquebins < = 25 and > 55 covering edge cases.

To create a histogram in Excel 2016, do the following:

1. Add the data for the chart and transform it :

For this example, the birthday date transformed to the age of people:

To calculate full years, use the formula:

= DATEDIF , “y”)

Note: The function TODAY returns the current date, andDATEDIFF with the last “y” parameter returns the full number of yearsbetween two dates.

2. Create a histogram chart:

2.1. Select the prepared data .

2.2. On the Insert tab, in the Charts group,click the Statistic button:

From the Statistic dropdown list, select Histogram:

Excel creates the histogram chart from the data:

As you can see, the default histogram has too many bins , but you can change thenumber of bins and bin widths.

3. Format the histogram chart:

3.1. Right-click on the horizontal axis and chooseFormat Axis… in the popup menu:

3.2. In the Format Axis pane, on the Axis Optionstab, under Bins:

Make other adjustments as you desire.

Note: Histograms show the shape and the spread of continuous data. Forcategorical data, discrete by its definition, you should use bar charts that representcategory frequencies.

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.

Customize And Improve Your Excel Histogram

Whether you create a histogram using the Analysis ToolPak, Excel functions or a PivotChart, you might often want to customize the default chart to your liking. We have a special tutorial about Excel charts that explains how to modify the chart title, legend, axes titles, change the chart colors, layout and style. And here, we will discuss a couple of major customizations specific to an Excel histogram.

Don’t Miss: How To Draw A Toucan Easy

How To Make A Histogram In Excel Using Formulas

Another way to create a histogram in Excel is using the FREQUENCY or COUNTIFS function. The biggest advantage of this approach is that you won’t have to re-do your histogram with each change in the input data. Like a normal Excel chart, your histogram will update automatically as soon as you edit, add new or delete existing input values.

To begin with, arrange your source data in one column , and enter the bin numbers in another column , like in the screenshot below:

Now, we will use a Frequency or Countifs formula to calculate how many values fall into the specified ranges , and then, we will draw a histogram based on that summary data.

How To Make A Histogram In Microsoft Excel

How To… Draw a Simple Histogram Excel 2010

Wondering how to make a histogram in Excel to display your frequency data? Well walk you through making and customizing a histogram.

If you need to create a chart that displays frequency distributions, youll want to make a histogram. This type of chart can show how often various data occurs and can help you analyze process changes. A histogram is often used as a tool in quality control but can be beneficial for many industries.

Here, well explain how to create a histogram in Microsoft Excel. And to make your histogram more useful or attractive, well also show you how to customize it.

Also Check: Drawing Boots Front View

Ways To Create Histogram In Excel

Creating a histogram in Excel is a subject of minutes and can be achieved in a number of ways by using the particular Histogram tool of the Analysis ToolPak, formulas or the PivotTable. Further on in this informative article, you will find the step-by-step description of applying the Analysis ToolPak method. You can analyze your data and show it in a histogram by using the Histogram tool of the Evaluation ToolPak in Microsoft Office Excel.

Originally, you may need to organize your data into two columns on the spreadsheet: one column data and another for bin numbers. Input data is meant to analyze, whereas bin numbers signify the intervals you want the Histogram tool to utilize these data.

Here in this article, you could go through a typical example of how each one of these works and everything will become clearer if you are currently confused in generating a histogram on Excel.

Likewise, you can make a histogram in Excel to display the number of days with a temperature between 20-30, 31-40, 41-50, etc. degrees, the number of sales with amounts between $100-$200, $200-$300, $400-$500, the number of students with test scores between 41-60, 61-80, 81-100 percentage, and so on.

Note: To use the Histogram tool, you first need to recognize the bins that you intend to use to produce a frequency distribution. A histogram displays the same information in that column chart.

Create A Pareto Chart

A Pareto or sorted histogram chart contains both columns sorted in descending order and a line representing the cumulative total percentage. Pareto charts highlight the biggest factors in a data set, and are considered one of the seven basic tools of quality control as it’s easy to see the most common problems or issues.

You May Like: My Hero Academia Art Style Tutorial

What Does Microsoft Excel Mean

Microsoft Excel is a software program produced by Microsoft that allows users to organize, format and calculate data with formulas using a spreadsheet system.This software is part of the Microsoft Office suite and is compatible with other applications in the Office suite. Like other Microsoft Office products, Microsoft Excel can now be purchased through the cloud on a subscription basis through Office 365.

Creating The Histogram With The Pivot Chart

How To… Create an Overlapping Histogram in Excel

To create the histogram chart, we will create a column chart from the pivot table and adjust it a bit. First, we need to insert a chart. We select any field in the table, in the Insert tab click on the PivotChart button and choose PivotChart option:

In the pop-up window for chart insertion, we choose the suggested option Clustered column chart:

As you can see in Image 13, we get the chart presenting the number of sales in each interval:

To make the chart looks like histogram, we need to adjust the columns by deleting empty spaces between them. To enable that, we need to right click on any column and choose Format Data Series option. In new window on the right side, we need to set Gap Width to 0%. Now empty spaces between columns are removed and we have the histogram representing Number of sales by intervals of $100:

Also Check: How To Draw A Cute Pumpkin

Things To Remember About Histogram Chart In Excel

  • A Histogram chart is used for continuous data where the bin determines the range of data.
  • The bins are usually determined as consecutive and non-overlapping intervals.
  • The bins must be adjacent and are of equal size .
  • If you are working with Excel 2013, 2010 or earlier version, you need to activate the Excel Add-Ins for Data Analysis ToolPak.

Creating Histogram Using Analysis Toolpak

Click on the Data Analysis command in the data tab. Data Analysis dialog box appears. You will find several Data Analysis tools in the window. I select histogram and click OK. Or I can double click on the histogram option. Histogram dialog box appears.

In the Input range, I select these values from cell A2 to A41. In the bin range, I select values in the cells B2 to B11. There are Output options here. You can see your output in three ways:

  • You can show your output in the same worksheet, in that case, you will select the Output range option and on the right side field, you will set the cell where you want to see the output.
  • You can also show the output in a new worksheet, by default this option is selected.
  • Or you can see the output into a new workbook.
  • I want to see my output in a new worksheet. New Worksheet Ply is selected, it is OK. I want to see the result in a chart also, so I select this Chart Output option. Now I click OK.

    Histogram Options

    You see a new worksheet is created. In the worksheet, you get the Bin, the frequency distribution table and you get the histogram chart on the right side of the worksheet.

    Histogram with Chart

    Lets analyze the above chart a little bit. Between 0 and 10, we get 3 values, between 10 and 20, we get 6 values, between 20 and 30 we get 4 values and so on. It is clear from the chart that the data does not show any pattern.

    Histogram chart can show great insights from your sales and other business related data.

    Recommended Reading: Easy To Draw Deer Head

    How To Create A Histogram Using A Formula In Excel

    Using a formula to count the occurrences in each bin provides more flexibility for your histogram. This is my preferred method and also works in all versions of Excel.

    This method involves inserting a column chart instead of the histogram option and then formatting it to look like a conventional histogram.

    We will use the same exam score data used in the previous example, but two of the scores have been changed to less than 40.

    First we need to create the source data for our chart. This is the bins and the frequency of exam scores in each of those bins.

    The following image shows the source created in this example. The bins are typed into range G4:G10 and the COUNTIFS function is used to count the occurrences of the scores for each bin.


    The values in ranges E4:E10 and F4:F10 have been used to assist the COUNTIFS function. It makes it easier to change if the size of the bins changes in the future. These columns will be hidden as nobody wants to see them.

    To create the histogram:

  • Select range G4:H10
  • Click Insert > Insert Column or Bar Chart> Clustered Column.
  • The following column chart is inserted. It looks great already, but we will make some general improvements and then remove the gap between each column.

    3. I have made the following changes to the column chart.

    • Edited the chart title to Exam Score Distribution
    • Formatted the columns to a green colour
    • Removed the value axis
    • Removed the horizontal gridlines
    • Added data labels above each column

    How Is A Histogram Different To A Column Chart

    How To Create A Histogram in Excel (& change the bin size)

    A histogram is very similar to a column chart, but they are not exactly the same. A few differences between the two charts include:

    • The purpose of a histogram is to present frequency distribution e.g., the count of exam scores grouped into ranges. Column charts compare values e.g., the total sales value of six different products.
    • The X axis of a histogram must be in order of its size e.g., 1-5, 6-10, 11-15. A column chart does not need to be in a specific order. The order depends on its purpose.
    • The X axis is formed of intervals or bins in a histogram. A column chart typically has text values in this axis, such as names of cities.
    • There are no gaps between the columns of a conventional histogram.
    • 100% of the values in a data set must be included in a histogram. A column chart could be used to compare only the top 5 products.

    Read Also: Drawing Of Praying Hands

    Load The Analysis Toolpak In Excel

    If you need to develop complex statistical or engineering analyses, you can save steps and time by using the Analysis ToolPak. You provide the data and parameters for each analysis, and the tool uses the appropriate statistical or engineering macro functions to calculate and display the results in an output table. Some tools generate charts in addition to output tables.

    The data analysis functions can be used on only one worksheet at a time. When you perform data analysis on grouped worksheets, results will appear on the first worksheet and empty formatted tables will appear on the remaining worksheets. To perform data analysis on the remainder of the worksheets, recalculate the analysis tool for each worksheet.

  • Click the File tab, click Options, and then click the Add-Ins category.

    If you’re using Excel 2007, click the Microsoft Office Button, and then click Excel Options

  • In the Manage box, select Excel Add-ins and then click Go.

    If you’re using Excel for Mac, in the file menu go to Tools> Excel Add-ins.

  • In the Add-Ins box, check the Analysis ToolPak check box, and then click OK.

  • If Analysis ToolPak is not listed in the Add-Ins available box, click Browse to locate it.

  • If you are prompted that the Analysis ToolPak is not currently installed on your computer, click Yes to install it.

  • Notes:

    How To Create A Histogram In Excel

    Put simply, frequency data analysis involves taking a data set and trying to determine how often that data occurs. You might, for instance, be looking to take a set of student test results and determine how often those results occur, or how often results fall into certain grade boundaries.

    Histograms make it easy to take this kind of data and visualize it in an Excel chart.

    You can do this by opening Microsoft Excel and selecting your data. You can select the data manually, or by selecting a cell within your range and pressing Ctrl+A on your keyboard.

    With your data selected, choose the Insert tab on the ribbon bar. The various chart options available to you will be listed under the Charts section in the middle.

    In the Histogram section of the drop-down menu, tap the first chart option on the left.

    This will insert a histogram chart into your Excel spreadsheet. Excel will attempt to determine how to format your chart automatically, but you might need to make changes manually after the chart is inserted.

    Recommended Reading: How To Draw Eyes From The Side

    How To Make A Histogram With A Pivotchart

    As you may have noticed in the two previous examples, the most time-consuming part of creating a histogram in Excel is calculating the number of items within each bin. Once the source data has been grouped, an Excel histogram chart is fairly easy to draw.

    As you probably know, one of the fastest ways to automatically summarize data in Excel is a PivotTable. So, let’s get to it and plot a histogram for the Delivery data :

    1. Create a pivot table

    To create a pivot table, go to the Insert tab > Tables group, and click PivotTable. And then, move the Delivery field to the ROWS area, and the other field to the VALUES area, as shown in the below screenshot.

    If you have not dealt with Excel pivot tables yet, you may find this tutorial helpful: Excel PivotTable tutorial for beginners.

    2. Summarize values by Count

    By default, numeric fields in a PivotTable are summed, and so is our Order numbers column, which makes absolutely no sense 🙂 Anyway, because for a histogram we need a count rather than sum, right-click any order number cell, and select Summarize Values By> Count.

    Now, your updated PivotTable should look like this:

    3. Create the intervals

    The next step is to create the intervals, or bins. For this, we will be using the Grouping option. Right-click any cell under Row Labels in your pivot table, and select Group

    In the Grouping dialog box, specify the starting and ending values , and type the desired increment in the box.

    4. Plot a histogram

    Making A Histogram Using Countifs Function

    Create a Histogram with Excel

    Another function that can help you calculate frequency distributions to plot histogram in Excel is COUNTIFS. And in this case, you will need to use 3 different formulas:

    • The formula for the first cell – top bin :=COUNTIFS

      The formula counts how many values in column B are less than the smallest bin in cell D2, i.e. returns the number of items delivered within 1-5 days.

    • The formula for the last cell – over the highest bin :=COUNTIFS

      The formula counts how many values in column B are greater than the highest bin in D8.

    • The formula for remaining bins :=COUNTIFS

    The formula counts the number of values in column B that are greater than the bin in the above row and less than or equal to the bin in the same row.

    As you see, the FREQUENCY and COUNTIFS functions return identical results:

    “What is the reason of using three different formulas instead of one?” you may ask me. Basically, you get rid of the multi-cell array formula and can add and delete bins easily.


    Recommended Reading: How To Draw A Realistic Reindeer

    S Involved In Creating And Crafting A Histogram In Microsoft Excel

    The histogram making process in Microsoft Excel is extremely basic and does not involve any rocket science. It does not demand any expertise in designing and can be accomplished from the predefined data that needs to be manipulated. The steps can be advanced by incorporating a few clicks of the mouse.

    Available Data: 20 people giving their inputs on how many movies they watched in the last month.

    More articles

    Popular Articles