How to Plot Histogram with Unequal Class Intervals in Excel

Get FREE Advanced Excel Exercises with Solutions!

Microsoft Excel is one of the most useful software you can get. Using Excel’s features and tools, it is possible to do an infinite number of things with a dataset. We need to use Excel Charts to regularly show the Histogram with inequivalent periods of frequency density. Since this information does not follow a normal distribution, we must construct Histograms with unequal intervals. This article will examine two easy ways to graph histograms with unequal class periods in Excel. Therefore, review these two suitable ways to plot Histogram with unequal class intervals in Excel.


Why Do We Use Histograms?

A Histogram is a visual representation of data that uses a series of concentric rectangles to depict the frequency with which different data items occur within a range of values. One common type of graph is the histogram, which provides a concise summary of continuous variables, either continuous or discrete.

  • After creating the histogram, you can lower the average and high variance to meet existing or new needs.
  • A change in continuous data can show success in treating the problem’s fundamental causes by examining graphs before and after implementing countermeasures.
  • The histogram can demonstrate how far the item, service, or diagnostic testing don’t fulfill customer needs if the detail and explanations are accessible.

How to Plot Histogram with Unequal Class Intervals in Excel: 2 Suitable Ways

To illustrate this concept, let’s explore a representative dataset. The following dataset has columns labeled Age Class and Frequency. We will create a histogram graphic by utilizing the data from the dataset. In the first technique shown in this article, you will learn how to use the Charts Feature to depict a histogram chart with non-equivalent class periods. In contrast, another person creates a graph in Excel using Visual Basic for Applications that illustrates a histogram.

histogram with unequal class intervals in excel

Notes

I have been writing up this post with the help of Microsoft Excel 365. It is entirely up to you to choose the best variant that caters to your requirements. Whatever course of action you decide to take, we will support it.


1. Utilize Charts Feature to Plot Histogram with Unequal Class Intervals in Excel

The Chart Feature in Microsoft Excel allows users to create visual representations of data efficiently. A diagram is a graphical display of information, and often, the information is represented via figures like bars or lines. We will calculate necessary data throughout this context and plot a histogram with inequivalent intervals using the Excel Charts feature. Please read these directions carefully and follow them to the letter to complete the task.

STEPS:

  • First, create two columns titled Unequal Class and Interval between the Age and Frequency columns.
  • Second, build another column named Frequency Density throughout the F column.

Utilize Charts Feature to Plot Histogram with Unequal Class Intervals in Excel

  • Third, select the C5 cell.
  • After that, input the following formula in the Formula bar.

=IF($B5=90,B5&" < Age < "&100,B5&" < Age < "&B6)

  • Later, hit Enter or Tab to see the outcome in C5.

Utilize Charts Feature to Plot Histogram with Unequal Class Intervals in Excel

  • At this point, we have to use the same formula in other cells.
  • To achieve this, utilize the AutoFill Handle icon and drag it to cell C10 to see the result.

  • Likewise, choose cell D5.
  • Then, in the Formula bar, type the following formula.

=IF($B5=90,(100-B5),B6-B5)

  • The result will be shown in D5 when you hit Enter or Tab.

Utilize Charts Feature to Plot Histogram with Unequal Class Intervals in Excel

  • We have to use the same formula in other cells at this point.
  • To do this, use the AutoFill Handle icon and drag it to cell D10 to see the result.

  • Choose the F5 cell for now.
  • Then, in the Formula bar, type the following formula.

=E5/D5

  • Hit Enter or Tab later to see the result in the F5 cell.

Utilize Charts Feature to Plot Histogram with Unequal Class Intervals in Excel

  • We have to use the same formula in other cells at this point.
  • To do this, use the AutoFill Handle symbol and drag it to cell F10 to view the consequence.

Utilize Charts Feature to Plot Histogram with Unequal Class Intervals in Excel

  • Presently, select the C5:C10 range.
  • Afterward, hold the Ctrl key down and mark the F5:F10 range using the Mouse.

  • Latterly, navigate to the Insert tab and click on the Recommended Charts.

Utilize Charts Feature to Plot Histogram with Unequal Class Intervals in Excel

  • Subsequently, the Insert Chart window will pop up.
  • Choose the Clustered Column chart from the Recommended Charts tab and then hit OK.

Utilize Charts Feature to Plot Histogram with Unequal Class Intervals in Excel

  • Consequently, a chart area will appear and write the chart title.
  • As a result, we will get the desired output like the below one.

Output of Utilizing Charts Feature to Plot Histogram with Unequal Class Intervals in Excel


2. Run Excel VBA Code to Graph Histogram with Unequivalent Class Intervals

The abbreviation VBA refers to the Visual Basic for Applications programming language, and VBA is an application developed by Microsoft. Using VBA code, we can use functions incompatible with Excel, and Excel’s VBA provides an innovative alternative method for displaying histogram charts for unequal periods. This tutorial will show you how to use VBA code to create a histogram with unequal class intervals. Please turn in the assignment by following these instructions to the letter.

STEPS:

  • Start by creating two columns with the headings Unequal Class and Interval between the Age and Frequency.
  • Then extend the F column with a new column called Frequency Density.

Run Excel VBA Code to Graph Histogram with Unequivalant Class Interval

  • Third, go to the Developer tab and click on Visual Basic.

Run Excel VBA Code to Graph Histogram with Unequivelant Class Interval

  • Eventually, click on,

Insert → Module

  • Now, write the following code in the Module box.
Sub HistogramWithUnequalClassInterval()
    Range("C5").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC2=90,RC[-1]&"" < Age < ""&100,RC[-1]&"" < Age < ""&R[1]C[-1])"
    Range("C5").Select
    Selection.AutoFill Destination:=Range("C5:C10"), Type:=xlFillDefault
    Range("D5").Select
    ActiveCell.FormulaR1C1 = "=IF(RC2=90,(100-RC[-2]),R[1]C[-2]-RC[-2])"
    Range("D5").Select
    Selection.AutoFill Destination:=Range("D5:D10"), Type:=xlFillDefault
    Range("F5").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
    Range("F5").Select
    Selection.AutoFill Destination:=Range("F5:F10"), Type:=xlFillDefault
    Range("C5:C10,F5:F10").Select
    Range("F5").Activate
    ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
    ActiveChart.SetSourceData Source:=Range( _
        "'Excel VBA'!$C$5:$C$10,'Excel VBA'!$F$5:$F$10")
    ActiveChart.ChartTitle.Text = "Histogram with Unequal Class Intervals"
End Sub
  • Later, press  F5  or click on the Run button.

  • Consequently, the C, D, and F columns will fill with the necessary values to plot the histogram.

Run Excel VBA Code to Graph Histogram with Unequivelant Class Interval

  • As a result, it will produce the desired chart utilizing the intended values like the below one.

Output of Running Excel VBA Code to Graph Histogram with Unequivelant Class Interval


Download Practice Workbook

Please click the link below to download a copy of the sample workbook we used in the presentation.


Conclusion

If you follow what we instructed, you will soon be able to create a Histogram with unequal class intervals in Excel. Keep using these, and if you think of any other ideas or methods that are more effective, please let me know. Remember to jot down any questions, comments, or recommendations you have in the space provided.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Lutfor Rahman Shimanto
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto, BSc, Information Technology, Jahangirnagar University, Bangladesh, has been working with the ExcelDemy project for more than 1 year. He has written 50+ articles for ExcelDemy. Currently, he is working as an Excel & VBA Developer and also provides support and solutions in the ExcelDemy Forum. His work and learning interests are in developing various Excel applications. Outside of work, he enjoys Chess a lot. He is a founding Jahangirnagar University Chess Club member and an internationally rated chess player.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo