“Data link”

We may have different data in several sheets and may want to produce the sum, difference or carry out any other mathematical operation combining the data from different sheets and put into a separate sheet.

For example, when we have a “Workbook” with four “Worksheets” with the quarterly figures by region, we can create one fifth “Worksheet” containing the totals of all quarterly data.

We may use information from up to 255 sources and link them with each other in a “Worksheet”.

If we want to link several “Worksheets” together, we select the first cell in the “New worksheet” where we want to link.

Then we must select the different ranges that may be over several “Worksheets”.

Click “Data” – “Consolidate” in the menu.

The “Consolidate” dialog box opens.

In the “Consolidate” dialog box, first choose the “Function” you want to exercise (in our case the sum).

Then we select the data from several “Spreadsheets” that we want to connect.

We do this by pressing icon on the right of the “Reference” box : select the “Range” in the “Worksheet”.

When you have selected a “Range” click on the “Add” button.

For each “Range” in each “Worksheet” that you want to connect, select it by right clicking the button on the right of the “Reference” box.

If you want labels in your “New worksheet”, click the checkboxes “Top row” and / or “Left column”.

If you wish that when changes occur in one of the other worksheets, the new worksheet is updated, then click the check box “Create links to source data”.

The different Excel “Worksheets” bind the different rows or columns together when you check the “Create links to source data” checkbox.

The inserted columns or rows contain a reference to the original data.

This insert columns or rows are connected and hidden so that the “New worksheet” looks like a normal “Spreadsheet”.

Here also you can click on the ‘-‘ sign to hide or show the details.

Types of connections

We can connect Excel “Worksheets” or folders in two ways, by category or by position.

When we connect “Worksheets” by category, Excel will draw the data based on the column titles or row titles in categories, to process into the “New worksheet”.

For example, we may connect several worksheets with the same titles:
(Quarter 1, quarter 2, quarter 3, quarter 4)

Excel will allow the titles to be used, and thus connect by category.

Even though they are not in the same order and in different “Worksheets”.

When we connect by category, it is also necessary that the column headings and row titles are selected with the “Range” of each “Worksheet”.

We can also connect worksheets according to position.

In that case, Excel is not bothered about what the titles are.

All that Excel is interested in is where the data resides.

All data in all “Worksheets” must be exactly in the same position.

A positive connection connects cell addresses of the Excel data to the “Worksheet”.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats