To make a normal Q-Q plot in Microsoft Excel for Microsoft 365, sort your observations, calculate matching standard-normal quantiles with NORM.S.INV, and plot the quantiles against the sorted observations in an XY scatter chart. Excel does not have a dedicated Q-Q chart button, but this worksheet method creates one and lets you assess whether your data are reasonably consistent with a normal distribution.
What a Q-Q plot shows
A quantile-quantile plot, usually called a Q-Q plot, compares quantiles from your observed data with quantiles from a reference distribution. A normal Q-Q plot uses the normal distribution as its reference. Each point pairs one ordered observation with the normal-theory quantile expected at the same rank.
If the observed distribution is close to normal, the plotted points tend to follow a roughly straight line. The line does not have to pass through zero or have a slope of one: the sample’s center and spread determine its position and slope. Curvature or unusually distant points can indicate departures from normality, but a Q-Q plot is a diagnostic, not a pass-or-fail test.
Q-Q plots are useful before methods that assume normally distributed errors or measurements. For example, you might use one to inspect regression residuals or a small set of laboratory measurements. The relevant assumption depends on the analysis: in regression, it is often the residuals—not necessarily the raw outcome values—that should be checked.
Does Excel have a built-in Q-Q plot?
No dedicated Q-Q chart type is provided in Excel for Microsoft 365 on Windows. You can make the chart from a sorted data column and a column of theoretical quantiles, then use an XY scatter chart. This is different from a histogram, which shows frequencies in bins, and from a normal probability plot option found in some specialist statistics packages.
The steps below use dynamic-array functions available in Microsoft 365 and Excel 2021 or later. They are suitable for a clean, contiguous column of numeric observations. Excel for Mac has the same relevant functions; ribbon placement and some chart-formatting controls can differ slightly. Excel for the web also supports many of these functions, although chart editing options may be more limited.
Prepare the observations
Put one observation in each cell of a single column, with no heading inside the data range. For a short worked example, enter 8, 9, 10, 11, and 12 in A2:A6. These five values make it easy to verify the calculations, but they are only an illustration; a real analysis usually benefits from substantially more observations.
Before plotting, check that the range contains numbers rather than numbers stored as text. Decide how to handle blanks, errors, and missing observations rather than allowing them to be treated as data. A Q-Q plot can include tied values, but ties produce repeated vertical positions and may make the pattern look less smooth. Do not remove a valid extreme value simply because it makes the plot look less straight.
Calculate sorted values and normal quantiles
In the example, the observations are in A2:A6. Put this formula in B2 to sort them in ascending order; Excel spills the five results into B2:B6:
With the example observations in A2:A6, enter in B2: =SORT(A2:A6). It returns 8, 9, 10, 11, and 12 in order.
Next, calculate a plotting position for each rank. This example uses the common formula (rank − 0.5) divided by the sample size. With the sorted values spilling from B2, enter in C2: =(SEQUENCE(ROWS(B2#))-0.5)/ROWS(B2#). It returns 0.1, 0.3, 0.5, 0.7, and 0.9. For instance, the first position is (1 − 0.5) / 5 = 0.1, and the last is (5 − 0.5) / 5 = 0.9.
Convert those probabilities into standard-normal quantiles. With the probabilities spilling from C2, enter in D2: =NORM.S.INV(C2#). The results are approximately −1.2816, −0.5244, 0, 0.5244, and 1.2816. The 0.5 probability maps to zero, as expected for the median of the standard normal distribution.
The notation with a trailing # refers to the full spilled array in that cell’s output range. If you change the example to use a larger set, update the input range in the sorting formula; the formulas based on B2# and C2# then expand to the number of sorted observations. Keep the three output columns aligned, since each row must pair one theoretical quantile with the observation at the same rank.
Create the scatter chart
- Select the theoretical quantiles in D2:D6 and the sorted observations in B2:B6. If Excel does not assign the axes as intended, create a blank chart and specify the series explicitly: use D2:D6 for X values and B2:B6 for Y values.
- Choose Insert > Insert Scatter (X, Y) or Bubble Chart > Scatter. Select the basic scatter chart with markers, not a chart with connected lines.
- Add clear labels. Choose Chart Design > Add Chart Element > Axis Titles > Primary Horizontal and Primary Vertical. Label the horizontal axis “Theoretical normal quantiles” and the vertical axis “Sorted observations.” Give the chart a descriptive title such as “Normal Q-Q Plot.”
Using theoretical quantiles on the horizontal axis and ordered sample values on the vertical axis is the standard orientation for this explanation. Some software reverses the axes; the straight-line pattern and shape remain interpretable as long as you know which quantity is on each axis. On Mac, the chart commands are similar, though some versions present chart tools in a different ribbon layout.
Read the shape, not just the line
Look first for an overall straight pattern. Points close to a line suggest that the data are compatible with a normal distribution, but do not prove that they are normal. With a small sample, substantial departures can be hard to detect; with a very large sample, minor departures become visible even when they may have little practical consequence.
- A smooth bend or S-shaped pattern suggests that the distribution’s shape differs from a normal distribution. The direction and form of the curve provide clues, but interpretation depends on which axis is which.
- Points that pull away sharply at one or both ends can signal unusually heavy or light tails, or possible outliers. Check the source records and the context before deciding what they mean.
- A single point separated from the general pattern may be an unusual observation. It is a reason to investigate, not automatic grounds for deleting it.
- Repeated horizontal positions can occur when the data contain ties. This is common with rounded measurements, ratings, or counts.
The plotted points generally will not lie exactly on a line, even when the underlying population is normal. Random sampling variation creates scatter, and the expected amount varies with sample size. Consider the plot alongside subject-matter knowledge and other diagnostics rather than treating visual alignment as a formal statistical test.
Optional reference line and common mistakes
A straight reference line can make the visual pattern easier to judge. One quick option is to select a chart point and choose Chart Design > Add Chart Element > Trendline > Linear. This adds a fitted line through the plotted points. It is a visual guide, not a test of normality, and extreme observations can influence the fitted line.
For a more deliberate reference, some statistical methods draw a line through selected quartiles instead. The exact convention varies, so avoid comparing the line from one method with a different convention as if they were identical. Excel’s automatically fitted trendline is convenient, but it is not necessarily the reference line used by statistical software.
Common problems are usually setup issues rather than a failure of the Q-Q method:
- Using a line chart: A line chart treats the horizontal positions as categories. Use an XY scatter chart so the theoretical quantiles are numeric X values.
- Plotting unsorted data: The observed values must be ordered from smallest to largest before matching them to quantiles.
- Swapping or misaligning ranges: Each sorted observation must be paired with the theoretical quantile for its rank. Confirm that the X range is D and the Y range is B in the example.
- Including blanks or text: Clean the data range first. If your real dataset has missing values, filter to valid numeric observations before creating the sorted list.
- Expecting a perfect line: Real samples vary. Small bends alone do not establish that a normality assumption is unacceptable.
Alternatives and when to use them
For a quick view of shape, a histogram is simpler to create in Excel, but its appearance depends on the chosen bin widths and it does not pair observations with theoretical quantiles. A box-and-whisker chart summarizes center, spread, and possible outliers, but also does not directly compare the full sample distribution with a normal reference.
If you need formal normality testing, confidence bands, or a line based on a particular statistical convention, a dedicated statistics program may be more suitable. Excel’s chart is still useful for exploration and communication, especially when you want the calculations visible and editable in the workbook. Keep a record of the plotting-position convention and any filtering decisions so another reader can reproduce the plot.
Frequently Asked Questions
Can Excel make a Q-Q plot automatically?
No. Excel does not provide a dedicated Q-Q plot command. Create theoretical quantiles and sorted observations in worksheet columns, then insert an XY scatter chart.
Which Excel function gives normal quantiles?NORM.S.INV returns the inverse standard-normal cumulative distribution for probabilities between 0 and 1. In the method above, it converts each plotting probability into a theoretical normal quantile.
Why do my Q-Q plot points curve?
A curve can indicate that the observed distribution differs from normality, but first check sorting, axis assignment, data types, and extreme values. A visible curve is a diagnostic clue, not by itself a conclusion about the cause or importance of the difference.
How many observations do I need?
There is no universal minimum that makes a Q-Q plot conclusive. Very small samples provide limited information, while larger samples can reveal subtle departures. Use the plot in context and consider the requirements of the analysis you are performing.
Can I make a Q-Q plot in older Excel versions?
Yes, but the dynamic-array formulas SORT and SEQUENCE are not available in some older releases. In those versions, sort a copy of the data with Data > Sort and fill plotting-position and NORM.S.INV formulas down one row per observation, adjusting the formulas to the sample size.
Related
- Excel Circle Bullets: How to Add, Format, and Align Them
- 4-Segment Circle in Excel: How to Make One
- Excel Enum Cell: What It Means and How to Create One
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Dependent Drop-Down Lists in TablesYes. In Excel for Microsoft 365 on Windows, you can put a dependent drop-down list in an Excel…
- How to Calculate VaR 99 in ExcelTo calculate one-day historical VaR at 99% confidence in Excel, use the first percentile of portfolio returns and…
- How to Use Styles from Another Word Document in WordTo use styles from another Word document, copy them with Word’s Organizer, then apply the imported styles to…
- Excel Column Stays When Scrolling? Freeze Panes GuideYes. In Excel for Microsoft 365 on Windows, choose View > Freeze Panes > Freeze First Column to…
- How to Set Double Spacing in WordTo double-space text in a Word document, select the text, then choose Home > Line and Paragraph Spacing…
- Excel Scatter Plot Bubble Size: Set It Up and Read ItIn Excel, a standard XY scatter plot uses markers of a fixed size; to make marker size represent…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.