Applications 

How to link cells in Excel. Excel workbook. Communication between worksheets. Data sharing. Relative and absolute references

When performing certain tasks in Excel, sometimes you have to deal with several tables, which are also interconnected. That is, data from one table is pulled into others and when they change, the values ​​in all associated table ranges are recalculated.

Linked tables are very convenient to use for processing large amounts of information. Having all the information in one table, moreover, if it is not homogeneous, is not very convenient. It is difficult to work with such objects and search for them. The specified problem are precisely intended to eliminate related tables, information between which is distributed, but at the same time is interconnected. Related table ranges can be located not only within one sheet or one workbook, but also located in separate workbooks (files). The last two options are used most often in practice, since the purpose of this technology is precisely to get away from the accumulation of data, and piling them on one page does not fundamentally solve the problem. Let's learn how to create and work with this type of data management.

First of all, let's look at the question of how it is possible to create a relationship between different table ranges.

Method 1: Directly linking tables with a formula

The easiest way to link data is to use formulas that reference other table ranges. It's called direct binding. This method is intuitive because it performs linking in almost the same way as creating links to data in a single table array.

Let's see how, using an example, a bond can be formed by direct bonding. We have two tables on two sheets. On one table, wages are calculated using a formula by multiplying the workers’ rate by a coefficient common to all.

The second sheet contains a table range containing a list of employees with their salaries. The list of employees in both cases is presented in the same order.

It is necessary to make sure that the data on rates from the second sheet is pulled into the corresponding cells of the first.


Method 2: using a combination of INDEX - SEARCH operators

But what to do if the list of employees in the table arrays is not in the same order? In this case, as discussed earlier, one option is to manually establish a link between each of those cells that need to be linked. But this is only suitable for small tables. For massive ranges, such an option, at best, will take a lot of time to implement, and at worst, in practice it will not be feasible at all. But this problem can be solved using a bunch of operators INDEX – SEARCH. Let's see how this can be done by linking data in tabular ranges, which were discussed in the previous method.

  1. Select the first element of the column "Bid". Let's go to Function Wizard by clicking on the icon "Insert Function".

  2. IN Function Wizard in Group "Links and Arrays" find and highlight the name "INDEX".

  3. This operator has two forms: a form for working with arrays and a reference form. In our case, the first option is required, so in the next form selection window that opens, select it and click on the button "OK".

  4. The operator arguments window is launched INDEX. The purpose of this function is to display the value in the selected range in the line with specified number. General operator formula INDEX is this:

    INDEX(array, row_number, [column_number])

    "Array"— an argument containing the address of the range from which we will extract information by the number of the specified line.

    "Line number"- an argument that is the number of this very line. It is important to know that the line number should not be specified relative to the entire document, but only relative to the selected array.

    "Column number"- an optional argument. We will not use it to solve our specific problem, and therefore there is no need to describe its essence separately.

    Place the cursor in the field "Array". After this we go to Sheet 2 and, holding left button mouse, select the entire contents of the column "Bid".

  5. After the coordinates are displayed in the operator window, place the cursor in the field "Line number". We will output this argument using the operator SEARCH. Therefore, we click on the triangle, which is located to the left of the function line. A list of recently used operators opens. If you find a name among them "SEARCH", then you can click on it. Otherwise, click on the very last item in the list - "Other functions...".

  6. A standard window opens Function Wizards. Let's go to the same group "Links and Arrays". This time, select the item from the list "SEARCH". Click on the button "OK".

  7. The operator arguments window is activated SEARCH. This function is designed to display the number of a value in a specific array by its name. It is thanks to this feature that we will calculate the line number of a specific value for the function INDEX. Syntax SEARCH presented like this:

    MATCH(lookup_value, lookup_array, [match_type])

    "Search Value"— an argument containing the name or address of the cell in the third-party range in which it is located. It is the position of this item in the target range that should be calculated. In our case, the first argument will be references to cells on Sheet 1, in which the names of employees are located.

    "Browsing Array"— an argument that is a reference to an array in which the specified value is searched to determine its position. For us, this role will be played by the address of the column “ Name" on Sheet 2.

    "Matching Type" is an optional argument, but, unlike the previous operator, we will need this optional argument. It specifies how the operator will match the search value to the array. This argument can have one of three meanings: -1 ; 0 ; 1 . For unordered arrays you should select the option «0» . This option is suitable for our case.

    So, let's start filling out the fields of the arguments window. Place the cursor in the field "Search Value", click on the first cell of the column "Name" on Sheet 1.

  8. After the coordinates are displayed, place the cursor in the field "Browsing Array" and go to the shortcut "Sheet 2", which is located at the bottom of the Excel window above the status bar. Hold down the left mouse button and select all the cells of the column with the cursor "Name".

  9. After their coordinates are displayed in the field "Browsing Array", go to the field "Matching Type" and use the keyboard to set the number there «0» . After this we return to the field again "Browsing Array". The fact is that we will copy the formula, as we did in the previous method. There will be a shift in addresses, but we need to fix the coordinates of the array being viewed. It shouldn't move. Select the coordinates with the cursor and press the function key F4. As you can see, a dollar sign has appeared in front of the coordinates, which means that the reference has turned from relative to absolute. Then click on the button "OK".

  10. The result is displayed in the first cell of the column "Bid". But before we copy, we need to secure one more area, namely the first argument of the function INDEX. To do this, select the column element that contains the formula and move to the formula bar. Selecting the first argument of the operator INDEX (B2:B7) and click on the button F4. As you can see, a dollar sign appears near the selected coordinates. Click on the key Enter. In general, the formula took the following form:

    INDEX(Sheet2!$B$2:$B$7,MATCH(Sheet1!A4,Sheet2!$A$2:$A$7,0))

  11. You can now copy using the fill handle. We call it in the same way that we talked about earlier and extend it to the end of the table range.

  12. As we can see, despite the fact that the order of the rows of the two linked tables does not match, nevertheless, all values ​​are adjusted according to the last names of the employees. This was achieved by using a combination of operators INDEX—SEARCH.

