Stopping Email Addresses from Activating

David has hundreds of workbooks, each for a project he is working on now or has worked on. They contain, among other information, email addresses. Occasionally Excel will convert these addresses to hyperlinks so that when Dave clicks on the cell it opens an email. Dave is wondering how he can stop Excel from doing this.

It entirely depends on what you actually want to stop. If you simply don't want to launch an email for that address, notice what happens if you hover the mouse pointer over the address—you should see a ToolTip that indicates you can select the cell if you simply hold down the mouse button. In other words, an email is created if you click once, but if you click and hold down the mouse button, then the cell is selected and no email is created.

If what you want to stop, however, is Excel converting email addresses to active links, then all it takes is a simple configuration change:

1. Display the Excel Options dialog box. (In Excel 2007 click the Office button and then click Excel Options. In Excel 2010 or a later version, display the File tab of the ribbon and then click Options.)
2. Click Proofing at the left side of the dialog box.
3. Click AutoCorrect Options. Excel displays the AutoCorrect dialog box.
4. Make sure the AutoFormat As You Type tab is selected. (See Figure 1.)



Figure 1. The AutoFormat As You Type tab of the AutoCorrect dialog box.

5. Clear the Internet and Network Paths with Hyperlinks check box.
6. Click OK to close the AutoCorrect dialog box.
7. Click OK to close the Excel Options dialog box.

Now Excel will not assume you want an active link. This change affects other types of links, as well, such as those to websites. It will not affect, however, any existing links within a workbook. The easiest way to get rid of those active links is to press Ctrl+A to select everything in the worksheet. Then, right-click on any selected cell and choose Remove Hyperlinks from the resulting Context menu.

If you have hundreds of workbooks, as David has, then you can use a macro to remove all hyperlinks from all worksheets in the currently active workbook:

Sub RemoveAllHyperlinks()
    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        ws.Hyperlinks.Delete
    Next
End Sub

Whenever you open a workbook, just run the macro and all the hyperlinks are removed.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13038) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.