To make a normal Q-Q plot in Excel, sort your numeric observations, calculate matching theoretical normal quantiles with NORM.S.INV, then plot the theoretical quantiles on the x-axis against the sorted observations on the y-axis in an XY scatter chart. A roughly straight pattern suggests the data are reasonably consistent with a normal distribution; systematic curves or distant points can signal departures worth investigating.
What a Q-Q plot shows
A Q-Q plot, short for quantile-quantile plot, compares quantiles from a sample with quantiles from a chosen reference distribution. For a normal Q-Q plot, the reference is a normal distribution. Each dot pairs one ordered observation with the normal quantile expected at the same rank. If the sample follows a normal distribution reasonably well, the dots tend to fall near a straight line.
The plot is a visual diagnostic, not a pass-or-fail test. Its shape can reveal features that a histogram may hide, including skewness, unusually heavy or light tails, and individual outliers. It also does not prove that the data are normal: results should be considered alongside the data’s context, sample size, and the assumptions of the analysis you plan to perform.
Prepare the data in Excel
Put the observations in one column, with a single numeric value in each row. For the worked example below, enter these eight values in A2:A9: 4, 5, 5, 6, 7, 8, 9, and 12. The example is intentionally small enough to check by eye; with only eight points, it is not a strong basis for deciding whether a real dataset is normal.
- Remove labels from the data range, or keep the heading above the first observation and exclude it from every formula.
- Check that values are genuine numbers rather than text that only looks numeric. Blank cells, error values, and text entries can disrupt calculations.
- Decide how to handle missing observations before plotting. Do not replace missing values with zero unless zero is genuinely the observed value.
- Repeated values are allowed. They create tied observations, which can make several dots share the same vertical position.
For a larger dataset, keep all the values in one uninterrupted range where possible. If data are filtered or split across non-adjacent areas, confirm that the formulas and chart use the intended observations only.
Calculate ordered observations and normal quantiles
In this example, column A contains the eight observations. Column B will hold them in ascending order, column C will hold plotting positions, and column D will convert those positions into theoretical standard-normal quantiles. Enter the first formula in row 2 and fill it down through row 9.
With the original observations in A2:A9 and the first ordered result in B2, enter =SMALL($A$2:$A$9,ROWS($B$2:B2)) in B2 and copy it down. The first result is 4, the smallest value; the last result in B9 is 12. The absolute references keep the source range fixed as the formula is copied, while the ROWS reference increases the requested rank.
With the same observations in A2:A9 and the first rank in row 2, enter =(ROWS($B$2:B2)-0.5)/COUNT($A$2:$A$9) in C2 and copy it down to C9. The first plotting position is (1 − 0.5) / 8 = 0.0625, and the last is 0.9375. These positions put the probabilities between zero and one, avoiding infinite normal quantiles at the endpoints.
With plotting positions in C2:C9, enter =NORM.S.INV(C2) in D2 and copy it down. D2 returns approximately −1.5341, the standard-normal quantile at probability 0.0625; D9 returns approximately 1.5341. Values shown in the worksheet may have more decimal places, so formatting the cells to four decimals is only a display choice and does not change the underlying values.
SMALL, ROWS, COUNT, and NORM.S.INV are valid Excel functions in Microsoft 365 for Windows. This approach avoids relying on newer dynamic-array functions and works for a conventional rectangular sample range. If your data range changes size, update the fixed range in the formulas or create an Excel Table and use a consistent range for all calculations.
Build the scatter chart
Use theoretical normal quantiles as x-values and sorted observations as y-values. In Microsoft 365 for Windows, insert an XY scatter chart and specify the series ranges:
- Choose Insert > Insert Scatter (X, Y) or Bubble Chart > Scatter. Excel may first create an empty chart if you have not selected a data range.
- Select the chart, then choose Chart Design > Select Data > Add.
- Set the series name to something descriptive, such as Normal Q-Q. Set Series X values to
=$D$2:$D$9and Series Y values to=$B$2:$B$9, then confirm the dialogs. - Add axis titles so readers know that the horizontal axis is the theoretical standard-normal quantile and the vertical axis is the observed value. A chart title such as Normal Q-Q Plot is also useful.
Check that Excel has made an XY scatter chart, not a line chart with category labels. An XY chart positions each point according to its numerical x-value; a category chart spaces points evenly and does not represent theoretical quantiles correctly. On Excel for Mac, the chart types and series settings have equivalent controls, though ribbon layout and dialog appearance can differ. Excel for the web also supports scatter charts, but the available chart-editing controls may vary; desktop Excel is a practical choice when you need to set series ranges precisely.
Read the shape, not just the line
In a normal Q-Q plot, look first for the overall pattern. A fairly straight band is consistent with approximate normality. The points need not land exactly on a line, especially in a small sample, where random variation can create visible irregularities.
- Curvature: A smooth bend can indicate skewness or another departure from normal shape. The direction and form of the bend matter; do not label every curve the same way without checking the data.
- Tail departures: If the points at one or both ends pull away from the central pattern, the sample tails may differ from normal tails. Large departures at both ends can be consistent with heavier or lighter tails, but investigate the observations and context before concluding why.
- An isolated point: A point separated from the others may be an unusual observation. Check the source data for entry or measurement errors, but do not delete a valid observation merely because it looks inconvenient.
- A straight line with a different slope or intercept: This alone does not mean the sample is non-normal. A normal distribution can have any mean and positive standard deviation; those parameters affect the line’s position and steepness.
The small example includes a high value of 12. Its location relative to the other observations may draw attention, but eight values are too few for a confident general conclusion. A Q-Q plot helps frame a question for further checking; it does not identify the cause of a pattern.
Use a reference line carefully
A reference line makes deviations easier to see, but Excel’s chart trendline is not the only possible choice. To add a quick fitted line in Microsoft 365 for Windows, select the scatter chart and choose Chart Design > Add Chart Element > Trendline > Linear. This fits a straight line to the plotted points. It can be useful as a visual guide, but because it is fitted to the same points, it may make a systematic departure look less obvious.
Another common approach is a line based on sample and theoretical quartiles. Its slope reflects the sample’s spread relative to the standard normal, and its intercept reflects location. This kind of line can offer a useful reference without being a formal test. Excel does not turn the chart into a definitive normality test simply by adding either line. State which reference line you used if the chart will be shared or used in a report.
Common problems and how to fix them
- The chart looks like a staircase or has evenly spaced x-values: Confirm that you inserted an XY scatter chart and assigned column D as X values. A line chart may treat the rows as categories.
- The points run from upper left to lower right: The series ranges may be reversed or the x-axis may be displaying observed values. Set theoretical quantiles as X and ordered observations as Y.
- Excel shows an error or unexpected result: Check for text, blanks, and errors in the source range. Confirm that the range in COUNT and SMALL covers exactly the same numeric observations used in the chart.
- Several points overlap: Ties in the original data produce repeated sorted values. You can make markers smaller or partly transparent, but overlapping points do not necessarily indicate a formula mistake.
- The plot seems to change when data are edited: The formulas and chart need to include the added rows. Copy formulas down and update the series ranges, or use a structured data setup that expands consistently.
Formatting can improve readability, but avoid connecting the dots as though they were a time series. A Q-Q plot compares ranks; the points are not sequential measurements over time.
Q-Q plots versus histograms and tests
A histogram shows the distribution’s overall shape, but its appearance depends on the chosen bin widths. A Q-Q plot compares individual ordered values with reference quantiles and often makes tail departures easier to notice. The two views complement each other: a histogram can communicate overall shape, while a Q-Q plot helps reveal where observations diverge from a normal reference.
A statistical normality test answers a different question and produces a test statistic or p-value under its assumptions. Excel does not provide a built-in worksheet function called a Q-Q plot or a universal one-click normality test. Some add-ins and external statistical tools offer formal tests, but a visual plot should not be presented as a p-value or as proof of normality. Whether normality matters also depends on the analysis: some procedures require a particular model’s errors or residuals to be approximately normal, not necessarily the raw predictor or response values.
Frequently Asked Questions
Does Excel have a built-in Q-Q plot function?
No dedicated Q-Q plot command is required or built into the standard chart menu. You can calculate ordered values and theoretical quantiles with worksheet formulas, then display them in an XY scatter chart.
Which axis should contain the theoretical quantiles?
For the method described here, put theoretical standard-normal quantiles on the horizontal axis and ordered sample observations on the vertical axis. Some software uses the opposite orientation; either can show the comparison if the axes are clearly labeled, but keep the convention consistent.
Can I make a Q-Q plot for a distribution other than normal?
Yes. The method is to compare sample quantiles with quantiles from the chosen reference distribution. The theoretical-quantile formula must match that distribution, so the normal-quantile formula shown here is specifically for a normal Q-Q plot.
How many observations do I need?
There is no single sample-size cutoff that makes a Q-Q plot reliable. With very few observations, the pattern is difficult to assess; with large samples, small departures can become visually noticeable. Interpret the plot in light of the sample size and the practical importance of the departure.
Does a straight Q-Q plot prove my data are normal?
No. A straight-looking pattern is evidence of compatibility, not proof. Different distributions can look similar in a limited sample, and the plot cannot establish that data collection or other model assumptions are appropriate.
When to use another tool
Excel is convenient for a transparent, reusable Q-Q plot when the dataset is modest and you want to inspect the calculations. For repeated analysis, very large datasets, automated reporting, or formal distribution fitting and testing, statistical software may provide more specialized tools. Regardless of the software, retain the original observations, label the axes and reference line, and explain what the plot can and cannot establish.
Related
- Analysis Group in Excel: Where It Is and How to Use It
- Excel Analysis ToolPak: Setup, Uses, and Limitations
- Excel Circle Bullets: How to Add, Format, and Align Them
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Make One Page Landscape in Word for MacTo make just one page landscape in Word for Mac, isolate it in its own section with a…
- Excel Flash Fill for Number Sequences: How It WorksExcel Flash Fill can sometimes extend a number pattern when you show it examples, but it is designed…
- How to Open an Excel Link in the Desktop App on WindowsTo open a shared Excel link in the desktop app on Windows, open the link in a browser,…
- Freeze Just the Top Two Rows in Excel: A Quick GuideTo keep only the top two rows visible while you scroll in Excel for Microsoft 365 on Windows,…
- Excel Text to Value: Convert Numbers Stored as TextTo turn numeric text into a real Excel number, select the cells and choose Convert to Number from…
- How to Add a Draft 2 Watermark in Word on Every PageTo add a DRAFT 2 watermark in Word for Microsoft 365 on Windows, open the document and choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.