Category: Excel Tips

Have you ever imported data to Excel from an external source with many unwanted hyperlinks that came along?

Also, you might have come across multiple undesired hyperlinks automatically generated by Excel for emails and URLs inserted into a workbook.

Unwanted hyperlinks can cause undue trouble. Every time you select a cell containing a hyperlink, excel would automatically redirect you to the destination link.

Hyperlinks that are not needed can be removed from Excel with sheer ease.

Continue reading to know how you can instantly remove hyperlinks in Excel.

Remove Hyperlink in Excel

To better learn the easy two-step process of removing hyperlinks from selected cells in Excel, let’s stipulate an example below.

Remove hyperlinks from selected cells

The data set below collates different links containing information relevant to various topics. As soon as the links are input, Excel recognizes them as hyperlinks and formats them into blue color and underlines them.

To remove the hyperlinks from only some selected cells, here are the steps that you need to follow.

Step 1:

Select the cells containing the hyperlinks that you want to be removed.

Step 2:

Right-click by keeping the cursor on the selected cells to see the pop-up menu as below.

From the pop-up menu that opens up, select the option, ‘Remove Hyperlinks’.

Excel removes hyperlinks from the selected cells as evident below.

That is it. Removing hyperlinks from Excel is only that easy.

However, what if you have an Excel file that is densely packed with hyperlinks? Is it not hectic to select each cell to remove the hyperlink therefrom? It definitely is. Here is a shortcut that may help you with it.

Remove all hyperlinks together 

Continuing with the same example stipulated above, to remove hyperlinks from the entire spreadsheet at the same time, follow the steps below.

Step 1:

Press ‘Ctrl+A’. This is the shortcut to select all the contents of an active sheet.

Step 2:

Right-click against the selected cells to have the pop-up menu opened. Opt for ‘Remove Hyperlinks’ as follows.

Excel removes hyperlinks from the entire spreadsheet as follows.

Pro Tip: Prevention of Automatic Navigation to the Destination Link

As soon as you select a cell that contains a hyperlink, you will be navigated to the destination link. Or in case of an email address, Excel would present you with the option to send an email to the subject address.

If Excel redirects you to the destination link automatically upon selecting a cell, you may turn it off as follows.

File > Options > Advanced > Editing options

Check the option for 'Use CTRL + Click to follow hyperlink'. Once this is done, Excel would present you with the option to press ‘Ctrl’ to follow a link. Merely selecting a cell in Excel wouldn’t navigate you to the destination page.

Suggested Tutorial: How to Extract URL from Hyperlinks in Excel

Put Off Automatic Hyperlinks in Excel

It is often the case that you’ve to work with Excel files containing excessive Hyperlinks. For example, the Human Resource department of a Company may have to maintain a record for all of its employees, including their names, numbers, and email addresses.

Whenever fed into Excel, email addresses are recognized by Excel as hyperlinks. If you don’t want the file to contain hyperlinks, such settings of Excel might prove problematic. This is because every time you feed the details of a new employee into the Excel sheet, Excel would automatically turn the email address into a hyperlink.

Removing each hyperlink time and time again is time-consuming and frustrating. To prevent Excel from generating hyperlinks automatically, you can disable the same from the settings. Here’s how you can do it.

Step 1:

Go to File > Options as follows.

Step 2:

Selecting options would open up a window of Excel options as follows.

From the said window, choose Proofing > AutoCorrect Options as highlighted above. This would open up a pop-up window.

Choose ‘AutoFormat as you type’ and uncheck the box for ‘Internet and network paths with hyperlinks’ as highlighted below.

This will turn off the automatic hyperlinking within Excel. Now, as you type or Copy / Paste links in Excel, those would remain as text, and Excel would not automatically turn them into Hyperlinks.

Pro tip: This setting will be applied to the entire Excel application. Any workbook that you create or edit with Excel will have the same settings applied.

