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

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.

## 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.

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

### 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.

• Afterward, in the VBA window select Insert > 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
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

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``````

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
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.

Final Histogram Output:

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

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

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

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

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

Get FREE Advanced Excel Exercises with Solutions!
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

Advanced Excel Exercises with Solutions PDF