When done, close browser tab or window.
Start video.
McGraw Hill red cube logo. Because learning changes everything™
Title screen: Frequency polygon using PivotChart. Copyright ©2017 McGraw-Hill Education. All rights reserved.
Narrator: "In this video, we will build a frequency distribution with a PivotTable and, using the PivotChart function, we'll build a frequency polygon. Our frequency polygon will look like the one you see here. Similar to a histogram, it shows the shape of our distribution. The points in the graph are the intersection of each class midpoint and class frequency. We'll build this polygon as a line graph with markers. This is our data set. A company collected data on the daily number of oil changes at one of its shop locations for 20 days. We're going to convert this raw data into a frequency distribution using a PivotTable. Click inside the data, Insert, PivotTable. Click 'Okay.'"
PivotTable is selected from the Tables section of the Insert tab. The Create PivotTable dialog box appears. When OK is clicked, a new sheet appears with the PivotTable Fields pane on the left side of the screen. Fields are dragged from the top half of the pane to the labeled areas in the lower half: Filters, Columns, Rows, and Values. As fields are moved, the PivotTable is populated with data.
Narrator: "Drag changes to the Rows section and changes to the Values section. Click on the data. Under Field Settings, change it to Count and say 'Okay.'"
The Field Settings button is selected from the Active Field section of the Analyze tab. The Value Field Settings dialog box appears and contains two tabs: Summarize Values By and Show Values As. Within Summarize Values By, Sum is changed to Count.
Narrator: "Now we have a frequency count. However, it's every value from our data set, and it's not very useful to show the shape of our distribution if we see that 51 occurred once and 62 occurred twice. We'd like to group them into bins in order to see the frequency across classes. Excel offers built-in functionality to create groups for us in a PivotTable. Click inside your PivotTable to activate the menu. Click on Group, Group Selection."
The Group button, found left of the Filter section in the Analyze tab, is clicked to select Group Selection from its drop-down list. The Grouping dialog box appears with checkboxes and text fields for Starting at, Ending at, and By.
Narrator: "The 'Starting at' will be the lowest value in the first bin. The 'Ending at' is the highest value. And 10 is the interval suggested by Excel. In other words, the class width. A rule of thumb in creating groups is to have them be multiples of 10 or 100. So this looks like a good interval for us to use. However, another rule of thumb is that the starting bin, the first lowest value, should be a multiple of your interval. So let's make this 50, and then Excel will automatically change the ending."
The Starting at checkbox is unchecked, 50 is entered to its text field, and the OK button is clicked. The table is reduced to five data rows.
Narrator: "And, as you can see, I now have bins that go from 50 to 59, et cetera. They are 10 units wide. And I now have a frequency distribution based on those classes. A frequency polygon should be anchored at the start and the finish point by a zero so that it looks like a mountain rather than a line in space. If you click the drop-down by Row Labels, you'll see that there are actually classes less than 50 and greater than 100, but they have values of zero."
The drop-down arrow to the right of Row Labels is clicked. A list of checkboxes is at the bottom of the menu ranging from less than 50 to greater than 100.
Narrator: "Click on Field Settings, Layout & Print, and Show Items with No Data."
Within the Field Settings dialog box, two tabs are listed at the top of the box: Subtotals & Filters and Layout & Print. Layout & Print is selected. The Show items with no data checkbox is checked.
Narrator: "And then right-click in your table, click on PivotTable Options, and for Empty Cells, show zero."
Upon selecting PivotTable Options, a dialog box appears. In the box's Layout & Format section, the For empty cells show is checked and zero is entered to its text field.
Narrator: "Now we have a starting and ending class in our frequency distribution, which we will use to anchor our frequency polygon. A frequency polygon uses the midpoint of classes as the values shown on the x-axis. The midpoint is simply the lowest bin value from bin one and bin two averaged together."
Bins 50 to 59 and 60 to 69 are highlighted.
Narrator: "In other words, I've taken the 50 from my 50 to 59 class and the 60 from my 60 to 69 class, averaged them, and found the midpoint for class 50 to 59 is 55."
A text box points to cells A15 to A17: midpoints are 10 units apart. The equation in cell A16: equals left parenthesis 50 plus 60 right parenthesis divided by two.
Narrator: "I did the same for the class above it."
In reference to cell A15.
Narrator: "Now I can see easily my midpoints are 10 units apart, which is, not coincidentally, also the interval, and now I can substitute those midpoint values in for my row labels. And I'm now ready to insert my graph. Click inside the data to select it. Insert, PivotChart."
PivotChart is selected from the Charts section of the Insert tab. The Insert Chart dialog box appears.
Narrator: "Choose Line Chart, and the fourth one over has markers. It shows us what our graph will look like when we hover over it. Click 'Okay.' And we now have a frequency polygon very similar to the one that you saw at the beginning of the video. I'm going to move the graph over so that some of the pop-up menus are visible. We don't need the legend. We can delete that."
The legend on the right side of the chart is selected and deleted.
Narrator: "We can change our titles by clicking in the box and typing in whatever we'd like. We can add axis titles by clicking the plus sign and choosing 'Axis Titles.'"
The plus sign outside the top right corner of the chart is selected to view the Chart Elements list of checkboxes. Axis Titles is checked.
Narrator: "We have now created a frequency polygon using the PivotTable feature and PivotCharts 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.