How to Create a Linked Consolidation in Excel (2 Useful Methods)

To make a summary report, we often have to consolidate data. But if we don’t create a link between consolidated data and source data, then the consolidated data won’t be updated automatically when source data is changed. So it’s an important issue while consolidating data. We can handle this issue in 2 useful ways. In this article, I’ll show those two quick ways to create a linked consolidation in Excel.

Let’s get introduced to our dataset first. It represents the monthly sales report for three consecutive years in three different regions.

1. Using Formula to Create a Linked Consolidation in Excel

First, we’ll apply a formula to create a linked consolidation in Excel. We’ll just find the sum of sales using the SUM function for every connected month and region as a summary report. But keep in mind, for this method, the source dataset should be of the same size and the same cell reference in every sheet. Otherwise, this method won’t work properly.

Steps:

• In Cell C5, type-

• Then click on the first sales sheet.

• Next, just select the first sale.

• After that, press and hold the SHIFT key and click the last sales sheet. So it will select all the same cells till the last sheet. No need to select one by one manually.
• Then hit the ENTER button.

It’s the total sales of January in the UK region for the three consecutive years.

• Later, drag the Fill Handle icon in the right direction to apply the same formula for other months of the UK region.

• Lastly, drag down the Fill Handle icon to apply the formula in the rest two regions.

Then the output will look like the following image.

Now, if we change any data, it will be updated automatically. Look, I changed a value in the sales report of 2019- inserted 0 there.

Now go back to the main sheet and see that the output updated automatically.

2. Using Excel Consolidate Command for Creating a Linked Consolidation

There is a dedicated command in Excel named Consolidate to create a linked consolidation and to consolidate data from multiple ranges in Excel. The advantage of it is- there is no need for the same cell references in every sheet, you can place the dataset anywhere.

Steps:

• First, select a cell in a new sheet.
• Then click as follows: Data > Data Tools > Consolidate.

Soon after, a dialog box will open up.

• Select the function from the Function drop-down section that you want to apply. I selected Sum.

• Later, click on the Open icon from the Reference box.

• Next, go to the first sheet by clicking on it.

• Then select the data range including the headers by dragging with the mouse
• Press the ENTER button.

• After that, click the Add button.

Now see, the reference is added.

• Again, click on the open icon and follow the previous three steps to add the references from the rest of the two sheets.

• Now we have come to the last and most important step. As we have headers along a row and a column, mark the Top row and Left column option from the ‘Use labels in’ section.
• As we want to create linked consolidation, mark Create links to source data.
• Finally, just press OK.

Now take a look, we got the same output as the previous method, including the Group feature of Excel. However, it will not extract formats from the source data. You will have to apply again.

I applied some formats.

• If you click on any plus sign (+) then it will show you the source data of the corresponding region.

See, it shows the source data of the UK region. If you press the minus (-) sign then it will be minimized.

• Now let’s check the linking. I inserted zero in Cell C3 of the 2019 sheet.

And yes! The output updated perfectly.

Download Practice Workbook

You can download the free Excel workbook from here and practice independently.

Conclusion

I hope the above procedures will be good enough to create a linked consolidation in Excel. Feel free to ask any questions in the comment section and give me feedback.

<< Go Back To Consolidation in Excel | Merge Sheets in Excel | Merge in Excel | Learn Excel