A linked table is a collection of data that can be managed as a single unit.

Use the button to create a linked table "Format as table" on the panel "Styles" tapes "Home".


Select the desired style for the future table and specify the range of cells on the basis of which the related table will be created.



After setting all the parameters, we will get the associated table. Buttons with arrows will appear next to the column headings (similar to filtering), and a contextual tool will appear in the program window "Working with tables" containing the tape "Constructor".



Each linked table is given a unique name. The default is "Table_number". You can change the table name in the panel "Properties".



On the panel "Table styles" You can, if necessary, change or customize the style of the linked table.

You can turn a linked table back into a regular range of cells. There is a button for this "Convert to Range" on the panel "Service" contextual feed "Constructor".

As mentioned earlier, next to the column headers there are sort and filter buttons that allow you to sort the data and apply filters.

In a linked table, you can calculate column totals. To do this, check the box "Total Row" on the panel "Table Style Options".



Using the drop-down list, you can select the desired value of the final function.



You can add/remove rows and columns to a linked table.

This can be done in several ways.

1. Use the button "Resize table" on the panel "Properties".



2. Place the cursor in the cell of the linked table, next to which you want to add a new column (row) and in the panel "Cells" tapes "Home" use the button "Insert".

In this article, you'll learn how to quickly merge data from two Excel tables when key columns don't match exactly. For example, when the unique identifier from the first table is the first five characters of the identifier from the second table. All solutions proposed in this article were tested by me in Excel 2013, 2010 and 2007.

So, there are two Excel sheets that need to be combined for further data analysis. Let's assume that one table contains prices (the Price column) and descriptions of the goods (the Beer column) that you sell, and the second table displays data on the availability of goods in stock (the In stock column). If you or your colleagues compiled both tables for the catalog, then both must contain at least one key column with unique product identifiers. The product description or price may change, but the unique identifier always remains the same.

The difficulties begin when you receive some tables from the manufacturer or from other departments of the company. Things can become even more complicated if a new format for unique identifiers is suddenly introduced or if warehouse stock keeping units (SKUs) change even slightly. And you are faced with the task of uniting Excel new and old tables with data. One way or another, a situation arises when there is only a partial match of records in key columns, for example, “ 12345 " And " 12345-new_suffix“. You understand that this is the same SKU, but the computer is not so smart! This non-exact match makes it impossible to use regular Excel formulas to combine data from two tables.

And what’s really bad is that the correspondences can be completely fuzzy, and “ Some company” in one table can turn into “ CJSC "Some Company"” in another table, and “ New company(formerly Some Company)" And " Old Company” will also turn out to be a record of the same company. You know this, but how can Excel explain it?

Comment: The solutions described in this article are universal. You can adapt them for further use with any standard formulas, such as VLOOKUP(VLOOKUP) SEARCH(MATCH) GPR(HLOOKUP) and so on.

Select the appropriate example to jump straight to the solution you need:

A key column in one of the tables contains extra characters

Let's look at two tables. The columns of the first table contain the product number (SKU), the name of the beer (Beer) and its price (Price). The second table records the SKU and the number of bottles in stock (In stock). Instead of beer there can be any product, and the number of columns in real life there could be much more.

In a table with additional symbols, create an auxiliary column. You can add it to the end of the table, but it's best to insert it next to the right after the key column so it's visible.

The key column in the table in our example is A with SKU data, and you need to extract the first 5 characters from it. Let's add an auxiliary column and name it SKU helper:

Ready! We now have key columns with exact value matches - column SKU helper in the main table and column SKU in the table where the search will be performed.

Now using the function VLOOKUP(VLOOKUP) we get the desired result:

Other formulas

  • Extract first X characters to the right: for example, 6 characters to the right from the entry “DSFH-164900”. The formula will look like this:

    RIGHT(A2,6)
    =RIGHT(A2,6)

  • Skip first X characters, extract the following Y characters: for example, you need to extract “0123” from the entry “PREFIX_0123_SUFF”. Here we need to skip the first 8 characters and extract the next 4 characters. The formula will look like this:

    PSTR(A2;8;4)
    =MID(A2,8,4)

  • Extract all characters up to the delimiter; the length of the resulting sequence may vary. For example, you need to extract “123456” and “0123” from the records “123456-suffix” and “0123-suffix”, respectively. The formula will look like this:

    LEFTSIMB(A2,FIND("-",A2)-1)
    =LEFT(A2,FIND("-",A2)-1)

In a word, you can use such Excel functions, How LEVSIMV(LEFT), RIGHT(RIGHT), PSTR(MID), FIND(FIND) to retrieve any parts of a composite index. If you have any difficulties with this, we will do everything possible to help you.

Data from a key column in the first table is split into two or more columns in the second table

Suppose the table you are searching contains a column with identifiers. The cells in this column contain entries like XXXX-YYYY, Where XXXX is a code designation of a group of goods ( Cell phones, televisions, video cameras, cameras), and YYYY– this is the product code within the group. The main table consists of two columns: one contains product group codes (Group), the second contains product codes (ID). We cannot simply discard product group codes, since the same product code may be repeated in different groups.

In a cell C2 Let's write the following formula:

CONCATENATE(A2;"-";B2)
=CONCATENATE(A2,"-",B2)

Here A2– this is the address of the cell containing the group code; symbol " – ” is a separator; B2– this is the address of the cell containing the product code. Let's copy the formula to the remaining lines.

Now combining data from our two tables will not be difficult. We will match the column Full ID first table with column ID second table. If a match is found, entries from the columns Description And Price the second table will be added to the first table.

Data in key columns does not match

Here's an example: You are the owner of a small store, you receive goods from one or more suppliers. Each of them has its own nomenclature, different from yours. As a result, situations arise when your “Case-Ip4S-01” entry corresponds to the “SPK-A1403” entry in Excel file received from the supplier. Such discrepancies occur randomly and there is no general rule to automatically convert “SPK-A1403” to “Case-Ip4S-01”.

Bad news: The data contained in these two Excel tables will have to be processed manually in order to be able to combine them later.

Good news: This only needs to be done once, and the resulting auxiliary table can be saved for future use. Next, you can merge these tables automatically and thus save a lot of time

1. Create an auxiliary table for the search.

Create a new Excel sheet and name it SKU converter. Copying the entire column Our.SKU from a sheet Store to a new sheet, delete duplicates and leave only unique values ​​in it.

Add a column next to it Supp.SKU and manually look for matches between the column values Our.SKU And Supp.SKU(the descriptions from the column will help us with this Description). This is a boring job, let you be happy with the thought that you only have to do it once :-).

As a result, we have the following table:

2. Update the main table using data from the lookup table.

Insert a new column into the main table (Store sheet) Supp.SKU.

Next, using the function VLOOKUP(VLOOKUP) compare sheets Store And SKU converter Our.SKU, and for updated data – a column Supp.SKU.

Column Supp.SKU filled in with original manufacturer codes.

Comment: If in the column Supp.SKU empty cells appear, then you need to take all the codes SKU corresponding to these empty cells, add them to the table SKU converter and find the corresponding code from the supplier table. After this, repeat step 2.

3. Transfer data from the lookup table to the main table

Our main table has a key column with an exact match for the lookup table entries, so this task is now easy

Using the function VLOOKUP(VLOOKUP) merge sheet data Store with sheet data Wholesale Supplier 1, using the column to find matches Supp.SKU.

Here is an example of updated data in a column Wholesale Price:

It's simple, isn't it? Ask your questions in the comments to the article, I will try to answer as soon as possible.

As you use and create more Excel workpapers, you may need to link them together. You might want to write a formula that uses data from different Sheets. Or maybe you can even write a formula that will use data from different Books (the standard name for a working Excel document- workbook (eng.))

I realized that if I want my files to be clear and tidy, it's better to separate the large Sheets with data from the formulas that process it. I often use one Workbook or Sheet that handles everything I need.

In this lesson, you will learn how to add links to data in Excel. First, we will learn how to connect data from different Sheets within one Book. Next, we'll move on to linking multiple Excel workbooks to import and sync data between files.

How to Quickly Link Data in Excel Workbooks (Watch and Learn)

I'll show you two examples of how you can link data from your spreadsheets. You will learn how to extract data from another table in Excel and how to create a link between two Workbooks. We will also look at basic examples of writing formulas that use data from different Sheets within the same working document.

Let's see an illustrated guide on linking Sheets and Workbooks in Excel.

Basics: How to Add a Link Between Sheets in Excel

Let's start by learning how to create formulas that use data from other Sheets. You may already know that an Excel working document (Workbook) can contain several working pages (Sheets). Each sheet has its own tab, and you can switch between them by clicking the tabs at the bottom of the Excel document.

Complex documents may contain a large number of sheets. And at some time, you will have to write a formula that will use data from different sheets.

Maybe you will use one sheet in the document for all your formulas that process data, and you will distribute the data into separate sheets.

My document has three tabs with Sheets. I'm going to write a formula that will work with the data from each sheet.

Let's figure out how to write a multi-page formula that works with data from different Sheets within one working document.

1. Create a new Formula in Excel

Many formulas in Excel begin with a sign equals (= ). Double click or start typing in a cell and you will begin to create the formula where you want to insert the link. For example, I'm going to write a formula that will sum values ​​from different cells.

I open the entry with a sign = , and then click the first cell on the current worksheet to create the first part of the formula. Then, I will print the sign + to add the value from the second cell in this formula.

Start writing a formula in a cell and click on the first cell to include it in the formula, but do not finish entering the formula yet.

Do not finish entering the formula and press Enter just yet! You need to leave the formula open before you switch to another Sheet.

2. Switch between Sheets in Excel

While your formula is not finished, click on the other sheet tab at the bottom of the program. It is very important that you do not close the formula before you click on another cell that you want to insert into the formula.

Switch to another Excel Sheet

After you switch to another sheet, click on the cell that you want to add to the formula. As you can see in the picture below, Excel will automatically write the portion of the formula that will access a cell in another Sheet.

Notice in the picture below that to refer to a cell on another Sheet, Excel has added "Sheet2!B3" which refers to cell B3 on the sheet named Sheet2. You can write this manually, but when you click on the cell, Excel does this automatically for you.

Excel automatically writes some of the formulas for you to add a cell reference from another Sheet.

3. Close the Excel formula

Now, you can click enter to finish creating the multi-page formula. When you do this, Excel will return to where you started creating the formula and show you the result.

You can continue to type the formula, including cells from other sheets and other cells on the same sheet. Continue combining links from throughout the working document to all the data you need.

Upping the Difficulty Level: How to Create a Link Between Multiple Excel Workbooks

Let's learn how to extract data from another working document. With this skill, you will be able to write formulas that link data from different Excel workpapers.

For this section of the lesson, you will need two working documents that you can for this lesson. Open both of them in Excel and follow the instructions.

1. Open Two Workbooks

Let's start creating a formula that will use data from two different documents.

The easiest way to do this is to open two Excel documents at the same time and place them next to each other. I use for this Windows feature Snap so that each window takes up half the screen. You need to have two books in front of you in order to write a formula that will interact with two books.

In the picture below, I have two working documents open next to each other for which I will create formulas. In my example, I run a business and buy various products and sell them at different countries. So, I use different work documents to track purchases/sales and cost data.

In this example, I'm using different work documents to track purchases/sales and cost data.

2. Start Creating Formula in Excel

The price at which I buy goods may change, and the ratio of exchange rates to the currency in which I receive payments may also change. Therefore, I need to have a list that tracks exchange rates and multiply them by the cost of my purchases. This is a good opportunity to use the information from the two files and write a formula that will use the data from both files.

Let's look at the number of barrels of oil I buy each month and the price per barrel. First in a cell from a column Cost(cell C3), I start creating the formula by clicking in it and pressing = ( = ), and then click on the cell AT 3 to take the value for quantity. Now, I press the sign * to multiply the amount per course.

So your formula should look like this for now:

Don't close your formula yet. Make sure it remains open before moving on to the next step; we need to provide Excel data for the price by which the quantity will be multiplied.

3. Switch to Another Excel Working Document

It's time to switch to another working document, which is why it is so important to have data from two documents in between when you are working with two Workbooks.

While your formula is open, click on the other workbook's tab. Then, click on a cell in your second workbook to link the data from the two Excel files.

B3*Sheet1!$B$2

After you press Enter, Excel will automatically calculate the final cost by multiplying the quantity in the first workbook by the price from the second workbook.

Practice your Excel skills by multiplying the quantity of an item or volume by its corresponding price from the document " Prices".

The main thing is to place the books one next to the other, and create a formula by switching between them.

At the same time, nothing prevents you from using more than two working documents. You can open many workpapers that you want to reference and create formulas that link data across multiple sheets.

How to Update Data When Working with Multiple Workbooks

When you use formulas that reference different Excel workbooks, you need to take care of how the data will be updated.

So, what happens if the data in the Book you are referring to changes? Will automatic changes be made to your Book, or do you need to refresh your files to retrieve the latest changes and import them?

The answer is, “it depends,” and it mostly depends on whether you have two documents open at the same time.

Example 1: Both Excel Workbooks Open

Let's test the example using the same books we used before. Both books are open. Let's see what happens if we change the price of oil per barrel from $45 to $75:

In the screenshot below, you can see that when we changed the oil price, the results in the other document automatically changed.

It is important to remember: if both working documents are open at the same time, changes will be made automatically in real time. When you change a variable, the information in another document will be automatically changed or recalculated based on the new data.

Example 2: If One Working Document is Closed

What if you only have one work document open? For example, every morning we update the prices of our goods and currencies, and in the evening we review the impact of the changes on our purchases and sales.

The next time you open a document that references other tables, you should see a message similar to the one below. You can click the Update button to retrieve recent data from the document referenced by the formula.

Click the Refresh button in the drop-down window when you open the document to retrieve the latest data from another file.

You may also see a menu where you can click the Enable Content button to automatically update data between Excel files.

Let's summarize and continue to learn how to work in Excel

Writing formulas that take data from different Sheets and Workbooks is an important skill when working in Excel. Moreover, it is not at all difficult if you know how to do it.

Check out these additional lessons to learn more about how to work with Excel program and how to process data in it. These lessons are good for continuing your learning.

Let me know in the comments if you still have any questions about how to organize communication between different Excel working documents.

Sometimes the data you need to find may be located in different tables. Often you have to select data from several Excel tables. The user should be able to determine from which table to make a selection based on the criteria of the conditions for a particular type of data. You can create a special formula for this.

How to link two tables with one formula to sample a VLOOKUP by condition

The figure below shows a table for calculating the tax amount. The user has the ability to define Family status employee (married or single). If the user selects the “Single” condition, the selection should be made according to the “Single Employees” table. If the “Married” criterion is selected, the sample will be made according to the “Married employees” table. Formula for calculating taxes assuming a married or unmarried employee of the company:

To create a switch between tables, you can use cell range names and the INDIRECT function. Then you need to create a formula. You must first create two named ranges:

  1. Married – for the “Married Employees” table.
  2. Unmarried – for the “Unmarried Employees” table.


To assign separate names for each of the ranges of these two tables, do the following:

For accuracy and convenience of entering input values ​​in a cell... a drop-down list is used created by the tool: “DATA” - “Working with data” - “Data verification” - “Data type:” - “List”.


The drop-down list consists of only two values: “Married” “Single”. Exactly the same as the names of the names of the cell ranges created earlier. The value of cell E12 will be used to switch between tables when searching by condition. Therefore, the values ​​and names of the ranges must be identical.

This formula is based on the VLOOKUP function. Its second argument, which specifies the source table, contains the INDIRECT function. This function has a first argument, Cell Reference, which converts the incoming text into a cell or range reference. In the very first picture, cell E12 contains the value "Single". The INDIRECT function attempts to convert this text into a cell reference or range name. If the text is not converted to a cell reference (as in in this example), then the INDIRECT function checks to see if there are cell range names with the same name in this workbook. If such range names had not been created, then the function would return an error with code #REF!

The INDIRECT function syntax has a second optional argument called "A1". The value TRUE in this argument means that the cell reference is written in A1 format, and the value FALSE means that the cell reference is written in R1C1 format. In the case of named cell range names, the INDIRECT function will return the correct result regardless of whether its second optional argument “A1” is TRUE or FALSE.

The INDIRECT function can also return external links to other sheets and even other Excel workbooks. But provided that the workbook referenced by the function is open. Otherwise, an error will be returned with the code #LINK!