Excel Dashboards: Add a Cumulative Percent Series to Your Histogram

By Michael Alexander

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.

A nice feature to add to your histograms is a cumulative percent series. With a cumulative percent series, you can show the percent distribution of the data points to the left of the point of interest.

This figure shows an example of a cumulative percent series. At each data point in the histogram, the cumulative percent series tells you the percent of the population that fills all the bins up to that point. For instance, you can see that 25% of the sales reps represented sold 15 units or less. In other words, 75% of the sales reps sold more than 15 units.

image0.jpg

Take another look at the chart in this figure and find the point where you see 75% on the cumulative series. At 75%, look at the label for that bin range (you see 35–45). The 75% mark tells you that 75% of sales reps sold between 0 and 45 units. This means that only 25% of sales reps sold more than 45 units.

To create a cumulative percent series for your histogram, follow these steps:

  1. After you have created a histogram, add a column to your chart feeder table that calculates the percent of total sales reps for the first bin, as shown in this figure.

    image1.jpg

    Note the dollar symbols ($) used in the formula to lock the references while you copy the formula down.

  2. Copy the formula down for all the bins in the table.

  3. Use the chart feeder table to plot the data into a line chart.

    As you can see in this figure, the resulting chart needs some additional formatting.

    image2.jpg

  4. Right-click the series that makes up your histogram (Count of Sales Reps), select Change Chart Type, and then change the chart type to a column chart.

  5. Right-click any of the columns in the chart and choose Format Data Series.

  6. Adjust the Gap Width property to 0.

  7. Right-click Cumulative % series in the chart and choose Format Data Series.

  8. In the Format Data Series dialog box, change the Plot Series On option to Secondary Axis.

  9. Right-click Cumulative % series in the chart and choose Add Data Labels.

    At this point, your base chart is complete. It should look similar to the one shown in the first figure. When you get to this point, you can adjust the colors, labels, and other formatting.