OmniDocHow to Make a Frequency Chart in Excel
Learn how to count values into bins in Excel and chart them as a distribution, with formula options, a checklist, and fixes for common errors.
If you have survey scores, test results or response times and want to see how often values fall in each range, a frequency chart is the tool you need. A plain bar chart of every row would be unreadable, and an average would hide the shape of the data. An Excel chart frequency view counts how many values land in each bin and plots those counts so you can see where the data clusters and where it thins out.
This guide shows how to set up bins, count the values in each one, and turn the counts into a chart. It covers two counting methods, FREQUENCY and COUNTIFS, and explains what to do when your numbers sit inside a PDF. The chart itself is the last step. The counts behind it are what make the picture accurate.
The short answer is that you list bin limits, count the values in each bin with a formula, and plot those counts as a column chart with the gaps removed.
How do you make a frequency chart in Excel? The short answer
Count the values in each bin with FREQUENCY or COUNTIFS, then chart the counts as columns with little or no space between them.
| Your situation | What to do | Why |
|---|---|---|
| Numbers you want grouped into ranges, such as 0 to 10, 11 to 20 | Use FREQUENCY with a list of bin upper limits | One formula returns every count at once |
| You want each count to be easy to read and audit | Use COUNTIFS in one row per bin | Each formula shows its own conditions |
| Categories such as product names or days of the week | Count each category with COUNTIF, or use a pivot table | FREQUENCY only works on numbers |
| Your data is in a PDF table | Export the table to Excel with the PDF editor, then check the cells | Counts go wrong if numbers come through as text |
| Your data is in a scanned PDF | Run OCR on the file first, then export it | Export needs readable text to work with |
Before you start
Check these points before you build anything. Each one prevents a common error that makes the chart look right while the counts are wrong.
- Confirm the values are numbers. Numbers that are stored as text are ignored by FREQUENCY and COUNTIFS, so the bins come out too low. Right-aligned values usually indicate real numbers.
- Remove headers and empty cells from the data range. A header or a blank cell inside the range can be read as a value or distort the total, so keep the data in one clean column.
- Choose a bin width. Equal widths are easiest to read. For a few dozen to a few hundred values, five to ten bins usually show the shape without too much noise.
- Decide how boundary values are handled. FREQUENCY counts a value equal to a limit in the lower bin, so a score of 20 falls into "10 to 20", not "21 to 30". Write this rule down so your labels match.
- Keep the raw data on its own sheet. If you need to change the bins later, you can recalculate without retyping anything.
Building a frequency chart in Excel, step by step
The example below uses 100 response times in seconds, stored in cells A2 to A101. Replace the cell addresses with your own.
- Enter the raw values. Put a header in A1, such as "Response time (s)", and the values in A2 to A101 so the data sits in one column.
- List the bin upper limits. In C2 to C6, type 10, 20, 30, 40 and 50. Each bin includes values up to and including its limit.
- Count with FREQUENCY. Select five cells in D2 to D6, which is one more than the number of limits, and enter =FREQUENCY(A2:A101,C2:C6). The last cell, D6 in this layout, counts values above 50, so the result is one row longer than your bin list.
- Confirm the array in older versions. In recent versions of Excel the formula fills the selected cells when you press Enter. In older versions, press Ctrl+Shift+Enter instead, which makes the formula fill the whole block.
- Build the labels. In B2 to B6, type "0 to 10", "11 to 20", "21 to 30", "31 to 40" and "Over 40". The labels should match the rule you chose for boundary values.
- Use COUNTIFS if you prefer one formula per row. In D2, enter =COUNTIF(A2:A101,"<="&C2). In D3, enter =COUNTIFS(A2:A101,">"&C2,A2:A101,"<="&C3) and fill it down. Use COUNTIF with ">"&C6 in the last row to count values above the final limit.
- Insert a column chart from the labels and counts. Select the label column and the count column, then insert a column chart from the chart options.
- Remove the gaps and add titles. Set the gap width between columns to zero or close to it so the chart reads as a histogram. Add a chart title, axis titles for the bins and the count, and remove any gridlines that compete with the bars.
If you want percentages instead of counts, add a column that divides each count by the total number of values, using COUNT on the raw range, and format it as a percent.
Check the result
Run these checks before you use the chart in a report or presentation. Most errors show up as a total that does not match the number of values.
| Check | How to see it | If it's wrong |
|---|---|---|
| The counts add up to the total | Sum the count column and compare it with COUNT on the raw range | Look for text values, blanks or a missing overflow cell |
| Boundary values fall in the right bin | Find a value equal to a limit, such as 20, and confirm which bin it is in | Change the labels or the formula so they follow the same rule |
| The chart matches the table | Compare each column height with its count | Reselect the label and count ranges, then rebuild the chart |
| Numbers are numeric | Compare COUNT and COUNTA on the raw range | Convert text values to numbers, then recalculate |
| The data came from a PDF | Compare a few rows with the original PDF | Re-export the table, or fix the cells that were misread |
If your values came through OCR, check more rows than usual. Scanned text can be misread, such as a 5 read as an S, and a single wrong value can move a count into the wrong bin. Important figures should always be checked against the source.
📊 At a glance
There are three common ways to build a frequency count in Excel. Each suits a different kind of data.
| Method | Works with | Trade-off |
|---|---|---|
| FREQUENCY | Numeric values in bins | Fills several cells at once, and older versions need array confirmation |
| COUNTIFS | Numbers or conditions, one bin per row | Each row needs its own formula, but each one is easy to audit |
| Pivot table | Categories or grouped numbers | Needs a refresh when the source data changes |
❓ FAQ
1. Why does my FREQUENCY formula show only one number?
You probably entered it in a single cell. Select the full block of cells, one more than the number of bin limits, and enter the formula so it fills the block. In older versions of Excel, press Ctrl+Shift+Enter.
2. Is a frequency chart the same as a histogram?
They are very close. A histogram plots the frequency of numeric values in bins with no gaps between columns. A bar chart of categories is also a frequency chart, but the gaps between its bars show that the categories are separate.
3. How wide should each bin be?
Choose a width that gives you about five to ten bins for your data. Too few bins hide the shape of the distribution, and too many make the chart noisy.
4. Can I build the chart in OmniDoc?
OmniDoc's PDF editor can export tables from a PDF to Excel, which is useful if your data starts in a PDF. The chart itself is built in Excel, using the steps above.
📝 Summary
A frequency chart starts with bins and counts, and the chart only shows what those counts contain. Build the counts with FREQUENCY or COUNTIFS, check that they add up, then plot them as columns. Whether your data came from a spreadsheet or a PDF, an Excel chart frequency view is only as accurate as the numbers behind it.
