Find a Z-Score in Excel VBA: A Simple Step-by-Step Guide

To find z-scores with Excel VBA, calculate the mean and standard deviation of your data, then use the formula (value − mean) ÷ standard deviation for each observation. The macro below reads numeric data from column A, starting at A2, and writes sample z-scores to column B. You will need desktop Excel for Microsoft 365 on Windows, a workbook containing the data, and permission to run macros.

What You’ll Need

Microsoft Excel for Microsoft 365 on Windows. Excel for the web can store a macro-enabled workbook but cannot run VBA macros.
A worksheet with observations in column A, starting at A2. Put a heading in A1 if you like; the macro starts below it.
A macro-enabled workbook saved as an .xlsm file. Save a separate copy before running the macro: macro actions cannot be undone with Ctrl+Z.
A basic understanding of whether your data represents a sample or an entire population. The supplied code calculates sample standard deviation.

Step-by-Step

Decide which standard deviation is appropriate. A z-score measures how far a value is from the mean in standard deviation units. Its calculation is z = (x − mean) / standard deviation. For a sample drawn from a larger group, use the sample standard deviation, which divides the squared deviations by n − 1. For a complete population, use population standard deviation, which divides by n. The example macro uses the sample version. Use the population version only when the values represent the whole population you intend to describe.
Arrange and check the observations. Enter one observation per row in A2 downward, with no heading mixed into the data. The macro finds the last used row in column A, so remove unrelated notes beneath the observations before running it. Blank cells, error values, and nonnumeric text are skipped; numeric-looking text is treated as a number. Dates are stored as numbers in Excel, so do not include dates unless their serial values are genuinely the measurements you want to analyze. Column B is the output column: existing contents in the matching rows will be replaced or cleared.
Save a working copy as a macro-enabled file. Choose File > Save As, select Excel Macro-Enabled Workbook (*.xlsm) as the file type, and save. Keep an untouched copy as a fallback. VBA macros can change workbook contents, and Ctrl+Z will not reverse what a macro has done. If you are using a workbook from another person, make sure you trust its source before enabling its macros.
Open the Visual Basic Editor and add a module. Press Alt+F11 on Windows, then choose Insert > Module in the editor. If the Developer tab is hidden, enable it with File > Options > Customize Ribbon, check Developer, and select OK; you can then open the editor with Developer > Visual Basic. On a Mac, use Developer > Visual Basic if available; the keyboard shortcut is commonly Option+F11, sometimes with Fn as well, depending on the keyboard. Excel for the web does not provide a VBA editor for running this macro.

Paste the macro into the module. This version uses the active worksheet, reads A2 to the last used cell in column A, skips blanks and nonnumeric entries, calculates a sample standard deviation, and writes each result to column B on the same row.

Option Explicit

Sub CalculateZScores()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim inputRange As Range
    Dim cell As Range
    Dim numbers() As Double
    Dim numericCount As Long
    Dim meanValue As Double
    Dim sampleSD As Double
    Dim zScore As Double

    On Error GoTo HandleError
    Set ws = ActiveSheet

    ' Find observations in column A, starting at row 2.
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then
        MsgBox "No observations found in A2 downward.", vbExclamation
        Exit Sub
    End If
    Set inputRange = ws.Range("A2:A" & lastRow)
    ReDim numbers(1 To inputRange.Cells.Count)

    ' Collect numeric observations; ignore blanks, text, and errors.
    For Each cell In inputRange.Cells
        If Not IsError(cell.Value) Then
            If Len(Trim$(CStr(cell.Value))) > 0 Then
                If IsNumeric(cell.Value) Then
                    numericCount = numericCount + 1
                    numbers(numericCount) = CDbl(cell.Value)
                End If
            End If
        End If
    Next cell

    If numericCount < 2 Then
        MsgBox "At least two numeric observations are required.", vbExclamation
        Exit Sub
    End If
    ReDim Preserve numbers(1 To numericCount)

    meanValue = Application.WorksheetFunction.Average(numbers)
    sampleSD = Application.WorksheetFunction.StDev_S(numbers)
    If sampleSD = 0 Then
        MsgBox "The standard deviation is zero; z-scores are undefined.", vbExclamation
        Exit Sub
    End If

    ' Write scores to column B and clear nonnumeric matching rows.
    For Each cell In inputRange.Cells
        If Not IsError(cell.Value) Then
            If Len(Trim$(CStr(cell.Value))) > 0 Then
                If IsNumeric(cell.Value) Then
                    zScore = (CDbl(cell.Value) - meanValue) / sampleSD
                    ws.Cells(cell.Row, "B").Value = zScore
                Else
                    ws.Cells(cell.Row, "B").ClearContents
                End If
            Else
                ws.Cells(cell.Row, "B").ClearContents
            End If
        Else
            ws.Cells(cell.Row, "B").ClearContents
        End If
    Next cell

    MsgBox "Z-scores are in column B.", vbInformation
    Exit Sub

HandleError:
    MsgBox "The macro stopped: " & Err.Description, vbExclamation
End Sub

The macro declares its variables, checks that it has at least two numeric observations, and stops if the sample standard deviation is zero. It uses Excel’s built-in Average and StDev_S worksheet functions from VBA, rather than estimating the statistics itself. It does not alter column A, but it does write to or clear column B for each row in the input range.

Review the code’s assumptions before running it. If your observations are in another column or begin on another row, adjust the references to column A, row 2, and the output column B consistently. If the data is a complete population, replace Application.WorksheetFunction.StDev_S(numbers) with Application.WorksheetFunction.StDev_P(numbers). Keep a backup before adapting the macro, and make sure the output column does not contain results or notes you need to preserve.
Run the macro and inspect the results. Return to Excel, select the worksheet containing the data, and press Alt+F8. Choose CalculateZScores and select Run. You can also use Developer > Macros, select the macro, and choose Run. On a Mac, use Tools > Macro > Macros if the Developer ribbon route is not available. Confirm that the output appears in column B and that the message indicates completion. Format column B with a suitable number of decimal places if you want a more readable display; formatting changes how results look, not their underlying values.
Check the calculation with a small example. Enter 10, 12, 14, 16, and 18 in A2:A6. Their mean is 14, and their sample standard deviation is approximately 3.1623. The macro should return approximately −1.2649, −0.6325, 0, 0.6325, and 1.2649 in B2:B6. The scores are centered around zero, and the score for 18 is positive because it is above the mean. Small differences in the final displayed digit can result from rounding.

Common Mistakes

Using the wrong standard deviation. Sample and population standard deviations do not give identical z-scores. Decide what the data represents before choosing StDev_S or StDev_P; the distinction matters especially with small datasets. Including headings or unrelated values. Text headings are skipped, but an accidental numeric value in column A will be included in the mean and standard deviation. Mixing units. If some measurements are in centimeters and others in inches, convert them to one unit first. Overwriting useful results. The macro writes to column B and clears its matching cells when the source row is blank, text, or an error. Choose another output column in the code if column B contains information you need. Expecting a score when all values are identical. Their standard deviation is zero, so division by zero makes a z-score undefined; the macro warns and stops rather than returning misleading numbers.

Tips

Keep a clear layout: labels in row 1, observations in A2 downward, and results in a separate column. Use a different worksheet for the output if you want to preserve existing column B data. If you change the data range, check both the starting row and how the macro locates the final row; it searches for the last used cell specifically in column A, so values elsewhere do not extend the input range.

You can cross-check a result with a worksheet formula. With observations in A2:A6, put their mean in D1 and their sample standard deviation in D2; in B2, enter =STANDARDIZE(A2,$D$1,$D$2) and fill down. For the example values 10, 12, 14, 16, and 18, the result in B6 should be about 1.2649. STANDARDIZE uses the mean and standard deviation you supply, so use a sample standard deviation in D2 to match this macro. The formula is a check, not a replacement for confirming that the chosen data and deviation type are appropriate.

For easier comparison, keep full precision in the calculations and only round the displayed output. If you need four decimal places on screen, select the results and use Home > Number to adjust the displayed decimals. Do not round the mean or standard deviation in the VBA code before calculating scores; early rounding can cause small discrepancies. If you want to reuse the macro in other workbooks, store it in the current workbook or a trusted macro-enabled personal workbook, and be cautious about enabling macros from unknown files.

Troubleshooting

The macro is missing from Alt+F8. Check that the code is in a standard module, that the procedure is named CalculateZScores, and that the workbook is saved as .xlsm. A procedure marked Private Sub will not appear in the macro list; the supplied code uses Sub CalculateZScores. Close and reopen the macro list after saving if needed.

Excel blocks the macro. Confirm that you opened the .xlsm copy in the desktop Excel application and that the workbook is trusted. Check the security notification or your organization’s macro policy rather than enabling every macro globally. Excel for the web cannot execute VBA; open the file in desktop Excel to run it.

The macro reports that there are not enough observations. It needs at least two numeric values for a sample standard deviation. Check that your data actually starts in A2, that numbers are not stored as errors, and that column A has not been left empty. Numeric text can be recognized, but values containing units such as “12 kg” are not numeric and will be skipped.

The results seem wrong or no rows were processed. Check that the active worksheet is the one containing your data; the macro operates on whichever sheet is active when it runs. Look for unintended numeric entries in column A and confirm that all measurements use the same units. If the output looks rounded, increase the displayed decimal places. If you changed the input column or starting row, check that every reference in the code matches your layout.

You see an error message or unexpected results remain in column B. Read the message for clues, then verify that the workbook has not been protected against editing and that column B is available for output. The macro clears matching output cells for blanks, text, and errors, but it stops before writing scores when fewer than two numeric observations exist or the standard deviation is zero. If you need to preserve old results, restore your backup and change the output-column reference before running it again.

Frequently Asked Questions

What does a z-score tell me? It tells you how many standard deviations an observation is above or below the selected dataset’s mean. A positive score is above the mean, a negative score is below it, and zero is at the mean.

Should I use sample or population standard deviation? Use sample standard deviation when your observations are a sample used to describe a larger group. Use population standard deviation when the values are the entire population of interest. The macro uses the sample version unless you change StDev_S to StDev_P.

Can the macro calculate z-scores for a different column? Yes. Change the input references from column A to your data column and update the output reference if needed. Keep the row range consistent, and test on a copy so you do not overwrite important cells.

Why does the macro skip some cells? It skips blank cells, Excel error values, and text that is not numeric. Numeric text can be converted to a number; a value such as “12 kg” cannot. Check skipped entries and clean or convert them if they are intended to be observations.

Can I run VBA in Excel for the web or undo it with Ctrl+Z? No. Excel for the web cannot run VBA macros, and macro changes cannot be undone with Ctrl+Z. Use desktop Excel and save a backup before running the code.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.