How to Make a Histogram in Excel (3 Methods)

A histogram is one of the most effective statistical charts for uncovering the shape, spread, center, and skewness of continuous numerical data. Whether you are analyzing exam scores, customer wait times, product weights, or financial returns, grouping observations into intervals (called bins) reveals patterns that raw columns of numbers conceal.

Microsoft Excel provides three distinct ways to build a histogram:

1. The Native Statistic Chart (Excel 2016, 2019, 2021, and Microsoft 365) — the fastest, one-click method.
2. The Analysis ToolPak Add-in (all versions) — the traditional approach for custom frequency tables.
3. The FREQUENCY Function + Column Chart — the fully dynamic method for complete control over bins and styling.

If you need a publication-ready histogram in seconds without fiddling with Excel’s clunky formatting menus, you can also use our free online Histogram Maker with automated binning formulas and instant SVG/PDF export. For small datasets under 50 observations, consider checking our Dot Plot vs Histogram comparison to see if a dot plot is a better fit.

Below is the complete, version-accurate walkthrough for each method.

---

Method 1: The Built-in Histogram Chart (Excel 2016 and Later)

If you are using Microsoft 365, Excel 2021, Excel 2019, or Excel 2016, Excel features a built-in statistical histogram engine that automatically calculates bin ranges and creates an interactive chart object.

Step-by-Step Instructions

1. Select your data: Highlight your continuous numerical data column, including the column header (e.g., cells A1:A51).
2. Open the Insert tab: Click Insert on the top Excel Ribbon.
3. Locate Statistic Charts: In the Charts group, click the Insert Statistic Chart icon (a blue histogram icon).
4. Choose Histogram: Under the Histogram category, click the first Histogram chart option.
5. Review the generated chart: Excel will instantly plot a histogram on your worksheet, automatically estimating bin counts and widths using Scott’s variation rule.

Ribbon Path:
Insert  ──>  Charts Group  ──>  Insert Statistic Chart (Icon)  ──>  Histogram

Advantages & Limitations of Method 1

• Pros: Dynamic chart object. If you update or add numbers in your data range, the histogram recalculates and refreshes automatically.
• Cons: Excel defaults to automatic decimal intervals (such as [10, 24.5], (24.5, 39]), which look cluttered. You must manually customize axis formatting to get round, professional bin numbers.

---

Method 2: The Analysis ToolPak (Legacy & Custom Bins)

The Analysis ToolPak is a built-in Excel add-in that has existed since early versions of Excel (2007, 2010, 2013). Unlike Method 1, the ToolPak allows you to define your exact custom bin upper limits before generating the visualization.

Step 1: Enable the Analysis ToolPak

If you have never used statistical tools in Excel, the Analysis ToolPak is usually disabled by default.

1. Click File in the top-left corner and select Options (or Excel > Preferences > Add-ins on Mac).
2. In the Excel Options dialog, click Add-ins on the left navigation bar.
3. At the bottom, make sure the Manage dropdown says Excel Add-ins, then click Go….
4. In the Add-ins pop-up window, check the box next to Analysis ToolPak (do not select VBA unless you write macros).
5. Click OK. A new Data Analysis button will now appear on the far right of the Data tab on your Ribbon.

Step 2: Set Up Your Bin Range

In a blank column next to your raw data, create your Bin Range. Enter the upper limit of each desired interval in ascending order:

Raw Data (Column A)	Bin Limits (Column C)	Meaning of Interval
14	20	Values $\le 20$
28	40	Values $> 20$ and $\le 40$
35	60	Values $> 40$ and $\le 60$
52	80	Values $> 60$ and $\le 80$
77	100	Values $> 80$ and $\le 100$

Step 3: Run the Histogram Analysis

1. Switch to the Data tab on the Ribbon and click Data Analysis.
2. Scroll down the list of statistical tools, select Histogram, and click OK.
3. Configure the fields in the Histogram dialog:
   • Input Range: Select your raw data cells (e.g., $A$1:$A$51).
   • Bin Range: Select your custom bin column (e.g., $C$1:$C$6).
   • Labels: Check this box if you included headers in your selections.
   • Output Range: Click an empty cell on your worksheet where the output table should start (e.g., $E$1).
   • Chart Output: Check this box (if you forget this checkbox, Excel only outputs numbers without a chart).
4. Click OK.

Excel will produce an exact frequency distribution table along with a column chart.

Critical Warning: ToolPak outputs are static snapshots. If you edit, add, or delete values in your source data, the chart and table will not update. You must re-run the entire Data Analysis dialog every time your data changes.

---

Method 3: The FREQUENCY Function + Clustered Column Chart (Fully Dynamic Custom Bins)

For advanced financial modeling or dynamic dashboards where you require both custom bin cutoffs and automated real-time formula updates, combine Excel’s =FREQUENCY() array function with a standard 2-D column chart.

Step 1: Set Up Bins and Formula

1. Write your custom upper bin limits in a column (e.g., C2:C6 with values 20, 40, 60, 80, 100).
2. In the adjacent column (D2:D7), calculate the frequencies using the formula:

   =FREQUENCY(A2:A51, C2:C6)

   • In Microsoft 365 & Excel 2021: Simply press Enter. The dynamic array formula will automatically “spill” down the column, including an extra final row for numbers exceeding your top bin.
   • In Legacy Excel (2019 and older): Highlight all destination cells (D2:D7), type the formula in the formula bar, and press Ctrl + Shift + Enter (CSE formula).

