Yes. In Excel for Microsoft 365 on Windows, a histogram skips genuinely blank cells; it does not count them as zero or put them in a bin. The same is generally true of the Analysis ToolPak Histogram and the FREQUENCY function, but cells that look blank because of formulas, spaces, or other text can behave differently.
What Excel means by a histogram
A histogram groups numeric observations into intervals, called bins, and shows how many values fall in each interval. For example, a set of test scores can be grouped into ranges such as 0–59, 60–69, 70–79, and 80–100. The height of each bar represents the number of numeric observations in its bin.
Excel offers two common ways to make one: the built-in Histogram chart and the Histogram command in the Analysis ToolPak. Both work from a range of data, but their outputs differ. The chart is a visual object whose bin settings can be adjusted on the chart. The ToolPak produces a frequency table on a worksheet, which can then be used to create or format a chart. In either case, a truly empty cell is missing data, not a numeric observation.
How the built-in Histogram chart handles blanks
To create the chart in Excel for Microsoft 365 on Windows, select the numeric data and choose Insert > Insert Statistic Chart > Histogram. Blank cells within the selected range are not included in the counts. Selecting a larger range that happens to include empty rows therefore does not ordinarily add observations to a bar.
For instance, suppose A2:A7 contains 2, a blank cell, 3, 4, 4, and 5. The chart has five numeric observations, not six. The empty cell contributes nothing; Excel does not silently convert it to 0. If zero is a real measurement, enter the number 0. A zero is a valid numeric observation and will be assigned to the appropriate bin.
After inserting the chart, you can adjust how Excel groups the values by selecting the horizontal axis, opening Format Axis, and changing the bin settings under Axis Options. Depending on the data and chart settings, you can set a bin width or number of bins, and can configure underflow or overflow bins. Those choices affect where numeric values are grouped; they do not turn empty cells into observations.
Using the Analysis ToolPak instead
The Analysis ToolPak Histogram is useful when you want a worksheet table of frequencies as well as, or instead of, a chart. On Windows, choose Data > Data Analysis > Histogram, specify an Input Range, and optionally specify a Bin Range. Choose an Output Range or a new worksheet, then select Chart Output if you also want a chart. A blank in the input range is not a measured value and is omitted from the frequency counts.
If Data Analysis is missing from the Data tab, enable the add-in through File > Options > Add-ins. At the bottom, choose Excel Add-ins in the Manage box, select Go, and check Analysis ToolPak. On Mac, add-ins are managed from Tools > Excel Add-ins; the exact dialog presentation can vary by Excel release. The ToolPak is not the same thing as the built-in Insert Histogram chart, so look for the appropriate command for the output you need.
When using a Bin Range with the ToolPak, the bin values are upper limits, not labels for the start of each interval. If the bin limits are 10, 20, and 30, the output groups values up to 10, then values above 10 and up to 20, then above 20 and up to 30, followed by an additional category for values above 30. This is a frequent source of confusion when a table seems to have a different number of categories than expected.
Blank cells, zeros, and cells that only look blank
The key distinction is between an empty cell and a numeric zero. A blank contains no value; zero is a value. If a dataset has six rows and one row is genuinely missing, the histogram should usually count five observations. If the measurement for that row was actually zero, entering 0 is appropriate and the histogram should count all six.
Some cells look empty but are not empty. A cell may contain a space, a nonprinting character, a text label such as N/A, or a formula that returns an empty text string. A formula result of an empty string is not the same as a cell that has never contained anything. Excel’s histogram tools are designed to count numbers, so text is not a meaningful numeric observation, but mixed content can make the source range harder to audit and may lead to confusing results in a chart or an exported frequency table.
To check a suspected blank, select the cell and inspect the formula bar. You can also use Excel’s Go To Special command to select genuinely blank cells: press F5, choose Special, and select Blanks. This selects empty cells, not cells containing a space or a formula. If the missing values come from formulas, inspect those formulas and decide whether the empty-text result should be excluded or replaced with a numeric value according to the data’s meaning.
Check the data before interpreting the bars
A histogram can ignore blanks correctly and still give a misleading picture if the source data contain errors, text, or inconsistent units. Before drawing conclusions, verify that the selected range contains the intended measurements and that numbers are stored as numbers. A value such as “12” imported as text may look numeric but is not equivalent to the number 12 in every Excel operation.
Useful checks include:
- Compare the number of numeric observations with the number of rows you expect to have measurements.
- Look for text entries, error values, spaces, and formulas that return empty text in the source range.
- Confirm that values use consistent units and that any real zero has not been left blank by mistake.
- Check the bin width and the first and last bin limits so the chart is not hiding a concentration of values in an overly broad interval.
For a quick count, with the data in A2:A20, enter =COUNT(A2:A20). The result is the number of numeric cells in that range; for example, if there are 16 numbers and four empty cells, it returns 16. To count truly empty cells in the same range, use =COUNTBLANK(A2:A20). That result includes cells that are empty and cells whose formulas return an empty string, so it is not always a count of physically untouched cells. These checks can help explain why a chart has fewer observations than the number of rows in its source range.
Build a frequency table with FREQUENCY
If you want explicit control over counts, the FREQUENCY function returns the number of numeric values in each bin. With values in A2:A7 (2, a blank, 3, 4, 4, and 5) and bin limits 2, 4, and 6 in C2:C4, enter =FREQUENCY(A2:A7,C2:C4) in a clear cell. In current Microsoft 365 Excel, the result spills into four cells: 1, 3, 1, and 0. Those counts mean one value is at most 2, three are above 2 and at most 4, one is above 4 and at most 6, and none are above 6. The blank is not counted.
FREQUENCY returns one more count than the number of bin limits because it includes a final category for values above the last limit. In older Excel versions, the function may need to be entered as a legacy array formula rather than allowed to spill. In either version, make sure the cells below the formula are clear if you are using a spilling result. A blocked spill range produces an error rather than a complete table.
Common reasons a histogram count seems wrong
If the bars do not match your expected row count, first check whether you are comparing the chart’s numeric observations with all worksheet rows. A table may include headings, notes, blank rows, or text labels. Those are not extra measurements. Also check for data outside the selected range: Excel only analyzes the source range used to create the chart or ToolPak output.
Next, distinguish chart settings from data problems. A wide bin can combine many values into one bar, while a narrow bin can create many bars with small counts. Neither choice changes whether a genuine blank is counted, but it can make the distribution look different. Review the axis bin settings and, for the ToolPak, check that the Bin Range is sorted in ascending order and contains the intended upper limits.
Finally, check whether the source includes formulas or imported data. Formulas returning empty text, cells containing spaces, and numbers imported as text can all explain why a range does not behave as a simple list of numbers. Correct the source or create a clean numeric-only helper range when appropriate. Do not replace missing values with zero simply to make the chart include more rows: that changes the data and can create an artificial bar near zero.
Frequently Asked Questions
Does Excel count blank cells as zero in a histogram?
No. A genuinely blank cell is skipped. A numeric 0 is counted and placed in a bin that includes zero.
Does the Excel Histogram chart ignore cells with formulas returning an empty string?
A formula returning an empty string is not a truly empty cell. It is generally treated as nonnumeric rather than as a measured value, but mixed data and chart behavior can make the result less obvious than with a genuine blank. Check the formula and verify the numeric count in the source range.
Does the Analysis ToolPak Histogram ignore blank cells too?
Yes, it does not count a genuinely blank input cell as an observation. It counts numeric values according to the bin limits you provide. Make sure the Input Range does not include unintended headings unless you select the Labels option.
How can I make Excel include missing observations?
First determine what each missing entry means. If it is a real measured zero, enter 0. If the value is unknown, it is usually more accurate to leave it missing and report the number of valid observations separately than to invent a value.
Why does my histogram have fewer observations than my row count?
Some rows may be blank, contain text rather than numbers, be outside the chart’s source range, or contain values that are not valid numeric measurements. Use COUNT on the intended data range and inspect the cells that are not included.
Related
- Excel Heat Map: How to Create and Customize One
- Excel Heat Map Matrix: Create, Format, and Read One
- Excel Heat Map by ZIP Code: Create and Troubleshoot
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Create a Speedometer Chart in Excel: A Step-by-Step GuideTo create a speedometer chart in Excel for Microsoft 365 on Windows, arrange your gauge bands as values…
- Enable Power Pivot in Excel: Windows Steps and OptionsTo enable Power Pivot in Excel for Microsoft 365 on Windows, go to File > Options > Add-ins.…
- Excel Gantt Chart Schedule Template: Build, Format, and UseAn Excel Gantt chart schedule template combines a task list with a dated timeline, then uses conditional formatting…
- Excel Chart Not Displaying Data Correctly? How to FixIf an Excel chart is skipping, mislabeling, or mis-scaling data, select the chart and check Chart Design >…
- Excel Pivot Table: Remove Row TotalsTo remove the bottom grand-total row in an Excel PivotTable, select the table and choose PivotTable Design >…
- Excel Sparklines Examples for Sales, Trends, and KPIsExcel sparklines are tiny charts placed inside worksheet cells, useful for showing a trend beside the number it…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.