In our previous tip we showed you how to present data with PivotTable groupings and then add a column chart and convert that into a histogram. This week, we’ll show you how to create a real histogram by installing the Analysis ToolPak which provides you with a host of data analysis tools.
Note: Download the workbook to practice this exercise
Applies To: Microsoft® Excel® 2010 and 2013
- Download the practice workbook.
- To Install the Analysis ToolPak:
a. Select File then Options.
b. Select Add–ins and next to Manage select Excel add-ins.
c. Select Go..
d. Tick the check box next to Analysis ToolPak and select OK.
e. The Analysis ToolPak is now available under the Data Tab in the Analysis group.
- To create a histogram:
a. Select Data Analysis under Data Tab.
b. Select Histogram, then OK.
c. A dialogue box like the below will appear.
d. Place the cursor in the Input Range text box. Input data is the data that you want to analyse by using the histogram tool.
e. Select the data range C4:C28.
f. Place the cursor in the Bin Range text box. Bin numbers are the numbers that represent the intervals that you want the histogram tool to use. For instance how many times did clients buy quantities of 10, 20, 30 etc.
g. Select the data range D4:D13.
h. Ensure the Chart Output checkbox is ticked.
i. Select OK.
j. A histogram and a data table will be displayed.
- Right click on one of the data bars in the chart.
- Select the Format Data Series menu item.
- From the Series Options items, change the Gap Width to 0%.
As you can see with this example, customers often buy quantities of 20 for product x, however quantities of 60 are very unpopular. This could be something worth investigating. Once again, you can apply a histogram to other types of data – do give it a try!