Step 2: Insert and Style the Chart

1. Select your bin labels and calculated frequency counts.
2. Go to Insert > Charts > Insert Column or Bar Chart > 2-D Clustered Column.
3. Make the bars touch (a true histogram requirement):
   • Right-click any column in the chart and select Format Data Series….
   • In the sidebar panel, under Series Options, locate Gap Width.
   • Drag the slider or type 0% (or 2% to 5% if you prefer a subtle gap).
   • Under Fill & Line, add a thin contrasting border (e.g., #243040 or white) so adjacent bars remain visually distinct.

---

How to Change Bin Width in Excel

If you used Method 1 (the native 2016+ chart), Excel’s default bin width is rarely optimal. You can adjust the binning parameters directly from the chart axis.

Format Axis Options

1. Double-click the horizontal (X) axis of your histogram chart, or right-click the numbers and choose Format Axis….
2. In the Format Axis pane that appears on the right, expand the Axis Options section.
3. Review the available bin settings:

• By Category: Groups by distinct text values (used when plotting categorical groups rather than numerical intervals).
• Automatic: Excel’s internal formula estimates bin width based on sample variance.
• Bin Width: Enter a specific numerical span. For example, typing 10 forces each bar to span exactly 10 units (e.g., 50–60, 60–70).
• Number of Bins: Type an exact total number of bars. Excel calculates the necessary bin width automatically.
• Overflow Bin: Check this box and enter a threshold (e.g., 90) to group all numbers greater than 90 into a single catch-all tail bar.
• Underflow Bin: Check this box and enter a threshold (e.g., 20) to group all extreme low observations ($\le 20$) into a single lower tail bar.

When choosing the right bin width, remember that too few bins over-smooths your distribution and hides bimodality, while too many bins creates noisy, jagged spikes.

---

---

Excel Histogram Limitations (Why Analysts Look for Alternatives)

While Excel is standard office software, professional data analysts and educators frequently encounter major roadblocks when creating histograms in spreadsheets:

1. Static ToolPak Output: The Analysis ToolPak produces disconnected static tables that do not synchronize when source numbers change.
2. Confusing Axis Notation: Excel’s native histogram uses mathematical interval notation like (50, 65] and (65, 80], which confuses non-technical stakeholders and students who do not know whether the bracket means inclusive or exclusive.
3. No Lossless Vector Export: Excel cannot export charts directly as scalable vector graphics (SVG) or publication-ready PDF files. Copying into reports or PowerPoint often degrades resolution.
4. Rigid Formatting Restrictions: You cannot easily overlay normal distribution curves, toggle mean/median markers, or compute formal outlier fences without writing complex custom VBA or secondary formula columns.

---

---

Frequently Asked Questions

1. Which versions of Excel support native built-in histogram charts?

Microsoft introduced native statistical histogram charts in Excel 2016. They are available in Excel 2016, Excel 2019, Excel 2021, and all Microsoft 365 subscription versions for both Windows and Mac. For Excel 2013 and older, you must use the Analysis ToolPak or manual FREQUENCY formulas.

2. How do I enable the Analysis ToolPak in Excel?

Go to File > Options > Add-ins. At the bottom of the window, ensure Excel Add-ins is selected in the Manage dropdown, then click Go…. In the pop-up, check the box next to Analysis ToolPak and click OK. The tool will appear under the Data tab on your Ribbon.

3. How do I change the bin width of an Excel histogram?

Right-click the horizontal numbers on your histogram chart and click Format Axis…. In the right-hand sidebar under Axis Options, select the radio button for Bin width and enter your desired interval size (such as 5 or 10). Alternatively, choose Number of bins to specify how many total bars you want.

4. How do I make a histogram in Excel for Mac?

In Microsoft 365 or Excel 2016+ for Mac, highlight your data, click the Insert tab on the Ribbon, locate the Statistic Chart dropdown, and choose Histogram. If you prefer the ToolPak, go to Tools > Excel Add-ins, enable Analysis ToolPak, and access it via Data > Data Analysis.

5. Does an Excel histogram automatically update when new data is added?

If you created the chart using Method 1 (Insert > Statistic Chart) or Method 3 (FREQUENCY function), the chart updates dynamically whenever data changes. However, if you used Method 2 (Analysis ToolPak), the output is a static snapshot and will not update automatically.

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

A histogram displays the frequency distribution of continuous numerical data along an unbroken number line, with bars touching each other. A bar chart displays distinct, discrete categorical groups (e.g., departments, countries) with visible gaps separating the bars.

7. Is there a faster, free alternative to Excel for making histograms?

Yes. Our Free Histogram Maker runs directly in your web browser with zero software installations or signups. Simply paste numbers straight from your spreadsheet to generate high-resolution SVG and PNG charts with automatic bin calculations.

8. Can I make a histogram in Google Sheets?

Yes. In Google Sheets, select your data, click Insert > Chart, and in the Chart Editor sidebar, change the Chart Type dropdown to Histogram chart. You can adjust bucket size under the Customize > Histogram tab. (A dedicated in-depth Google Sheets histogram guide is coming soon).

---