Excel Data Run Analysis: A Practical Runs Test

How-to basics · Updated November 18, 2026

Excel data run analysis checks whether values in a sequence appear in a meaningful order or are consistent with randomness. Excel has no dedicated Runs Test button, but you can classify each observation as above or below a reference value, count consecutive groups (runs), and compare the result with the expected count; a run chart provides a useful visual check.

What a run means in data analysis

A run is one or more consecutive observations in the same category. If a process produces readings above and below its median in this order—above, above, below, above, below, below—the sequence has four runs. The number of runs, not just the values themselves, is the focus of the analysis.

A runs test asks whether the order of those categories looks unusual if the observations were randomly ordered. Too few runs can indicate long stretches on one side of the reference value. Too many can indicate frequent alternation. Either pattern may point to a change, cycle, grouping effect, or other structure that a summary such as the average would conceal.

Run analysis is often used with process or quality data collected over time, such as daily defect counts, waiting times, or product measurements. It is a screening tool: an unusual result suggests investigating the sequence, but does not by itself identify a cause or prove that a process is out of control.

Choose the right reference and data

Use observations in their actual order, usually time order. A runs test cannot answer a meaningful question about sequence if the rows have been sorted by value, category, or another field. Keep the original order intact, and check that each row represents one comparable observation from the same process or population.

A common approach classifies values as above or below the sample median. The median is useful because it divides the observations into two groups without being strongly affected by a few extreme readings. If there is a meaningful target or specification value, that may be a better reference for a particular question, but the interpretation and assumptions should match that choice.

Values exactly equal to the median are generally omitted from the median-based runs test. Remove or set aside those observations when forming the sequence of signs, then count runs across the remaining sequence. Do not treat ties as a third category or allow a tie to break a run without checking the method you intend to use. If ties are numerous, a standard two-category test may be a poor fit.

Prepare the worksheet in Excel

For a worked example, enter twelve measurements in A2:A13 in the order they were collected: 52, 48, 53, 47, 51, 49, 54, 46, 52, 48, 53, and 47. The median is 50, so there are six observations above it and six below it, with no ties. The alternating sequence makes this a useful example for illustrating a high-run result.

  1. Put the observations in one column and preserve their original order. Add a clear label such as Measurement in A1.
  2. Use a separate cell for the reference value. With the example data in A2:A13, enter this formula in E1 to calculate the median: =MEDIAN(A2:A13). It returns 50.
  3. In B2, classify the first observation relative to the reference in E1. With the measurement in A2 and the median in E1, enter =IF(A2>$E$1,1,IF(A2<$E$1,-1,0)) and fill it down through B13. A result of 1 means above the median, and -1 means below. A zero flags a tie; exclude tied observations and compact the remaining signs into consecutive rows before using the run-count formula below.

For this example, the signs are 1, -1, 1, -1, 1, -1, 1, -1, 1, -1, 1, -1. Keeping the signs in a helper column makes the classification visible and easier to audit than hiding it inside one long formula.

Count runs and calculate the expected count

A new run begins whenever the sign changes from the preceding observation. With no ties and the signs in B2:B13, enter this formula in E4: =1+SUMPRODUCT(--(B3:B13<>B2:B12)). It compares each sign with the one immediately before it. The example returns 12 runs because every observation switches sides.

Count the observations in each category. With signs in B2:B13, enter =COUNTIF(B2:B13,1) in E2 to count values above the median; it returns 6. Enter =COUNTIF(B2:B13,-1) in E3 to count values below the median; it also returns 6. These counts are the inputs to the expected-run calculation.

Under a random ordering with fixed group sizes, the expected number of runs depends on how many observations fall in each group. With the above count in E2 and below count in E3, enter =1+2*E2*E3/(E2+E3) in E5. The expected count for the example is 7. This is an average over possible orderings, not a prediction that every random sequence will contain exactly seven runs.

The spread of possible run counts also matters: a difference of two runs may be notable in one sample size and ordinary in another. With the counts in E2 and E3, enter =2*E2*E3*(2*E2*E3-E2-E3)/((E2+E3)^2*(E2+E3-1)) in E6 to calculate the variance used by the usual large-sample approximation. For six observations in each group, it returns about 2.727. The standard deviation is the square root of that value.

Interpret the statistical result

