Chart & Visualization Issues

How to Set Histogram Bin Width and Starting Value in Excel

Olivia Miller Oct 12, 2026 869 views

Question details

The user needs to create a histogram in Excel with a customized bin width and a specific starting boundary, such as setting $5,000 intervals that begin exactly at $40,000.

How to Set Histogram Bin Width and Starting Value in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating custom histogram charts for precise data analysis where default automated intervals do not meet reporting requirements.
Observed behavior: Excel's standard histogram chart automatically calculates intervals and does not provide native options to set a customized starting boundary, forcing users to seek alternative charting methods.

Before you start

Ensure your dataset contains only numerical values without blanks, text strings, or errors before attempting to group them into histogram bins.

Solution 1Recommended

Use PivotChart Grouping for Custom Bins

This is the most efficient way to create custom intervals and define an exact starting value without writing complex array formulas.

By utilizing a PivotTable, you can bypass the limitations of Excel's built-in Histogram chart. The grouping feature gives you complete control over where your bins start and how wide they are.

Insert a PivotTable

Select your entire data range, navigate to the 'Insert' tab on the ribbon, and click 'PivotTable'. Choose to place it on a new worksheet.

Set Up the PivotTable Fields

Drag your numerical data field (e.g., Salary) into the 'Rows' area. Drag the exact same field into the 'Values' area. Ensure the 'Values' calculation is set to 'Count' rather than 'Sum'.

Group the Data

Right-click on any row label number in the PivotTable and select 'Group'. In the grouping dialog box, enter your specific starting value in the 'Starting at' field (e.g., 40000) and your desired bin width in the 'By' field (e.g., 5000).

Create the PivotChart

Click anywhere inside the PivotTable, go to the 'Insert' tab, and select a standard 'Clustered Column' chart.

Format as a Histogram

To make the column chart look like a true histogram, right-click any of the bars in the chart, select 'Format Data Series', and change the 'Gap Width' slider to 0%.

Dynamic Updates: If your source data changes, you simply need to click 'Refresh' on the PivotTable to instantly update your custom histogram.

Solution 2

Create Manual Bin Limits Using the FREQUENCY Function

Best for exact control over irregular bin sizes or specific boundary limits without using PivotTables.

Solution 3

Utilize a Histogram Add-in

Third-party add-ins can provide automated controls for bin limits if native features feel too manual.

Create Custom Histograms Easily with WPS Office

WPS Spreadsheet provides powerful data analysis tools, including PivotTables and advanced statistical functions, allowing you to easily create and customize histograms with specific bin widths and starting values.

1. Open Your Data: Launch WPS Spreadsheet and open your existing data file.
2. Insert a PivotTable: Go to the 'Insert' tab, click 'PivotTable', and drag your data field into both 'Rows' and 'Values'.
3. Group Data into Bins: Right-click the row values, select 'Group', and input your starting boundary and bin width.
4. Generate the Chart: Insert a Column Chart from the grouped data and set the gap width to 0% to finish your histogram.

Free, lightweight, and easy to install on any device100% compatible with Microsoft Excel (.xlsx) formats and featuresIntuitive PivotTable grouping for quick histogram generationFully supports advanced array formulas like the FREQUENCY function

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I change the starting value in Excel's native Histogram chart?

Excel's built-in Histogram chart (introduced in Excel 2016) calculates bins automatically based on Scott's normal reference rule. While you can change the bin width in the Format Axis pane, setting a specific manual starting value is restricted. Using a PivotChart or the FREQUENCY function bypasses this limitation.

How do I remove the gaps between bars in my column chart to make it a histogram?

Right-click on any data bar in your standard column chart and select 'Format Data Series'. In the formatting panel that appears on the right, look for the 'Gap Width' option and drag the slider down to 0%.

Does grouping data in PivotTables alter my original spreadsheet data?

No, grouping data in a PivotTable does not change, overwrite, or delete your source data. It only creates a summarized visual representation for your chart, leaving the original dataset completely intact.

Can I use unequal bin sizes in a histogram?

Yes, but not with PivotTable grouping. To use unequal bin widths, you must manually define your specific bin boundaries in a spreadsheet column and use the FREQUENCY array function to calculate the counts for each specific interval.

Excel & Data Problems