How to Make a Distribution Graph in Excel

Seeing how your data is spread out is often more useful than looking at a single number like the average. A distribution graph, or a histogram, gives you that full picture, revealing patterns you'd otherwise miss. This tutorial will walk you through exactly how to create and customize a distribution graph right within Excel.

What is a Data Distribution Graph?

A data distribution graph - most commonly a histogram in Excel - groups your numbers into ranges (called "bins") and then shows you how many data points fall into each range. Think about it with a list of recent customer order values. An average order value of $55 is helpful, but a distribution graph answers much more interesting questions:

• Are most people spending a small amount, with a few big spenders skewing the average up?
• Is there a "sweet spot" price range where most purchases happen?
• Are there any unusual gaps or clusters in spending habits?
• Does the data follow a classic bell curve, or is it skewed in one direction?

This visual understanding is the foundation of good data analysis. Instead of relying on a single metric, you get to see the shape and texture of your data, making it easier to spot trends, outliers, and opportunities.

Step 1: Get Your Data Ready in Excel

Before you can build a chart, your data needs to be in the right format. This is the simple part: a distribution graph works with a single column of raw numerical data. You don't need summaries, totals, or complex tables. Just one long list of numbers.

For this tutorial, we'll use a common marketing dataset: Website Session Durations (in seconds). Here is our sample data in a single column in Excel:

25 151 35 78 95 210 45 63 112 180 32 48 55 240 88 10 75 99 124 15 68 130 42 280 91

Your goal is to have your numbers organized just like that, ready for analysis.

Method 1: Using Excel's Built-in Histogram Chart

For modern versions of Excel (Excel 2016, a.k.a. Office 365 and newer), creating a histogram is straightforward using the built-in chart types. It’s the fastest and most flexible approach.

1. Select Your Data: Click and drag to highlight the entire single column of your numerical data, including the header if you have one.
2. Insert the Chart: Navigate to the Insert tab on Excel's ribbon. In the Charts section, look for a small icon that looks like a blue column chart and click the dropdown arrow for "Insert Statistic Chart". Here, select the first option under "Histogram."

Excel will instantly drop a basic histogram onto your worksheet. It automatically analyzes your data range and creates a set of default bins to display the distribution.

Step 2: Customizing Your Distribution Graph

Excel does a great job creating a starting point, but the default settings rarely tell the most compelling story. The real value comes from tailoring the chart to better fit your specific data. Customizing the "bins" - the numeric ranges along the bottom axis - is the most important adjustment you'll make.

Adjusting the Bins (The Most Important Part)

Having the right bin size is crucial. Too many bins, and your chart will look jagged and noisy without a clear pattern. Too few bins, and you risk oversimplifying the data and hiding important details.

To adjust the bins:

1. Right-click on the horizontal (X-axis) labels at the bottom of your chart (e.g., the labels that might say "[10, 68]").
2. From the menu that appears, select Format Axis....
3. A formatting panel will open on the right side of Excel. Make sure the Axis Options tab (the icon that looks like a bar chart) is selected.

Here, you have a few powerful options for defining your bins:

• Bin Width: This is my preferred method. It lets you set a fixed numerical range for each bin. For our website session data, telling Excel to use a Bin Width of 50 would create logical groupings of 0-50 seconds, 51-100 seconds, 101-150 seconds, and so on. This makes the chart easy to interpret.
• Number of Bins: This option lets you define how many bars you want to see in total. If you set this to 5, Excel will do the math to divide your data's entire range into five equal bins. This is useful when you want a high-level overview.
• Overflow Bin: This is handy for catching outliers. If you check this box and enter 250, it creates a final bin labeled "≥250" which groups all values of 250 and higher. It prevents one or two very high values from stretching out your axis unnecessarily.
• Underflow Bin: The same concept, but for the low end. Checking this and entering 30 would create an initial group for all values of 30 or less.

Play with the Bin Width or Number of Bins until the chart clearly reveals the underlying shape of your data's distribution.

Improving Chart Readability

Once your bins are set, a few cosmetic tweaks can make your chart clearer and more professional:

• Give It a Title: Double-click the default chart title and change it to something descriptive. For our example, "Distribution of Website Session Durations" is much better than "Histogram."
• Label Your Axes: Click on the chart, then click the green "+" button that appears to the top right. Check the box for Axis Titles. You can now add a title for the vertical (Y) axis, like "Number of Sessions," and the horizontal (X) axis, like "Session Duration (Seconds)."
• Add Data Labels: Using that same green "+" button, you can check the box for Data Labels to show the exact count on top of each bar.

Method 2: Using the Analysis ToolPak (for Older Excel Versions)

If you're using an older version of Excel (before 2016), you won't have the built-in histogram chart. Instead, you'll need to use a free add-in called the Analysis ToolPak. It’s a bit more manual but gets the same job done.

Enabling the Analysis ToolPak

It comes with Excel, but you have to activate it first.

1. Go to File > Options > Add-ins.
2. At the bottom of the window, next to "Manage," make sure Excel Add-ins is selected and click the Go... button.
3. In the new dialog box, check the box next to "Analysis ToolPak" and click OK. A new "Data Analysis" button will now be available on your Data tab.

Creating the Histogram with the ToolPak

This method requires you to first define your Bins in a separate column yourself.

1. Define Your Bins: In a separate column, create a list of the upper limits for the ranges you want. For our example, we could create a "Bin" column with the values: 50, 100, 150, 200, 250, 300. This tells Excel to create buckets for ≤50, 51-100, 101-150, and so on.
2. Open the Histogram Tool: Go to the Data tab and click the Data Analysis button.
3. From the pop-up list, select Histogram and click OK.
4. Fill In the Details:

• Input Range: Select your single column of raw session duration data.
• Bin Range: Select the column of bin values you just created.
• Output Range: Choose a blank cell on your worksheet where you want the report table and chart to be created.
• Make sure to check the Chart Output box at the bottom.

1. Click OK.

Excel will generate a frequency table with the counts for each bin and a corresponding column chart. The main difference is this chart will have gaps between the bars. To make it look like a true histogram, right-click on one of the bars, select Format Data Series..., and in the panel that opens, slide the Gap Width down to 0%.

Step 3: What Is Your Distribution Graph Telling You?

Creating the graph is only half the battle. Now you can analyze it to find insights.

• Look for the shape: Based on our session duration data, you'll likely see a chart with a big pile of values on the left side and a long, tapering "tail" stretching out to the right. This is called a right-skewed distribution. It's very common and tells us that most sessions are short, but a few sessions are extremely long, pulling the average duration up.
• Other common shapes include:

Understanding the shape of your data helps you select better statistical models and, most importantly, provides real context beyond a simple average.

Final Thoughts

Creating a distribution graph in Excel elevates your analysis from simple averages to a clear visual map of how your data behaves. Whether using the modern built-in chart or the classic Analysis ToolPak, understanding its shape - be it a bell curve or heavily skewed - is the first step toward uncovering genuinely useful insights.

While Excel is great for a static analysis, the reality is often messier. You spend too much time pulling fresh data from Google Analytics, Salesforce, or Shopify, tidying it up, and rebuilding the same reports week after week. We built Graphed because we believe there is a better way. We connect directly to your apps for live data and allow you to build real-time dashboards just by asking in plain English - like, “show me the distribution of order values from Shopify last month.” The entire process takes seconds, not hours, so you can spend your time acting on your data's story instead of just trying to find it.