Contents
In a nutshell: Adding a scrollbar to a histogram or frequency distribution plot to make it dynamic or interactive.
Difficulty level: advanced.
The following figure shows what a finished dynamic histogram looks like:
What is a histogram or frequency distribution plot?
A distribution histogram groups values from a data set and shows the number (frequency) of numbers in each group. This histogram is also called a frequency plot because it shows how often values are represented.
In our example, we divide the people who volunteered to take part in the event into age groups. First of all, we will create age groups, then we will calculate how many people fall into each of the groups, and then we will show all this on a histogram.
What questions does a distribution histogram answer?
The histogram is one of my favorite chart types because it provides a wealth of information about the data.
In this case, we want to know how many participants will be in the 20s, 30s, 40s, and so on. The histogram will clearly show this, so it will be quite easy to identify patterns and deviations.
«Is our event not interesting for citizens aged 20 to 29?»
Perhaps we want to slightly change the detail of the picture and break the population into two age groups. This will show us that the event will be attended mostly by young people:
Dynamic histogram
After constructing a histogram of the frequency distribution, it is sometimes necessary to change the size of the groups in order to answer various questions that arise. In a dynamic bar chart, this can be done thanks to the scroll bar (slider) below the chart. The user can increase or decrease the size of the groups by clicking the arrows on the scroll bar.
This approach makes the histogram interactive and allows the user to scale it by choosing how many groups to show. This is a great addition to any dashboard!
How does it work?
Short answer: Formulas, dynamic named ranges, scrollbar control combined with a bar chart.
formula
For everything to work, the first step is to use formulas to calculate the size of the group and the number of elements in each group.
To calculate the group size, divide the total number (80-10) by the number of groups. The number of groups is set by the scrollbar settings. We’ll explain this in more detail a little later.
Next, using the function FREQUENCY (FREQUENCY) I count the number of elements in each group in a given column. In this case, we return the frequency from the column Age tables named tblData.
=ЧАСТОТА(tblData[Age];C13:C22)
=FREQUENCY(tblData[Age],C13:C22)
Function FREQUENCY (FREQUENCY) is entered as an array formula by pressing Ctrl + Shift + Enter.
Dynamic named range
The chart uses a named range as the data source to retrieve data from only the currently selected groups.
As the user moves the scrollbar slider, the number of rows in the dynamic range changes to display only the desired data on the graph. In our example, two dynamic named ranges are defined: one for data – rngGroups (Frequency column) and the second for the horizontal axis labels – rngCount (Bin Name column).
Scrollbar control
Control element Scroll bar (Scroll Bar) can be inserted from a tab developer (Developer).
The figure below shows how I configured the control’s parameters and bound it to the cell C7. So, by changing the state of the scrollbar, the user manipulates the formulas.
bar chart
The graph is the easiest part of the task. We create a simple histogram and set dynamic named ranges as the data source.
Any questions?
Well, that was just a brief overview of how a dynamic histogram works.
Yes, this is not the easiest diagram, but I think users will enjoy working with it. Definitely, any report can be decorated with such an interactive diagram.
A simpler version of a bar chart can be created using pivot tables.
Write in the comments any questions and suggestions. Thank you!