To graph z scores in Excel, calculate each score’s z value, then plot the results with a histogram or an XY scatter chart. A z score shows how many standard deviations a value is above or below the mean. This guide uses Microsoft Excel for Microsoft 365 on Windows and a small worked example; you can use the same process with your own numerical data.
What You’ll Need
- Excel for Microsoft 365 on Windows. Excel for Mac has similar chart and formula features, though ribbon placement can vary slightly.
- A column of numerical observations, such as test scores, measurements, or response times. Use comparable values from the same group.
- A decision about which standard deviation to use: sample or population. For a sample drawn from a larger group, use the sample standard deviation. For a complete population, use the population standard deviation.
Step-by-Step
- Enter and check your data. Put a clear heading such as Score in A1, then enter one numeric observation per row in A2:A7. For the example, enter 62, 68, 74, 80, 86, and 92. Keep text labels and blank rows out of the data range where possible. Correct obvious entry errors before calculating: a misplaced decimal or an accidentally included total can shift the mean and standard deviation, changing every z score.
- Calculate the mean. Click an empty cell, such as B2, and label it Mean in B1. With the example scores in A2:A7, enter
=AVERAGE(A2:A7)in B2. The result is 77. The mean is the centre used to measure how far each observation lies from the group average. - Calculate the standard deviation. Put Standard deviation in C1. With the example values still in A2:A7 and treating them as a sample, enter
=STDEV.S(A2:A7)in C2. The result is about 11.225. Use=STDEV.P(A2:A7)instead only when those observations are the entire population you want to describe; its result here is about 10.247. Do not switch between these choices casually, because the resulting z scores will differ. - Calculate a z score for each observation. Enter Z score in B4. With the raw score in A2, the mean in B2, and the standard deviation in C2, enter
=STANDARDIZE(A2,$B$2,$C$2)in B5, then copy or fill the formula down through B10. The dollar signs keep the mean and standard deviation references fixed as the formula is filled. For the sample example, the z scores are approximately -1.336, -0.802, -0.267, 0.267, 0.802, and 1.336. A negative value is below the mean; a positive one is above it. - Check the calculations before charting. The z score for a value equal to the mean should be zero, apart from a tiny rounding difference. In this example, 80 is 3 points above 77, and 3 divided by 11.225 is about 0.267, matching its z score. The example z scores also balance around zero because the deviations from the mean balance. If your results look implausible, check that the formula uses the correct data range and the intended standard deviation.
- Choose a chart that matches your question. Use a histogram to show the shape and spread of a larger set of z scores. Use a scatter chart when you want to inspect every observation or compare z scores with another numeric variable. A histogram is usually more useful for a distribution; a scatter chart is better for seeing individual cases. With only six example observations, either chart is mainly a demonstration, not strong evidence about the shape of a population.
- Create a histogram of the z scores. Select the z-score cells only, such as B5:B10 in the example. On Windows, choose Insert > Insert Statistic Chart > Histogram. Excel creates bins automatically. To adjust them, right-click the horizontal axis, choose Format Axis, and set a Bin width or a number of bins under Axis Options. A width such as 0.5 can make a small z-score range easier to read, but choose bins that suit your dataset rather than forcing a particular appearance.
- Label and format the chart. Click the chart title and replace it with a descriptive title, such as Distribution of Z Scores. Use axis titles from Chart Design > Add Chart Element > Axis Titles if the chart needs them. Label the horizontal axis Z score and the vertical axis Frequency. Keep the zero point and negative values visible; they are meaningful, not errors. Avoid excessive decimal places in labels. For an accessible chart, use readable text and do not rely on colour alone to convey information.
- Make an individual-value scatter chart when needed. Add an observation number or category in C4:C10 and put Z score in D4, with the calculated z values in D5:D10. Select the category and z-score columns, then choose Insert > Scatter (X, Y) or Bubble Chart > Scatter. If the categories are text, Excel may treat them as numeric positions in an XY chart; for named categories, a line chart with markers or a column chart may display labels more clearly. Give the axes meaningful titles and check that the plotted values match the worksheet.
- Optionally draw the standard normal curve. This is useful when you want to compare a distribution with a theoretical normal shape, but it is not a substitute for the histogram. In D2:D26, enter values from -3 to 3 at equal intervals, such as -3, -2.75, and so on. With a z value in D2, enter
=NORM.S.DIST(D2,FALSE)in E2 and fill down; this returns the standard normal probability density at each z value. Select columns D and E and choose Insert > Scatter (X, Y) or Bubble Chart > Scatter with Smooth Lines. Title the chart and label the axes Z score and Probability density. This curve represents a theoretical distribution, not the observed frequency counts. Do not place the density curve directly over raw histogram counts without scaling, since those vertical axes measure different quantities.
On Excel for Mac, use the Insert tab to select the corresponding Histogram or Scatter chart type; the chart formatting pane and some command positions may differ from Windows. Excel for the web also supports common chart types, but some formatting controls may be more limited. If you do not see Histogram, check that you are using a desktop version that includes statistical charts or make a frequency table and chart its counts instead.
Common Mistakes
Using the wrong standard deviation. STDEV.S is appropriate when your observations represent a sample; STDEV.P is for a complete population. Choosing the wrong one changes the scale of every standardized value. State which version you used in a note or chart caption so another reader can interpret the results.
Including headers, blanks, or unrelated numbers. A heading entered in the data range may be ignored by AVERAGE, but an unrelated numeric value will be included. Select the exact observations, not a nearby total, ID number, or summary cell. If your data has missing values, do not replace them with zero unless zero is a genuine observation.
Reading a z score as a percentage. A z score of 1.2 does not mean 1.2 percent. It means the observation is 1.2 standard deviations above the mean. A z score alone does not give the probability that a particular observation will occur; probability interpretation also relies on a distributional assumption, commonly normality.
Making a strong claim from a tiny histogram. A histogram based on a handful of values will look uneven because each observation has a large effect on the bars. Do not infer that data are normally distributed just because the chart looks roughly bell-shaped, or conclude that they are not normal from a small, jagged chart.
Mixing chart scales. Histogram bars show counts or frequency, while NORM.S.DIST with FALSE returns density. Overlaying them as if they shared a scale can mislead. Use separate charts, or construct a carefully scaled frequency comparison and label it clearly.
Tips
Keep the raw values and calculated z scores in separate columns. That makes the calculation transparent and lets you check an observation against its original value. Use descriptive column headings and, for a chart that may be shared, add a note such as Sample standard deviation used. Round displayed values for readability, but keep the underlying calculation unrounded so the graph is not based on prematurely rounded z scores.
For a quick interpretation, values near zero are close to the mean, and values near -1 or 1 are about one standard deviation below or above it. Values such as -2 or 2 are farther from the mean, but do not automatically label them unusual without considering the data and its distribution. If you compare groups, calculate each group’s z scores using the mean and standard deviation appropriate to the question; standardizing everything together answers a different question than standardizing within each group.
Make the chart title say what the numbers represent, not just Z scores. For example, Z Scores for Reading Assessment Results gives more context. If the dataset changes regularly, convert the raw data range to an Excel table using Insert > Table so new records are easier to manage. Check the formulas and chart range after adding rows; chart ranges do not always expand as expected when data are arranged outside a table.
Troubleshooting
Every z score is zero or the same value: Check whether the formula references one fixed raw-data cell instead of the score on the current row. In the z-score formula, the score reference should change as you fill down, while the mean and standard deviation references should remain fixed.
You see #DIV/0! or an error: A standard deviation cannot be calculated from fewer than two numeric observations using STDEV.S, and it is zero if every value is identical. Confirm that the selected range contains enough numbers and that the data actually vary. A z score cannot be meaningfully calculated when the standard deviation is zero.
The histogram has too many, too few, or empty-looking bins: Right-click its horizontal axis, open Format Axis, and adjust Bin width or Number of bins. Check the axis bounds and make sure you selected the z-score column rather than the original values. For a tiny dataset, a dot or scatter chart may communicate the individual results better.
The chart shows the wrong values or no useful labels: Use Chart Design > Select Data to inspect the chart’s source range. Re-select the intended z-score cells if needed, then add or edit the title and axis titles. If category names do not appear in an XY scatter chart, switch to a chart type that supports category labels, such as a line chart with markers.
Frequently Asked Questions
What does a z score of zero mean?
A z score of zero means the observation equals the mean used in the calculation. A small difference from zero can arise from rounding or floating-point precision.
Can a z score be negative?
Yes. A negative z score means the observation is below the mean. The negative sign indicates direction; it does not mean the observation itself is invalid.
Should I use STDEV.S or STDEV.P?
Use STDEV.S when the data are a sample from a larger group and STDEV.P when the data include the entire population of interest. If you are unsure, clarify what group your analysis is intended to describe.
How many z scores do I need for a useful histogram?
There is no universal minimum, but very small datasets produce unstable, coarse histograms. With only a few observations, show individual values with a dot or scatter chart and avoid making claims about the distribution’s shape.
Can I graph z scores without Excel?
Yes. Any spreadsheet or statistics program that can standardize values and create a histogram or scatter chart can do this. The same principles apply: choose the correct standard deviation, preserve the raw values, and label the chart’s axes.
Do z scores prove that data are normally distributed?
No. Standardizing shifts values to a mean of zero and a standard deviation of one under the chosen calculation; it does not make the original data normal. Use a histogram or other appropriate diagnostic and interpret it alongside the context and sample size.
Related
- Java split(): How It Handles Delimiters and Limits
- Java String split(): Syntax, Regex, and Examples
- Split a String in Excel: Separate Text into Columns
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Pivot Chart Date Format: Fix the Axis LabelsTo change date labels on an Excel PivotChart, select the chart’s date axis, right-click it, choose Format Axis,…
- Excel Column Chart With Error Bars: Setup and MeaningTo add error bars to a column chart in Excel for Microsoft 365 on Windows, select the chart,…
- Excel Scatter Plot X Axis Wrong? Find and Fix the CauseIf an Excel scatter plot’s x-axis is wrong, first check the series’ X values: select the chart, choose…
- Excel Pivot Table Filter Multiple Values: Quick GuideTo filter a PivotTable to several values in Excel for Microsoft 365 on Windows, open the drop-down for…
- Excel Trendline Greyed Out? Causes and FixesIf Excel’s trendline option is greyed out, first select a plotted data series—not just the chart background—and check…
- How to Apply an Outline to a Chart Area in ExcelTo outline a chart in Excel for Microsoft 365 on Windows, right-click the chart’s outer background, choose Format…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.