Microsoft Office Tutorials and References
In Depth Information
Using Histograms to Track Relationships and Frequency
2. Create a new column that holds the FREQUENCY formulas. Name the new column Frequency
Formulas, as seen in Figure 9-10.
Excel’s FREQUENCY function counts how often values occur within the ranges you specify in
a bin table.
3. Select a number of cells equal to the cells in your bin table.
4. Type the FREQUENCY formula you see in Figure 9-10 and then press Ctrl+Shift+Enter on your
The FREQUENCY function does have a quirk that often confuses first-time users. The
FREQUENCY function is an array formula — that is, it’s a formula that returns many
values at one time. In order for this formula to work properly, you have to press
Ctrl+Shift+Enter after typing the formula. If you just press the Enter key, you won’t get
the results you need.
Figure 9-10: Type the FREQUENCY formula you see here; be sure to hold down the Ctrl+Shift+Enter
keys on your keyboard.
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, build
a simple chart feeder table that creates appropriate labels for each bin, which you do as follows:
1. Create a new table that feeds the charts a bit more cleanly (see Figure 9-11). Use a simple
formula that concatenates Bins into appropriate labels. Use another formula to bring in the
results of your FREQUENCY calculations.
In Figure 9-11, we made the formulas in the first record of the chart feeder table visible.
These formulas are essentially copied down to create a table appropriate for charting.
2. Use your newly created chart feeder table to plot the data into a column chart.
Figure 9-12 illustrates the resulting chart. You can certainly use the initial column chart as
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