Linked table is a table created in Excel and linked to a table in the PowerPivot window. The advantage of creating and maintaining data in Excel over importing it is that you can continue to modify the data by Excel worksheet and at the same time use them for analysis in PowerPivot.

The name of the new table that appears in the PowerPivot window is always the same as the name of the Excel table. Therefore, before creating the linked table, you must give the Excel table a meaningful name. By default, Excel generates table names automatically (Table1, Table2, etc.), but you can easily rename them using the Excel interface.

Create a table in Excel

In the window Excel, select the data you want to present as a table.

Click the button Format as table on the Root Folder tab and select a table style. Be sure to select Table with headers.

Give the table a name as described in the following procedure.

Rename a table in Excel

In the window Excel tab Working with tables in the Properties group on the tab Construction enter a name for the table.

Create a linked table in Excel

Select the created table in the Excel window.

On the PowerPivot tab, in the Excel group, click Create linked table.

The new table appears in the PowerPivot window. The data in this table is linked to the selected Excel table and can be modified in Excel rather than in the PowerPivot window.

Renaming an Excel table after creating a linked table breaks the link between the tables. Dialog box appears when trying to update data Errors in linked tables. To select an option to fix the error, click Options. Changing the name in the PowerPivot window does not affect the link.

When renaming column headings in a table Excel changes in the related Gemini table are applied automatically. However, if you rename a column header in Excel that is used in a PowerPivot relationship, the PowerPivot relationship must be recreated. For more information on relationships, see.

By default, PowerPivot tables linked to Excel tables are automatically refreshed when the data changes. Alternatively, they can be manually refreshed in the PowerPivot window or in the Excel window.

Setting Refresh Mode in PowerPivot

Working with tables go to tab Linked table and in the group Linked tables press the button Update mode, and then click the Manual or Automatic button.

Updating Linked Tables Manually in PowerPivot

In the PowerPivot window, under Working with tables go to tab Linked table and in the group Linked tables click the Update All button or Refresh Selected.

Updating linked tables manually in Excel

In the window Excel go to the PowerPivot tab and select in the group Excel data Refresh All.

If we need to automatically data from one Excel table was transferred to another, you need to link these tables. There are several ways how to link data from two tables in Excel. One way - using a formula, see How to Link Tables in Excel.
Consider here, how to link two tables in different Excel workbooks, by establishing links between tables. When everything is set up, it is enough to open one table so that the data is transferred to this table from another table. You don't need to open all the books for the link to work. She will work with one open book.
So, we have two Excel workbooks - Book1 and Book2. In Book1 there is such a table, already filled in. Table 1.Attention!
If there are merged cells in the table, then the connection in them will not work or will get lost.
In Book2 we make the same table, but do not fill it in.

Now we need to make sure that the data from Table1 is automatically transferred to Table2. In Book1, right-click on cell A1. In the emerging context menu click on the "Copy" function.
Go to Table 2. Select cell A2. Click on the "Home" tab the arrow next to the "Insert" button and click on the "Insert link" button.

Another function "Insert link" can be called like this. We also copy, but in the context menu, click on the "Paste Special" function. In the dialog box that appears, click on the "Insert link" button. Attention!
If we press the "Enter" key, the connection may not be established. Therefore, when we establish a connection, just click on an empty cell. And remove the pulsating frame at cell A1 in Table 1 by pressing the "Esc" key.
When we establish a link in a cell, the path will be written in the formula bar.
In cell A2 of Table2, the column name appeared, as in Table1.
Now, to copy the link across the entire column, remove the $ sign in the path address in cell A1 - replace the absolute link with the relative one. For more information on links, see the article "Relative and absolute links in Excel".
Copy the formula with the link from cell A1 down the column. We also set up and, then, copy, the connection in other cells. The result is such a table2.

Let's change the numbers in Table1.

In Table 2 it turned out like this.

Let's save both books, close. Now, if we open Book 1, change the numbers in it, work and close. But, when we open Book2, all the same, the data in Table2 when opening Book2 will change to those that we changed in Table1.
For other paste special functions, see the article "Excel Paste Special".
For how to link tables on different computers, see the Sharing an Excel file article.
In Excel, you can configure the automatic transfer of data from Excel spreadsheet to Word and vice versa. See the article "

If one worksheet uses data from another worksheet, then these worksheets are considered linked. Linking can be used to combine cell values ​​from multiple different tables on one worksheet.

Changing the contents of a cell in one sheet or table ( source) of a workbook causes the associated cells in sheets or tables to change ( receivers). This principle distinguishes sheet binding from simple copy contents of cells from one sheet to another.

Depending on the performance technique, the binding is “ direct"And through the command" Paste special ".

Direct linking Sheets is used directly when entering a formula in a cell, when a reference to a cell of another sheet is used as one of the formula elements. For example, if table cell B4 on worksheet2 contains a formula that uses a reference to cell A4 of another worksheet (for example, Sheet 1) and both sheets are loaded with data, then this linking of sheets is called "direct".

The term "direct" linking means that the user himself, when entering the formula, specifies the sheet name and the absolute cell address, separated by an exclamation mark "!".

