Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

# 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. You can download the practice workbook from here for a better understanding.

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

## Step-by-Step Procedures to Create Histogram in Excel Using VBA

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 • 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. ## 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. Visit our ExcelDemy Website for similar articles regarding Excel.

## Related Articles #### Mehedi Hasan Shimul

Hi! I am Mehedi Hasan Shimul. As I am an Engineer solving different problems with the help of Excel amuses me. I write Excel related different problem solving articles here. Hope it will help you.

We will be happy to hear your thoughts 