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

Get FREE Advanced Excel Exercises with Solutions!

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.


Watch Video – 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-
=SUM(
  • Then click on the first sales sheet.

Using Formula to Create a Linked Consolidation

  • Next, just select the first sale.

Using Formula to Create a Linked Consolidation

  • 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.

Using Formula to Create a Linked Consolidation

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

Using Formula to Create a Linked Consolidation

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

Using Formula to Create a Linked Consolidation

  • 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.

Using Excel Consolidate Command to Create a Linked Consolidation

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

Using Excel Consolidate Command to Create a Linked Consolidation

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

Using Excel Consolidate Command to Create a Linked Consolidation

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

Using Excel Consolidate Command to Create a Linked Consolidation

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

Using Excel Consolidate Command to Create a Linked Consolidation

  • After that, click the Add button.

Using Excel Consolidate Command to Create a Linked Consolidation

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.

Using Excel Consolidate Command to Create a Linked Consolidation

  • 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 insection.
  • As we want to create linked consolidation, mark Create links to source data.
  • Finally, just press OK.

Using Excel Consolidate Command to Create a Linked Consolidation

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.

Using Excel Consolidate Command to Create a Linked Consolidation

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo