Creating links in Excel

Each Microsoft Excel user probably creates links quite often, because formulas are constantly used in the program, and links are necessary to work with them. Also, links (this is how “links” are called in another way) are used to navigate to certain pages on the Internet, access to other documents or external sources. So, let’s see how exactly you can create links in Excel.

Varieties of links

Links fall into two main categories:

• The first includes such references that are used in various functions, as well as in formulas that allow calculations.
• To the second – those links that allow you to navigate to the desired object. They are also called differently hyperlinks.

Also, all links are divided into internal and external, and they are used in different cases:

• Internal links are used to navigate within a particular book. Usually they are used as arguments to functions, or as components of formulas. They are used to specify certain elements in the document: single cells or entire ranges containing certain values ​​that are planned to be processed. These can be links leading to elements both within the same sheet and to other sheets, but within the same document.
• External links allow you to jump to objects that are outside the scope of this book. For example, a link to another Excel spreadsheet, a document in a different format, or a web page.

Accordingly, the method of creating links should be chosen based on what type of link you want to create. We’ll go over all the options in detail below.

How to create links on the same sheet

To get started, let’s see how you can create links for functions, formulas, and other calculation-related tools within the same sheet, since these are the most commonly used links.

The simplest link is just specifying the address of the cell in the form: =B2.

In this case, an indispensable component of the link is the “equal” sign (“=”) at the very beginning. After we write it in a cell, the program will treat the entire expression as a link. The main thing here is to correctly specify the cell coordinates: the letter designation of the column and the serial number of the row.

In our case, the expression= B2” means that in cell D3 (any other cell can be in its place), where we wrote this formula, the value from the cell with address B2 will be “pulled up”.

Since cell D3 refers to B2, if we change the value in B2, the contents of D3 will automatically change as well.

Thanks to such simple links, we have the ability to perform various arithmetic operations. Let’s, for example, enter the expression in cell D3: =A5+B2.

After we have typed this expression, we press the Enter key to get the result of the calculation, which involves cells A5 and B2.

In the same way, you can divide, multiply and subtract numbers, as well as perform other arithmetic operations.

To make using formulas even more convenient, there is no need to manually enter the coordinates of the required cell each time. We just put the “equal” sign at the beginning of the expression, after which we simply left-click on those cells (select cell ranges, if necessary) that should participate in the calculations.

We draw your attention to the fact that Excel has two styles of links.:

• in a more familiar form A1
• in the R1C1 format, when the coordinates are represented by letters and numbers, but only by numbers. Moreover, the first indicates the row number, and the second indicates the column number.

To change the address style, do the following:

1. Go to the “File” menu.
2. In the side list on the left, click on the “Settings” item at the bottom of the window.
3. A window with program parameters will open, where we select the “Formulas” section. Here, in the “Working with formulas” block, we can check the box next to the “R1C1 link style” option if we need just such a style, or uncheck it if we want to return to a more familiar look. After we make a selection, click OK to save the settings.

Despite the difference in display, both styles in the program are absolutely equivalent, although initially the program chose links in A1 format.

Let’s now focus on the option links R1C1. This is how our example with links of this type looks like.

As you can see, the cell addresses have changed and are displayed as a module, which may not be entirely clear. In the same form, they will be shown if the cell coordinates are entered in the formula not manually, but by clicking the left mouse button.

But if you type the expression manually, everything will take on a more understandable form.

The thing is that in the first case, the relative type of links was displayed (=R[2]C[-3]+R[-1]C[-2]), while with manual typing, links are presented in absolute terms (=R5C1+R2C2).

The difference between type data is that absolute references refer to the location of a particular object regardless of the cell with the given expression, while relative references refer to the location of objects relative to the final cell in which our expression is written.

In the usual way of displaying links, relative links look like just a letter with a number (A1), while in absolute links, a “$” sign ($A$1) is added before the column name and row number.

By default, all created links in Excel are relative. This is done so that when copying / stretching formulas to other cells, the values ​​​​are adjusted in accordance with the movements performed.

Let’s see how this works in cell B1 as an example.

