It’s no doubt the reason why many people need to calculate the square root of numbers in Excel. With such a common task, there are many ways to get it done in Excel. In this post, I’ll show you 5 ways you can use to calculate the square root of a number.
What is a Square Root of a Number?
The square root of a number is another number that produces the original number when multiplied by itself. You would say A is the square root of B if and only if A x A = B. For example, 3 is the square root of 9 because 3 x 3 = 9. 1.5 is the square root of 2.25 because 1.5 x 1.5 = 2.25.
Calculate the Square Root with the Carat Operator
One way you can use to calculate the square root of a number is using the carat (^) operator. This is Microsoft Excel’s exponentiation operator and will allow you to raise a number to an exponent or power. Finding the square root of a number is the same as raising that number to a power of 1/2 = 0.5. This is because A1/2 x A1/2 = A(1/2+1/2) = A1 = A. You can use the carat operator as above to raise the number in cell B3 to the power of 1/2 or 0.5 which will produce the square root.
Calculate the Square Root with the SQRT Function
Another useful way to get the square root is with the SQRT function. In fact, the sole purpose of this function is to return the square root of a number you supply it.
Syntax for the SQRT Function
number is the positive number of which you would like to calculate the square root.
Example of the SQRT Function
The above example will return the square root of the number in cell B3. In this case, it returns 2 as the result.
Calculate the Square Root with the POWER Function
The SQRT function is a very focused function with a single-use case of calculating the square root. There is a more generalized function that will allow you to calculate the exponent of any number including the square root value.
Syntax for the POWER Function
base is the number which you would like to calculate the square root.exponent is the power to which you would like to raise the base value.
Example of Calculating the Square Root with the POWER Function
If you want to use the POWER function to find the square root, then you can use an exponent of 1/2 = 0.5. The example above will return the square root of the value in cell B3.
Calculate the Square Root with the SERIESSUM Function
The SERIESSUM is a specialized function that allows you to evaluate a series. A series is just the sum of a sequence of terms like the above generic formula. The SERIESSUM function will evaluate this sum. Since x(1/2) is a special case of this generic formula, you can use the SERIESSUM function to evaluate the square root of a value.
Syntax for the SERIESSUM Function
x is the input value of the series. This is the value which you would like to evaluate the series at.n is the starting power of the series.m is the step by which the power will increase in the series.coefficients is an array of values (a1, a2, a3,…,ai) to multiply each term of the series.
Example of Calculating the Square Root with the SERIESSUM Function
Now if you’d like to use the SERIESSUM function to calculate the square root of a number, then all you need to do is use a single value instead of an array of values for the coefficients. This way the series will only have a single term. You can then set the starting power as 1/2 and the step increases as 0. In the above formula, cell B3 contains the value you wish to find the square root of. The starting power of the series is n = 1/2 and this will increase by a factor of m = 0 for each term in the series. Because the coefficients = 1, there will only be one term in the series which will be the square root of B3.
Calculate the Square Root with Power Query
Power query is the best way to import and transform data in Excel. If your data is from an external source, then you might be using a power query to get the data into Excel. Power query would also be a great place to add any calculations, such as a square root, during the import. If your data is already in Excel, you can also use a power query. Add your data into an Excel table. Select the data and press Ctrl + T to create a table. Go to the Data tab and press the From Sheet command. This will open up the power query editor with your data. Go to the Add Column tab and click on Custom Column to create a new column with the square root calculation. This will open the Custom Column menu. Give the column a name such as Square Root, and insert the above formula into the formula editor, then press the OK button. In this example, the Numbers column contains the number which you want to find the square root. This will create a new column in the data! You can now go to the Home tab and press the Close and Load button to load the data back into Excel. The Import Data menu will appear and you can select to load the data into a Table then choose the location where you’d like to load the data. In this example, the data is being loaded into an Existing worksheet in cell D2. Your data will load into another Excel table with the additional Square Root column.
Conclusions
Excel offers many different options when calculating the square root! This blog post showed you five different ways you can find the square root of a number. The carat operator, SQRT function, POWER function, SERIESSUM functions, and power query can all be used to calculate the square root. Do you know any other methods? Let me know in the comments section below!
