Chart & Visualization Issues

Create Separate Histograms in Excel with the Data Analysis ToolPak

Khadija KhanKhadija Khan Nov 18, 2026 869 views

Question details

Create separate histograms for acorn measurements from two collection sites using Excel's Data Analysis ToolPak.

Create Separate Histograms in Excel with the 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.
Before you start

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.

Solution 1Recommended

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.

1
Enable the ToolPak

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.

2
Open the Histogram tool

Go to Data > Data Analysis, select Histogram, and click OK.

3
Select the ranges

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.

4
Choose the output

Select an Output Range or a new worksheet, check Chart Output to create the histogram, and click OK.

5
Repeat for the second site

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.

Create a Histogram for Each Site with the Data Analysis ToolPak
How the bins work: Each bin value is an upper limit. Excel also reports measurements above the highest bin in a More category.
Create histograms in WPS Spreadsheets

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. 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. 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. 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. 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.
Create separate frequency charts using the same bin limits.Open and edit Microsoft Excel XLSX and XLS files.Use familiar spreadsheet formulas and chart controls.Work with a free, easy-to-use office suite.
microsoft office alternative - wps office

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.