How to make a link in excel. Creating links in Excel to another sheet, to another book, hyperlink

Creating links is a procedure that absolutely every Excel spreadsheet user faces. Links are used to implement redirects to specific web pages, as well as to access any external sources or documents. In the article, we will take a closer look at the process of creating links and find out what manipulations can be carried out with them.

Varieties of links

There are 2 main types of links:

1. References used in various calculation formulas, as well as special functions.
2. Links used to redirect to specific objects. They are called hyperlinks.

All links (links) are additionally divided into 2 types.

• external type. Used to redirect to an element located in another document. For example, on another sign or a web page.
• Internal type. Used to redirect to an object located in the same workbook. By default, they are used in the form of operator values ​​or auxiliary elements of the formula. Used to specify specific objects within a document. These links can lead both to the objects of the same sheet and to the elements of other worksheets of the same document.

There are many variations of link building. The method must be selected, taking into account what kind of reference is needed in the working document. Let’s analyze each method in more detail.

How to create links on the same sheet

The simplest link is to specify cell addresses in the following form: =B2.

The “=” symbol is the main part of the link. After writing this character in the line for entering formulas, the spreadsheet will begin to perceive this value as a reference. It is very important to correctly enter the address of the cell so that the program correctly processes the information. In the considered example, the value “=B2” means that the value from cell B3 will be sent to field D2, in which we entered the link.

It is worth noting! If we edit the value in B2, then it will immediately change in cell D3.

All this allows you to perform a variety of arithmetic operations in a spreadsheet processor. For example, let’s write the following formula in field D3: =A5+B2. After entering this formula, press “Enter”. As a result, we get the result of adding cells B2 and A5.

Other arithmetic operations can be performed in a similar way. There are 2 main link styles in the spreadsheet:

1. Standard view – A1.
2. Format R1C The first indicator indicates the line number, and the 2nd one indicates the column number.

The walkthrough for changing the coordinate style is as follows:

1. We move to the “File” section.

2. Select the “Options” element located in the lower left part of the window.

3. A window with options appears on the screen. We move to the subsection called “Formulas”. We find “Working with formulas” and put a mark next to the element “Reference style R1C1”. After carrying out all the manipulations, click on “OK”.

There are 2 types of links:

• Absolute refer to the location of a specific element, regardless of the element with the given content.
• Relative refers to the location of elements relative to the last cell with the written expression.

Pay attention! In absolute references, the dollar sign “$” is assigned before the column name and line number. For example, $B$3.

By default, all added links are considered relative. Consider an example of manipulating relative links. Walkthrough:

1. We select a cell and enter a link to another cell in it. For example, let’s write: =V1.

2. After entering the expression, press “Enter” to display the final result.

3. Move the cursor to the lower right corner of the cell. The pointer will take the form of a small dark plus sign. Hold LMB and drag the expression down.

4. The formula has been copied to the bottom cells.

5. We notice that in the lower cells the entered link has changed by one position with a shift of one step. This result is due to the use of a relative reference.

Now let’s look at an example of manipulating absolute references. Walkthrough:

1. Using the dollar sign “$” we fix the cell address before the column name and line number.

2. We stretch, as in the above example, the formula down. We notice that the cells located below have the same indicators as in the first cell. Absolute reference fixed the cell values, and now they do not change when the formula is shifted.

In addition, in a spreadsheet, you can implement a link to a range of cells. First, the address of the top leftmost cell is written, and then the bottom right cell. A colon “:” is placed between the coordinates. For example, in the picture below, the range A1:C6 is selected. The reference to this range looks like: =A1:C6.

Create a link to another sheet

Now let’s look at how to create links to other sheets. Here, in addition to the cell coordinate, the address of a specific worksheet is additionally indicated. In other words, after the “=” symbol, the name of the worksheet is entered, then an exclamation mark is written, and the address of the required object is added at the end. For example, the link to cell C5, located on the worksheet called “Sheet2”, looks like this: =Sheet2!C5.

Walkthrough:

1. Move to the desired cell, enter the symbol “=”. Click LMB on the name of the sheet, which is located at the bottom of the spreadsheet interface.

2. We have moved to the 2nd sheet of the document. By clicking on the left mouse button, we select the cell that we want to assign to the formula.

3. After carrying out all the manipulations, press “Enter”. We found ourselves on the original worksheet, in which the final indicator has already been displayed.

External link to another book

Consider how to implement an external link to another book. For example, we need to implement the creation of a link to cell B5, located on the worksheet of the open book “Links.xlsx”.

Walkthrough:

1. Select the cell where you want to add the formula. Enter the symbol “=”.

2. We move to the open book in which the cell is located, the link to which we want to add. Click on the required sheet, and then on the desired cell.

3. After carrying out all the manipulations, press “Enter”. We ended up on the original worksheet, in which the final result has already been displayed.

Link to a file on the server

If the document is located, for example, in a shared folder of a corporate server, then it can be referenced as follows:

Referencing a named range

