Creating a Histogram Using Excel's PivotTable Tool

Histograms allow you to visualize the look-and-feel of your numerical data one variable at a time. In this video, I show how to create a histogram using the PivotTable tool inside Windows Excel 2007 or newer. First, start Excel and open the data set that you would like to explore. I will assume that your data is arranged with the variables as columns and that your data also includes column headings. Next, select the rectangular array of cells around the entire data set. Then click on the Insert tab and choose PivotTable. In the next dialog box, you will see that Excel has already referenced the data that you selected. You can change several options here, but clicking OK is a good default. You will then be presented with a blank PivotTable. In the list of variables on the right, click and drag the variable of interest down into the section labeled Row Labels. Then click and drag the same variable again, this time down into the Values section. You will see on the left that an initial PivotTable has been automatically created. However, we need to modify several items before we will have our histogram. The first modification is to make sure the PivotTable is counting the raw data. So click on the Value Field in the bottom right and choose Value Field Settings. Then ensure that Count is chosen and click OK. The second modification we need is to group the rows into several intervals ... or bins ... as a means of aggregating the data. Make sure you have selected one of the cells in the row data ... Then on the Options tab, choose Group Selection. In the next dialog box, you can select the starting value of the first bin, the ending value of the last bin, and the width of each bin. You can play around with these numbers, but often the default values work well. When ready, click OK. ... The PivotTable should then update with the counts for the specified bins. The final step is to choose the Options tab and then PivotChart. Then choose a basic Column chart (which is typically the default) and Click OK. Excel will then prepare the chart that goes along with the table. This is our histogram. Histograms can be extended in several ways to show different perspectives on the same information. One common perspective is called the cumulative histogram, which counts up the data as it accumulates from left to right in the defined bins. A cumulative histogram is often shown as a line chart on top of a regular histogram. To create a cumulative histogram here, once again click and drag the column Price into the Values field. This will replicate the work we have already done with a column called Price2. However, this time, choose Value Field Settings for Count of Price2, select the Show Values As tab, and choose Running Total In from the dropdown list. Then click OK. The PivotTable and PivotChart will update, showing both the original count information as well as the cumulative count information. To change the new columns to a line chart, first click on the bars and then right-click, and choose Change Series Chart Type. At the bottom here, change Clustered Column to Line Chart. In this case, I'll choose Line Chart with Markers. Click OK, and the chart updates to show both the histogram and cumulative histogram in their final form.