Create Separate Histograms in Excel with the Data Analysis ToolPak
Question details
Create separate histograms for acorn measurements from two collection sites using Excel's Data Analysis ToolPak.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants to compare the distributions of measurements collected at two sites.
- Observed behavior
- Each site should have its own histogram based on its measurement range and a suitable range of bin upper limits.
Place each site's numeric measurements in a separate column and list the desired bin upper limits in ascending order. Use the same bin limits for both sites if you want to compare their distributions.
Create a Histogram for Each Site with the Data Analysis ToolPak
Use the Histogram tool to generate a frequency table and chart for each collection site.
If Data Analysis is missing from the Data tab, enable Analysis ToolPak. In Excel for Windows, go to File > Options > Add-ins, select Excel Add-ins under Manage, click Go, and check Analysis ToolPak. On a Mac, look under Tools > Excel Add-ins.
Go to Data > Data Analysis, select Histogram, and click OK.
Set Input Range to the measurements for the first site and Bin Range to the column of bin upper limits. Include headers in both selections only if you also check Labels.
Select an Output Range or a new worksheet, check Chart Output to create the histogram, and click OK.
Open Data > Data Analysis > Histogram again. Select the second site's measurements as the Input Range, use the same Bin Range, and choose a separate output location.

Compare Site Measurements with WPS Spreadsheets
In WPS Spreadsheets, calculate frequencies from bin upper limits and insert a column chart for each site. This approach creates comparable histograms without using Excel's ToolPak.
- 1. Arrange the data: Open your workbook in WPS Spreadsheets. For example, put the first site's measurements in A2:A101 and five ascending bin upper limits in D2:D6; adjust the ranges to fit your data.
- 2. Calculate bin frequencies: In E2, enter =COUNTIF($A$2:$A$101,"<="&D2). In E3, enter =COUNTIF($A$2:$A$101,"<="&D3)-COUNTIF($A$2:$A$101,"<="&D2), then fill E3 down through E6.
- 3. Add values above the last bin: Enter More in D7 and =COUNTIF($A$2:$A$101,">"&D6) in E7. Select D2:E7 and choose Insert > Chart > Column to create the first site's chart.
- 4. Chart the second site: Copy the bin limits and formulas to a separate area, change the formulas' measurement range to the second site's column, and select the new bin and frequency cells. Choose Insert > Chart > Column to create the second chart.

Frequently Asked Questions
Where is Data Analysis in Excel?
Look on the Data tab for Data Analysis. If it is missing, enable Analysis ToolPak through File > Options > Add-ins in Excel for Windows, or Tools > Excel Add-ins on a Mac.
What should I select as the Input Range?
Select the numeric measurement cells for one collection site. If you include the column header, check Labels in the Histogram dialog box.
Should both sites use the same bins?
Use the same ascending bin upper limits for both histograms when you want to compare the distributions directly. Select that bin column as the Bin Range each time.
What happens to measurements above the highest bin?
The ToolPak Histogram output includes them in a More category. In WPS Spreadsheets, you can count them with COUNTIF using a greater-than condition against the highest bin.




