To calculate relative standard deviation in Excel, divide the sample standard deviation by the average: =STDEV.S(B2:B7)/AVERAGE(B2:B7), then format the cell as a percentage. For six absorbance readings of 0.512, 0.498, 0.505, 0.521, 0.509 and 0.494, the formula returns 0.019294, which displays as 1.93%.
Excel has no dedicated RSD function, so everything below is built from STDEV.S, AVERAGE and a few supporting functions. The guide covers the percentage-format trap, blank cells, error handling and a multi-sample RSD table you can copy down.
The Excel RSD Formula
The standard definition, RSD (%) = s / x̄ × 100, translates into two equivalent Excel formulas. Pick one and use it consistently.
| Goal | Formula | Cell format | Display |
|---|---|---|---|
| RSD as a percentage | =STDEV.S(B2:B7)/AVERAGE(B2:B7) | Percentage, 2 decimals | 1.93% |
| RSD as a plain number | =STDEV.S(B2:B7)/AVERAGE(B2:B7)*100 | Number, 2 decimals | 1.93 |
| Population RSD | =STDEV.P(B2:B7)/AVERAGE(B2:B7) | Percentage | 1.76% |
The first version is usually cleaner, because Excel keeps the true ratio in the cell and only changes how it is displayed. The second is useful when the result feeds another formula that expects a number such as 1.93.
Step-by-Step Worked Example
Enter the data with a header in row 1 and six replicate absorbance readings in B2:B7.
| A | B | |
|---|---|---|
| 1 | Replicate | Absorbance |
| 2 | 1 | 0.512 |
| 3 | 2 | 0.498 |
| 4 | 3 | 0.505 |
| 5 | 4 | 0.521 |
| 6 | 5 | 0.509 |
| 7 | 6 | 0.494 |
Building the result in three cells makes each step easy to check:
- In B9, enter
=AVERAGE(B2:B7). The mean is 0.5065. - In B10, enter
=STDEV.S(B2:B7). The sample standard deviation is 0.0097724. - In B11, enter
=B10/B9. The result is 0.019294. - Select B11 and apply Percentage format with Home > Number > Percent Style, or press Ctrl+Shift+%. Increase the decimals to two. The cell shows 1.93%.
Once the steps agree with what you expect, you can replace them with the single formula =STDEV.S(B2:B7)/AVERAGE(B2:B7). The intermediate cells are still worth keeping in validation workbooks, because a reviewer can check the mean and SD directly.
STDEV.S, STDEV.P or STDEV?
Excel offers several standard deviation functions. For RSD, the choice changes the answer, especially for small data sets.
| Function | Divisor | Use for RSD when | Example result |
|---|---|---|---|
| STDEV.S | n − 1 (sample) | Replicates, lab measurements, any sample of a larger process | 1.93% |
| STDEV.P | n (population) | The data is the complete population you want to describe | 1.76% |
| STDEV | n − 1 | Older workbooks only; same result as STDEV.S | 1.93% |
| STDEVA | n − 1 | Rarely; treats text and FALSE as 0 and TRUE as 1 | Not recommended |
STDEV.S and STDEV.P have been available since Excel 2010. Microsoft keeps STDEV as a compatibility function, so it still works, but STDEV.S states the calculation clearly. For most laboratory and quality data, STDEV.S is the correct choice. If you are unsure which applies, the sample vs population standard deviation guide explains the difference.
Percentage Formatting: Avoid Scaling Twice
Excel’s Percentage format multiplies the stored value by 100 for display. Microsoft’s formatting guide gives the example of a cell containing 10 that displays as 1000.00% once the format is applied.
That behavior causes the most common RSD mistake in Excel:
| Formula | Cell format | What you see | Correct? |
|---|---|---|---|
=STDEV.S(B2:B7)/AVERAGE(B2:B7) | Percentage | 1.93% | Yes |
=STDEV.S(B2:B7)/AVERAGE(B2:B7)*100 | Number | 1.93 | Yes |
=STDEV.S(B2:B7)/AVERAGE(B2:B7)*100 | Percentage | 192.94% | No, scaled twice |
The rule is simple: either format as a percentage or multiply by 100, never both. If a colleague’s workbook shows an RSD above 100% for data that looks tightly clustered, check this first.
RSD Table for Multiple Samples
Most real workbooks calculate RSD for many samples at once. Put each sample on its own row with the replicates across the columns.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Sample | Rep 1 | Rep 2 | Rep 3 | Rep 4 | Rep 5 | RSD |
| 2 | S-101 | 10.21 | 10.35 | 10.18 | 10.29 | 10.24 | 0.66% |
| 3 | S-102 | 25.6 | 25.1 | 25.9 | 25.4 | 25.3 | 1.20% |
| 4 | S-103 | 4.87 | 4.95 | 4.79 | 4.91 | 4.83 | 1.30% |
In G2, enter =STDEV.S(B2:F2)/AVERAGE(B2:F2) and format it as a percentage. Then drag the fill handle down to G4, or double-click it. Because the references are relative, Excel adjusts them to B3:F3 and B4:F4 automatically.
If your layout runs down the columns instead, with samples in columns and replicates in rows, enter the formula under the first column and fill it across. The same relative references adjust sideways.
Use absolute references, such as $B$2:$B$7, only when every copy of the formula should point at the same fixed range. Mixing them up is a common reason for an RSD table where every row shows the same value.
RSD for One Group in a Long List
Instrument exports and LIMS downloads usually arrive in “long” format: one row per result, with the sample ID in column A and the result in column B. You can calculate the RSD for one sample without rearranging the data.
In Excel 2021 and Microsoft 365, FILTER pulls out the matching results first:
=LET(v,FILTER(B2:B16,A2:A16="S-102"),STDEV.S(v)/AVERAGE(v))
In older versions, an IF inside STDEV.S does the same job. Non-matching rows return FALSE, which STDEV.S ignores inside an array:
=STDEV.S(IF(A2:A16="S-102",B2:B16))/AVERAGEIF(A2:A16,"S-102",B2:B16)
In Excel 2019 and earlier, confirm that formula with Ctrl+Shift+Enter so it is evaluated as an array formula. Current versions evaluate it directly. If rows 2 to 16 hold the three samples from the table above, five rows each, both formulas return 1.20% for S-102, the same as the row-based table.
To replace the hard-coded “S-102”, point the condition at a cell, such as A2:A16=E2, and fill the formula down next to a list of sample IDs. A PivotTable is another option: add the result field twice, summarize one copy by Average and the other by StdDev, and divide the two columns next to the PivotTable.
Create Your Own RSD Function With LAMBDA
In Excel 2024 and Microsoft 365, you can save the RSD calculation as a named function and reuse it anywhere in the workbook.
- Go to Formulas > Name Manager > New.
- In Name, type
RSD. - In Refers to, enter
=LAMBDA(range,STDEV.S(range)/AVERAGE(range)). - Select OK.
You can now type =RSD(B2:B7) in any cell and format the result as a percentage. The named function travels with the workbook, which keeps the formula consistent for everyone who uses the file. Anyone opening it in an older Excel version will see #NAME? instead.
Flag RSD Values Above a Limit
Once the RSD column is built, conditional formatting can highlight results that exceed your own acceptance limit.
- Select the RSD cells, for example G2:G4.
- Go to Home > Conditional Formatting > Highlight Cells Rules > Greater Than.
- Enter the limit and choose a format.
If the RSD cells hold ratios formatted as percentages, enter the limit as a percentage (for example 1.5%) or as the equivalent decimal (0.015). If the cells hold numbers multiplied by 100, enter 1.5. Entering 1.5 against ratio-based cells would never trigger, because a ratio of 1.5 means an RSD of 150%.
The limit itself should come from your method, specification or quality system. RSD acceptance limits vary widely between applications, so there is no single value that fits every workbook.
What About the Analysis ToolPak?
Excel’s Analysis ToolPak add-in includes a Descriptive Statistics tool that reports a mean and a standard deviation for a column of data, among other statistics. It does not report an RSD or coefficient of variation, so you still need to divide the standard deviation by the mean yourself.
The ToolPak output is also static. If a value changes, the report does not update, whereas the STDEV.S and AVERAGE formulas recalculate immediately. For RSD work, the formula approach is usually the better choice.
Blank Cells, Zeros and Text
Excel’s AVERAGE function ignores empty cells and text in a range but includes cells that contain zero. STDEV.S treats ranges the same way. This distinction has a large effect on RSD.
Suppose one replicate of a sample is missing:
| Row | Replicates | Values used | RSD |
|---|---|---|---|
| Missing value left blank | 61.2, 60.4, (blank), 61.0, 60.7 | 4 | 0.58% |
| Missing value entered as 0 | 61.2, 60.4, 0, 61.0, 60.7 | 5 | 55.91% |
A blank is correctly treated as “no result”. A zero is treated as a real measurement of zero, and the RSD explodes. Never type 0 as a placeholder for a missing or rejected result. Leave the cell empty, or use a text note such as “n/a” in a separate comments column.
Numbers stored as text cause the opposite problem. Data pasted from instruments or other software sometimes arrives as text that looks like a number. STDEV.S and AVERAGE skip those cells without any warning, so the RSD is calculated from fewer values than you think.
Check the count whenever you build an RSD formula:
=COUNT(B2:B7)
If COUNT returns fewer values than you entered, some cells are blank or stored as text. Convert them with Data > Text to Columns or by multiplying the column by 1 in a helper column.
Error Handling: #DIV/0! and Friends
An RSD formula can fail in three common ways.
- Fewer than two numbers. A sample standard deviation needs at least two values. With a single value, STDEV.S returns #DIV/0!.
- A mean of zero. Dividing by an AVERAGE of 0 also returns #DIV/0!.
- Error values in the data. A cell containing #N/A or #VALUE! from an upstream formula can break the calculation.
A guard formula handles the first case explicitly and leaves the cell blank until there are enough results:
=IF(COUNT(B2:B7)<2,"",STDEV.S(B2:B7)/AVERAGE(B2:B7))
IFERROR catches any error and replaces it with text you choose. In Excel, both arguments are required:
=IFERROR(STDEV.S(B2:B7)/AVERAGE(B2:B7),"check data")
For ranges that may contain error values you cannot clean up, AGGREGATE can ignore them. Function number 7 is STDEV.S, 1 is AVERAGE, and option 6 means “ignore error values”:
=AGGREGATE(7,6,B2:B7)/AGGREGATE(1,6,B2:B7)
Use IFERROR carefully. It hides every error, including ones that point to a real problem with the data. The IF and COUNT test is more transparent.
Watch means near zero and negative means. Excel will happily return an RSD of 400% or −35% if the mean is small or negative. The formula does not know that RSD is intended for positive, ratio-scale data. For blanks, baseline readings or data that can be negative, report the standard deviation instead. The home page explains when not to use RSD.
Rounding
Percentage format changes only what you see. The cell still holds the full-precision value, which is what you want for any later calculation.
Use ROUND only when the rounded number itself must be stored, for example to match a report exactly:
=ROUND(STDEV.S(B2:B7)/AVERAGE(B2:B7),4)
That returns 0.0193, which displays as 1.93%. Never round the mean or the standard deviation before dividing. Rounding intermediate values is a common reason a workbook disagrees with a calculator or a colleague’s result.
One related quirk: if every value in a range is identical, you might expect an RSD of exactly zero, but floating-point arithmetic can return a tiny non-zero value many decimal places down. That is a binary rounding artifact, not real variation. Formatting the cell to two decimals displays it as 0.00%.
Modern Excel: LET, Tables and BYCOL
Newer versions of Excel offer cleaner ways to write the same calculation.
Excel Tables (all current versions). Convert your data to a table with Ctrl+T and name it, for example, Results. Structured references expand automatically as you add rows:
=STDEV.S(Results[Absorbance])/AVERAGE(Results[Absorbance])
LET (Excel 2021 and Microsoft 365). LET names the range once, which removes repetition and makes the guard easy to read:
=LET(r,B2:B7,IF(COUNT(r)<2,"",STDEV.S(r)/AVERAGE(r)))
BYCOL and LAMBDA (Excel 2024 and Microsoft 365). BYCOL applies a calculation to every column of a range and spills one result per column. With samples in columns B to E and replicates in rows 2 to 6:
=BYCOL(B2:E6,LAMBDA(c,STDEV.S(c)/AVERAGE(c)))
BYROW does the same for rows. If your organization uses Excel 2016 or 2019, these functions return #NAME?, so stick with the fill-handle method for shared workbooks.
Check Your Setup Against a Known Result
Microsoft’s STDEV.S help page includes a small data set of ten breaking-strength values (1345, 1301, 1368, 1322, 1310, 1370, 1318, 1350, 1303 and 1299). STDEV.S returns 27.46392 for that data, and the mean is 1328.6, so:
If your formula gives 2.07% for those values, it is set up correctly. STDEV.P gives 1.96% for the same data, which is a quick way to confirm which function a workbook is using.
You can run the same check on your own data with the RSD Calculator. Enter the values, choose Sample (n − 1), and compare the mean, standard deviation and %RSD with the three intermediate cells in your workbook.
Troubleshooting Quick Reference
| Symptom | Likely cause | Fix |
|---|---|---|
| RSD shows around 100 times too large | Formula multiplies by 100 and cell is formatted as a percentage | Remove *100 or use Number format |
| RSD shows 0.02 instead of 1.93% | Ratio formula in a General or Number cell | Apply Percentage format |
| #DIV/0! | Fewer than two numbers, or a mean of zero | Check with COUNT; add an IF guard |
| RSD far higher than expected | A zero typed for a missing value, or an outlier | Leave missing values blank; review the data |
| RSD slightly different from a calculator | STDEV.P used instead of STDEV.S, or rounded inputs | Use STDEV.S and unrounded values |
| Every row of an RSD table shows the same value | Absolute references copied down | Use relative references such as B2:F2 |
| #NAME? | LET, LAMBDA or BYCOL in an older Excel version | Use the plain STDEV.S and AVERAGE formula |
Going the Other Way: SD From a Known RSD
Sometimes the RSD is known and you need the standard deviation, for example from a method specification. With RSD as a plain number in B2 and the mean in C2, use =B2*C2/100. If B2 is formatted as a percentage, the stored value is already a fraction, so use =B2*C2. The algebra and the common mistakes are covered in Rearranging the RSD Formula.
Working in Google Sheets instead? The function names are similar, but Sheets handles some details differently, including percent helpers and multi-column formulas. See How to Calculate Relative Standard Deviation in Google Sheets.
Frequently Asked Questions
Does Excel have a built-in RSD function?
No. Excel has no RSD or CV function, so you combine two functions: =STDEV.S(range)/AVERAGE(range). Format the cell as a percentage, or multiply by 100 and use a normal number format.
Should I use STDEV.S or STDEV.P for RSD in Excel?
Use STDEV.S for replicate measurements and most laboratory data, because the values are a sample of what the method could produce. Use STDEV.P only when your data is the entire population you want to describe.
Why does my RSD show 193% instead of 1.93%?
The formula multiplies by 100 and the cell also uses Percentage format, so the value is scaled twice. Remove *100 from the formula or change the cell to a Number format.
Why does my RSD formula return #DIV/0!?
Either the range contains fewer than two numbers, so STDEV.S cannot be calculated, or the mean is zero. Check the range with COUNT and wrap the formula in an IF or IFERROR test.
What is the difference between STDEV and STDEV.S in Excel?
They return the same sample standard deviation. STDEV is kept for compatibility with older workbooks, and STDEV.S, available since Excel 2010, is the current name that makes the sample calculation explicit.