1. We select, say, cell D1 and write in it an expression referring to cell B1. It looks like this: =B1.
2. After the formula is typed, press the Enter key to get the result.
3. Now we move the mouse pointer to the lower right corner of the cell, as soon as it changes its appearance to a cross, holding down the left mouse button, we stretch the formula down.
4. Done, the formula is copied to the rest of the cells.
5. Now if we place the cursor on cell D2, we can see that it refers to B2 and not B1. This means that the link has been changed according to the offset (+1). Thus, for example, cell D3 refers to B3 (+2), and so on.

Of course, this function is extremely useful when, for example, you need to perform calculations using the same formula for a large number of rows.

But in certain situations it is required to apply the given formula without any changes. In this case, absolute references will help us.

1. You can fix the cell address using the “$” symbol before the column name and row number.
2. Now, if we expand the formula to the bottom rows again, we can easily notice that the values ​​in the bottom cells are the same as in the first cell. The thing is, because of the reference in the form of the absolute address of the cell, when copying the formula, no adjustments were made to it in accordance with the offset.

But that’s not all. In addition to relative and absolute references, there are also mixed links. In such links, it is possible to “freeze” the column coordinates, and in this case, the “$” symbol is placed only before the column name. For example, =$B1.

Or we can fix only a specific line, and then we need to put a “$” sign in front of the ordinal number of the corresponding line. For example, =B$1.

We can type the “$” symbol manually by finding it on the keyboard. It is printed with the English layout using the combination Shif + 4.

However, you can use another method using the F4 function key.

• being in the formula, simply select the address of the desired cell, then press “F4”, after which the “$” sign will immediately appear at both cell coordinates.
• if you press “F4” again, the “$” symbol will remain only before the line address.
• pressing the “F4” key again, we will change the appearance of the mixed reference, leaving the “$” sign only in front of the column coordinates.
• if we want to remove the mixed link, press the “F4” key again.

Reference to a range of cells

In addition to referring to a specific cell in Excel, you can refer to a range of cells, the coordinates of which include the address of the upper left cell and the lower right cell, and between them the “:” sign. For example, the coordinates A1:С6 corresponds to the following range in the picture below:

Thus, the reference to this range is written as follows: =A1:С6.

Create a link to another sheet

In the examples discussed above, we considered links only within the same sheet. Now let’s figure out how to create links to other sheets.

In general, the algorithm is similar to what we have already described above for operations performed on the same sheet. However, now, in addition to the address of the cell itself (or a range of cells), you also need to specify the address of a specific sheet. To do this, after the “=” sign, write its name, then put an exclamation mark (“!”), After which we already indicate the address of the required element.

For example, a link to cell C5 on Sheet 2 looks like this: =Лист2!C5.

As always, you can type this value yourself, or use a more convenient method.

1. We go to the desired cell, write the “=” sign, then click on the name of the required sheet at the bottom of the program window.
2. We will find ourselves on the second sheet, where we also select the cell that we want to add to our expression with a mouse click.
3. Press the Enter key, which will return us to the original sheet with the finished result.

External link to another book

So, let’s move on to external links and consider creating a link to another book. It is important to understand here that the mechanism of work of Excel tools and functions with other documents is different. Some links work regardless of whether the document they link to is open or not. For others, it is important that the file must be launched.

Accordingly, the types of references to other books are different. When we use them only in conjunction with open documents, then it is possible, as in the case of a link to another sheet within the same book, to specify only the title of the book.

But in cases where we want to link to documents whether they are running or not, then we will have to specify their location on the computer.

Ceteris paribus, it is most preferable to use the second option, since it implies more freedom of action.

So, let’s say we need to create a link to cell B5, which is on a sheet in the open workbook “Links.xlsx”. In this case, we need to write an expression that looks like: =[Ссылки.xlsx]Лист3!B5.

A link to a closed file looks a little different: ='С:Папка1[Ссылки.xlsx]Лист3'!B5.

Here you can also, instead of manually entering addresses, switch to the necessary elements and add them to the expression by clicking on them with the left mouse button.

1. Go to the cell where we plan to add the expression. We put the sign “=”.
2. We switch to the open workbook that contains the cell we want to refer to. We click on the desired sheet and then on the required cell.
3. After we press Enter, we will return to the original workbook with the finished result for the given expression.
4. In case of uselessness, the book to which we referred can be closed. The link will change its appearance, and the path to the document will be added to it. Accordingly, in cases where a function, formula or other tool is able to work with closed documents, such link auto-replacement will be extremely useful.

Thus, as you may have noticed, instead of manually writing addresses, it is much easier to create links by clicking the left mouse button on the necessary elements, and it does not matter at all where they are located – on the same sheet, in the same document or in another book. And even if after we have created an external link to another document and then closed it, the program will automatically modify the link, saving the full path to the file in it.

Using the INDIRECT operator

In addition to using formulas in Excel, it is possible to use a special operator INDIRECT in order to create a link. The operator formula looks like this:

=ДВССЫЛ(Ссылка_на_ячейку;A1), where:

1. “Link_to_cell” is an argument that contains the address of the cell (indicated in quotation marks).
2. “A1” – defines the style of coordinates used:
   • “True” – for style A1
   • “False” – for style R1C1
   • if the value is blank, the A1 style will be applied.

Let’s now try to put the function into practice.

1. Select the desired cell and click on the “Insert Function” icon (next to the formula bar).
2. The Function Wizard window opens. We click on the current category and in the list that opens, select the line “Links and arrays”.
3. In the proposed list of operators, select INDIRECT and press the OK button.
4. We will see a window for editing the arguments of the function, after filling which we click OK.
   • put the cursor in the “Link_to_cell” area, then manually write the address of the cell we plan to refer to. You can also, instead of manually entering coordinates, simply click on the required element.
   • in the field “A1” we can write one of the two values ​​discussed above, or leave it blank.
5. The result will appear in the selected cell according to the settings we have specified.

Create hyperlinks

Unlike the links that we described above, hyperlinks are not only needed to “pull” data from other cells. In addition, they also allow navigation to the element they refer to.

1. To create a hyperlink, you need to go to a special window that allows you to create it. You can do this in different ways:
   • right-click on the cell where we plan to insert a hyperlink, and in the list that appears, click on the item “Link …”.
   • select the desired cell, switch to the “Insert” tab, where we click on the “Link” button.
   • or you can use CTRL+K to insert a hyperlink.
2. A window will appear allowing you to configure the hyperlink. The following objects for the bundle are offered for selection:
   • file or web page (default);
   • new document;
   • place in the document
   • email;
3. Let’s try to link a cell to a document. To do this, in the main part of the window, open the folder with the required file and mark it. Files with the extension “xls” (“xlsx”) or other formats can serve as a document. After selecting the desired document, click OK.
4. In situations where you need to create a link to a page on the Internet, by selecting the same item, write a link to a web page in the “Address” field, and then click OK.
5. When you need to create a hyperlink to a specific place in the current document, select the “Place in document” item. In the main area of ​​the window, mark the desired sheet and the coordinates of the cell with which you want to create a link. As usual, when done, click OK.
6. In cases where we need to create a link to a new Excel document, select the appropriate item. Then we come up with a name for the new book, select a save location, and click OK.
7. In certain situations, there is a need to associate some element of the book with e-mail. The item “E-mail” can help with this. Here in the field “Email address. mail” we write, respectively, the required e-mail, after which we click OK.
8. Once we have selected the type of hyperlink, filled in the required fields and clicked OK, an active blue link will appear in the selected cell, clicking on which will navigate to the object associated with it.

Using the HYPERLINK function

Also, the Excel program provides the ability to create a hyperlink using the function, which is called “HYPERLINK”. The operator formula looks like this:

=ГИПЕРССЫЛКА(Адрес;Имя), where:

• “Address” is, in fact, the specific address of the page on the Internet or the path to the file with which you want to associate the selected element.
• “Name” is a text value displayed in the selected element, which, when clicked, activates the hyperlink.

How to use the “HYPERLINK” operator:

1. Select the desired cell and click the “Insert Function” button.
2. In the “References and arrays” category, select the “HYPERLINK” operator and click OK.
3. Fill in the function arguments and click OK.
   • in the “Address” field, specify the path to the file or a specific address of the page on the Internet.
   • in the “Name” field, enter the text value that will be displayed in the selected cell.
4. We get in the selected cell an active link leading to the web page whose address we have specified.

Conclusion

Thus, Excel uses two types of links, depending on the purpose. Some are used in formulas and functions, with the help of others (hyperlinks) you can navigate to the desired objects in the form of other documents, web pages or e-mail. Depending on the selected link type, the algorithm of actions to create the desired link changes.