How to Create a Histogram in Excel: Chart, ToolPak and FREQUENCY

There are three ways to create a histogram in Excel. The quickest is the built-in Histogram chart, which has been part of Excel since Excel 2016. The older route is the Analysis ToolPak, an add-in that builds a frequency table and a chart. The third is the FREQUENCY function, which counts values into bins with a formula so you control every detail. This guide walks through all three with the same sample data, shows how to set the bin width, the overflow and underflow bins, and how to make an Excel histogram start at 0. The steps come from Microsoft’s own support pages, checked in September 2026.

The Sample Data

Every method below uses the same 40 test scores, so you can compare the results. Copy them into column A of a new sheet, one score per cell, with the header Score in A1.

58, 62, 64, 66, 67, 68, 69, 70, 70, 71, 72, 72, 73, 73, 74, 74, 74, 75, 75, 75,
75, 76, 76, 76, 77, 77, 78, 78, 79, 80, 80, 81, 82, 83, 84, 85, 86, 88, 90, 92

This is the histogram those scores should produce with bins 10 points wide. Use it as the answer key.

Method 1: The Built-In Histogram Chart

Microsoft’s support page defines a histogram as a column chart that shows frequency data. Excel’s Analysis ToolPak page adds that from Excel 2016 you can create a histogram chart directly, and the support article lists Excel 2016, 2019, 2021, 2024 and Microsoft 365 among the versions it applies to.

Steps in Excel for Windows

1. Select your data. Select the cells with the scores, header included.
2. Open the Insert tab. Select Insert Statistic Chart, then Histogram.
3. Or use Recommended Charts. Select Recommended Charts, open the All Charts tab and pick Histogram.
4. Style the chart. Click anywhere in the chart to bring up the Chart Design and Format tabs.

Excel picks the bins for you. Its automatic bins often land on awkward numbers, which is why the next section matters.

Steps in Excel for Mac

Microsoft’s instructions for Excel for Mac are nearly the same: select the data, open the Insert tab, select the Statistical chart icon, and under Histogram choose Histogram. The Chart Design and Format tabs appear when you click the chart.

In Word and PowerPoint

The same chart exists in Word and PowerPoint. Microsoft’s steps: select Insert > Chart, choose Histogram under All Charts and select OK, then use Select Data on the Chart Design tab to enter your numbers.

How to Change the Bins in an Excel Histogram

Right-click the horizontal axis of the chart, choose Format Axis, and open Axis Options. Microsoft lists these settings.

Automatic

The default. Microsoft says the bin width is calculated with Scott’s normal reference rule, which tries to keep the histogram close to the data while assuming the data are roughly normally distributed. Scott’s rule sets the width to 3.49 times the standard deviation divided by the cube root of the number of values, according to Wikipedia’s article on histograms. The result is statistically sensible, but it is rarely a round number.

Bin Width

Type a width, such as 10 or 5. This is the setting most people want, because round widths give readable labels. Here are the same scores with a width of 5.

Number of Bins

Type how many bins you want and Excel works out the width. Microsoft notes that the number includes the overflow and underflow bins if you turn them on.

Overflow and Underflow Bins

These catch the extremes. The Overflow bin collects all values above the number you type. The Underflow bin collects all values below or equal to its number. Use them when a few extreme values would otherwise stretch the axis. For example, an underflow bin at 60 puts every score of 60 or less into one bar at the left.

Be careful with them. An overflow bin hides how far the tail really goes, and it is usually wider than the other bins, so its bar is not comparable with the rest. Say so in the chart title or a note.

By Category

This option is for text, not numbers. Excel groups identical text values and sums them. For categories, a regular column or bar chart is usually clearer.

Method 2: The Analysis ToolPak

The Analysis ToolPak is a free add-in that ships with Excel. Microsoft describes its Histogram tool as calculating individual and cumulative frequencies for a range of data and a range of bins. It is much older than the histogram chart: Kent State University’s course notes describe it with the menus of early Excel versions. That makes it the route for any version older than Excel 2016.

Load the ToolPak

On Windows, Microsoft’s steps are:

1. Select File > Options, then the Add-Ins category.
2. In the Manage box, choose Excel Add-ins and select Go.
3. Check Analysis ToolPak and select OK. If Excel says it is not installed, select Yes to install it.

On a Mac, select Tools > Excel Add-ins, check Analysis ToolPak, select OK, then quit and restart Excel. Either way, Data Analysis now appears on the Data tab.

Type the Bin Numbers

The ToolPak needs a column of bin numbers. Each number is the upper boundary of its class, as Kent State University’s course notes put it. For whole-number scores, the classes 50 to 59, 60 to 69 and so on end at 59, 69, 79, 89 and 99. Type those five numbers in column C under a header such as Bin.

Ending each class on 59 rather than 60 is deliberate. A score of exactly 60 then falls into the 60s class, where a reader expects it. OpenStax’s Introductory Statistics textbook recommends the same idea from the other side: put boundaries half a unit away from the data, so no value can land on one.

Run the Histogram Tool

The textbook Business Statistics, adapted from OpenStax and published on LibreTexts, walks through the dialog:

1. Select Data > Data Analysis, choose Histogram and select OK.
2. In Input Range, select the scores.
3. In Bin Range, select the bin numbers.
4. Choose an Output Range, such as cell E1, or a new worksheet.
5. Check Chart Output and select OK.

Excel writes a frequency table at the output cell, one row per bin, and with Chart Output checked it also draws a chart of the counts.

Close the Gaps Between the Bars

The ToolPak chart is an ordinary column chart, so the bars have gaps. A histogram’s bars should touch, because the bins are a continuous number line. The LibreTexts guide fixes it this way: click the bars, choose Format Data Series, and set Gap Width to 0%.

You may also want to relabel the axis. The ToolPak labels each bar with its upper boundary, such as 69, and Kent State’s notes change those labels by hand to ranges such as 60 to 69.

Method 3: The FREQUENCY Function

FREQUENCY counts how many values fall within each range and returns the counts as a vertical list. It is the most flexible method, and the counts update when the data change.

The Syntax

=FREQUENCY(data_array, bins_array)

• data_array is the range with your values, such as A2:A41.
• bins_array is the range with your bin numbers, such as C2:C6.

Microsoft points out two details. The result has one more cell than there are bins, and the extra cell counts the values above the highest bin. And FREQUENCY ignores blank cells and text, so stray labels in the column do no harm.

Enter It

In current Microsoft 365, type the formula in one cell, D2, and press Enter. The counts spill down into D2:D7 on their own. In older versions, select D2:D7 first, type the formula, and press Ctrl+Shift+Enter to enter it as an array formula. Excel adds curly brackets around it.

The Result for the Sample Scores

The counts add up to 40, the number of scores. That quick sum is the best check that every value was counted once.

Turn the Counts Into a Chart

Select the class labels and the counts, insert a Clustered Column chart, and set the Gap Width to 0% as above. The result matches the histogram at the top of this page.

Or skip the chart step: paste the class and count columns into our histogram maker in frequency table mode and it draws the histogram at once.

Open this frequency table in the histogram maker

Which Method Should You Use?

How to Make an Excel Histogram Start at 0

This is one of the most common questions about Excel histograms. The Format Axis options that Microsoft documents for the built-in chart include a bin width and a bin count, but no setting for where the first bin starts. There are two practical fixes.

Use Your Own Bins

With the ToolPak or FREQUENCY, the bins are whatever you type. Start the bin column at 9, 19, 29 and so on, and the chart begins at zero. Classes with no data show as empty bars, which is correct: Wikipedia’s article on the histogram notes that empty intervals should be shown, not skipped.

Ask Whether You Need It

A histogram’s vertical axis should start at zero, because bar heights are counts. The horizontal axis is different: it is a number line, and starting it at the data is normal. Start the bins at zero only when zero means something to the reader, such as a scale that runs from 0 to 100.

Two Data Sets on the Same Bins

To compare two groups, such as two classes that took the same test, give both the same bins. With FREQUENCY, put the second group’s scores in another column and run the function a second time against the same bin numbers. Place the two count columns side by side and insert a clustered column chart. Each bin then shows two bars, one per group.

If the groups differ in size, compare percentages rather than counts: divide each count by the group total. Our histogram maker has a Percent option for the same reason.

Common Problems and Fixes

The Histogram Chart Is Missing

The built-in chart needs Excel 2016 or newer. In an older version, use the ToolPak or FREQUENCY.

Data Analysis Does Not Appear on the Data Tab

The ToolPak is not loaded. Follow the steps above, and on a Mac, restart Excel afterward as Microsoft instructs.

Some Values Are Not Counted

Microsoft notes that FREQUENCY ignores text, so a number stored as text, for example one pasted from a web page, is silently left out. If a count looks short, check that every cell holds a real number and convert any that do not.

The Counts Do Not Match Another Program

Programs handle a value that sits exactly on a bin boundary differently. Excel’s bin numbers are upper boundaries, while OpenStax, and our histogram maker, count a boundary value in the bin that starts there. Boundaries that sit half a unit away from the data make the question disappear.

The Bins Look Strange

Automatic bins from Scott’s rule are often awkward decimals. Set a round bin width instead. There is no single correct number of bins, and trying two or three widths is part of the job.

Histogram in Google Sheets

Google Sheets has a histogram chart too. Google’s help page describes it as showing the distribution of a data set across buckets, with each bar’s height equal to the count in its range. To change the bins, double-click the chart and select Customize. The Histogram options there let you change the bucket size or the outlier percentile, or show item dividers.

Make a Histogram Without Excel

If you only need the chart, you do not need a spreadsheet program at all. Our histogram maker runs in the browser, with nothing to install and no account.

1. Copy the column of numbers from Excel or Google Sheets.
2. Paste it into the histogram maker. Anything that is not a number is skipped and reported.
3. Set the bin width and start, or keep the automatic bins.
4. Download a PNG or SVG, or copy the image straight into your document.

Open the sample scores in the histogram maker

For more on reading the result, see our guide What Is a Histogram?, and if you are not sure a histogram is the right chart, read bar graph vs histogram.