Question

explain link to source data option in consolidation dialog box​

Answer 1

Hopefully it helps you
Mark me as brainliest


Create links to source data - This option will mean that your consolidated table of data will update automatically when any of the original values change. This option inserts the necessary formulas in to the consolidated table.

Answer 2

Begin by opening the worksheet (in this example: Quarter 1) that is to contain the consolidation table. Now select the cell that is to receive the first consoldiated value. In this case, select cell A4 on the Quarter 1 worksheet.

The excel Consolidate button

is located on the Data tab, in the Data Tools group.

Click the excel Consolidate button. The excel Consolidate dialog box with its several input fields will open:

Here you can select a consolidation function from a list of mathematical and statistical functions. By default, the Sum function is preselected.

This box is where you will be identifying cell ranges that are to be consolidated.

In this box, the ranges identified in the reference box above will be collected. The buttons Add and Delete are used to add or delete such ranges.

This area provides options that control row and column headers. It also allows you to specify that links to the source data should be created in the consolidation table.

In our current example, you will leave the Sum function unchanged in the Function: box. Now, to identify the first cell range to be consolidated, click in the Reference: input box. Next, click the Berlin worksheet’s tab. In that worksheet, select the entire table (B4:E9). As a result, the corresponding cell range – identified as: Berlin!$B$4:$E$9 – will be displayed in the Reference: input box. Now click the Add button. The cell range you just identified will be copied into the All References: area (as shown below):

In a similar fashion, you can select the second consolidation cell range from the Frankfurt worksheet. Before selecting the cell range and using the Add button to copy it into the All References: box, make sure your cursor is again positioned in the Reference: box.

Finally, follow the same procedure for the Munich worksheet. When done, all three cell-range references should be listed in the All References: box.(

Now, to insert the consolidated table into the Quarter 1 worksheet with correct column and row headers, you must activate the Top row and Left column options in the Use labels in area.

If you now click OK, the consolidated table will be inserted.

CAUTION

In order to correctly merge data from columns and rows that contain the data to be consolidated, Excel requires that identical headings be spelled identically. If one row heading were spelled Chocolate, for example, and another were spelled Chocolat, Excel would consider them to be two different items.