Though Excel is most commonly used to perform simple addition and multiplication, it has all the functionality of a calculator and more. In this guide, we show you how to find the square root of a number in Excel using three separate formulas.
The Square Root Formulas in Excel (SQRT and POWER)
As well as the most commonly used Excel SQRT function, we’ll be covering the power function and exponent operator. Though it isn’t strictly necessary to know all of these methods, being able to use them all will grant you more flexibility in your spreadsheets. Thankfully, each of them is relatively easy to perform, so if you follow the steps below you’ll know how to square root in Excel in just a few minutes.
How to Find the Square Root of a Number via the Excel SQRT Function
The SQRT function is the most well-known square root formula for Excel, primarily because its name is memorable. But what exactly is it, and how does it work?
What is SQRT in Excel?
Quite simply, the SQRT function returns a positive square root when you define a number or cell for it to analyze. It’s important to note that if the value of the number is negative, you’ll receive the “#NUM!” error. We’ll teach you a way around this.
The SQRT formula syntax looks something like this: =SQRT(number). Here’s how you can use it in your spreadsheets:
- Type the SQRT Formula
To utilize the SQRT function, click on the cell where you wish the result to be displayed and type “=SQRT(cell/number)”, substituting “cell/number” with the cell containing the number or the number itself you’re interested in finding the square root of. For example, for a number in cell B2, your formula would be “=SQRT(B2)”. If you’re working with a direct number, you could input “=SQRT(151)”. Press Enter, and Excel will display the square root of the specified number.
- Avoid Direct SQRT Usage with Negative Numbers
Using the SQRT function with a negative number will lead to a “#NUM!” error. This is because the mathematical principle dictates that the square root of a negative number does not result in a real number. If you encounter this issue, proceed to the next step for a workaround.
- Combine ABS with SQRT for Negative Numbers
To calculate the square root of a negative number, combine the ABS function with SQRT. The ABS function converts negative numbers to their absolute value, enabling the SQRT function to process them. The formula becomes “=SQRT(ABS(cell/number))”. This approach allows the SQRT function to be used universally, regardless of the number’s sign.
How to Calculate Square Root In Excel with the POWER function
Another way to find the square root of a number in Excel is the POWER function. Aside from the amusement factor of typing POWER in all caps, it works slightly differently.
What is POWER in Excel?
The POWER function differs from SQRT in that this formula for square root finds its answer by raising the specified number to the Nth power.
The POWER square root formula, therefore, takes two inputs: the number to raise to a power, and the power to raise the number to, also known as the exponent. If that still doesn’t make sense, don’t worry – you can check our example below.
- Type the Square root POWER formula in your cell
In the desired result cell, enter “=POWER(number/cell, 1/2)”, replacing “number/cell” with the number or cell reference for which you seek the square root. The “1/2” signifies the exponent, effectively calculating the square root. Press Enter to view the result.
How to Square Root in Excel with the Exponent Operator
- Use the exponent operator with your number or cell
To calculate the square root using the exponent operator, type “=cell/number^(1/2)” in the cell where you want the result, replacing “cell/number” with the actual cell or number. For instance, for a number in cell B2, the input would be “=B2^(1/2)”. Press Enter, and Excel will display the square root of the specified number.
FAQ – Frequently Asked Questions About Square Roots in Excel
Can I calculate the square root of a range of cells at once?
To calculate square roots for a range of cells simultaneously, you can apply an array formula. Select the range where you want the results to appear, enter =SQRT(first_cell_of_original_range:last_cell_of_original_range), and instead of pressing Enter, press Ctrl+Shift+Enter. Excel then processes the formula as an array formula, filling the selected range with the square roots of the corresponding cells in the original range. Remember, array formulas work differently in newer Excel versions (Office 365 and Excel 2019), where pressing Enter is sufficient.
How can I find the nth root of a number in Excel?
To find any nth root of a number in Excel, use the POWER function with the formula =POWER(number, 1/n). Here, replace ‘number’ with the value for which you want to find the nth root, and ‘n’ with the degree of the root. For instance, to find the cubic root (3rd root) of 27, you would use =POWER(27, 1/3), which returns 3. This method is efficiently versatile, allowing you to calculate not just square roots but any nth roots.
What’s the best way to deal with errors when calculating square roots of negative numbers?
To manage errors from trying to calculate square roots of negative numbers without modifying your data, you can use Excel’s IF and SQRT functions together with ABS. A formula like =IF(number<0, “Imaginary”, SQRT(number)) can differentiate between negative and positive inputs, providing “Imaginary” for negative values and calculating the square root for non-negative values. This method ensures your formulas remain error-free and alerts you to negative inputs.
How can I make square root calculations in Excel more efficient with many data points?
When working with a large dataset, leveraging Excel tables for dynamic data range and using array formulas can significantly streamline square root calculations. By converting your data range into a table (Insert > Table), any formula you apply to the first row will automatically extend to new rows added to the table. This setup saves time and ensures consistency across your calculations, making your data analysis more efficient.
Is there a way to calculate both the positive and negative square roots of a number in Excel?
Since Excel’s SQRT function returns only the positive square root of a given number, to also find the negative square root, you can simply multiply the positive square root by -1. Use two adjacent cells: in the first, calculate the positive square root with =SQRT(number), and in the second, multiply the result by -1 for the negative counterpart. This method effectively gives you both the positive and negative square roots of a number.
Can I use the SQRT function for a list of negative numbers if I’m only interested in their absolute values?
When dealing with a list of negative numbers but only concern yourself with their absolute values’ square roots, combine the ABS and SQRT functions like =SQRT(ABS(cell)). This formula first converts each negative number to its positive equivalent using ABS and then calculates the square root with SQRT, effectively providing the square root of the absolute value of any number, negative or positive.
How do I calculate the square root of a number stored as text?
For numbers stored as text, convert them to a numeric format using the VALUE function before applying the square root calculation. The formula =SQRT(VALUE(text)) first transforms the text to a numeric value using VALUE, then calculates its square root with SQRT. It’s crucial when importing data from other sources that might not initially be in the correct format for calculations.
Can I calculate square roots in Excel Online?
Excel Online supports all standard Excel functions, including SQRT, POWER, and any operator-based calculations like ^0.5 for square roots. This means you can calculate square roots in Excel Online using the same methods described for the desktop version, ensuring seamless transition and functionality across platforms.
What happens if I input a text value by mistake in the SQRT function?
Inputting a text value by mistake in the SQRT function results in a #VALUE! error, as SQRT expects a numerical input. To avoid this, ensure your inputs are numbers. If working with data that might contain non-numeric values, consider using error handling with IFERROR(SQRT(cell), “Error Message”) to catch and manage these errors gracefully, replacing “Error Message” with informative text or corrective action.
How do I reverse a square root calculation in Excel?
Reversing a square root calculation, essentially finding the original number from its square root, involves squaring the square root value. If your square root is in cell A1, use =A1^2 or =POWER(A1, 2) to find the original number. This method applies the basic mathematical principle that the square of a square root returns to the original number.
Can the SQRT function be used in conditional formatting to highlight cells?
The SQRT function can indeed be incorporated into conditional formatting rules to dynamically highlight cells based on the square root of their values. For example, to highlight cells with square roots greater than a specific value, use a formula within the conditional formatting rule like =SQRT(A1)>10. This usage allows visual distinctions based on calculated criteria, enhancing data analysis and interpretation.
Is it possible to automate square root calculations for dynamic data in Excel?
Automation of square root calculations for dynamic data is achievable using Excel tables or defined names for your ranges. Once a formula is entered in a column within an Excel table, it is automatically applied to new data entered in the table. This dynamic approach ensures calculations are always up to date without manual formula adjustments.
Can Excel handle complex numbers for square root calculations?
For square roots of complex numbers, Excel provides the IMSQRT function, specifically designed for complex numbers in the form “x + yi” or “x + yj“. Using IMSQRT(complex_number), you can calculate the square root of a complex number, enhancing Excel’s capabilities to handle a wider range of mathematical problems.
Can I use Excel to visually graph the function of a square root?
Excel effectively supports graphing a wide range of functions, including square roots. By entering a series of x-values in one column and calculating their square roots in another, you can use Excel’s charting tools to plot these values, creating a visual representation of the square root function. This approach is particularly valuable for visual data analysis and presentations.
Why does Excel return an error when I try to use the SQRT function in a certain way?
Errors from the SQRT function generally result due to non-numeric inputs, negative values (for which square roots are imaginary in the real number system), or incorrectly structured formulas. To mitigate these, ensure your inputs are numeric and use error handling like IFERROR for more resilient calculations. For negative inputs, consider using =SQRT(ABS(cell)) if absolute values are acceptable for your application or the IMSQRT function for complex numbers.
Related: How to Password Protect Excel, Word, PowerPoint, or PDF Files with Microsoft Office
While most of us already have login passwords on our PC, at times it’s necessary to go one step further. When you password protect documents with modern versions of Office, they’re typically encrypted. This means that anybody who tries to read them that doesn’t have your password will just see random strings of letters and numbers. In our other guide, we show you how to password protect Excel, Word, PowerPoint and PDF files in Microsoft Office so that you can keep your documents private.
Related: How to Find Duplicates in Excel and Remove Their Rows
If you’ve been combining multiple data sources, you can quickly end up with duplicate rows or cells in Excel. Whether it’s due to an accidental copy and paste or a typing error, this can be time-consuming to remedy. Thankfully, though, there are several tricks you can use to find and remove duplicates. In our other guide, we show you how to find duplicates in Excel and remove them with three methods: the dedicated tool, find and replace, and Advanced Filters.
Related: How to Calculate the Percentage Change between Two Numbers in Excel
It can be beneficial in many situations to display the difference between two numbers as a percentage. Doing so helps to put a change into a more understandable format for the average reader. In our other guide, we show you how to calculate percent change in Excel using a percentage change formula and some handy formatting tools.
Related: How to Add Shading to Alternating Rows in Excel
It’s an old trick at this point, but applying shading (zebra stripes) to alternative rows in Excel makes your sheet easier to read. The effect, also known as banded row, allows your eyes to keep their place more easily when you’re scanning a spreadsheet. The difficulty, then comes in knowing where to look and how to format cells as a table in the first place. In our other guide, we show you how to apply and customize table formatting to form alternating rows in Excel.
