When done, close browser tab or window.
Start video.
McGraw Hill red cube logo. Because learning changes everything™
Title screen: Histogram charting feature in Excel. Copyright ©2017 McGraw-Hill Education. All rights reserved.
Narrator: "In this video, we will build a histogram using the new Histogram chart feature in Excel 2016. The data you see here represents the number of times 30 different ATMs were used on a single day. I've sorted the data so that we can see the distribution of raw data. In other words, one ATM was used 36 times, and one was used 95 times. The others were in between those two values. A histogram is a great tool to see the shape of the distribution of our data. Excel now makes it easy to build one. Select your data, with or without the label. I'm going to exclude the label. On the Insert tab, in Recommended Charts, click the All Charts tab. Select 'Histogram' from the list. Click on the graph. It will show you a preview of your graph."
Recommended Charts is selected from the Charts section of the Insert tab. When clicked, the Insert Chart dialog box appears, which contains two tabs: Recommended Charts and All Charts. All Charts is clicked, Histogram is expanded, and the chart option is selected.
Narrator: "Click 'Okay.' A histogram has been inserted into our chart. I'm going to move the histogram to the top, and then make it just a bit bigger. We have some cleanup to do on this histogram, and we need to make some decisions. As you can see, our data have been sorted into four bins. How did Excel choose the number of bins? Instead of the formula you may have seen before, 2 to the 'K,' greater than or equal to 'N,' where 'K' represents the number of classes that we are seeking to solve for and 'N' is the number of data points, which in this case would have suggested five classes."
A formula appears on screen: 2 superscript k is greater than or equal to n.
Narrator: "Excel uses something called Scott's Normal Reference Rule, a binning algorithm that seeks to fit data to a normal distribution. In histograms, all bins should be of equal width. Two things to be aware of with the way these bins are set up. The first bin is wider than all the other bins. Note how the intervals are 36 to 53, 53 to 70. The upper limit in the bin is included in a bin. So the 53 is included in the first bin. In the next bin, though it seems to start at 53, that bin does not include the value of 53. However, in the first bin, 36 is part of that value. Therefore, the first bin is one unit wider than all the others. Also, recall that I said that Scott's Normal Reference Rule assumes that data are normally distributed. If we look at a different number of bins, we may see a different picture. You can modify the number of bins or the bin sizes rather than accepting Excel's automatically-chosen limits. Click on your x-axis, and in the Format tab, under Chart Tools, choose 'Format Selection.'"
Format Selection is found in the Current Selection section of the Format tab. The Format Axis pane appears on the right side of the screen.
Narrator: "Here I can choose bin width or number of bins. If I click on 'Number of Bins,' you can see that some change has already occurred in my data."
As the Number of bins radio button is selected within the pane and its text field is set to four, the histogram automatically updates to list four bins.
Narrator: "I'm going to change this to five bins. Now the data looks quite a bit different in our histogram than it did when we first began. Now I'm going to change my bin width to 10."
The Bin width radio button is clicked and 10 is entered to its text field.
Narrator: "Notice that Excel will automatically adjust the number of bins based on my bin width. Again, the picture changes. It's a good idea to play around with the bin width and number of bins to see how the picture of your data changes. When you're happy with the histogram, don't forget to add axis titles by clicking the plus sign."
The plus sign is found outside the top right corner of the chart when the chart is selected. The Chart Elements list of checkboxes appears, and Axis Titles is checked.
Narrator: "You can change the titles by clicking inside the text boxes and typing the title that you'd like. In this video, we have built a histogram using the Histogram chart feature in Excel."
Video has ended.
Described transcript ©2023 McGraw Hill. All rights reserved. No reproduction or further distribution permitted without the prior written consent of McGraw Hill.
Close browser tab or window.