Peltier Tech
Chart Utilities

Excel Dashboards

Books at Amazon.com

Buy me a coffee

If this topic or the whole site has been helpful, please support further development by treating me to a cup of coffee.

Custom Histograms in Microsoft Excel.

Suppose you want to chart the relative frequency of numbers in a list. This is typically done with a histogram, showing the values across the horizontal axis, and a column above each value where the height of each column is proportional to the number of occurrences of the corresponding value.

Suppose further that instead of a bland column chart, you want to put an X in the histogram for every occurrence of a value. You can do this with a scatter chart, using the following procedure offered by Excel MVP Debra Dalgleish in the Microsoft Charting news group. Let's use the following array of values:

1. Enter the numbers in a column, e.g. A1:A30. You don't even need to sort them.
    
2. In an adjacent column enter a formula to count the occurrence of each number. For example, in cell B1:
   =COUNTIF($A$1:$A1,A1)
    
3. Copy this formula down as far as needed.
    
4. Select a cell in this list, and click the Chart Wizard button.
    
5. Select the XY (Scatter) graph, and the first sub-type (markers without lines).
    
6. Click Finish.
    
7. Select one of the points, and choose Format menu > Selected Data Series.
    
8. For the Marker background, choose No Color. Choose the color you want for the Marker foreground.
    
9. For the Marker style, choose the X. Enlarge the marker if desired.
    
10. Double-click the grey Plot Area background, and choose None for the Border and the Area, click OK. Or simply select the grey rectangle, and press the Delete Key.
     
11. Double click the Y axis, and on the Scale tab, enter 0.5 for Minimum.
     
12. Select the Y axis, and press the Delete key.
     
13. Select one of the gridlines, and press the Delete key.
     
14. Delete the Title and Legend.
     
15. Double-click the X-axis, select the Scale tab, and set the Minimum and Maximum values for the axis.
     
16. Click OK.

This produces a handsome histogram, where the number of X markers indicates the number of each value in the array.

Variations

You could make this histogram with any of the built-in chart markers. This chart uses the circle instead of the X marker:

Or you could use any drawing object for the markers, as described in my Custom Chart Series Markers page. Create the drawing object, copy it, select the series in the chart, and use Ctrl-V to paste the shape as the series marker. These examples use WordArt letters and a Star drawing object for their markers:

Peltier Technical Services, Inc.

Commercial Utilities | Custom Solutions | Training | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2010. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

My MVP Profile