How to create a histogram chart in Excel 2019, 2016, 2013 and 2010
Remove spacing between bars What is a histogram in Excel? Wikipedia defines a histogram in the following way: Well, let’s think about histograms in another way. Have you ever made a bar or column chart to represent some numerical data? I bet everyone has. A histogram is a specific use of a column chart where each column represents the frequency of elements in a certain range.
In other words, a histogram graphically displays the number of elements within the consecutive non-overlapping intervals, or bins. For example, you can make a histogram to display the number of days with a temperature between , , , etc.
The following screenshot gives an idea of how an Excel histogram can look like: However, this add-in is not loaded automatically on Excel start, so you would need to load it first. If Excel shows a message that the Analysis ToolPak is not currently installed on your computer, click Yes to install it. Now, the Analysis ToolPak is loaded in your Excel, and its command is available in the Analysis group on the Data tab. Specify the Excel histogram bin range Before creating a histogram chart, there is one more preparation to make – add the bins in a separate column.
The intervals must be consecutive, non-overlapping and usually equal size. Excel’s Histogram tool includes the input data values in bins based on the following logic: A value is included in a certain bin if it is greater than the lowest bound and equal to or less than the greatest bound for that bin. If your input data contain any values greater than the highest bin, all such numbers will be included in the More category. If you do not specify the bin range, Excel will create a set of evenly distributed bins between the minimum and maximum values of your input data range.
Considering the above, type the bin numbers that you want to use in a separate column. The bins must be entered in ascending order, and your Excel histogram bin range should be limited to the input data range. In this example, we have order numbers in column A and estimated delivery in column B.
In our Excel histogram, we want to display the number of items delivered in days, days, days, days and over 20 days. So, in column D, we enter the bin range from 5 to 20 with an increment of 5 as shown in the below screenshot: Make a histogram using Excel’s Analysis ToolPak With the Analysis ToolPak enabled and bins specified, perform the following steps to create a histogram in your Excel sheet: On the Data tab, in the Analysis group, click the Data Analysis button.
In the Histogram dialog window, do the following: Specify the Input range and the Bin range. To do this, you can place the cursor in the box, and then simply select the corresponding range on your worksheet using the mouse.
If you included column headers when selecting the input data and bin range, select the Labels check box. Select the Output options. To place the histogram on the same sheet, click Output Range, and then enter the upper-left cell of the output table. To paste the output table and histogram in a new sheet or a new workbook, select New Worksheet Ply or New Workbook, respectively. Finally, choose any of the additional options: To present data in the output table in descending order of frequency, select the Pareto sorted histogram box.
To include a cumulative percentage line in your Excel histogram chart, select the Cumulative Percentage box. To create an embedded histogram chart, select the Chart Output box. For this example, I’ve configured the following options: And now, click OK, and review the output table and histogram graph: To improve the histogram, you can replace the default Bins and Frequency with more meaningful axis titles, customize the chart legend, etc.
Also, you can use the design, layout, and format options of the Chart Tools to change the display of the histogram, for example remove gaps between columns. For more details, please see How to customize and improve Excel histogram. As you’ve just seen, it’s very easy to make a histogram in Excel using the Analysis ToolPak.
However, this method has a significant limitation – the embedded histogram chart is static, meaning that you will need to create a new histogram every time the input data is changed.
To make an automatically updatable histogram, you can either use Excel functions or build a PivotTable as demonstrated below. 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 column B in this example , and enter the bin numbers in another column column D , like in the screenshot below: Now, we will use a Frequency or Countifs formula to calculate how many values fall into the specified ranges bins , and then, we will draw a histogram based on that summary data.
B40, bin array is D2: D8, so we get the following formula: An Excel Frequency formula should be entered as a multi-cell array formula. It’s recommended to enter one more Frequency formula than the number of bins. The extra cell is required to display the count of values above the highest bin. The last Frequency formula in cell E9 returns the number of values greater than the highest bin i. To make things easier to understand, the following screenshot shows the bins column D , corresponding intervals column C , and computed frequencies column E: If you decide to change the number of bins, you will have to delete the existing formula first, then add or delete the bins, select a new range of cells, and re-enter the formula.
And in this case, you will need to use 3 different formulas: The formula for the first cell – top bin F2 in the screenshot below: The formula for the last cell – over the highest bin F9 in the screenshot below: The formula for remaining bins cells F3: F8 in the screenshot below: Basically, you get rid of the multi-cell array formula and can add and delete bins easily.
In this example, the source data are in cells B2: But you can supply the range B2: B or even B2: B, just in case: For example: 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: If Excel converts your intervals to dates e. If you want the labels of your Excel histogram to display bin numbers, type them with preceding apostrophes too, e.
The apostrophe just converts numbers to text and is invisible in cells and on the histogram chart. 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 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 column B: If you have not dealt with Excel pivot tables yet, you may find this tutorial helpful: Excel PivotTable tutorial for beginners.
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: Now, your updated PivotTable should look like this: Create the intervals bins 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 usually Excel enters the minimum and maximum value automatically based on your data , and type the desired increment interval length in the By box.
In this example, the minimum delivery time is 1 day, maximum – 40 days, and the increment is set to 5 days: Click OK, and your pivot table will display the intervals as specified: Plot a histogram One final step is left – draw a histogram. To do this, simply create a column pivot chart by clicking the PivotChart on the Analyze tab in PivotTable Tools group: And the default column PivotChart will appear in your sheet straight away: And now, polish up your histogram with a couple of finishing touches: Replace the default Total title with something more meaningful.
Additionally, you may want to achieve a conventional histogram look where bars touch each other. And you will find the detailed guidance on how to do this in the next and final part of this tutorial. 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.
Frequency Distributions for Quantitative Data
To do this, pull down the Tools menu, and choose Add-Ins. You need to have a column of numbers in the spreadsheet that you wish to create the histogram from, AND you need to have a column of intervals or “Bin” to be the upper boundary category labels on the X-axis of the histogram. See example of spreadsheet below: Enter the Input Range of the data you want In the example above it would be C5: C29 and enter the Bin Range E5:
VIDEO: Histogram in Excel – Easy Excel Tutorial
Creating a histogram on EXCEL 1. Start up Excel. 2. Title the A1 and B2 column Class Boundaries and Frequency accordingly. Histograms most often. In Excel, you can use the Histogram Data Analysis tool to create a frequency Make choices from the Output Options check boxes to control what sort of. It’s in the green ribbon that’s at the top of the Excel window. Doing so switches the toolbar near the.