However, after these settings are applied, you can still add hyperlinks to one or more cells by right-clicking on the desired cell. From the pop-up menu that then opens up, select ‘Link’. This would open up a dialogue box where you can insert the destination link to be added as hyperlink to that cell.

Conclusion:

Hyperlinks make an intelligent feature of Excel. However, undesired hyperlinks are often a nuisance. Hope this article helped you learn all that you needed to know how to remove hyperlinks in Excel.

If you’ve ever worked in an HR role, you know how challenging it can be to maintain a large volume of personal details for multiple employees, including their CNIC numbers, IBANs, Passport numbers, or even cellphone numbers.

All of these numbers are often accompanied by dashes that may appear in the center, at the beginning, or the end of the number.

Manually removing the dashes is definitely not an option as this could take hours or even days if dealing with a substantial amount of data. Additionally, there is a high chance of data loss or errors due to accidental backspacing and clicks.

3 Methods to Remove Dashes in Excel

1.  Find and Replace Function

The very well-known ‘Find and Replace’ function can help you remove dashes from Excel only in a few clicks. Take a look below to see how.

The data above represents the IBANs for a list of employees. As evident, these numbers are paired with multiple dashes that must be removed.

To do so, simply select the Column containing the dashes (i.e. Column B) and go to the Find and Replace function as follows.

Home > Editing > Find & Select > Replace

Pro Tip: Press Ctrl + H to instantly open up the Find and Replace dialogue box in Excel.

Against ‘Find’, enter dashes (-), whereas, leave the box against ‘Replace’ vacant as we want the dashes to be removed.

Hit ‘Replace All’ to have the results as follows.

Related Tutorial: How to Remove Commas in Excel

2.  The Substitute Function

Next on the list is the Substitute Function that works on the pattern of the ‘Find and Replace’ Function. Syntax of the Substitute Function reads as follows.

=SUBSTITUTE (text, old_text, new_text, [instance])

• Text: The text where changes are to be made.
• Old_text: The text to be replaced
• New_text: The text to be replaced with
• Instance: Optional. The instance where changes are made. All instances are replaced if omitted.

You may access the Substitute function from the Functions Library as follows.

Home > Editing > Find & Select > Replace

In the example given above, to use the substitute function to remove dashes from Employee IBANs in Excel, compose it as follows.

=SUBSTITUTE (B2, “-“, “”)

Where B2 defines the cell containing dashes. The Old text is set as “-“, and the new text is left blank “” as we want the dashes to be replaced with nothing but removed.

Note: Both the strings for the Old and New text must be enclosed in double quotation marks.

Hit Enter to see the results and drag the fill-handle to yield similar results against the whole list of IBANs.

Related Tutorial: How to Remove Leading Zeroes in Excel

3.  Kutools

The third and last option on this list involves Kutools. Putting in formulas to remove dashes may prove a little hectic and technical for new Excel users. Kutools offer an in-built tool for character removal from Excel.

Continuing the same example as above, to remove dashes between IBANs from Excel, follow these simple steps.

Step 1:

Select the Column containing the dashes i.e. Column B and then,

Go to Kutools > Text > Remove Characters

This opens up the ‘Remove characters’ window, as shown below.

Step 2:

Under the option to ‘Remove characters’, choose Custom and select dash (-) from the dropdown menu.

Take a look at the data post-dashes removal through the Preview Pane on the right and Hit ‘Okay’ if everything looks good.

Excel filters out dashes from the selected range, as shown below.

Note: Kutools is an add-in to Excel. If you do not see the tabs ‘Kutools’ or ‘Kutools Plus’ on your Excel Ribbon, you probably do not have it installed in your Excel. But no worries, you can easily download it from the web and have it added to your Excel.

Bottom Line

Try either of the above methods to remove unwanted dashes from your dataset in Excel. Or try installing Kutools to ease the job through an in-built character removal function.

Suggested Tutorial: How to Strikethrough in Excel?

Data exported from a system are usually in a fixed report format. Sometimes though, it even includes unnecessary rows in between the data.

In my example below, notice that there’s a total row added between each record.

These rows may be helpful in certain instances. But if you intend to create charts from these data or maybe add formulas in your file to do some calculations, these rows can be bothersome.

You can manually delete these rows, but if you’re working on large datasets, that would be too much work.

In this tutorial, I’ll show you ways to mass delete every other (or every nth) row in Excel.

Before we start, please ensure that there is no other data next to the dataset. Otherwise, they may get deleted in the process. You may want to create a backup of the file first or copy the dataset onto an empty sheet.

Delete every other row in Excel by filtering odd or even rows 

This method is perfect if you want to delete odd or even rows in your dataset.

1. On a new column next to the dataset, type the following formula:

These formulas will return TRUE if the current row is odd or even (depending on the function you have used).

We want to mark the rows we will delete with TRUE, so be sure to add the right formula.

2. Drag the Fill Handler until you reach the last row in your dataset. Doing so will copy the formula to the remaining rows.

3. Once you have added all formulas, check if all the rows you intend to delete are marked as TRUE.

If you see that the “row markers” are correct, it’s time to filter the data.

4. Select all the cells within your dataset (including the header).

You can do so by selecting one cell in the dataset and pressing CTRL + A to select all.

5. Go to the Data tab and click on the Filter button under the Sort & Filter section.

You should see the filter buttons added on the header row.

6. Click the filter button on top of the formula we have previously added, uncheck the FALSE option, and click OK.

This will filter the records to only display rows marked as TRUE.

7. Highlight all the cells in your dataset that are marked as TRUE.

Remember not to include the header row.

8. From the Home tab, click the Find & Select button and click Go To Special.

9. In the Go To Special menu, select ‘Visible cells only’ and click OK.

10. Notice that the rows in your dataset are now individually selected.

11. Right-click on one of these rows and select Delete Row.

12. A prompt will appear to confirm the process. Click OK to continue.

Remember that this action deletes the entire row, which means any data next to the dataset gets deleted.

13. And that’s it! You have successfully removed all the selected rows.

14. Let’s now remove the filters to view the dataset again.

From the Data tab, click on the Filter button.

15. You now have successfully deleted the odd or even rows from your dataset.

Remove the formulas you have added as “row markers” and you’re all set.

Delete every nth row in Excel by filtering the nth row

If you intend to delete every third, fourth, fifth, etc., row in your dataset, then this method is for you.

We are going to follow the same steps listed in the previous method, except that we will use a different formula to mark the rows we are going to delete.

1. On a new column next to the dataset, type the following formula:

=MOD(ROW()-[header row],[nth row])

• [header row] refers to the row where your header row is. If it’s in row 1, then type 1. Set this to 0 if your dataset doesn’t have a header.

• [nth row] refers to the nth row in the dataset you would like to delete. So, if you’d like to delete every 3^rd row in your dataset, set this to 3.

Examples:

In case you don’t know, MOD() is a function in Excel that returns the remainder of the number divided by a number.

With the formula above, we are looking at marking the rows that we intend to delete with 0 (which means that the row is divisible to the nth row we have set).

2. Copy the formula to the remaining cells in the worksheet.

See if all the rows you intend to delete are marked with zero. If not, you may need to adjust the formula.

3. Select a cell within the dataset and press CTRL + A. Doing so will select all items in the dataset.

4. Go to the Data tab and click on the Filter button under the Sort & Filter section.

The filter buttons will appear on the header row of your dataset.

5. Click the filter button on top of the formula we have just added.

Adjust the filters so that only zero (0) appears in the dataset.

6. Once the filters are applied, highlight all visible rows in the dataset (those that are marked with 0). Remember not to include the header in the selection.

7. Go to the Home tab, click the Find & Select button, and click Go To Special.

8. In the Go To Special menu, select ‘Visible cells only’ and click OK.

9. The rows in your dataset should now be individually selected.

10. Right-click on one of these rows and select Delete Row.

11. A prompt will appear warning you that this action will delete the entire row. Click OK to continue.

12. And that’s it! The rows that you have selected are now deleted.

13. To view the dataset again, remove the filters by going to the Data tab and clicking on the Filter button.

14. You now have a “clean” dataset. Remove the formulas you’ve previously added, and you’re all set.

Related Tutorial: How to Delete Columns in Excel

Delete every other row in Excel using a VBA Code

If you regularly need to delete every other row in Excel, it may be best to have a macro do this for you.

1. While your Excel file is open, press ALT + F11. This should open the VBA Editor.

2. Go to the Insert menu and click on Module.

3. On the new module added, paste the following code:

Option Explicit

Sub DeleteEveryOtherRow()
    Dim rng As Range, nthRow As Long, ctr As Long, lastrow As Long
    
    On Error Resume Next
    ThisWorkbook.ActiveSheet.ShowAllData
    On Error GoTo 0
    
    On Error Resume Next
    Set rng = Application.InputBox(prompt:="Select your entire dataset (excluding the headers):", Title:="Range Selection", Type:=8)
    If Err.Number > 0 Then Exit Sub
    On Error GoTo 0
    
    nthRow = Application.InputBox(prompt:="Every which row should be deleted? (e.g., 1 for first, 2 for second)", Title:="Row to be Deleted", Type:=1)
    If nthRow <= 0 Then Exit Sub
    
    With ThisWorkbook.ActiveSheet
        lastrow = rng.Rows.Count
        nthRow = nthRow * -1
        
        For ctr = lastrow To 1 Step nthRow
            rng.Rows(ctr).Delete
        Next
    End With
End Sub

Now, it’s time to test the code. But before we do that, please create a backup of your dataset. Remember that changes done by a macro cannot be undone.

To run the code:

1. Click on any line on the code and press F5.

2. A prompt will appear asking you to select your entire dataset.

3. Go back to your workbook and select from your worksheet the range that contains your dataset.

IMPORTANT: The headers should not be included in your selection.

4. Once you have selected the entire dataset, click OK.

5. Another prompt will appear asking you to enter ‘every which row you would like to be deleted’.

In my example above, I typed ‘3’ because I wanted to delete every third row in my dataset.

6. After typing your row number, click OK.

7. And that’s it! Every nth row in your dataset should now be deleted.

To reuse this code next time, remember to save the workbook in .xlsm or .xlsb format.

Also, for easier use, you may want to add a new button in your worksheet and link the button to the code that we have added. This way, you don’t need to open the code each time you want to run it.

Conclusion

As you can see, deleting every other row (or every nth row) is quite simple. You only need to add the correct formula for Excel to determine which row to delete. After that, you only need to filter the records based on these “row markers”, delete the visible rows, and you’re all set!

Related Tutorials:

Insert the Same Text in Every Other Row in Excel

How to Alternate Row Colors in Excel

How to Remove Blank Rows in Excel

How to Copy Row Height in Excel

Adding alternate row colors in your data is a great way to enhance its readability. It makes it easier for your intended audience to find a record and see its related information. Plus, it’s so nice to look at!

When you look at the image below, I’m pretty sure your eyes are immediately drawn to the table with alternate row colors.

Adding alternate colors (also known as color banding) can immediately amp up your data and make it more professional-looking.

Before you proceed, you might want to freeze the top rows first so you get to see the header names as you scroll down the page.

1. Alternate Row Colors in Excel by Formatting the Cells as a Table

This method is probably my favorite as it offers preset formats that you can readily choose from and only involves a few steps!

1. Select your entire dataset.

2. From the Home menu, go to the Styles section and click on the Format as Table button.

A list of preformatted table designs will appear. Select the design of your liking – one with alternate row colors.

3. Once selected, the Create Table prompt will appear. Click OK to continue.

NOTE: If you don’t want to convert your cells into a table, don’t worry. We can easily convert them back to regular cells later on.

4. And that’s it! You already have your data in alternate row colors. Easy-peasy, right?

What’s great about having your data in table format is that when you add new rows at the bottom or delete rows in between, Excel automatically adds or adjusts the alternate colors.

QUICK TIP:

If you want to add the alternate colors to the columns, instead of rows, you only need to:

1. Select a cell within the table.
2. Go to the Table Design menu.
3. From the Table Style Options, uncheck the Banded Rows checkbox and tick the Banded Columns checkbox.

You’ll then have alternate colors for your columns (as shown below).

To convert your table back to the normal range:

1. Right-click on any cell within the table.

2. From the dropdown list, select Table >> Convert to Range.

3. A prompt will appear to confirm the process. Click Yes to continue.

4. That’s it! Your cells are now converted back to a normal range with the table design still intact.

Note that if you add new rows to the range, the alternate row colors are no longer automatically added. Use the Format Painter to copy the cell format from the existing rows to the new ones.

2. Alternate Row Colors in Excel Without a Table by Adding Conditional Formatting

This next method may involve more steps, but it allows you to pick the alternate colors to apply upfront.

Also, this gives you the option to specify how many rows before an alternate color is added. (More about this in the latter part of the article).

1. Select the cells you want to apply the alternate row colors to.

If you like, you can select the entire column where your data are so that alternate colors are still added to the bottom rows even if they have no data yet.

Or you can opt to hide the rows containing blank cells for a cleaner look.

2. From the Home menu, go to the Styles section and click on the Conditional Formatting button.

From the list of options, select New Rule…

3. The New Formatting Rule form will appear.

From the list of Rule Types, select the “Use a formula to determine which cells to format” option.

Once selected, type either of these formulas in the formula textbox:

• =MOD(ROW(),2)=0
• =ISEVEN(ROW())

These two formulas are only checking if the current row is even.

If this condition is met, Excel will apply the cell format that you will specify next.

Click on the Format… button.

The Format Cells menu will appear.

Go to the Fill tab and select the color you would like to apply to the rows in even numbers.

The selected color will appear in the Sample section at the bottom. If you’re happy with it, click OK.

Click OK again to the New Formatting Rule menu.

That’s it! There’s now a cell color to your rows in even numbers.

Pretty cool, right? You can stop here.

But if you would like to add color to the other set of rows, that is, the odd number rows, redo the same steps as above, except that in the formula textbox, type either of the following formulas:

• =MOD(ROW(),2)>0
• =ISODD(ROW())

These formulas will check if the current row is an odd number.

2.1. Add alternate color to every nth number of rows

There are certain types of datasets where two or more data rows are referring to just one record.

For these kinds of datasets, it would be ideal to highlight multiple rows at a time so that it would be easier to identify rows that are related to each other.

In my example below, there are 3 members for each group – which is equivalent to 3 rows per record.

Note that the number of rows per record is fixed.

Apply the same steps in adding the conditional formatting and use this formula:

=MOD(ROW()-[starting row],[row count]*2)+1<=[row count]

• [starting row]
  • Refers to the first data row (or the row that comes after the header).
  •  If your header is in row 1, your [starting row] would be 2.

• [row count]
  • Refers to the number of rows to be grouped.
  • In my example above, [row count] would be 3.

With my example, my formula would be:

=MOD(ROW()-2, 3*2)+1<=3

And that’s it! You will have alternate colors for every nth number of rows.

You can manually add the row color for the header to have it stand out, like what I did in the example above.

But for tables with calculations, you might consider deleting every other row if you find that the data in them isn’t needed.

2.2. Add alternate row colors based on a specific field

If the number of rows per record varies, don’t worry. There is still a way for you to have alternate row colors.

Before adding the conditional formatting, we need to identify first the column that serves as the basis for saying that a group of rows is related to each other.

We will refer to this column as our “Parent Field”.

