How to Create Histogram in Excel Using VBA (With Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

A histogram helps to analyze and visualize data very quickly. For that, it has great importance in data analysis. Here, we will discuss the stepwise procedures to create a histogram in Excel using VBA macro code.

Histogram Using VBA


What Is Histogram in Excel?

A histogram apparently looks quite similar to a bar chart. Except, the histogram is the representation of the frequency (Number of times that data appears) of data which is represented by rectangular shapes. Instead of specific data, the bars of the histogram can also represent a range of data. Histograms can be created in Excel very easily. We can create a histogram by using the Statistical Chart feature of Excel or by running a simple piece of VBA code.


How to Create Histogram in Excel Using VBA: Step-by-Step Procedures

In this section, we will show the stepwise procedures to create a histogram using a simple piece of VBA code. Without any further delay, let’s jump to the stepwise procedures.


STEP 1: Create Dataset for Histogram

  • Primarily, we have to include some numeric values in the dataset.
  • For the demonstration, we have included some numeric values in the Marks column.

Dataset for histogram

  • Before any further steps, we have to select the data from the Marks column including the header.

Selecting data to make the histogram


STEP 2: Open VBA Window

  • Then, go to the Developer tab and select Visual Basic to open the VBA window.
  • Also, you can open the VBA window by pressing Alt + F11.

Opening VBA Window

  • Afterward, in the VBA window select Insert > Module.

Inserting Module


STEP 3: Type a Suitable Code in the VBA Module

  • After that, it’s time to write the code for creating the histogram. You can copy the following piece of code in the appeared code window.
Sub Create_Histogram()
Dim src_sht As Worksheet
Dim new_sht As Worksheet
Dim selected_rng As Range
Dim title As String
Dim x As Integer
Dim scor_cel As Range
Dim num_scores As Integer
Dim count_rng As Range
Dim nw_chart As Chart

    Set selected_rng = Selection
    Set src_sht = ActiveSheet
    Set new_sht = Application.Sheets.Add(After:=src_sht)
    title = selected_rng.Cells(1, 1)
    new_sht.Name = title & " Histogram Using VBA"

    x = 1
    For Each scor_cel In selected_rng.Cells
        If Not IsNumeric(scor_cel.Text) Then
            new_sht.Cells(x, 1) = title & "/Scores"
        Else
            new_sht.Cells(x, 1) = scor_cel
        End If
        x = x + 1
    Next scor_cel
    num_scores = selected_rng.Count

    Const BIN_SIZE As Integer = 10
    Dim num_bins As Integer
    num_bins = 100 \ BIN_SIZE

    new_sht.Cells(1, 2) = "Bins"
    For x = 1 To num_bins - 1
        new_sht.Cells(x + 1, 2) = x * BIN_SIZE - 1
    Next x


    new_sht.Cells(1, 3) = "Frequency"
    Set count_rng = new_sht.Range("C2:C" & num_bins + 1)
    count_rng.FormulaArray = "=FREQUENCY(A2:A" & _
        num_scores & ",B2:B" & num_bins & ")"

    new_sht.Cells(1, 4) = "Score Range"
    For x = 1 To num_bins - 1
        new_sht.Cells(x + 1, 4) = "'" & _
            10 * (x - 1) & "-" & _
            10 * (x - 1) + 9
        new_sht.Cells(r + 1, 4).HorizontalAlignment = _
            xlRight
    Next x
    x = num_bins
    new_sht.Cells(x + 1, 4) = "'" & _
        10 * (x - 1) & "-100"
    new_sht.Cells(x + 1, 4).HorizontalAlignment = xlRight

    Set nw_chart = Charts.Add()
    With nw_chart
        .ChartType = xlColumnClustered
        .SetSourceData Source:=new_sht.Range("C2:C" & _
            num_bins + 1), _
            PlotBy:=xlColumns
        .Location Where:=xlLocationAsObject, _
            Name:=new_sht.Name
    End With

    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = title & " Histogram Using VBA"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, _
            xlPrimary).AxisTitle.Characters.Text = "Marks/Scores"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text _
 _
            = "Frequency"

        .SeriesCollection(1).XValues = "='" & _
            new_sht.Name & "'!R2C4:R" & _
            num_bins + 1 & "C4"
    End With
    ActiveChart.SeriesCollection(1).Select
    With ActiveChart.ChartGroups(1)
        .Overlap = 0
        .GapWidth = 0
        .HasSeriesLines = False
        .VaryByCategories = False
    End With

    x = num_scores + 2
    new_sht.Cells(x, 1) = "Average"
    new_sht.Cells(x, 2) = "=AVERAGE(A1:A" & num_scores & _
        ")"
    x = x + 1
    new_sht.Cells(x, 1) = "Std. Deviation"
    new_sht.Cells(x, 2) = "=STDEV(A1:A" & num_scores & ")"
End Sub

Typing VBA Code

