A really simple and often needed task is to calculate the square root in Excel. And as I said, there are multiple ways you can do that in Excel (formulas, VBA, Power Query). In this tutorial, I will show you different ways to calculate square root in Excel (and you can choose whatever method you like best). But before I get into how to calculate it, let me quickly cover what is square root (feel free to skip to the next section if I get too preachy).
What is Square Root?
When you multiply a number (let’s say X) with itself, you get a value (let’s say Y). Here X is the square root of Y. For example, 10 multiplied by 10 is 100. Here 10 is the square root of 100. That’s it! It’s simple. But it’s not so simple to calculate it. For example, if I ask you to calculate the square root of 50, I am sure you can’t calculate it in your head (unless you’re a math whiz). Here is a detailed article on Wikipedia in case you’re interested in learning more about it. In this tutorial, I will show you a couple of ways (easy ones I promise) to calculate square root in Excel. So let’s get started!
Calculate Square Root Using the SQRT function
Yes, there is a dedicated function in Excel whose purpose of existence is to give you the square root SQRT function takes one single argument (could be the number or reference to the number) and returns the square root of that number.
To calculate the square root of 100 in Excel, you can use the below formula: The above formula will give you 10, which is the square root of 100. You can also use a cell reference in the SQRT function as shown below:
While this function works great with positive numbers, if you give it a negative number, it will return a #NUM error.
This is understandable as in mathematics, a negative number doesn’t have a square root. Even if a number is negative, when you multiply it by itself, the result is positive. But in case you still want the square root of a negative number (assuming it was a positive), you can need to first convert the negative number into a positive one and then find the square root of it. For example, if you want to get the square root of -100, you can use SQRT function with the ABS function:
ABS function gives you the absolute value and ignores the negative sign. Note: There are two more square root related functions in Excel:
Calculate Square Root Using the Exponential Operator
Another way to calculate the square root (or cube root or Nth root) in Excel is by using the exponential operator. If all you need is the square root of a number, it’s easier to use the SQRT function. The benefit of using the exponential operator is that you can use it to find the square root, the cube root, or the nth root as well. You can also use it to find the square of a number. Below is the formula that will give you the square root of 100 or
Similarly, you can get the cube root or the Nth root of a number as well. Below is the formula to get the cube root of 100 And below is the formula to get the Nth root of 100 (replace N with any number you want)
Calculate Square Root Using the POWER Function
Another easy way to calculate square root in Excel (or cube root or Nth root) is by using the POWER function. Below is the syntax of the POWER function: It takes two arguments: Unlike the SQRT function, you can use the POWER function to calculate the roots (such as square root or cube root) or powers (such as square or cube) of a number. Below is the formula that will give you the square root of 100 in Excel: or
In case you want the cube root, you can use the below formula: And similarly, if you want the square of a number, you can use the same formula with the relevant second argument.
Getting the Square Root Using Power Query
While the above formulas as quick and, if you work with a lot of data and this is something you need to do quite often, you may also consider using Power Query. This method is more suited when you have a large dataset where you want to calculate the square root of values in a column, and you get a new dataset every day/week/month/quarter. Using Power Query will minimize effort as you can simply plug in the new data and refresh the query and it will give you the result. For the purpose of this example, I will use a simple dataset as shown below where I need to calculate the square root on values in column B.
Below are the steps to calculate square root using Power Query in Excel: The above steps would create a new worksheet in the Excel workbook and insert the table from the Power Query. This new table will have the square roots of the original table data. In case you have any negative values in the cells, you will get a #NUM error in the resulting table. While there are a few steps involved in getting this to work with Power Query, the awesome benefit of Power Query is that now you can simply refresh and get the new results when you get the new data. For example, if you now get a new data next month, you need to simply copy and paste that data in the table we created (in Step 4), go to any cell in the table you got from Power Query, right-click and hit refresh. So, while it takes a few clicks on to get this done the first time, once set, you easily transform the new data with a simple refresh. Apart from the square root, you can also use the Power function in Power Query to get the cube root or Nth root (or to get the square or cube of the numbers).
Inserting the Square Root Symbol (√) in Excel
While a little off-topic, I thought I will also let you know how to insert the square root symbol in Excel. This could be useful when you want to show value with the square root symbol and the square root of it side by side. Something as shown below. And just like we have different formulas to calculate the square root value in Excel, we also have a couple of methods to insert the square root symbol too.
Insert Square Root Symbol with a Shortcut
If you use a numeric keypad, here is the keyboard shortcut to insert the square root symbol: Hold the ALT key and then press the number keys 2,5, and 1 on the numeric keypad. Now when you leave the keys, the square root symbol will be inserted.
Insert Square Root Symbol with a Formula
You can also use a formula to get the square root symbol in Excel. This can be useful when having a column of values and you want to quickly add the square root symbol to all of these. To get the square root symbol, you can use the below formula:
Since this is a formula, you can also combine it with other formulas or cell references. For example, if you have a column of values and you want to add the square root symbol to all these values, you can use the formula as shown below:
Note: If you only want to get the square root symbol a couple of times, you can get it once using the keyboard shortcut or the formula, and then simply copy and paste it.
Insert Square Root Symbol by Changing the Custom Number Format
And finally, the third way to add the square root symbol is by changing the cell formatting in such a way that it would appear whenever you type anything in the cell Below are the steps to change the cell formatting to automatically add the square root symbol: Once done, you will see that all the selected cells automatically gets the square root symbol. One good thing about using this method is that it doesn’t change the value in the cell. It only changes how it’s being displayed. This means that you can use these values in formulas and calculations without worrying about the square root symbol interfering. You can confirm this by selecting a cell and looking at the formula bar (which shows you the actual value in the cell). Hope you found this tutorial useful! You may also like the following Excel tutorials:
What is Standard Deviation and How to Calculate it in Excel? Calculating Moving Average in Excel How to Calculate Compound Annual Growth Rate (CAGR) in Excel How to Calculate and Format Percentages in Excel How to Calculate Compound Interest in Excel How to Find Range in Excel