In my example below, my “Parent Field” is column A (Group Name).

Sort the data by this column so that related rows are next to each other.

Next, go to the last column in your dataset, and add a new column.

In this new column, we will add a formula to determine if the current row belongs to the same group as the previous row.

I suggest adding it as the last column so you can easily hide it later.

In my case, I added it in column F (as shown below).

The formula to use is:

=MOD(IF(ROW()=2,0,IF($A2=$A1,F1,F1+1)),2)

Add this formula to the same row as your first data row (e.g., row 2).

We are going to modify this formula to match your dataset.

1. Change the highlighted letters with the column letter where your “Parent Field” is located.

2. Change the highlighted letters with the column letter where you have placed this formula.

3. Change the yellow highlighted numbers with the row number containing your first data row.

Change the green highlighted numbers with the row number minus 1.

Copy this formula to the rest of the cells in that column.

This formula marks rows with 1’s and 0’s to identify rows within the same group.

Now, it’s time to add conditional formatting.

Follow the same steps in adding the conditional formatting but use this formula: =$F1=1

Change the letter “F” with the column where you have added the formula. 

And that’s it! You should now have alternate row colors added.

Hide the column containing the formulas by right-clicking on the entire column and selecting Hide.

2.3. Add alternate colors to columns

If you would instead like to apply alternate colors to your columns, use either of the following formulas:

Edit the conditional formatting

If you want to choose a different color for the rows (or columns):

1. From the Home menu, click on the Conditional Formatting button and select Manage Rules…

2. The Conditional Formatting Rules Manager will appear.

In the Show formatting rules dropdown list, select “This Worksheet”.

This will ensure that all the conditional formatting within the worksheet appears in the list of rules.

Next, select the formatting rule that you would like to edit. Look for the formula that you have previously added. Once selected, click the Edit Rule button.

You will then see the same menu when you added the conditional formatting. Click on the Format button to select a different cell color.

Remove the Conditional Formatting

If you changed your mind and would like to remove the alternate colors altogether:

1. Select the cells with alternate rows or columns.
2. Go to the Homemenu and click on theConditional Formatting button.
3. Select Clear Rules >> Clear Rules from Selected Cells.

Conclusion

Adding alternate row colors is a simple yet effective way to give your data a more professional appeal. I hope the suggestions above have helped you achieve the format that you are looking for.

Before anything, what is a square root symbol? Let me quickly take you back to high school mathematics. Here’s what the square root symbol looks like.

We often need this symbol in your spreadsheets for a variety of tasks. Some of the time to compose a formula, write down an expression, and so much more.

How do you add that in Excel? There are multiple ways how you can do that. Let me take you through all of them in the article below.

Also if you wonder how to add other symbols, you can check our other articles:

Copy/Paste the Square Root Symbol in Excel

Out of many ways to add the square root symbol in Excel, the first and the easiest is to copy it from below simply.

1. Select this square root symbol √.
2. Right-click on it > Click on Copy.
3. Go to the relevant cell in your Excel sheet.
4. Double-click on the cell to activate it.

5. Right-click to launch the Paste Special Options.
6. Click on Paste as shown below.

And there you go.

Ta-da! The symbol is pasted to your Excel sheet. Make as many copies of the same as desired, and you’re all set.

However, this method is only advisable if you only need to add one (or a very few) square root symbols to your sheet. If you need them more than that, try the other methods discussed below.

Insert Square Root Symbol Using Symbol Option

The square root is recognized as a symbol by Excel and is included in the symbols library of Excel. Let’s find it out together.

1. Activate the cell where you want the square root symbol inserted.
2. Go to the Insert Tab > Symbols as shown below.

This will launch the Symbol dialog box as follows.

3. Set the Font to normal text (as shown below).
4. Set the Subset to Mathematical Operators (as shown below).

From the signs that appear, select the “Square Root” symbol.

5. Click on Insert.

And there, you have it added to your sheet.

