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.


Download Practice Workbook

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


2 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

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

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.

Read More: How to Automate Consolidation in Excel (with Easy Steps)


2. Using Excel Consolidate Command to Create a Linked Consolidation

There is a dedicated command in Excel named Consolidate to create a linked consolidation. 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, so mark the Top row and Left column option from the ‘Use labels insection.
  • As we want to create linked consolidation, so mark Create links to source data.
  • Finally, just press OK.

Using Excel Consolidate Command to Create a Linked Consolidation

Now have a look, we got the same output as the previous method including the Group feature of Excel. But 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’s showing 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.

Read More: How to Do Consolidation in Excel (2 Useful Cases)


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. Visit ExcelDemy to explore more.


Related Articles

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo