Use Histograms to Track Frequency in Excel Dashboards
A histogram is a graph that plots frequency distribution in your Excel dashboards and reports. A frequency distribution shows how often an event or category of data occurs. With a histogram, you can visually see the general distribution of a certain attribute.
You can create a formula-driven histogram, which allows for a level of automation and interactivity, which comes in handy when updating dashboards each month.
Before you create your histogram, you need a table that contains your raw data, and you need to create a bin table.
The raw data should ideally consist of records that represent unique counts for the data you want to group. For instance, the raw data table in this figure contains unique sales reps and the number of units each has sold.
The bin table dictates the grouping parameters used to break your raw data into the frequency groups. The bin table tells Excel to cluster all sales reps selling fewer than 5 units into the first frequency group, any sales reps selling 5 to 14 units in the second frequency group, and so on.
You can freely set your own grouping parameters when you build your bin table. However, it’s generally a good idea to keep your parameters as equally spaced as possible. You typically want to end your bin tables with the largest number in the dataset. This allows you to have clean groupings that end in a finite number — not in an open-ended greater-than designation.
Create a new column in the bin table to hold the Frequency formulas. Name the new column Frequency Formulas, as shown in this figure.
Excel’s Frequency function counts how often values occur within the ranges you specify in a bin table.
Select the cells in the newly created column, and then type the Frequency formula you see in previous figure and press Ctrl+Shift+Enter on your keyboard.
The Frequency function has a quirk that often confuses first-time users. The Frequency function is an array formula — a formula that returns many values at one time. In order for this formula to work properly, you have to press Ctrl+Shift+Enter on your keyboard after typing the formula. If you just press the Enter key, you won’t get the results you need.
At this point, you should have a table that shows the number of sales reps that fall into each of your bins. You could chart this table, but the data labels would come out wonky. For the best results, you should build a simple chart feeder table that creates appropriate labels for each bin. You do this in the next step.
Create a new table that feeds the charts a bit more cleanly, as seen in the next figure. Use a simple formula that concatenates bins into appropriate labels. Use another formula to bring in the results of your Frequency calculations.
In this figure, the formulas in the first record of the chart feeder table are visible. These formulas are essentially copied down to create a table appropriate for charting.
Use your newly created chart feeder table to plot the data into a column chart.
This figure illustrates the resulting chart. You can very well use the initial column chart as your histogram.
If you like your histograms to have spaces between the data points, you’re done. If you like the continuous, blocked look you get with no gaps between the data points, follow the next few steps.
Right-click any of the columns in the chart and choose Format Data Series.
The Format Data Series dialog box appears.
Adjust the Gap Width property to 0%, as shown in this figure.
The spaces between columns will be eliminated.