This guide will show you how to batch rename multiple hyperlinks at once in Microsoft Excel.
Some hyperlinks can be very long and unreadable when inserted into an Excel spreadsheet. The good thing about Excel is that we can assign friendly names to these links to make them more distinct and easier to understand. However, it could be tedious to manually set friendly names for each link when dealing with multiple links.
Suppose you are creating a training manual for new hires. It is helpful to have a master document that contains links to different training documents from other websites. With the
HYPERLINK function, this task will become much more manageable.
Another example is when you are trying to create a review sheet for a subject in school. You would need to list down all your references from research. The
HYPERLINK function can be very helpful as well. While this focuses more on Google Sheets, it works the same way for Microsoft Excel.
Now that we know when to use the
HYPERLINK function in Microsoft Excel let’s dive into how to use it for multiple links and work on an actual sample spreadsheet.
A Real-Life Example of Batch Renaming Multiple Hyperlinks At Once in Excel
Let’s look at a real-life example of batch renaming multiple hyperlinks at once in Excel.
In this example, imagine you are an employee that was assigned to create a training manual for new hires. However, all existing guides and other training materials can be found on different websites of your organization. What you can do is create a spreadsheet containing a list of websites that can serve as a guide to new employees.
This document with multiple links can look overwhelming for new employees, especially when links are very long. It is a lot more readable and easier to navigate through the spreadsheet if we use friendly names. We can also display the same name for all links as shown in the screenshots below.
It is also possible to display the same name for all links, like the screenshot below:
As you now understand how hyperlinks work, we can move to renaming multiple hyperlinks at once together!
You can make a copy of the spreadsheet above using the link attached below.
How to Batch Rename Multiple Hyperlinks at Once in Excel
This section will help you in batch renaming multiple hyperlinks in Microsoft Excel. There are two ways we can rename multiple hyperlinks at once. One method uses the
HYPERLINK function, and the other is by the Find and Replace dialog. This step-by-step guide below will demonstrate both methods using the example mentioned earlier.
Method 1: By Using HYPERLINK Function
If you wish to change the display text of the links based on values from another column, this method is very useful. Follow these simple steps below:
- Firstly, create two columns containing the display name and link for each display name.
- Next, create a new column where you would like to display the names.
- Select a cell under the new column. In this example, select C2. Next, simply type the equal sign ‘=‘ followed by the word ‘HYPERLINK‘ and left parentheses ‘( ‘. An auto-suggest box appears, displaying a short definition of the
- The first parameter in the formula will be the links under column B, and the friendly name will come from column A. Following our example, select B2, then type comma ‘= ‘. Next, choose A2, then type right parentheses ‘) ‘.
- Now, hit the ENTER key. You’ll now notice that the display name will now be displayed instead of the link.
- Instead of doing this repeatedly, you can do this for the other links by using the fill handles. Go back to C2, then hover over the small dot on the lower right corner of the active cell.
- Finally, drag the cursor down to automatically input the formula and populate the display names for the other cells.
Method 2: By Using Find and Replace Dialog
Another method you can use is the Find and Replace dialog. This is helpful if you want to display the same name for all links. You can do this by following the steps below:
- First, select all the hyperlinks that you want to change the display text. In this example, select cells B2–B6.
- Now, press Ctrl+H to display the Find and Replace dialog box. You can also navigate through Home > Editing > Find & Select > Replace.
- Next, under Find What, type an asterisk ‘*‘. And then under Replace with field, input ‘Click Here‘.
- Now, click the Replace All button. As a result, all links will now be displayed as Click Here. But don’t worry, as the links will remain unchanged.
That’s pretty much everything you need to know when working with the GROWTH function in Google Sheets. This guide shows how simple it is to provide data and predict future values, which can be essential to your business, research, or any other field where an exponential trend is being used.
That’s all you need to remember in batch renaming multiple hyperlinks in Microsoft Excel. This guide shows how simple it is to create distinct names and work on multiple links.
This feature is just one of many which help you make your Excel spreadsheets more comprehensible and friendlier for other users. With so many other Excel functions and features, you can surely find ways to improve your Excel experience and for others as well.
Are you interested in learning more about how Google Sheets can help you make more powerful spreadsheets? Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.