Examples of formulas: = C5 * Sheet1! A4

Sheet3! B2 * 100%

Sheet1! A1-Sheet2! A1

Note. To specify a link to cells and sheets that are in unloaded (unopened) workbooks, you need to specify the full path of the file location in the formula without spaces. The path is specified in single quotes, which indicates the name of the disk, directory (folder), the name of the workbook (file name) in square brackets and the name of the sheet to which the link is located.

For example:

= "D: \ Student documents [Income calculation.xls] Purchase of products"! H19

Linking via a command " Paste special " produced if any cell of the table on one worksheet should contain the value of a cell from another worksheet.

To reflect in cell C4 on the sheet Product price the value of cell H4 on the original sheet Purchase of products, you need to place the cursor on cell H4 of the original sheet and execute the command Edit / Copy. On the sheet Product price put the cursor on cell C4, which must be linked to the source, and execute the command Edit / Paste Special / Paste Link(see fig. 6). Then on the sheet Selling price a reference to the cell of the original sheet will appear Purchase of products, eg: = Purchase products! $ H $ 4

With this binding, EXCEL automatically uses the absolute address per cell, since a relative address can lead to errors, especially when accessing unloaded files (workbooks).

Task 7. Link cells C4, C5, C6, C7, C8 in the Purchase Costs table on the sheet Selling price with matching cells on the sheet Purchase of products, while using different ways linking cells (fig. 28).

Fig. 28. Linking cells of different worksheets

Unfortunately, if the source book was deleted / moved or renamed, then the link will be broken. Also, the link will be lost if you move the final file (containing the link). If you send only the final file by mail, then the recipient will also not be able to update the links.

How to break a bond

One way to solve this problem is to break the connection. If there is only one link in the file, then it is quite simple to do this:

1. Go to the tab Data.
2. Select a team Change links In chapter Connections.
3. Click on Break the bond.

IMPORTANT! When the link is broken, all formulas referring to the source book will be converted to values! Cancellation of this operation is not possible!

How to break ties with all books

For convenience, you can use a macro that breaks links with all books. The macro is included with the VBA-Excel add-in. To use it you need:

VBA code

The macro code that removes all links to the book is presented below. You can copy it to your project.

Sub UnlinkWorkBooks () Dim WbLinks Dim i As Long Select Case MsgBox ("All references to other books will be removed from this file, and formulas referring to other books will be replaced with values." & VbCrLf & "Are you sure you want to continue?", 36, "Break the link?") Case 7 "No Exit Sub End Select WbLinks = ActiveWorkbook.LinkSources (Type: = xlLinkTypeExcelLinks) If Not IsEmpty (WbLinks) Then For i = 1 To UBound (WbLinks) ActiveWorkbook.BreakLink Name: = WbLinks (i), Type: = xlLinkText MsgBox "B this file no links to other books. ", 64," Links to other books "End If End Sub

How to break ties only in a dedicated range

Sometimes there are many links in a book and there is a concern that when you delete a link, you can delete an unnecessary one. To avoid this, using the add-in, you can delete links only in the selected range. For this.

Workbook sheets

Until now, we have worked with only one sheet of the workbook. It is often helpful to have multiple worksheets.

The Sheet Labels are visible at the bottom of the screen. If you click on the tab with the left mouse button, the specified sheet becomes active and moves to the top. Right-clicking on a tab will bring up a menu for sheet actions such as moving, deleting, renaming, etc.

In the lower left corner of the workbook window there are scroll buttons with which you can move from one worksheet to another.

By right-clicking on the scroll buttons on the tabs, a context sensitive menu can be opened to select the desired worksheet.

Location of workbooks

Let's say you want to see everything on the screen at once. open books Excel will easily fulfill your desire, and you can easily navigate your workbooks. Using Excel command Window Arrangement You can arrange your open workbooks on the screen in four ways.

* beside - workbooks open in small windows, into which the entire screen is divided in a "tiled" way;

* from top to bottom - open workbooks are displayed in windows that look like horizontal stripes,

* from left to right - open workbooks are displayed in windows that look like vertical stripes;

* cascade - workbooks (each in its own window) are "laid out" on the screen in layers.

Transitions between workbooks

Whether you decide to arrange all open workbooks on the screen or simply "stack" them on top of each other in the order of opening, you can easily navigate from one workbook to the next. Excel provides several quick ways to navigate to a workbook. To do this, you can use the mouse, the emergency keys, or the Excel Window menu. These are the ways:

* click on the visible part of the workbook window;

* press keys to move from the window of one book to the window of another.

* open the Excel menu Window. The bottom part contains a list of open workbooks. To go to the book you want, just click on the name.

Copying data from one workbook to another

You can use the Excel Copy command to copy data from one workbook to another. For example, you have two workbooks open, one containing the quarterly budget and the other with the annual budget. To save time, it might be a good idea to copy the first quarter data from the first workbook to the second. In this case, the original data in the first workbook will appear without changing a copy of this data.

To copy data from one workbook to another, open both workbooks. Select the data in the first book and click the Copy button on the Standard toolbar to switch to another book, use any of the above methods. For example, according to one of them, choose the name of the second workbook from the Window menu. Go to the desired worksheet and select the cell where you intend to insert data. Click the Insert button on the Standard toolbar. Excel will instantly copy the data into the second workbook.

Transferring data between workbooks

This procedure is the same as for copying data. The difference lies in the use of a different button. Select the data in the first workbook and click the Cut button on the Standard toolbar to extract the data. Going to another workbook, select the required cell and click the Insert button on the Standard toolbar. As a result, Excel will delete the data from the first workbook and insert it into the second.

Exists quick way transfer of worksheet (s) data between workbooks. It consists of using the drag and drop method. First, open the books involved in the data transfer operation. Select Window / Arrange from the Excel menu. In the Window Arrangement dialog box that opens, select the option beside and click the OK button. You should be able to see at least a small portion of each workbook window. Highlight the tab for the sheet (s) you want to copy. Position the mouse pointer over the highlighted sheet tab, click and hold down the mouse button and drag the tab to the window of another workbook. When you release the mouse button, the sheet will be "registered" in the new (for it) workbook.

Create links between worksheets and workbooks.

Excel allows you to use data from other sheets and from other tables in a table.

Binding is a process dynamic update data in a worksheet based on data from another source (worksheet or workbook). The linked data reflects any changes made to the original data.

Linking is done through special formulas that contain so-called external links. External reference can refer to a cell from another worksheet of the same workbook, or to a cell of any other worksheet of any other workbook. For example, a link between two sheets is achieved by introducing a link formula into one sheet with a link to a cell in another sheet.

Excel allows you to create links to other worksheets and other workbooks of three types:

When working with large quantity data and creating multiple worksheets to store that data, situations arise when a formula in one worksheet uses data from another worksheet. Formulas like these are useful because they save you the trouble of storing redundant data across many worksheets.

To refer to a cell in another worksheet, place an exclamation mark between the sheet name and the cell name. The syntax for this type of formula is: = SHEET! Cell. If your sheet has a name, use the sheet's name instead of labeling the sheet. For example, Report! B5.

If the name contains spaces (for example, Budget 99), then when creating links to other sheets this name must be enclosed in single quotes.

Linking multiple worksheets

There are often situations where a formula must refer to a range of cells that includes two or more sheets in a workbook. This usually happens when identical worksheets are created to distribute the budget over different periods of time, for information about the work of different teams, or for information about sales in different regions. You may also have different worksheets, but their totals are specifically contained in cells with identical addresses. And all of these totals can then be brought together to get a grand total in a single formula containing a link to all of these worksheets and cell addresses.

In such cases, Excel refers to the ranges of cells using 3D references. The 3D reference is established by including a sheet range (specifying a start and end sheet) and a corresponding cell range. For example, a formula using a 3D reference that includes sheets from Sheet1 to Sheet5 and cells A4: A8 might look like this: = SUM (SHEET1: SHEET5! A4: A8).

You can include 3D references in a formula in another way. To do this, just click on the worksheet that you want to include in the formula. But first start the formula in the cell where you want the result. When it comes to using a 3D link, click on the tab for the first sheet to be included in the link, then press (and hold) and click on the tab for the last sheet to be included in the link. After that it will highlight required cells... When you finish building the formula, press .

Linking workbooks

There are a number of terms you should be aware of when linking workbooks. The workbook containing the linking formula is called the dependent workbook, and workbook that contains the data to be linked - the original workbook.

The link between two files is achieved by introducing a link formula into one file with a link to a cell in another file, the file that receives data from the other is called the destination file, and the file that provides the data is called the source file.

As soon as the connection is established. Excel copies the value from the cell in the source file to the cell in the target file. The value in the destination cell is automatically updated.

When referring to a cell contained in another workbook, the following syntax is used: [Book] Sheet! Cell. When entering a binding formula to refer to a link from another workbook, use the name of that workbook, enclosed in square brackets, followed by the worksheet name without spaces, followed by an exclamation mark (!), Followed by the address of the cell (s). For example "C: Petrov [Journal1.хls] Literature"! L3.

Updating links

When working with multiple workbooks and linking formulas, you need to know how these links are updated. Whether the formula results are updated automatically if you change the data in referenced cells only if both workbooks are open.

If the data in the original workbook changes while the dependent workbook (which contains the linking formula) is closed, then the linked data is not updated immediately. When you open the dependent workbook the next time, Excel will ask you for confirmation to update the data. Select Yes to update all related data in the workbook. If you have links that are manually updated, or if you want to update the links yourself, select No.

To delete a sheet, select the Delete command from the context-sensitive menu for tabs. To delete several worksheets at once, select them first while holding down the Ctrl key.

Check:

· Do you know what: Excel workbook; worksheet; rules for writing formulas for linking worksheets;

· Do you know how to: insert a worksheet; delete; rename; move; copy; open windows; close; streamline; link between sheets of the same and different workbooks.

Laboratory work on Microsoft Excel.