A standardized statistic expresses how far the observed count is from the expected count relative to the expected variation. A continuity correction of 0.5 is commonly used with the normal approximation for this discrete count. With observed runs in E4, expected runs in E5, and variance in E6, enter =MAX(0,(ABS(E4-E5)-0.5)/SQRT(E6)) in E7. The example returns about 2.73.

To get an approximate two-sided p-value from that absolute statistic, enter =2*(1-NORM.S.DIST(E7,TRUE)) in E8. For the example it is about 0.0064. This is below the commonly used 0.05 threshold, so the example has unusually many runs under this approximation. In practical terms, the repeated alternation is less consistent with a random ordering than an ordinary sequence would be. A threshold is a convention, not proof that a particular cause exists.

The normal approximation can be inaccurate for small samples or very unbalanced group sizes. The example has only twelve observations, so its approximate p-value should be treated cautiously; an exact runs-test calculation is preferable when a reliable small-sample result matters. Excel does not provide a dedicated built-in exact runs-test function. Statistical software or a validated reference table can supply an exact result. Also distinguish statistical significance from practical importance: investigate the data and process before drawing operational conclusions.

Use a run chart as a visual companion

A run chart plots measurements in time order and adds a reference line, often the median. It can show clusters, shifts, trends, or cycles that a simple runs count does not explain. To make one, select the time and measurement columns, then choose Insert > Insert Line or Area Chart > Line. Add the median as a second data series if you want it displayed as a horizontal reference line. Give the axes useful labels and avoid sorting the source data.

The chart and the test answer related but different questions. The runs test reduces each observation to above or below the reference and summarizes the sequence with a count. A chart retains the measurement size, so a large jump and a tiny crossing are visibly different even though both may cause a sign change. Review both when possible; a test result should not replace examination of the original readings.

Common mistakes and practical checks

  • Sorting the data: Sorting changes the sequence and therefore the runs. Restore collection order before analysis.
  • Ignoring missing or irregular observations: Check whether blanks represent missing measurements, downtime, or a real observation. Document how missing periods are handled rather than silently treating blanks as values.
  • Counting ties inconsistently: Decide how median ties will be handled, and apply that rule consistently. If you exclude ties, remove them from the sign sequence so the remaining observations are compared consecutively.
  • Using an unsuitable reference: The sample median is not always the operational target. A target may be more meaningful for detecting departures from a known standard, but it changes the question being asked.
  • Overinterpreting the p-value: A small p-value does not show why a pattern occurred, measure its business impact, or establish causation. Look for process changes, sampling issues, seasonality, and data-entry errors.

For Excel for Microsoft 365 on Windows, the formulas above use standard worksheet functions and comma separators. Excel for Mac has the same formulas, although menu layout and some keyboard shortcuts can differ. In either version, check that the reference cell and data ranges include all intended observations before copying formulas.

Runs tests compared with other methods

A runs test is useful when the question is whether observations fall into an unexpectedly patterned order around a reference value. It is not a general-purpose test for every kind of time-series behavior. A run chart is more informative about the size and shape of changes; a control chart is designed to assess process behavior using control limits and rules appropriate to the chart type. A trend test focuses specifically on a systematic upward or downward direction.

Choose based on the question. If the concern is whether values alternate or cluster above and below a center, the runs test is a direct option. If you need to detect gradual drift, estimate process stability, or account for autocorrelation, a chart or time-series method may be more appropriate. Consecutive observations are often related, so the assumption of independent observations deserves attention. A runs test can flag a pattern, but it does not model every dependence structure.

Frequently Asked Questions

Does Excel have a built-in runs test?
No. Excel for Microsoft 365 does not include a dedicated Runs Test command or worksheet function. You can classify observations, count runs, and calculate an approximate test using worksheet formulas; an exact small-sample test generally requires another validated method.

What does a high number of runs mean?
A high count means the sequence switches categories often compared with the expected count. That can suggest alternation or another nonrandom pattern, but the test alone cannot identify its cause.

What does a low number of runs mean?
A low count means observations tend to remain on the same side of the reference for longer stretches. This can suggest clustering, a shift, or a trend, which should be checked against the time-ordered data and process context.

Can I use a target instead of the median?
Yes, when a target is meaningful to the question. Classify each value as above or below that target, handle values equal to it consistently, and interpret the result as a test around the target rather than as a median-based test.

How many observations do I need?
There is no single sample size that guarantees a useful result. Very small samples provide limited evidence, and the normal approximation can be unreliable when group counts are small or unbalanced. For small samples, use an exact method and consult an appropriate statistical reference.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.