The spreadsheet allows you to create a reference to a named range, implemented through the “Name Manager”. To do this, you just need to enter the name of the range in the link itself:

To specify a link to a named range in an external document, you need to specify its name, as well as specify the path:

Link to a smart table or its elements

Using the HYPERLINK operator, you can link to any fragment of a “smart” table or to the entire table. It looks like this:

Using the INDIRECT operator

To implement various tasks, you can use the special INDIRECT function. General view of the operator: =INDIRECT(Cell_reference,A1). Let’s analyze the operator in more detail using a specific example. Walkthrough:

1. We select the required cell, and then click on the “Insert Function” element, located next to the line for entering formulas.

2. A window called “Insert Function” was displayed on the screen. Select the “References and Arrays” category.

3. Click on the INDIRECT element. After carrying out all the manipulations, click on “OK”.

4. The display shows a window for entering the operator’s arguments. In the line “Link_to_cell” enter the coordinate of the cell to which we want to refer. Line “A1” is left blank. After carrying out all the manipulations, click on the “OK” button.

5. Ready! The cell displays the result we need.

What is a hyperlink

A hyperlink is a fragment of a document that refers to an element in the same document or to another object located on a hard drive or on a computer network. Let’s take a closer look at the process of creating hyperlinks.

Create hyperlinks

Hyperlinks allow not only to “pull out” information from cells, but also to navigate to the referenced element. Step by step guide to create a hyperlink:

1. Initially, you need to get into a special window that allows you to create a hyperlink. There are many options for implementing this action. First – right-click on the required cell and select the “Link …” element in the context menu. The second – select the desired cell, move to the “Insert” section and select the “Link” element. Third – use the key combination “CTRL + K”.

2. A window appears on the screen that allows you to set up a hyperlink. There is a choice of several objects here. Let’s take a closer look at each option.

How to create a hyperlink in Excel to another document

Walkthrough:

1. We open a window to create a hyperlink.
2. In the “Link” line, select the “File, web page” element.
3. In the line “Search in” we select the folder in which the file is located, to which we plan to make a link.
4. In the line “Text” we enter text information that will be shown instead of a link.
5. After carrying out all the manipulations, click on “OK”.

How to create a hyperlink in Excel to a web page

Walkthrough:

1. We open a window to create a hyperlink.
2. In the “Link” line, select the “File, web page” element.
3. Click on the “Internet” button.
4. In the line “Address” we drive in the address of the Internet page.
5. In the line “Text” we enter text information that will be shown instead of a link.
6. After carrying out all the manipulations, click on “OK”.

How to create a hyperlink in Excel to a specific area in the current document

Walkthrough:

1. We open a window to create a hyperlink.
2. In the “Link” line, select the “File, web page” element.
3. Click on “Bookmark …” and select the worksheet to create a link.
4. After carrying out all the manipulations, click on “OK”.

How to create a hyperlink in Excel to a new workbook

Walkthrough:

1. We open a window to create a hyperlink.
2. In the “Link” line, select the “New Document” element.
3. In the line “Text” we enter text information that will be shown instead of a link.
4. In the line “Name of the new document” enter the name of the new spreadsheet document.
5. In the “Path” line, specify the location for saving the new document.
6. In the line “When to make edits to a new document”, select the most convenient option for yourself.
7. After carrying out all the manipulations, click on “OK”.

How to Create a Hyperlink in Excel to Create an Email

Walkthrough:

1. We open a window to create a hyperlink.
2. In the “Connect” line, select the “Email” element.
3. In the line “Text” we enter text information that will be shown instead of a link.
4. In the line “Email address. mail” specify the email address of the recipient.
5. Enter the name of the email in the subject line
6. After carrying out all the manipulations, click on “OK”.

How to edit a hyperlink in Excel

It often happens that the created hyperlink needs to be edited. It is very easy to do this. Walkthrough:

1. We find a cell with a ready hyperlink.
2. We click on it RMB. The context menu opens, in which we select the item “Change hyperlink …”.
3. In the window that appears, we make all the necessary adjustments.

How to format a hyperlink in Excel

By default, all links in a spreadsheet are displayed as blue underlined text. The format can be changed. Walkthrough:

1. We move to the “Home” and select the element “Cell Styles”.

2. Click on the inscription “Hyperlink” RMB and click on the element “Edit”.
3. In the window that appears, click on the “Format” button.

4. You can change the formatting in the Font and Shading sections.

How to remove a hyperlink in Excel

Step by step guide to remove hyperlink:

1. Right-click on the cell where it is located.
2. In the context menu that opens, select the “Delete hyperlink” item. Ready!

Using non-standard characters

There are cases where the HYPERLINK operator can be combined with the SYMBOL non-standard character output function. The procedure implements the replacement of the plain text of the link with some non-standard character.

Conclusion

We found out that in the Excel spreadsheet there are a huge number of methods that allow you to create a link. Additionally, we learned how to create a hyperlink leading to different elements. It should be noted that depending on the selected type of link, the procedure for implementing the required link changes.