I have found that there are five unlike ways to calculate a hearty root in Excel. Yes, five different methods, which you can use in different situations. And nowadays in this post, I ’ d like to share with you these five simple methods to find a square rout of a number in Excel. So permit ’ mho learn this thing.
- What ’ s a SQUARE Root ?
- Method-1 : Use SQRT Function to find the SQUARE Root of a Number
- Method-2 : Calculate SQUARE Root of a Number Using POWER Function
- Method-3 : Apply an Exponent Formula to Get SQUARE root of a Number
- Method-4 : VBA Code to Show the SQUARE Root of a Number
- Method-5 : Using Power Query to Convert Numbers into SQUARE Roots
- [ Bonus Tip ] How to Add SQUARE Root Symbol in Excel
- Sample File
- conclusion
- Learn More
What ’ s a SQUARE Root ?
Before we learn to calculate it, it ’ s important to understand what it actually means, how we calculate it normally. Calculating a hearty ancestor is an inverse calculation for coming back to the solution of a squarely. sol when you calculate the square of 10 by multiplying it with itself, that ’ second ( 10 * 10 = 100 ). then, feather root means coming back from 100 to 10. You can learn more about it from Wikipedia. But now, let ’ s memorize how we can do this in Excel. As I said, we can use 5 different ways for the calculation of square etymon according to the need or personal predilection. not all the methods are the like but utilitarian.
Method-1 : Use SQRT Function to find the SQUARE Root of a Number
If you need to use only one method acting for calculating square root, the SQRT affair is that one. It ’ s a specific function to do this. You equitable just need to refer to a cell or specify a count. SQRT’s Syntax: Where the total is the numeral or cell reference point which contains the count for which you want to find the square solution. SQRT ( numeral )
Examples :
In the below exemplar, we have the numeral 625 in cell A1. so, to calculate the square ancestor for this we can insert the below recipe ( Formula Bar ) into B1. = SQRT ( A1 ) The value it has returned is 25 which is the square beginning of 625. As you see, SQRT is simple to use and easy to apply. If you have a negative number like below then it will return a # NUM !.
So the solution to this trouble is to use the ABS function along with SQRT. here ’ s an model : = SQRT ( ABS ( A1 ) )
When we use ABS it converts a number into an absolute number. That means a converts a negative total into a positive.
Method-2 : Calculate SQUARE Root of a Number Using POWER Function
Another way to calculate a square root is by using the POWER function. POWER function has more might than SQRT as it returns the result of a number raised Nth to a given ability.
POWER ’ south syntax :
office ( number, ability ) Where the number is the numeral or cell ’ sulfur reference which contains the phone number for which you want to find the square root and power is the advocate to raise office besides. Let ’ s say we need to find the square solution of a number from cell A1 ( that ’ s 625 ) then we can use 1/2 as a mighty argument in this affair. And, the convention would be : = POWER ( A1,1/2 ) As you see it returns 25 in the result which is the straight root of 625.
Method-3 : Apply an Exponent Formula to Get SQUARE root of a Number
If we need to insert a elementary formula ( early than the above two methods ) to calculate a square rout then we can use a childlike recipe using the exponent hustler. This formula is precisely like POWER. The only remainder is alternatively of the officiate we need to use an exponent operator.
=A1^ ( 1/2 ) In the above convention, A1 is the cellular telephone where we have a total for which we need to find the square root, and then we have used an exponent hustler and then ( 1/2 ) for lift to power. The best benefit of this formula is its application. apart from the square root, we can use it to calculate cube settle or any nth root.
Method-4 : VBA Code to Show the SQUARE Root of a Number
Let me tell you about a unlike scenario where we need to check the square ancestor of a number alternatively of calculating it in a cell.
Code-1
Below is a VBA code that 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
When we run this code, it verifies the value in the selected cell and if that value is a total then it calculates the hearty beginning of that total and shows it using a message corner. One matter you need to take care, that if you select more than one cell this code will not work.
Code-2
By using the below code we can check to enter a feather solution of a number without having the actual total 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 signal box asking for the numeral for which we need to get the square root. And when we enter that number, it calculates the squarely beginning for that number and shows a message box with the hearty root. You can use any of the above codes which are arrant for you.
Method-5 : Using Power Query to Convert Numbers into SQUARE Roots
here ’ s another way that we can use if you need to convert multiple numbers into their squarely roots. That’s Power Query. Below we have a postpone where we have a few numbers and here we want to get the square root of these numbers in one go. Note: Using a world power question for public square root is a moral force method, every time you enter a new value in your table it will return the square solution of that phone 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.
- 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.
- At this point, we have Custom Column window opened and in this window, we need to add the following:
- First of all, enter the column name “Square Root” in the column name input box.
- After that, enter (=Number.Sqrt([Numbers])) in the custom column formula input box and click OK.
- Now we have a new column with square roots of the number we have in the first column.
- From here, you need to delete the first column of original values. So, right click on it and click remove.
- After that, go to the home tab and click on close and load too.
- 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”.
As I said, this mesa is dynamic. When you enter a new value in the original data board it calculates its square solution 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 ancestor from a count, the next thing you can do is to add a straight root symbol. And for this, we have two different ways which we can use.
1. Using a Shortcut Key
A simpleton means 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 computer keyboard. and once you release the Alt key, 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 the custom formatting option, and add formatting into the format stimulation bar. √General
3. VBA to Add a Square symbol
You can besides use below VBA code to apply custom formatting to all the cells in the excerpt.
Sub add_squareroot()
Selection.NumberFormat = ChrW(8730) & “General”
End Sub
Note: The benefit of using the # 2 and # 3 method acting 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 respect in the cell becomes a text and can ’ t be used far as a number.
Sample File
download this sample distribution file from here
conclusion
If you merely want to know the square root of a number then the VBA method acting is the best, select the cellular telephone and run the code. differently, formulas are well if you need to use a square root value in far calculations, and we have three different formulas in this tilt. power Query is a moral force method and one must give it a judge. now you tell me one thing.
Which is your favored method to calculate a square root in Excel ? Share your views with me in the comment section, I ’ vitamin d love to hear from you. And, don ’ deoxythymidine monophosphate forget to share this number with your friends, I ’ thousand sure they will appreciate it. 🡨 Back to Excel Formulas