Shulou(Shulou.com)11/24 Report--

The original title: "the only function in Excel that can not be replaced, 90% of people do not know!" "

Hello, Hi~, I am Tian Tian, who loves to learn from Excel.

It's time for [0 basic learning Excel function] to start the class on time every week.

Today we learn about a very useful query function-the HYPERLINK function.

1. The main function of the cognitive function is to find the location of an object in the target area.

To make good use of it, you only need to remember two tips:

Where is the link?

What's the name of the link?

Let's take a look at the syntax of the MATCH function:

= HYPERLINK ("File path", "display name")

2, the basic usage wants to convert a very long URL into more refined Chinese prompts in batches, and let these words achieve the effect of clicking to open a web page, how should it be realized?

❶ enters the equal sign in the cell, then enters = HYPERLINK, and uses the TAB key to quickly insert the function.

❷ recalls 2 sentences and completes the corresponding operation according to the formula.

Where is the link-just select the link [B1] cell on the left, and don't forget to enter an English comma "," as the interval.

What's the name of the link-directly select the video source [C1] cell on the left. Then put the anti-parenthesis ")" and press enter. Fill the formula down, the hyperlink is created, and click to jump to the video.

The complete formula is as follows:

Advanced usage but it should be noted that although we create a new text link through the HYPERLINK function, the original URL column and source column in the table cannot be deleted, otherwise the link will fail and the formula will report an error.

Of course, it doesn't bother me! There is also a way to solve it!

This time, we use handwritten input to edit the formula.

❶ enters a single quotation mark ["] in the English state in the cell, indicating that the next content is all text and will not trigger the automatic calculation of the function. Enter = HYPERLINK and enclose the parenthesis" (").

❷ recalls 2 sentences and completes the corresponding operation according to the formula.

Where is the link-enter the first URL of the double quotation mark ["], complex [B1] cell, paste it between the double quotation marks, and put a comma as the first parameter.

What is the name of the link-enter double quotation marks, enter "Click Open" between the double quotation marks as the name of the link, as the second parameter of the formula, and enclose the anti-parentheses when you are done.

❸ in the cell below, use the [Ctrl+E] key combination to fill it intelligently, and you can fill all the function formulas completely at once.

However, at this time, the function will not be calculated automatically. Because these formulas are all in textual form.

❹ so we use the sorting function to batch refresh the column data format. Select the formula column, click "separate functions" under "data Tab", click finish, and the format is refreshed. In this way, our text link is created.

The complete formula is as follows:

'= HYPERLINK ("URL", "Click Open") after the link is created in this way, the original title link and source column can be deleted directly. If we click on any text link again, the URL can be opened quickly.

Is it super simple? go to practice and try it.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Tian Tian

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.