The SQRT function in Excel returns the positive square root of a number.
It is useful for reversing a square calculation, such as finding a square’s side length from its area. Negative inputs return an error rather than a real-number result.
In this article, I’ll show you how to calculate square roots across a column, find a rectangle’s diagonal, and derive standard deviation or standard error.
SQRT Function Syntax in Excel
The SQRT function takes one number and returns its positive square root.
=SQRT(number)
- number (required) is the number whose positive square root you want to calculate. It can be a value, cell reference, or range.
When to Use SQRT Function
- Find the side length of a square when you know its area.
- Calculate a diagonal using the Pythagorean theorem.
- Convert a variance into a standard deviation.
- Calculate standard error from a standard deviation and sample size.
- Check square-root results against the caret operator or POWER function.
Example 1: Square Root a Column of Numbers
Let’s start with square lots whose areas are already known.
Below is the dataset. Column A contains lot IDs, and column B contains each lot’s area in square feet.
We want to calculate every lot’s side length with one formula in C2.
Here is the formula:
=SQRT(B2:B9)
SQRT evaluates every area in B2:B9. In Excel 2021 and later, the eight side lengths spill from C2 through C9.
LOT-101 produces 35.00 feet, LOT-103 produces 50.00 feet, and LOT-106 produces 66.33 feet.
The displayed two-decimal values come from cell formatting. SQRT keeps the full underlying precision for later calculations.
Pro Tip: Keep C2:C9 empty before entering the formula. Any existing value in that output range causes a #SPILL! error.
Example 2: Fix SQRT #NUM! Errors with ABS
A common problem can appear after importing values from another system.
Below is the dataset. Column A contains panel IDs, and column B contains recorded panel areas. Three areas have incorrect negative signs.
We first want to see what SQRT returns when the source column includes negative values.
Here is the direct SQRT formula:
=SQRT(B2:B9)
The positive areas return 12, 25, 20, 9, and 23. Cells C3, C5, and C8 return #NUM! because SQRT cannot return a real root for a negative number.
If those negative signs are import errors, ABS can remove each sign before SQRT runs.
Here is the formula with ABS:
=SQRT(ABS(B2:B9))
ABS changes the negative magnitudes to positive values. The corrected results in D3, D5, and D8 are 14, 18, and 16.
The other rows remain 12, 25, 20, 9, and 23, matching their direct SQRT results.
Pro Tip: Use ABS only when the negative sign is genuinely wrong. If it represents real data, =IMSQRT(B3) handles complex roots, while =IFERROR(SQRT(B3),"") can leave the result blank.
Example 3: Calculate a Rectangle Diagonal
Now let’s use SQRT with the Pythagorean theorem.
Below is the dataset. Column A lists display models, while columns B and C contain each display’s width and height in inches.
We want one formula in D2 to calculate all eight display diagonals.
Here is the formula:
=SQRT(B2:B9^2+C2:C9^2)
Excel squares every width and height, adds the matching squared values, then takes each square root. The results spill down column D.
The 20.9 by 11.8-inch DM-2409 has a 24.00-inch diagonal. The 37.5 by 21.1-inch DM-4322 has a 43.03-inch diagonal.
The range operator binds more tightly than the exponent operator, so the formula squares every cell in each range exactly as written.
Example 4: Calculate Standard Deviation and Standard Error
Here’s a statistical use for SQRT that comes up often.
Below is the dataset. Columns A and B contain eight call IDs and their handle times in seconds.
We want to calculate the variance, convert it to a standard deviation, then calculate the standard error.
First, here is the sample variance formula in E2:
=VAR.S(B2:B9)
The eight handle times produce a sample variance of 6362.50.
Next, SQRT converts that variance into a standard deviation.
Here is the formula in E3:
=SQRT(E2)
The square root of the variance is a standard deviation of 79.77 seconds.
Then, we divide the standard deviation by the square root of the observation count.
Here is the standard error formula in E4:
=E3/SQRT(COUNT(B2:B9))
COUNT finds eight numeric handle times. Dividing 79.77 by the square root of that count gives the displayed standard error of 28.20 seconds.
You can calculate the standard deviation directly with =STDEV.S(B2:B9). The SQRT method is useful when the variance already exists in another cell.
Example 5: Compare SQRT, Caret, and POWER
Finally, let’s compare three formulas that calculate the same square roots.
Below is the dataset. Column A contains eight test values, including whole numbers, decimals, and zero.
We want to calculate the same results with SQRT, the caret operator, and POWER.
Here is the SQRT formula:
=SQRT(A2:A9)
And here is the equivalent caret formula:
=A2:A9^0.5
The POWER formula is:
=POWER(A2:A9,1/2)
All three columns match. They return 13.0000, 7.0711, 0.6000, 2.7000, 31.6228, 1.4142, 0.0000, and 3.5355.
SQRT is the shortest and clearest choice for square roots. The caret and POWER forms also support other roots by changing the fractional exponent.
For example, =POWER(A2:A9,1/3) calculates cube roots. Use a dedicated nth-root method when you need something other than a square root.
Tips & Common Mistakes
- SQRT always returns the positive, principal square root. Although a positive number has two mathematical square roots, Excel returns only the positive one.
- Negative inputs return #NUM!. Use ABS only for incorrect signs, because ABS changes the original value before SQRT evaluates it.
- SQRT has no rounding argument. Wrap the result in ROUND when you need rounded values rather than display formatting alone.
- A blank referenced cell is treated as 0, so SQRT can return 0 and hide a missing input. Check for blanks when gaps matter.
- Excel automatically converts text that looks like a number, but nonnumeric text returns #VALUE!.
- In Excel 2021 and later, blocked spill cells cause #SPILL!. Adding
@before SQRT in=@SQRT(B2:B9)returns only the top-left result, the square root of B2. - In Excel 2019 and earlier, enter SQRT row by row and fill down. You can also select the full output range and confirm an array formula with Ctrl+Shift+Enter.
SQRT handles positive square roots in single cells and spilled ranges.
SQRT also calculates the square-root step in geometry and statistics formulas.
Related Excel Functions / Articles:
