Excel’s built-in Histogram chart handles one variable at a time, so it cannot directly make a histogram from two variables. To show how often pairs of values occur, divide each variable into ranges, count the records in every pair of ranges, and display the resulting two-way frequency table as a color-coded grid.
What a two-variable histogram shows
A one-variable histogram groups values along a single scale. For example, it can show how many orders fall into each sales range. A two-variable histogram, often called a bivariate histogram or 2D histogram, groups observations on two scales at once. Each cell represents a combination of ranges, and its value is the number of records that fall into both ranges.
Suppose a dataset contains a measurement called X and another called Y. You could group X into ranges of 0–10, 10–20, and so on, while grouping Y into ranges of 0–100, 100–200, and so on. A cell at the intersection of X = 10–20 and Y = 100–200 counts records that satisfy both conditions. A table with color shading makes areas with many observations stand out.
This is different from plotting two raw variables in a scatter chart. A scatter chart shows each individual pair; a two-variable histogram summarizes those pairs into bins. The summary is useful when there are too many points to read individually, although it hides detail within each bin.
Why Excel does not have a standard 2D Histogram chart
In Excel for Microsoft 365 on Windows, the built-in Histogram chart is designed for one input series. You can create it from selected numeric values using Insert > Insert Statistic Chart > Histogram. It groups those values into bins, but it does not accept one variable for the horizontal axis and another for the vertical axis to produce a grid of joint counts.
The Analysis ToolPak’s Histogram tool also analyzes one input range at a time. It can produce a frequency table and a histogram chart for a single variable, but it is not a two-variable histogram tool. You can still use that feature for separate views of X and Y, but those charts do not show how the variables occur together.
For a bivariate distribution, a practical Excel method is to build a frequency matrix with COUNTIFS and apply a color scale. The matrix is the important statistical result; conditional formatting is a visual aid rather than a separate chart type. This approach is transparent, works with ordinary worksheet data, and lets you choose meaningful bin widths.
Set up bins for both variables
Start with one row per observation and a numeric value for each variable. Keep the two measurements on the same row so Excel can count pairs correctly. Decide on the range and width of bins for each variable before making the table. Equal-width bins are easy to interpret, but the appropriate width depends on the data and the question.
- Put X values in one column and corresponding Y values in the next. Remove or resolve rows where one of the paired values is missing.
- Choose lower and upper boundaries for every X bin and every Y bin. For example, X could use four 10-unit ranges and Y three 100-unit ranges.
- Use consistent boundaries with no gaps or unintended overlaps. A useful convention is to include the lower boundary and exclude the upper boundary, so 10 belongs to 10–20 rather than 0–10.
- Make the last bin wide enough to include the largest observation. If observations can exceed the chosen limit, add another bin or adjust the final boundary.
Equal-width bins are not mandatory. If the data are highly skewed or a meaningful threshold matters, custom boundaries may be more informative. But bins with very different widths can make cell counts difficult to compare: wider bins naturally have more opportunities to collect records. In that situation, consider showing a rate or density rather than raw counts, and label the display clearly.
Build a two-way frequency table with COUNTIFS
Here is a small example with eight paired observations. Enter X values in A2:A9 and matching Y values in B2:B9: (4, 30), (8, 80), (12, 40), (17, 130), (22, 170), (26, 240), (31, 210), and (35, 280). Set up four X bins with lower limits in D4:D7 and upper limits in E4:E7: 0–10, 10–20, 20–30, and 30–40. Put the lower limits of three Y bins in J1:L1 and their upper limits in J2:L2: 0–100, 100–200, and 200–300. The count matrix will occupy J4:L7.
With the paired data in A2:B9, the X lower and upper limits in D4:E7, and the Y lower and upper limits in J1:L2, enter this formula in J4. It counts observations in the first X bin and first Y bin; copy it across and down to fill J4:L7:
=COUNTIFS($A$2:$A$9,">="&$D4,$A$2:$A$9,"<"&$E4,$B$2:$B$9,">="&J$1,$B$2:$B$9,"<"&J$2)
For this example, the resulting rows are 2, 0, 0; then 1, 1, 0; then 0, 1, 1; then 0, 0, 2. The counts add to 8, matching the eight input pairs. For instance, (12, 40) falls in X = 10–20 and Y = 0–100, while (26, 240) falls in X = 20–30 and Y = 200–300. The formula uses inclusive lower limits and exclusive upper limits; the maximum example value, 35, is included in the 30–40 bin.
Label the rows and columns with the corresponding ranges so readers can interpret the cells without referring to the boundary helper cells. If you change the data size, update the input ranges or convert the source data to an Excel Table and use table references. Keep each X value aligned with its Y partner; sorting one column independently would corrupt the pairs and therefore the counts.
Turn the matrix into a readable visual
The simplest visual is a heatmap-style frequency table. Select only the cells containing counts, then choose Home > Conditional Formatting > Color Scales and select a scale. Include row and column labels, identify which variable is on each axis, and give the table a title such as “Number of observations by X and Y range.” A scale with a light color for small counts and a dark color for large counts makes clusters easier to spot.
Use a color scale thoughtfully. If zero is common, a neutral color for zero can distinguish empty bins from bins with a few observations. If the counts have a wide range, a few large values can make smaller differences hard to see. You can choose another scale or adjust its rules through Home > Conditional Formatting > Manage Rules. Keep the same scale when comparing two matrices; otherwise, equally colored cells may represent different counts.
A frequency table is often clearer than a 3D surface chart because it preserves exact counts and makes the bin labels visible. Excel can create other chart types from a matrix, but a surface or column display may introduce perspective, obscure small values, or suggest a smooth continuous surface where the data are actually grouped. If you do use a chart, state that the heights or colors represent binned counts, not individual measurements.
Choose between a heatmap, scatter chart, and other options
The best display depends on what you need to learn. A binned count grid answers, “Where do many observations fall?” A scatter chart answers, “What is the shape of the relationship between individual X and Y values?” Neither replaces the other in every situation.
- Use a two-way frequency table with color formatting when you want a compact view of concentration across chosen ranges, especially with many records.
- Use a scatter chart when individual observations, outliers, clusters, or a possible linear relationship matter. Select the paired columns and choose Insert > Insert Scatter (X, Y) or Bubble Chart > Scatter.
- Use separate one-variable histograms when you want to compare the marginal distributions of X and Y, but do not need to show which values occur together.
- Use a PivotTable when you want interactive summaries and are comfortable grouping numeric fields. A formula-based matrix can be easier to audit when you need explicit bin boundaries.
A frequent misconception is that putting two series into a Histogram chart creates a two-variable histogram. Multiple series may be displayed or compared in some chart arrangements, but that does not count paired observations into X-by-Y bins. To show joint frequencies, the table must apply both conditions to each record, as COUNTIFS does.
Common problems and checks
If every cell is zero, check that the source columns contain numbers rather than text that looks numeric, and confirm that the lower and upper limits use the same units as the measurements. Also check that the COUNTIFS ranges are the same size and that the boundary references moved as intended when the formula was copied. Absolute references keep the source ranges fixed; mixed references let the relevant bin limits change by row or column.
If the sum of the matrix is smaller than the number of valid paired rows, some values may fall outside the bin limits, or some pairs may be blank or nonnumeric. If the sum is larger, bins may overlap, or the formula may be counting the same row in more than one category. The lower-inclusive, upper-exclusive convention avoids overlap when one bin ends exactly where the next begins. To include the overall maximum, set the last upper limit above it, or deliberately handle the final boundary separately.
Another common issue is choosing bins that are too narrow or too wide. Too many narrow bins produce a sparse grid with mostly zeros; too few broad bins conceal useful structure. Try a few reasonable widths and compare whether the pattern remains understandable. Record counts are sensitive to the selected boundaries, so include the bin widths in the labels or accompanying notes.
For a dataset that changes regularly, use an Excel Table for the source rows and keep the bin boundaries in clearly labeled cells. The table expands as new observations are added, while the frequency matrix remains easier to maintain if its formulas refer to the table’s paired columns. In Excel for the web, ordinary COUNTIFS formulas and conditional formatting are available, though some desktop analysis add-ins and chart options may differ from the Windows application.
Frequently Asked Questions
Can Excel make a histogram with two variables?
Not with the standard Histogram chart directly. Create bins for both variables, count the pairs in a two-way table, and use conditional formatting or another suitable visualization.
Is a two-variable histogram the same as a scatter plot?
No. A scatter plot shows individual X-Y pairs. A two-variable histogram groups pairs into ranges and shows the count in each range combination.
How do I show the counts as colors in Excel?
Select the frequency cells and choose Home > Conditional Formatting > Color Scales. Keep the bin labels visible and explain what the colors represent.
What should I do with values on a bin boundary?
Use a consistent rule, such as including the lower boundary and excluding the upper boundary. With adjacent bins of 0–10 and 10–20, a value of 10 then belongs only to 10–20.
Can a PivotTable make a two-variable histogram?
A PivotTable can summarize two grouped numeric fields into a cross-tab, depending on how the fields are arranged and grouped. Explicit formula bins are often more predictable when exact boundaries matter.
Make the result interpretable
A useful two-variable histogram is more than a grid of numbers: it clearly identifies the paired measurements, bin boundaries, and meaning of the counts. Check that the cell totals reconcile to the valid observations, use a consistent boundary rule, and choose a display that does not hide the pattern you are trying to examine. For most Excel users, a COUNTIFS frequency matrix with a carefully labeled color scale is a straightforward way to see where two variables occur together.
Related
- Excel Table Structured References: Rules, Examples & Tips
- Excel Take Data From a Table: Methods and Examples
- Excel Table Data Filtering: How to Narrow Rows
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- How to Update Pivot Table Data in Google SheetsGoogle Sheets pivot tables update automatically when cells in their source range change. If you add rows or…
- Excel Histogram With a Line Chart: How to Build OneYes. In Excel for Microsoft 365 on Windows, the most dependable way to put a line on a…
- Excel Radar Chart: Change the Axis ScaleIn Excel for Microsoft 365 on Windows, select the radar chart’s value axis, open Format Axis, and enter…
- Excel Dashboard: How to Build a Clear, Useful ReportTo make a dashboard in Excel for Microsoft 365 on Windows, put clean data in an Excel Table,…
- Excel Power Pivot KPIs: How to Create and Use ThemAn Excel Power Pivot KPI is a Key Performance Indicator built from a Data Model measure and compared…
- Butterfly Graph in Excel: How to Build and Format OneTo make a butterfly graph in Excel, put one comparison series in negative values and the other in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.