Excel VBA: Create a Histogram Automatically with a Macro

VBA & macros · Updated November 21, 2026

Yes. You can create a histogram with Excel VBA by counting numeric values into bins and plotting those counts, or by using Excel’s built-in Histogram chart. The macro below reads measurements in column A, asks how many equal-width bins to use, writes a frequency table in columns D and E, and creates a column chart that looks like a histogram.

What a VBA histogram does

A histogram summarizes the distribution of numerical data. It groups observations into adjoining intervals, called bins, and shows how many values fall in each interval. For example, a list of delivery times might be grouped into ranges from 0 to 5 minutes, 5 to 10 minutes, and so on. A high bar means many observations fall within that range; a long tail or several peaks can reveal patterns that are hard to see in the raw list.

VBA can automate the steps of choosing or calculating bin boundaries, counting observations, and drawing a chart. The code here builds the frequency table itself, then uses a regular clustered-column chart with no gap between bars. This approach makes the bin boundaries and counts visible on the worksheet and gives you direct control over how the groups are calculated.

Excel for Microsoft 365 also has a built-in Histogram chart, so VBA is not necessary for a one-off chart. A macro is useful when you repeat the same report, need consistent bin settings, or want to produce charts for several data sets with less manual work.

Prepare the worksheet and choose bins

The sample macro assumes that the worksheet containing your data is active, that row 1 is a heading, and that measurements are in A2 downward. It writes its output to D:E and places the chart near G2. Before running it, make sure those output cells and the chart area are available; the macro overwrites the existing contents in D1:E for the bins it creates, and replaces a chart named HistogramChart if one is already present.

Decide what the data represents before selecting a bin count. A histogram can be misleading if its bins are too wide to show meaningful variation or so narrow that most bars contain only a few observations. Equal-width bins are a straightforward starting point, but they are not always the best choice. If a report needs fixed business thresholds—such as scores below 60, 60–69, and 70–79—use those meaningful cutoffs rather than automatically dividing the range into equal parts.

Check that the column contains measurements, not labels or totals mixed into the data. The code ignores blank cells and nonnumeric entries, and treats numeric text as a number when VBA recognizes it as numeric. Excel stores dates as numbers, so dates in the source column will be grouped by their underlying serial values, not as calendar periods. For monthly or daily counts, prepare suitable date boundaries or summarize the dates first.

VBA macro to build the histogram

In this example, the first bin starts at the smallest numeric value and the final bin ends at the largest. Bins are equal in width; a value on an internal boundary goes into the bin that starts at that boundary. The final bin includes the maximum value. If every observation is identical, the macro uses a width of 1, so those observations fall in the first bin.

In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste in this procedure. Return to Excel and run CreateHistogram from Developer > Macros. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon. Save a copy of the workbook before running the macro: VBA changes cannot be reversed with Ctrl+Z.

Sub CreateHistogram()
    Dim ws As Worksheet
    Dim source As Range
    Dim cell As Range
    Dim lastRow As Long
    Dim numericCount As Long
    Dim binCount As Long
    Dim b As Long
    Dim i As Long
    Dim answer As Variant
    Dim value As Double
    Dim minValue As Double
    Dim maxValue As Double
    Dim binWidth As Double
    Dim lowerBound As Double
    Dim upperBound As Double
    Dim frequency As Long
    Dim chartObject As ChartObject

    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then
        MsgBox "Put numeric data in A2 downward."
        Exit Sub
    End If
    Set source = ws.Range("A2:A" & lastRow)

    ' Find the numeric limits; ignore blanks, text, and errors.
    For Each cell In source.Cells
        If Not IsError(cell.Value2) Then
            If IsNumeric(cell.Value2) Then
                value = CDbl(cell.Value2)
                If numericCount = 0 Then
                    minValue = value
                    maxValue = value
                Else
                    If value < minValue Then minValue = value
                    If value > maxValue Then maxValue = value
                End If
                numericCount = numericCount + 1
            End If
        End If
    Next cell

    If numericCount = 0 Then
        MsgBox "No numeric observations were found in column A."
        Exit Sub
    End If

    answer = Application.InputBox( _
        Prompt:="How many equal-width bins? Enter a whole number from 1 to 100.", _
        Title:="Create histogram", Type:=1)
    If VarType(answer) = vbBoolean Then Exit Sub
    If answer < 1 Or answer > 100 Or answer <> CLng(answer) Then
        MsgBox "Enter a whole number from 1 to 100."
        Exit Sub
    End If
    binCount = CLng(answer)

    binWidth = (maxValue - minValue) / binCount
    If binWidth = 0 Then binWidth = 1

    ws.Cells(1, "D").Value = "Bin"
    ws.Cells(1, "E").Value = "Frequency"
    For b = 1 To binCount
        lowerBound = minValue + (b - 1) * binWidth
        upperBound = minValue + b * binWidth
        If b = binCount And maxValue > minValue Then upperBound = maxValue

        frequency = 0
        For Each cell In source.Cells
            If Not IsError(cell.Value2) Then
                If IsNumeric(cell.Value2) Then
                    value = CDbl(cell.Value2)
                    If b = binCount Then
                        If value >= lowerBound And value <= upperBound Then _
                            frequency = frequency + 1
                    Else
                        If value >= lowerBound And value < upperBound Then _
                            frequency = frequency + 1
                    End If
                End If
            End If
        Next cell

        ws.Cells(b + 1, "D").Value = Format$(lowerBound, "0.###") & " to " & _
                                     Format$(upperBound, "0.###")
        ws.Cells(b + 1, "E").Value = frequency
    Next b

    ' Replace only the chart created by this macro.
    For i = ws.ChartObjects.Count To 1 Step -1
        If ws.ChartObjects(i).Name = "HistogramChart" Then
            ws.ChartObjects(i).Delete
        End If
    Next i

    Set chartObject = ws.ChartObjects.Add( _
        Left:=ws.Range("G2").Left, Top:=ws.Range("G2").Top, _
        Width:=480, Height:=300)
    chartObject.Name = "HistogramChart"
    With chartObject.Chart
        .ChartType = xlColumnClustered
        .SetSourceData Source:=ws.Range("D1:E" & binCount + 1)
        .HasTitle = True
        .ChartTitle.Text = "Histogram"
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Text = "Value range"
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Text = "Frequency"
        .ChartGroups(1).GapWidth = 0
    End With

    MsgBox numericCount & " numeric observations grouped into " & _
           binCount & " bins."
End Sub

Run it, then check the result

The InputBox accepts a whole-number bin count from 1 to 100. After you enter a value, the macro scans the source range to find the minimum and maximum, calculates equal-width intervals, counts the values in each interval, and writes one row per bin below the headings in D1:E1. The chart is positioned at G2, so it may overlap other worksheet content there.

Inspect the frequency table before relying on the chart. The sum of the frequencies should equal the number of numeric observations reported in the completion message. If it does not, check whether the source range includes unexpected values or whether you modified the boundary logic. The displayed bin labels are formatted to at most three decimal places for readability; that display formatting does not change the actual numeric boundaries used in the counts.

The procedure counts each value once for every bin, so its work increases with both the number of observations and the number of bins. That is generally fine for ordinary worksheet-sized lists, but a very large data set can make this nested counting approach slower. For repeated reports with very large ranges, consider calculating a frequency table with a more efficient method or using Excel’s built-in chart instead.

Adjust the chart and bin behavior

The macro’s bin count is an input, while its bin width is calculated from the observed range. To use different boundaries, change the section that sets lowerBound and upperBound and ensure that every value belongs to exactly one interval. A common convention is to include the lower boundary and exclude the upper boundary for all but the final bin. Including both ends of every interval would count a value on a shared boundary twice.

To change the chart’s appearance, adjust its width, height, position, title, or axis titles in the ChartObjects and Chart properties. A histogram usually has touching bars, which is why the code sets GapWidth to zero. If you deliberately want visible gaps, use a positive gap width. The chart is technically a clustered-column chart, not Excel’s specialized Histogram chart type; its categories are the text labels in column D and its bar heights are the frequencies in column E.

For a manually maintained report, you can edit the table in D:E and format the chart without changing the source data. If you run the macro again with fewer bins, old rows below the new table may remain on the sheet, but they are not included in the chart because its source range ends at the current bin count. Clear those unused rows yourself if you want a tidier worksheet.

Built-in Histogram chart or VBA?

For a quick chart in Excel for Microsoft 365 on Windows, select the numeric data and choose Insert > Insert Statistic Chart > Histogram. You can then right-click the horizontal axis, choose Format Axis, and adjust the bin settings in the Format Axis pane, including the number or width of bins where those options are available. This chart is purpose-built for distributions and avoids writing a macro.

VBA is a better fit when the same steps must be repeated, when a report needs a frequency table alongside its chart, or when you need to encode a specific set of rules. The sample macro also makes its bin-count choice explicit. The built-in chart can be more convenient for exploring settings interactively, while a macro can make recurring output consistent. Neither automatically makes a chart statistically appropriate: the choice of bins still affects what readers see.

Excel for the web can display a workbook containing a macro, but it cannot run or edit VBA macros. Excel desktop for Mac supports VBA, although keyboard shortcuts and some interface details differ from Windows; use the Mac Visual Basic Editor and macro controls available in that Excel version. The code uses standard worksheet and chart objects rather than Windows-only automation, but confirm behavior in your organization’s Mac setup before distributing a macro-enabled workbook.

Common problems and safe macro use

  • The macro does not appear in the Macros list: confirm the code is in a standard module, not a worksheet or ThisWorkbook code pane, and that the file is macro-enabled.
  • The chart is empty or counts are zero: check that the active worksheet has numeric values in A2 downward. Text headings belong in row 1, and the macro processes only column A.
  • The chart covers other content: move the chart or change the G2 anchor and its dimensions in the code.
  • The output replaces existing information: columns D and E are reserved for the frequency table. Move the output references in the code if those columns contain data you need.
  • Excel blocks the macro: macros may be disabled by security settings or organizational policy. Only enable macros in files from a source you trust; do not lower security protections just to run an unfamiliar workbook.

Save the file as an Excel Macro-Enabled Workbook (.xlsm) if you need to keep the VBA code. A standard .xlsx workbook does not retain VBA macros. Keep a separate copy of important data before running code, because a macro’s worksheet changes are not undone by Ctrl+Z. On Windows, Alt+F11 opens the Visual Basic Editor; on Mac, use the Visual Basic Editor command from Excel’s Developer tools or the available Tools menu, depending on the version and ribbon layout.

Frequently Asked Questions

Can Excel VBA create a histogram without the Analysis ToolPak?
Yes. The sample macro calculates bin frequencies itself and draws a column chart, so it does not require the Analysis ToolPak. You can also use Excel’s built-in Histogram chart for a manual chart without VBA.

How do I change the number of bins in the macro?
Enter a whole number from 1 to 100 when the macro prompts you. The code then divides the range from the smallest to the largest numeric value into that many equal-width bins. To use fixed-width or custom bins instead, edit the lower- and upper-bound calculations.

Why does my histogram have bars that do not touch?
The built-in Histogram chart and a regular column chart use different chart settings. In the sample macro, GapWidth is set to zero so the columns touch. If you changed that setting or used another chart, set the column gap width to zero to create the familiar histogram appearance.

Can the macro make a histogram from dates or text?
Excel dates are stored as numeric serial values, so the macro will count them as numbers, but its labels may not be useful calendar labels. Ordinary text categories are ignored. For dates, create date-based bins and format the labels as dates; for text categories, use a bar or column chart rather than a histogram.

Will this VBA macro run in Excel for the web?
No. Excel for the web can show a workbook that contains VBA, but VBA macros must be run in desktop Excel. The built-in charting tools available in the web app may still let you create a chart without running this code.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.