How to Calculate SQUARE ROOT in Excel + Insert Symbol

Excel is like a Swiss army knife for calculations.

From mathematical to financial, it’s for every single thing you need to calculate.

You can calculate simple to most complex things in Excel.

Take an example of SQUARE ROOT.

A square root is a quite common calculation which we do in our day to day work, and in Excel, you can calculate it with an ease.

You know what?

I have found that there are five different ways to calculate a square root in Excel. Yes, five different methods, which you can use in different situations.

And today in this post, I’d like to share with you these five simple methods to find a square root of a number in Excel.

So let’s learn this thing…

What's a SQUARE Root?

how to calculate square root in excel

Get Better at Excel in 2018

A bundle of E-Books you need to thrive in Excel this year.

Top 5 Ways to Calculate SQUARE Root in Excel

As I said, we can use 5 different ways for the calculation of square root according to the need or personal preference.

Not all the methods are same but useful.

#1. Use SQRT Function to find the SQUARE Root of a Number

If you need to use only one method for calculating square root, SQRT function is that one. It’s a specific function to do this.

You just simply need to refer to a cell or specify a number.

SQRT’s Syntax:

SQRT(number)

Where the number is the number or cell reference which contains the number for which you want to find the square root.

Examples:

In the below example, we have the number 625 in the cell A1. So, to calculate square root for this we can insert below formula into B1.

=SQRT(A1)

combining sqrt with abs to calculate square root for a negative

The value it has returned is 25 which is the square root of 625.

As you see, SQRT is simple to use and easy to apply.

But there’s a kicker.

If you have a negative number like below then it will return a #NUM!.

error while calculating square root in excel with negative number

So the solution to this problem is to use ABS function along with SQRT. Here’s an example:

=SQRT(ABS(A1))

using sqrt function to calculate square root in excel

When we use ABS it converts a number into an absolute number. That means a converts a negative number into a positive.

#2. Calculate SQUARE Root of a Number Using POWER Function

Another way to calculate a square root is by using POWER function.

POWER function has more power than SQRT as it returns the result of a number raised Nth to a given power.

POWER’s Syntax:

POWER(number, power)

Where the number is the number or cell’s reference which contains the number for which you want to find the square root and power is the Exponent to raise power to.

Let’s say we need to find the square root of a number from cell A1 (that’s 625) then we can use 1/2 as power argument in this function.

And, the formula would be:

=POWER(A1,1/2)

use power function to calculate square root of a number in excel

As you see it returns 25 in the result which is the square root of 625.

#3. Apply an Exponent Formula to Get SQUARE root of a Number

If we need to insert a simple formula (other than above two methods) to calculate a square root then we can use a simple formula using exponent operator.

This formula is just like POWER. The only difference is instead of the function we need to use an exponent operator.

=A1^(1/2)

using an exponent operator formula to get square root in excel

In the above formula, A1 in the cell where we have a number for which we need to find the square root and then we have used an exponent operator and then (1/2) for raise to power.

The best benefit of this formula is its application. Apart from the square root, we can use it to calculate cube root or any nth root.

#4. A VBA Code to Show the SQUARE Root of a Number

Let’s me tell you about a different scenario where we need to check the square root of a number instead of a calculating it in a cell.

Code-1

Below is a VBA code which returns the square root when we select a cell and run this 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

How this code works

Code-2

By using below code we can check enter a square root of a number without having the actual number in a cell.

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 this code works

When we run this code it shows us an input box asking for the number for which we need to get the square root.

And when we enter that number, it calculates the square root for that numbers and shows a message box with the square root.

You can use any of the above code which is perfect for you.

#5. Using Power Query to Convert Numbers into SQUARE Roots

Here’s another way which we can use if you need to convert multiple numbers into their square roots.

That’s Power Query.

Below we have a table where we have few numbers and here we want to get the square root of these numbers in a one go.

a simple table to calculate square roots in excel using power query

Note: Using power query for square root is a dynamic method, every time when you enter a new value in your table it will return the square root of that number.

Now, follow these simple steps.

  • First of all, select any cell from the table and go to Data Tab ➜ Get & Transform Data and click on From Table/Range.
select any cell from the table to calculate square root in excel using power query
  • Once you click on it, Excel opens the power query editor and add that table into it.
  • From here we need to create a new column with square root values and for this go to Add Column tab and click on Custom Column.
click on custom column to add a column to find square root of numbers in excel
  • At this point, we have Custom Column window opened and in this window, we need to add following:
  1. First of all, enter the column name “Square Root” in column name input box.
  2. After that, enter below formula in the custom column formula input box and click OK.

=Number.Sqrt([Numbers])

enter column values create a new column to calculate square roots of numbers in excel using power query
  • Now we have a new column with square roots of the number we have in the first column.
newly added column with square root values in excel using power query
  • From here, you need to delete the first column of original values. So, right click on it and click remove.
click remove to load table to worksheet to get square root in excel using power query
  • After that, go to home tab and click on close and load to.
click close and load to get square root data into existing worksheet using power query in excel
  • Here you get a window to load data. First of all, select the existing worksheet and add range B1. Second, tick mark "Add to the Data Model". 
window to add square root data into existing worksheet using power query in excel

As I said, this table is dynamic.

When you enter a new value in the original data table it calculates it’s square root automatically in your new table when you refresh it.

And, we are not using any type of formula here.

[Bonus Tip] How to Add SQUARE Root Symbol in Excel

Once you calculate a square root from a number, the next thing you can do is to add a square root symbol.

And for this we have two different ways which we can use.

#1. Using a Shortcut Key

A simple way to add a square root symbol is the shortcut key and the shortcut is Alt + 251.

You need to hold down the Alt key as you type 251 on the numeric keypad. and once you release the Alt key, and the symbol appears.

#2. Add a SQUARE Root Symbol by Applying Custom Formatting

This is the most effective way to add a symbol.

All you need to do is, select all the cells, open custom formatting option and add formatting into the formatting input bar.

√General

add square root symbol in a cell using custom formatting

In the end, click OK.

#3. VBA to Add a Square Symbol

You can also use below VBA code to apply a custom formatting to all the cells in the selection.

Sub Radical()

Selection.NumberFormat = ChrW(8730) & "General"

End Sub

Note: The benefit of using #2 and #3 method is you don’t need to add a symbol to all the cells one by one and when you add a symbol to a cell the value in the cell become a text and can’t be used further as a number.

Sample File

Download this sample file from here to learn more.

Conclusion

If you just want to know the square root of a number then VBA method is the best, select the cell and run the code.

Otherwise, formulas are best if you need to use a square root value in further calculations, and we have three different formulas in this list.

Power Query is a dynamic method and must to give it a try.

Now you tell me one thing.

Which is your favorite method to calculate a square root in Excel?

Share your views with me in the comment section, I’d love to hear from you. And, don’t forget to share this list with your friends, I’m sure they will appreciate it.


Content Protection by DMCA.com
2018-11-16T06:13:22+00:00

3 Comments

  1. Kirandeep 13 Dec, 18 at 2:28 pm - Reply

    Nice one puneet sir, please give info about use of kuttools Sir

  2. duncanwil 8 Nov, 17 at 9:16 am - Reply

    In power query there is a more direct way to convert numbers to square roots but it does so on the fly whereas your version is more programmatic.

    This is not a criticism, just FYI!

    • Puneet Gogia 8 Nov, 17 at 11:16 am - Reply

      Yes, you are quite right on this.

Leave A Comment