How to Graph a Histogram in Excel

A histogram is one of the most useful charts for understanding your data, but making one in Excel can feel a little confusing if you don't know where to look. It’s perfect for seeing how your data is distributed - for example, figuring out the most common order values from your Shopify store, the age ranges of your customers, or the test scores of a class. This guide breaks down exactly how to create and customize a histogram in Excel, from preparing your data to fine-tuning the final chart.

What Is a Histogram, Anyway?

Before jumping into the how-to, it’s helpful to quickly clarify what a histogram actually does. A histogram visualizes the frequency distribution of a set of continuous data. In simpler terms, it groups numbers into ranges (called "bins") and then shows how many values fall into each range using bars.

For example, if you have a list of 100 customer orders, a histogram doesn't just list all 100 prices. Instead, it might show you that 15 orders were between $0 and $10, 40 orders were between $10 and $20, 30 were between $20 and $30, and so on. This immediately gives you a feel for purchasing patterns.

Histogram vs. Bar Chart: What's the Difference?

This is a common point of confusion. While they both use bars, they show different types of information:

• A Bar Chart compares discrete, separate categories. Think of things like "Sales per Country" or "Website Traffic by Social Media Channel." Each bar is a distinct category.

• A Histogram visualizes the distribution of a single continuous variable. The bars represent sequential ranges of a single data set (like temperature, age, or price), and for this reason, the bars in a histogram typically touch to show that the scale is continuous.

Preparing Your Data for a Histogram

The good news is that histograms require very simple data formatting. All you need is a single column of numerical data. Whether it's a list of student exam scores, response times from a support desk, or session durations from Google Analytics, the process starts with this raw list.

Here's an example data set we will use, representing the dollar amount of recent orders for an online store. For best results, your data should be in a single column in your Excel sheet:

Example Data: Order Values

$35.10$12.50$48.22$25.00$33.78$19.99$55.00$28.50$15.20$21.75$42.00…and so on

Before you build your chart, quickly scan your column to make sure it contains only numbers. Any text entries or errors will cause problems when Excel tries to build the histogram.

Method 1: Using the Built-in Histogram Chart (The Easy Way)

If you're using Excel 2016 or newer (including Microsoft 365), you have access to a fantastic, built-in histogram chart type that does most of the heavy lifting. It's quick, interactive, and by far the recommended method.

Step-by-Step Instructions

1. Select Your Data: Click anywhere inside your data, and Excel will automatically detect the range. Alternatively, you can click and drag to highlight all the numerical data you want to include in the histogram (including the column header if you have one).

2. Insert the Chart: Navigate to the Insert tab on the Excel ribbon. In the Charts section, click on the icon that looks like a small blue bar chart, which is the "Insert Statistic Chart" menu.

3. Choose Histogram: From the dropdown, select the first option under the "Histogram" heading.

Voilà! Excel will immediately generate a histogram on your worksheet. It automatically analyzes your data and groups it into a default number of bins.

Customizing and Refining Your Histogram

The default chart is a great start, but the real power comes from tailoring it to better tell your data's story. Usually, this means adjusting the bins.

To do this, right-click on the horizontal axis (the one showing your data ranges at the bottom of the chart) and select "Format Axis...". This opens up a side panel with all your options.

Axis Options: Bins

In the "Format Axis" pane, you’ll find several ways to group your data:

• Automatic: This is the default setting where Excel makes its best guess for bin grouping. It's often pretty good, but you'll usually want more control.

• Bin Width: This is one of the most useful options. It lets you set a specific size for each range. For our order value example, you could set the "Bin width" to 10 to group orders in increments of $10 ($0-10, $10-20, $20-30, etc.). This makes the chart very easy to interpret.

• Number of Bins: Use this if you have a specific number of bars you want to see. For instance, if you want to see your data broken down into 5 main groups, you can enter "5" here.

Overflow and Underflow Bins

These two options are incredibly helpful for dealing with outliers without letting them distort your chart.

• Overflow Bin: Check this box and enter a value to group everything above that number into a single bar. For example, if most orders are under $100 but you have a few over $500, you could set an overflow bin at $100 to group all the large orders together into a bar labeled "≥$100.00".

• Underflow Bin: This does the opposite, grouping everything below a certain value. This works well for our ecommerce example to separate shipping fees from actual product sales. If shipping is under $10 you could set the underflow value there.

Method 2: Using the Analysis ToolPak (The Old-School Way)

If you're using an older version of Excel (2013 or earlier) or if you need to create a static frequency table alongside your chart, the Analysis ToolPak is your go-to. This method requires a few more manual steps and creates a static chart - meaning if your source data changes, you'll have to delete the chart and run the analysis again.

Step 1: Enable the Analysis ToolPak

The ToolPak is a free add-in from Microsoft, but it isn't enabled by default. Here's how to turn it on:

1. Go to File > Options.

2. Click on "Add-ins" on the left-hand menu.

3. At the bottom of the window, next to "Manage," make sure "Excel Add-ins" is selected and click "Go...".

4. In the new dialog box, check the box for "Analysis ToolPak" and click OK.

You should now see a "Data Analysis" button on the Data tab of the ribbon.

Step 2: Define Your Bins Manually

Unlike the modern chart type, this method requires you to define your bins upfront. In a separate column, type out the upper limit for each range you want. For our order value data, if we want to group by intervals of $10, our bin column would look like this:

Bins

102030405060

This tells Excel to count how many orders are between 0-10, 10.01-20, 20.01-30, and so on.

Step 3: Run the Histogram Tool

1. Go to the Data tab and click on "Data Analysis".

2. Select "Histogram" from the list and click OK.

3. Input Range: Select your raw numerical data (e.g., A1:A101).

4. Bin Range: Select the bin limits you just defined (e.g., B1:B7).

5. Check the Labels box if you included the headers in your selection.

6. Output Range: Choose a spot on your worksheet where you want the frequency table to be generated.

7. Chart Output: Check this box to create the histogram graph. Click OK.

Excel will then produce a frequency table and a corresponding static histogram chart.

Making Your Histogram Look Professional

Whether you used the new tool or the ToolPak, a few cosmetic tweaks can make your histogram much easier to read:

• Give it a Clear Title: Double-click the chart title and change it to something descriptive. "Histogram of Customer Order Values" is much better than "Histogram."

• Label Your Axes: Click the little "+" icon next to your chart and check the box for "Axis Titles." Label your horizontal (X) axis with what the data represents (e.g., "Order Value ($)") and your vertical (Y) axis with the count (e.g., "Number of Orders").

• Adjust the Gap Width: True histograms have no gaps between the bars. Right-click on one of the bars, choose "Format Data Series...", and in the "Series Options" pane, change the "Gap Width" to 0%. This will make the bars touch.

• Change the Color: Click on the bars, then go to the "Chart Design" or "Format" tab to pick a color scheme that fits your report or presentation.

Final Thoughts

Learning to create a histogram in Excel is a fantastic way to move beyond simple data tables and truly start visualizing what your numbers are telling you. Whether you use the modern chart type for interactivity or the Analysis ToolPak for older versions, the key is to experiment with your bin settings to find the 'view' that reveals the most useful insights about your data's shape and distribution.

Manually creating reports and digging for insights is exactly why we built Graphed. Instead of exporting CSVs and fighting with chart settings in Excel, you simply connect your data sources - like Shopify, Google Analytics, or your CRM - and describe what you want to see. You can ask, “create a histogram of Shopify order values last month,” and get a real-time, interactive visualization in seconds. Our goal is to eliminate the manual steps, so you can spend less time building charts and more time acting on the insights they provide.