The next time you need to add it, it will be saved in the recently used symbols, so the whole process gets quicker.

Pretty cool, right?

Insert the Square Root Symbol Using the UNICHAR Function

Another method you can use to add the square root symbol to an Excel sheet is by using the UNICHAR function. How? Let’s see that below.

TIP!

The UNICHAR function works with ASCII codes (which are numerical values). For each of these codes, the UNICHAR function returns a unique code. 

Inserting the square root symbol using the UNICHAR function is simple if you know the required ASCII code. And that code number is 8730. Let’s now see how to use it to insert the square root symbol in Excel.

1. Activate the cell where you want the square root symbol inserted.
2. Begin writing the UNICHAR function as follows:

= UNICHAR (

3. Write in the code 8730 as follows:

= UNICHAR (8730)

4. Hit Enter to see the results below.

See that?

However, there’s one problem with the UNICHAR function. It does add the square root symbol, but if you want to use it with other characters, you will have to concatenate it with them.

Like below.

To help this, you can copy and paste the square root symbol as a simple value. Here’s how to do it.

5. Copy the formula.
6. Go to another cell and right-click to launch the Paste Special options.
7. Paste it as Values.

Check out the formula bar to see that the UNICHAR formula has gone away. What’s left behind is a simple square root symbol.

Now you can easily use it around your spreadsheet without worrying about the UNICHAR formula running behind it. How cool is that?

Insert the Square Root Symbol Using a Keyboard Shortcut

It’s always easier to simply use your keyboard to type any character, and the square root symbol is no exception.

Although you will not find the square root symbol on your keyboard, you can still type it using the following keyboard shortcut.

What is that? The Alt key + 2 + 5 + 1.

All of these 4 keys are to be typed in succession, i.e., one after the other.

TIP!

Here’s something very important that you must know about using this keyboard shortcut. This keyboard shortcut will only work with a numeric keypad.

So if your keyboard (or laptop) doesn't have a numeric keypad, this keyboard shortcut would not work with the other number keys on your keyboard. In this case, you'd have to use any of the methods discussed above.

Let us now quickly go through the steps to be followed to insert the square root symbol using this shortcut.

1. Activate the cell where you want the square root symbol inserted.
2. Press the following keys on your keyboard in sequential order.

Alt key + 2 + 5 + 1

And that’s it. You have it inserted in the selected cell.

Change the Format of Values to Add the Square Root Symbol

This method comes last because it works differently than the other methods on this list.

Under this format, we only change the format of the values in our spreadsheet to prefix them with a square root symbol. So, in essence, you don’t add the character to your sheet. But the values in your sheet are formatted as such to exhibit it.

Let’s change the format of the values in the image below to see how we can do it.

Here are the steps to be followed.

1. Select all the cells where the square root symbol is to be added.

2. Go to the Home tab > Number > the small arrow button to launch the Format Cells dialog box.

3. From the Format Cells dialog box, go to the Number Tab.
4. Go to “Custom” from the pane on the left.
5. Add the square root symbol ( √ ) before the category General.

TIP!

You can simply copy it from here ( √ ) and paste it there. Or, if you have a numerical keypad, use the Alt key + 251 shortcuts to add one instantly.

6. Click on Okay. A square root symbol will be added before every number on the list.

However, these numbers are only formatted to include a square root symbol. A square root symbol doesn’t actually exist in the cell.

This is evident from the Formula bar.

Hence, this method is only suitable if you want the square root symbol for presentation purposes.

Conclusion

In this guide, we explored different ways of inserting the Square root symbol in Excel. We saw the technique of using UNICHAR function, we saw how to use formatting to insert the symbol, and we also tried some cool keyboard shortcuts.

You can use any of the above explained methods to insert Square root in Excel. It might take you some practice before you master the art, but once you do, it will seem easier than falling off a log.

Our favorite method is using the UNICHAR function to add the symbol – have you found yours? If not, then what are you waiting for? Find the best suited method for your purpose now!