Code Explanation

  • In this part, we declared a subprocedure named Create_Histogram. Also, we declared some variables.
Sub Create_Histogram()
Dim src_sht As Worksheet
Dim new_sht As Worksheet
Dim selected_rng As Range
Dim xtitle As String
Dim x As Integer
Dim scor_cel As Range
Dim inum_scores As Integer
Dim count_rng As Range
Dim nw_chart As Chart
  • Afterward, this part copies the data to the new sheet.
Set selected_rng = Selection
Set src_sht = ActiveSheet
Set new_sht = Application.Sheets.Add(After:=src_sht)
xtitle = selected_rng.Cells(1, 1)
new_sht.Name = xtitle & " Histogram Using VBA"
  • Then, this part of code creates a new sheet.
x = 1
For Each scor_cel In selected_rng.Cells
If Not IsNumeric(scor_cel.Text) Then
new_sht.Cells(x, 1) = xtitle & " /Scores"
Else
new_sht.Cells(x, 1) = scor_cel
End If
x = x + 1
Next scor_cel
num_scores = selected_rng.Count
  • Here, we created bin separators.
Const BIN_SIZE As Integer = 10
Dim num_bins As Integer
num_bins = 100 \ BIN_SIZE
  • This section counts the bins.
new_sht.Cells(1, 2) = "Bins"
For x = 1 To num_bins - 1
new_sht.Cells(x + 1, 2) = x * BIN_SIZE - 1
Next x
  • Further, this part makes the range labels.
new_sht.Cells(1, 3) = "Frequency"
Set count_rng = new_sht.Range("C2:C" & num_bins + 1)
count_rng.FormulaArray = "=FREQUENCY(A2:A" &num_scores & ",B2:B" & num_bins & ")"
  • Now, the code makes the counts.
new_sht.Cells(1, 4) = "Score Range"
For x = 1 To num_bins - 1
new_sht.Cells(x + 1, 4) = "'" &10 * (x - 1) & "-" &10 * (x - 1) + 9
new_sht.Cells(x + 1, 4).HorizontalAlignment =xlRight
Next x
x = num_bins
new_sht.Cells(x + 1, 4) = "'" &10 * (x - 1) & "-100"
new_sht.Cells(x + 1, 4).HorizontalAlignment = xlRight
  • After that, the code creates the chart.
Set nw_chart = Charts.Add()
With nw_chart
.ChartType = xlColumnClustered
.SetSourceData Source:=new_sht.Range("C2:C" &num_bins + 1),PlotBy:=xlColumns
.Location Where:=xlLocationAsObject,Name:=new_sht.Name
End With
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = xtitle & " Histogram Using VBA"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, _
xlPrimary).AxisTitle.Characters.Text = "/Scores"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text= "Frequency"
  • lastly, this part displays score ranges on the X-axis.
.SeriesCollection(1).XValues = "='" &new_sht.Name & "'!R2C4:R" &num_bins + 1 & "C4"
End With
ActiveChart.SeriesCollection(1).Select
With ActiveChart.ChartGroups(1)
.Overlap = 0
.GapWidth = 0
.HasSeriesLines = False
.VaryByCategories = False
End With
x = num_scores + 2
new_sht.Cells(x, 1) = "Average"
new_sht.Cells(x, 2) = "=AVERAGE(A1:A" & num_scores &")"
x = x + 1
new_sht.Cells(x, 1) = "StdDev"
new_sht.Cells(x, 2) = "=STDEV(A1:A" & num_scores & ")"
End Sub

STEP 4: Run VBA Code for Creating Histogram

  • Further, press the Run option from the window.

Running the vba code for histogram

Final Histogram Output:

  • Finally, we will see a Marks Histogram worksheet created with data for the histogram.

Output dataset for histogram.

  • Also, we will see a histogram for our data created in the same worksheet.

The final output of the histogram.

  • In addition, we can do a bit of formatting on the output dataset.

A bit of formatted dataset in Excel

  • Also, we can do some formatting (like giving labels and creating colored borders, etc.) on the histogram for better visualization.

Final Formatted Histogram Output in Excel

Read More: How to Create Probability Histogram in Excel


Download Practice Workbook

You can download the practice workbook from here for a better understanding.


Conclusion

The histogram is quite popular for analyzing data. Here, we have discussed the stepwise procedures to create a histogram using VBA code in Excel. Hope, you will be able to create a histogram using the VBA code now. If you have any queries or suggestions, please let us know by commenting.


Related Articles


<< Go Back to Excel Histogram | Excel Charts | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mehedi Hasan Shimul
Mehedi Hasan Shimul

Md. Mehedi Hasan, with a BSc in Electrical & Electronic Engineering from Bangladesh University of Engineering and Technology, holds a crucial position as an Excel & VBA Content Developer at ExcelDemy. Driven by a deep passion for research and innovation, he actively immerses himself in Excel. In his role, Mehedi not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently deliver exceptional and quality content.... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo