What a histogram does and when to use it

A histogram is a chart that shows how often values fall into ranges. Unlike a bar chart that compares separate categories, a histogram groups continuous data — like test scores, heights, or daily temperatures — into buckets and displays how many items land in each bucket. Excel builds this for you once you tell it which data to use and how wide each bucket should be.

Use a histogram when you want to see the shape of your data: whether most values cluster in the middle, whether they spread evenly, or whether outliers exist. A histogram of monthly sales shows you whether revenue is consistent or lumpy. A histogram of customer ages shows you whether your audience skews young, old, or mixed. A bar chart cannot show this pattern because it treats each value as a separate category instead of grouping them.

Key Takeaways

  • Excel's histogram tool lives in the Data Analysis Toolpak, which you may need to turn on first in the Add-ins menu.
  • Your data should be a single column of numbers with no text or blank cells mixed in — clean it before you start.
  • You choose the bucket width (called "bin size"), which controls how detailed the histogram looks: smaller bins show more detail, larger bins show the overall shape.
  • Excel creates the histogram as a new chart on your sheet, and you can resize it, move it, and change its title like any other chart.

Prepare your data in a single column

Open the spreadsheet with the numbers you want to chart. Select all the values you plan to histogram — they must be in one column, with no text labels or blank cells in the middle. If your data has a header row (like "Sales" or "Test Scores"), include it; Excel will recognize it as a label. If your column has gaps or text mixed in, delete the gaps and move text to a separate column first.

For example, if you have monthly revenue in column B from row 2 to row 25, select cells B2:B25. Do not include the column header in your selection unless it is a single cell at the top. Once selected, note the range — you will reference it in the next step.

Turn on the Data Analysis Toolpak if needed

Excel's histogram tool is part of an add-in called the Data Analysis Toolpak. On Windows, click the File tab, then Options, then Add-ins. At the bottom of the window, find the dropdown that says "Manage" and select "Excel Add-ins", then click Go. Check the box next to "Analysis ToolPak" and click OK. On Mac, click Tools in the menu bar, then Add-ins, then check "Analysis ToolPak" and click OK.

Once enabled, the Data Analysis Toolpak stays on. You will see a new "Data Analysis" button in the Data tab of the ribbon (Windows) or the Tools menu (Mac). If you do not see it after enabling, close and reopen Excel.

Open the histogram dialog and set your bin size

With your data column selected, click the Data tab (Windows) or Tools menu (Mac), then click Data Analysis. A list of analysis tools appears. Scroll down and click Histogram, then click OK. A dialog box opens with fields for Input Range, Bin Range, and Output Options.

The Input Range should already show your selected column. Leave it as is. For Bin Range, you have two choices: let Excel decide the bucket width automatically, or specify your own. To let Excel decide, leave the Bin Range field empty. To control the bucket width yourself, create a separate column with the upper limit of each bucket (for example, 10, 20, 30, 40 if you want buckets of 10), then enter that column's range in the Bin Range field. For most first histograms, leave Bin Range empty and let Excel choose.

Choose where the histogram appears and create it

In the Output Options section, select "Output Range" and click the field next to it, then click an empty cell on your sheet where you want the histogram to appear — usually a few columns to the right of your data. You can also select "New Worksheet" to put the histogram on a separate sheet. Check the box for "Chart Output" so Excel creates a visual chart, not just a table of numbers.

Click OK. Excel builds the histogram and places it on your sheet. The chart shows bars for each bucket, with the height of each bar representing how many values fell into that range. The x-axis shows the bucket ranges, and the y-axis shows the count.

Adjust the histogram if the buckets are too wide or too narrow

If the histogram looks too lumpy (many thin bars with gaps) or too smooth (just a few very tall bars), you can change the bucket size. Right-click one of the bars in the histogram and select "Format Data Series". Click the Series Options tab. Under "Gap Width", you can adjust spacing, but to actually change bucket size, you need to go back and run the histogram again with a different Bin Range.

Create a new column with different bucket limits — try half the original size if the bars are too wide, or double the size if they are too narrow — then run Data Analysis > Histogram again, this time entering that new column in the Bin Range field. Delete the old histogram and keep the new one. This trial-and-error process is normal; bucket size depends on your data and what story you want the histogram to tell.

Customize the chart title and labels

Click the histogram chart to select it. At the top of the chart, you will see a title like "Histogram". Click the title text to edit it — change it to something descriptive like "Monthly Revenue Distribution" or "Customer Age Range". Press Enter to save the change.

To add axis labels, right-click the chart and select "Edit Chart" (or double-click it), then click the Design tab in the ribbon. Click "Add Chart Element" and select "Axis Titles". Add a title for the horizontal axis (the buckets) and the vertical axis (the count). For example, label the x-axis "Revenue ($)" and the y-axis "Number of Months". Click outside the chart when done to deselect it.

Frequently Asked Questions

What is the difference between a histogram and a bar chart in Excel?

A bar chart compares separate categories — like sales by region or product type — where each bar is independent. A histogram groups continuous numbers into ranges and shows how many fall into each range. Use a bar chart for categories, a histogram for continuous data like measurements or time periods.

Can I change the bucket size after Excel creates the histogram?

Yes, but you must create a new histogram. Make a new column with different bucket limits, run Data Analysis > Histogram again with that column as the Bin Range, and delete the old chart. Excel does not let you adjust bucket size by editing the existing chart.

What if my data has negative numbers?

Histograms work with negative numbers. If your data ranges from -50 to 100, create a Bin Range column that covers that full span, like -50, 0, 50, 100. Excel will create buckets accordingly.

Why does my histogram show a blank bar at the end?

Excel sometimes adds a final bucket for values that exceed your highest bin limit. If your data ends at 95 but your highest bin is 90, Excel creates a bucket for 90+. Adjust your Bin Range to end above your highest data value to avoid this.

Can I make the histogram look different — different colors, different style?

Yes. Right-click the bars in the histogram and select "Format Data Series" to change bar color, outline, or transparency. Right-click the chart background to change the overall chart style. These changes work the same way as any other Excel chart.