How to Calculate the Square Root of a Number in Excel

Microfost Excel is an extremely powerful tool that can be used to solve complex calculations. However, a lot of casual users only make use of Excel for basic tabling needs, without using it to perform even the simplest mathematical operations. But there are situations where you are forced to do calculations in Excel in order to speed things up. One of the most common calculations that Excel users have to make is finding out the square root of a number.

With this in mind, we have created an article with five different methods that will help you calculate the square root of a number in Excel. They will all lead to the same result, but some of them are easier than others. The methods below are ordered by difficulty, so consider sticking with the first three methods if you’re not an avid Excel user.

Let’s begin!

Method 1: Calculating Square Root using SQRT Function

Using the SQRT function is one of the easiest ways to find out the square root of a number. It’s extremely easy to use since all you need to do is to pass the number (or reference) of a cell containing anumber to the SQRT function.

The Syntax for this method is :

SQRT(number)

Note: number is a placeholder for the actual number or for the cell reference that contains the number.

Example

To keep things simple, let’s say that we want to find out the square root of the number 9 (located on A2). To do this using the SQRT function, all we have to do is insert the following formula into the result cell (B2): ‘=SQRT(A2)’. 

Note: Keep in mind that we could have also used the number directly, instead of the cell reference –=SQRT(9)

However, there’s one small problem in using the SQRT function directly – If you try to pass a negative number, it will show the #NUM! error instead of the actual result.

To avoid #NUM! errors when using the SQRT function is recommended that you use the ABS function in conjunction with the SQRT function. What the ABS function does is it converts a number into an absolute number. In our case, it will convert the negative numbers into positive numbers. Here’s an example:

Method 2: Calculating the Square Root using the Power Function

Using the POWER function is yet another way to calculate the square root of a number in Excel. However, it works a little differently when compared with the SQRT function. Using the POWER function, we can find the square root of a particular number by raising the number to the Nth power.

Here’s the Syntax for the method:

POWER (number, power)

Note: number is a placeholder for the actual number or cell reference, while power is the exponent to raise the number to that power.

Given the fact that we want to find the square root of a number, we can use the power attribute as ‘1/2’. In this case, the formula becomes POWER (number, 1/2).

Example

To keep things simple, let’s assume again that we need to find the square root number of cell A2 (which is 9 in our case). To do this, we can use the power argument as 1/2 in the resulting cell (B2).

Method 3: Using the Exponent Operator to Find the Square Root of a Number

A lot of expert Excel users consider this method the easiest way of finding the square root of a number. We decided to rank it on the 3rd place since it’s not that common. It implies using an exponent operator to get us the result.

An exponent operator will allow us to raise a number to any power. Similar to Method 2, we can use (1/2) as the exponent in order to get the square root number in the resulting cell.

Here’s the syntax for it:

=A1^(1/2)

Note: As you can see, the formula is very similar to the method above where we used the Power function. The only key difference is that instead of the function, we need to use an exponent operator.

Example

In this example, we are going to use an exponent formula to get the SQUARE root of the number 9 (cell A2). To do this, we used ‘(1/2)’ as the exponent. Because we have the number in cell A2, using the formula A2^(1/2) on the result cell will give us the square root number.

Method 4: Using VBA scripts to find the square root of a number

This method is a little advanced, so if you’re not comfortable with VBA scripts, consider sticking to the first three methods. The 4th way of finding the square root of a number is to use VBA codes.

To tackle this particular scenario, there are two different codes that you can use to return the square root of a number. Continue reading below for the codes as well as instructions on how to enforce them.

VBA Code 1: Returning the square root when a cell is selected

Whenever you’ll run this VBA code, it will verify the value of the selected cell. If that value is a number, it will directly calculate the square root of that number and show it inside a message box.

But keep in mind that this code will only work as long as you make sure to not select more than one cell

Code:

Sub getSquareRoot()

Dim rng As Range

Dim sqr As Long

If Application.Selection.Cells.Count > 1 Then

MsgBox "Please select only one cell", vbOKOnly, "Error"

Exit Sub

End If

Set rng = ActiveCell

If IsNumeric(rng) = True Then

sqr = rng ^ (1 / 2)

MsgBox "The Square Root of " & rng & " is " & sqr, vbOKOnly, "Square Root Value"

Else

MsgBox "Please select a numeric value", vbOKOnly, "Error"

End If

End Sub

VBA Code 2: Checking the square root of a number without selecting it in a cell

This code will show us an input box asking for the number of which we need to get the square root. This means you don’t actually need to have it inserted in your a cell. When you enter that number, it will calculate the square root of that number and show a message with the square root in a message box.

Code:

Sub getSquareRoot()

Dim sq As Long

Dim sqr As Long

sq = InputBox("Enter the value to calculate square root", "Calculate Square Root")

If IsNumeric(sq) = True Then

sqr = sq ^ (1 / 2)

MsgBox "The Square Root of " & sq & " is " & sqr, vbOKOnly, "Square Root Value"

Else

MsgBox "Please enter a number.", vbOKOnly, "Error"

End If

End Sub

How to insert and run a VBA code in Excel

If you decide to use a VBA code, you can choose between the two featured above – pick the one that makes more sense for whatever it is you’re trying to do.

But in order to use that code, you need to know how to insert and run it first. Here’s a quick guide through the whole thing in case you need further guidance:

1. Open the spreadsheet that you wish to apply the VBA code on and press Alt + F11 to open up the Visual Basic Editor (VBE).
2. Once you’re inside the Visual Basic Editor, right-click on the spreadsheet that you’re targeting and choose Insert > Module (using the context menu).

   

3. In the newly opened module, copy one of the VBA codes that we featured above.

   

4. Once the code has been inserted. press Ctrl +  S to save the changes. Then, choose a location for your modified excel document and click the Save button.

   

5. If you are prompted that the VB project can’t be saved as a macro-free workbook, click No at the prompt.

   

6. Under Save as type, set the file type to Excel Macro-Enabled Workbook.

   

7. Once the code is saved, press  Alt + Q to close the VBA editor and return to your workbook.
8. Now to open the VBA code that you previously created, press Alt + F8 to open the Macro dialog. Once you get there, select the macro that you want to run and click the Run button.

   

9. After a brief while, you will see the result of your VBA code.

   

Method 5: Using a Power Query to Convert Numbers into Square Roots

This is the most advanced method out of the bunch, but there’s a huge advantage to this strategy – it allows you to convert multiple numbers into their square roots.

Creating a power query capable of doing this is a little bit of work, but it will save you a lot of time if you have a lot of numbers that need to be converted into square roots.

Another great advantage of this power query is that you will achieve a dynamic method – this means that everytime you enter a new value in your table, it will automatically return the square root of that number.

If you’ve deiced to create a power query capable of doing this, follow the instructions below:

1. First, select any cell from your table and go to the ribbon at the top to select Data > Get & Transform Data, then click on From Table.

   

2. As soon as you click that, Excel will open a power query editor that includes your table. Click Ok to confirm the creation of your table.

   

3. In the Power Query Editor, go to the ribbon at the top and click on the Add Column tab. Then, click on Custom Column.

   

4. This will open up a new Custom Column window.  Once you get there, enter Square Root under New Column name. Next, go down below and insert the following formula in the Custom column formula box:

   =Number.Sqrt([Numbers])

   

5. Click OK to confirm the creation of this new custom column. You will notice that the table has just gotten an additional column with the square roots of the numbers it previously had.