Office Hacks: Using Histogram Segmentation Statistics Smartly

Schools often conduct exams, and after each exam, teachers have to statistically analyze student scores and count the distribution status of students in each score range. If you use a histogram, you can visually see the shape of the data distribution. Next, we will see how to use the histogram to count the distribution of students in each score band (Figure 1).

1902A-ZFTTJ-1

First, add auxiliary columns to the original data as needed to set segmentation points. Here, segment points 570, 580, 590, 600, 620, 640 are set, indicating the distribution of the number of people in each score range of 570 and below, 570-580, 581-590, 591-600, 601-620, 621-640 and 640 or above (Figure 2).

1902A-ZFTTJ-2

Next, select the “Data” tab, click “Data Analysis”, select “Histogram” in the pop-up window, and then select “Histogram” in the histogram window. Select “$B$1:$B$19” in the “Input area” of “Input”, and select “$E$1:$B$19” in the “Receive area”. Select “$E$1:$E$7”, check “Flag”, select “Input area” in “Output options Select “$G$1” in “Output Options”, and check “Chart Output”. Click “OK” to generate a histogram and a list of default effects (Figure 3).

1902A-ZFTTJ-3

Tip: If you can not find the “Data Analysis” button, you can click “File → Options” in the Excel Options window, select “Add-ins”, click the “Go” button at the bottom of the window, in the pop-up window check the “Analysis Tools Library” on it (Figure 4).

1902A-ZFTTJ-4

Next, modify the segmentation points of column G so that the horizontal axis of the chart will be more intuitive. Delete the legend, double-click the data series of the chart, and set “Series Options” to 0% for “Series Overlap” in the “Set Data Point Format” on the right side of the window. Set the “Gap width” to 2% (Figure 5).

1902A-ZFTTJ-5

Finally, it is enough to beautify the chart accordingly as needed.

Leave a Comment