How to create a Histogram chart in Excel
This guide shows three easy ways to make a histogram Chart in Excel: using the built-in Histogram tool, formulas, or a PivotTable. Even though making charts is usually simple, histograms can be tricky. But in newer versions of Excel, it’s easy to create one in just a few steps. Let’s explore each method in detail.
Table of Contents
What is a histogram chart in Excel?
Wikipedia says a histogram is a picture that shows how numbers are spread out. That’s true, but it might sound confusing.
Think of a histogram like a special kind of bar chart. Each bar shows how many times a number appears in a certain group. For example, you could use a histogram to show how many days it was between 61 and 65 degrees, between 66 and 70 degrees, and so on.
How to create a histogram in Excel using Analysis ToolPak
The Analysis ToolPak is a special tool in Excel that helps you analyze data. It’s included in all newer versions of Excel, but you have to turn it on first.
Here’s how to turn on the Analysis ToolPak:
- Go to File > Options (or the Microsoft Office button in Excel 2007).
- Click Add-ins on the left side.
- Make sure Excel Add-ins is selected in the Manage box.
- Click the Go button.”
In the Add-ins window, find and check the Analysis ToolPak box. Then, click OK to close the window.
If Excel says the Analysis ToolPak isn’t on your computer, click Yes to install it
Now that you’ve turned on the Analysis ToolPak, you can find it under the Data tab.
Creating Bins for Your Histogram:
Before you make the histogram, you need to create a list of bins. Bins are like groups that your data will fit into. For example, you might want to group your data into bins of 1-5 days, 6-10 days, 11-15 days, and so on.
How Excel Uses Bins:
Excel puts each piece of data into the bin that it fits in. If a number is bigger than the lower end of a bin but smaller than or equal to the higher end, it goes in that bin. If a number is bigger than the biggest bin, it goes into a special category called “More.”
Creating Bins in Excel:
Type the numbers you want to use for your bins in a separate column. Make sure they are in order from smallest to largest. Your bins should cover all the numbers in your data.
In this example, we have order numbers in column A and delivery times in column B. We want to know how many items were delivered in 1-5 days, 6-10 days, 11-15 days, 16-20 days, and more than 20 days. So, in column D, we typed the numbers 5, 10, 15, and 20. This creates bins of 1-5, 6-10, 11-15, 16-20, and more than 20.”
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 Datatab, in the Analysis group, click the Data Analysis
In the Data Analysis dialog, select Histogram and click OK.
In the Histogram dialog window, do the following:
- Specify the Input rangeand the Bin range.
- To do this, click inside the box, then use your mouse to highlight the range on your worksheet. Another way is to click the Collapse Dialog button, choose the range on the sheet, and click the Collapse Dialog button again to go back to the Histogram box.”
- Let me know if this works for you!
Tip. If you included column headers when selecting the input data and bin range, select the Labels check box.
Select the Output options.
To put the histogram on the same sheet, click Output Range and enter the top-left cell where you want the table to start.
If you want the table and histogram on a new sheet or in a new workbook, choose either New Worksheet Ply or New Workbook.
Next, you can choose any of these extra options:
- To show data in the table from highest to lowest, select the Pareto (sorted histogram) box.
- To add a line showing the total percentage in the chart, check the Cumulative Percentage box.
- To make a chart inside the sheet, select the Chart Output box.
For this example, I’ve set these options:
And now, click OK, and review the output table and histogram graph:
As you’ve just seen, making a histogram in Excel with the Analysis ToolPak is easy. But there’s one big drawback—the histogram chart doesn’t update automatically. So, if your data changes, you’ll have to create a new histogram each time.
To make a histogram that updates on its own, you can use Excel functions or create a PivotTable, as shown below
How to make a histogram in Excel using formulas
Another way to make a histogram in Excel is by using the FREQUENCY or COUNTIFS function. The main benefit of this method is that your histogram will update automatically when you change the input data. You won’t need to create a new one every time—just like a regular Excel chart, it updates when you edit, add, or delete values.
To start, put your source data in one column (for example, column B) and your bin numbers in another column (like column D), as shown in the screenshot below:
Next, we will use a FREQUENCY or COUNTIFS formula to count how many values fall into each range (bin). After that, we will create a histogram based on the summary of this data.
Creating a histogram using Excel's FREQUENCY function
The easiest function to use for creating a histogram in Excel is the FREQUENCY function. It counts how many values fall within certain ranges, and it ignores any text or empty cells.
The FREQUENCY function has this format
FREQUENCY(data_array, bins_array)
Data_array – the set of values you want to count. Bins_array – the set of ranges (bins) to group the values.
In this example, the data_array is B2, and the bins_array is D2, so the formula looks like this:
=FREQUENCY(B2, D2)
Keep in mind that the FREQUENCY function works in a specific way, so follow these rules:
- You need to enter the FREQUENCY formula as an array formula. First, select the cells where you want the results to appear, then type the formula and press Ctrl + Shift + Enter.
- Add one more cell than the number of bins. This extra cell will show the count of values above the highest bin. You can label this cell as ‘More’ for clarity, like in the screenshot (but don’t include the ‘More’ cell in your bins_array!).
Just like the Histogram option in the Analysis ToolPak, the Excel FREQUENCY function counts values that are greater than the previous bin and less than or equal to the current bin. The last FREQUENCY formula (in cell E9) shows the number of values greater than the highest bin (for example, the number of delivery days over 35).
To make this clearer, the screenshot below shows the bins (in column D), their corresponding ranges (in column C), and the calculated frequencies (in column E):
Making a histogram using COUNTIFS function
Another function you can use to calculate frequency distributions and create a histogram in Excel is COUNTIFS. For this, you’ll need 3 different formulas:
- For the first cell (top bin, F2 in the screenshot): =COUNTIFS($B$2:$B$40,”<=”&$D2) This formula counts how many values in column B are less than or equal to the smallest bin in D2. For example, it shows the number of items delivered in 1-5 days.
- For the last cell (above the highest bin, F9 in the screenshot): =COUNTIFS($B$2:$B$100,”>”&$D8) This formula counts how many values in column B are greater than the highest bin in D8.
- For the other bins (cells F3 in the screenshot): =COUNTIFS($B$2:$B$40,”>”&$D2,$B$2:$B$40,”<=”&$D3) This formula counts how many values in column B are greater than the bin above and less than or equal to the current bin.
As you can see, both the FREQUENCY and COUNTIFS functions give the same 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.
Tip: If you plan to add more data later, you can use a larger range in your FREQUENCY or COUNTIFS formulas. This way, you won’t need to change the formulas when you add more rows. For example, in this case, the data is in cells B2, but you can use a bigger range like B2 or even B2, just to be safe 🙂
Make a histogram based on the summary data
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:
In general, you already have a histogram for your data, but it needs some improvements. The most important thing is to make it easier to understand by replacing the default numbers on the horizontal axis with your bin numbers or ranges.
The simplest way to do this is to type the ranges in the column next to the one with the FREQUENCY formula. Then, select both columns—Ranges and Frequencies—and create a bar chart. The ranges will automatically appear as the labels on the X-axis, as shown in the screenshot below:
Tip. If Excel converts your intervals to dates (e.g. 1-5 can be automatically converted to 05-Jan), then type the intervals with a preceding apostrophe (‘) like ‘1-5. If you want the labels of your Excel histogram to display bin numbers, type them with preceding apostrophes too, e.g. ‘5, ’10, etc. The apostrophe just converts numbers to text and is invisible in cells and on the histogram chart.
if you can’t type the labels you want on the sheet, you can add them directly to the chart without using the worksheet data. The last part of this tutorial will show you how to do that and suggest a few other ways to improve your Excel histogram
How to make a histogram with a PivotChart
As you might have seen in the previous examples, the longest part of making a histogram in Excel is counting how many items fall into each bin. Once the data is grouped, creating the histogram chart is pretty simple.
One of the quickest ways to automatically summarize data in Excel is by using a PivotTable. So, let’s use that to create a histogram for the Delivery data (column B)
Create a pivot table
To create a PivotTable, go to the Insert tab, then click PivotTable in the Tables group. Next, drag the Delivery field to the ROWS area and the other field (like Order No.) to the VALUES area, as shown in the screenshot below.
Summarize values by Count
By default, numeric fields in a PivotTable are added up, and that’s what happened with our Order numbers, which doesn’t make sense 🙂 Since we need a count for the histogram, not a sum, right-click any cell in the Order numbers column and choose Summarize Values By > Count.
Now, your updated PivotTable should look like this:
Create the intervals (bins)
The next step is to create the intervals, or bins, using the Grouping option. Right-click any cell under Row Labels in your PivotTable, and choose Group…
In the Grouping box, enter the starting and ending values (Excel usually fills in the minimum and maximum values for you), and set the interval (how wide each bin should be) in the By box.
In this example, the minimum delivery time is 1 day, the maximum is 40 days, and the interval is 5 days.
Click OK, and your pivot table will display the intervals as specified:
Plot a histogram
One last step is to create the histogram. To do this, just create a column pivot chart by clicking PivotChart in the Analyze tab under the PivotTable Tools group.
And the default column PivotChart will appear in your sheet straight away:
Now, let’s finish up your histogram with a few final touches:
- Remove the legend by clicking the Chart Elements button and unchecking Legend, or just select the legend and press the Delete key on your keyboard.
- Change the default ‘Total’ title to something more meaningful.
- If you want, you can pick a different chart style from the Chart Styles group on the PivotChart Tools > Design tab.
- To hide the chart buttons, click Field Buttons on the PivotChart Tools > Analyze tab under the Show/Hide group.”
I simplified the steps, making them direct and easy to follow while ensuring the instructions remain clear and focused on the task.
Change the axis labels on an Excel histogram chart
When you create a histogram in Excel using the Analysis ToolPak, Excel automatically adds labels on the horizontal axis based on the bin numbers you set. But what if you want to show ranges instead of bin numbers? You can change the labels on the X-axis by following these steps:
Right-click the labels on the X-axis, then click Select Data
On the right-hand side pane, under Horizontal (Category) Axis Labels, click the Edit button
In the Axis label range box, enter the labels you want to display, separated by commas. If you are entering the intervals, enclose them in double quotes like in the following screenshot:
Click OK. Done!
That’s how you create a histogram in Excel. To better understand the examples in this tutorial, you can download the sample Excel Histogram sheet with the data and charts. Thank you for reading, and I hope to see you on our blog next week!
