How to Do Consolidation in Excel?

Get FREE Advanced Excel Exercises with Solutions!

Looking for ways to consolidate data in Excel? Then, this is the right place for you. Sometimes, we need to consolidate data to summarize the data from separate ranges into a single output range. With this in mind, this article hopes to guide you on how to do consolidation in Excel.


Introduction of Consolidate Feature in Excel

Consolidation in Excel is the process of integrating different parts of the information into a single, structured format. In fact, you can merge rows, columns, and separate worksheets using the built-in consolidation feature in Excel.

Now, to perform data consolidation, go to Data > Consolidate options in the Data Tools section.

How to Do Consolidation in Excel

Now, let’s learn about the various options of the Consolidate dialog box.

  • Firstly, you can use the Function drop-down to perform various mathematical operations.

In this example, the Sum function adds up the Sales amount for each employee.

Function Box

  • Secondly, the Reference box allows us to select the dataset from other worksheets

Here, the $B$4:$C$14 cells represent the chosen dataset.

Reference Box

  • Moreover, you can use the Browse option to select data from a separate workbook.
  • Following, the Add and Delete options allow you to select multiple datasets or remove a dataset.

Browse Add Delete Box

  • Thirdly, the Top row and Left column options recognize the position of the table headers.
  • Lastly, the Create links to source data option links the original data with the consolidated report.

Data Labels Section


How to Do Consolidation in Excel: 2 Useful Cases

We’ll show 2 different cases to perform data consolidation using the Consolidate feature. So, without further delay, let’s see the process step-by-step.

1. Consolidation of Data from a Range of Cells

Our, first case considers the scenario where you have a range of cells in your dataset.

Let’s consider the following dataset, shown in B4:C14 cells. Here, the columns show the Employee Names and their corresponding Sales in USD.

Dataset 1


1.1 Consolidation of Multiple Rows

Clearly, we can see that there are multiple entries for Sales corresponding to each employee’s Name. So let’s consolidate this dataset to summarize the sales figure.

Steps:

  • Initially, select any cell outside the dataset, for example, the E4 cell is chosen below.
  • Next, go to Data > Consolidate.

How to Do Consolidation in Excel for Multiple Rows

  • Now, a dialog box appears where you have to choose the SUM function in the Function.
  • Following, select the range of cells  $B$4:$C$14 in the Reference.
  • Then, check the Top row and Left column boxes below.

How to Do Consolidation in Excel for Multiple Rows

Finally, the consolidated results appear beside the dataset.

How to Do Consolidation in Excel for Multiple Rows

Read More: Consolidate Data from Multiple Rows


1.2 Consolidation of Multiple Columns 

If you want to consolidate two columns into one, then you’ve come to the right place. Allow me to demonstrate.

Assuming the following dataset shown in the B4:C14 cells below. Here, the dataset consists of 2 columns with the First Name and the Last Name of the employees.

Dataset 2

Steps:

  • Firstly, insert a column with the heading Full Name.
  • Then, enter the expression in the D5 cell as shown below.

=B5&" "&C5

In the above formula, the B5 cell refers to the First Name and the C5 cell indicates the Last Name. Additionally, the Ampersand operator (&) is used to combine the string of text from both cells.

How to Do Consolidation in Excel for Multiple Columns

  • Next, drag the Fill Handle tool to copy the formula into the cells below.

How to Do Consolidation in Excel for Multiple Columns

Lastly, the result is shown in the picture below.

How to Do Consolidation in Excel for Multiple Columns

Read More: Consolidate Data from Multiple Columns


2. Consolidation of Data from Multiple Worksheets

Suppose, we have the Sales Data for 2 years in two separate spreadsheets, and we want to merge them into one worksheet. In this case, data consolidation becomes a handy tool. Let’s see it in action.

Considering the dataset in the B4:C13 cells. Here, the Employee Names, and their Sales for the year 2020 are shown.

Dataset 3

Likewise, the Sales Data for the year 2021 is also provided below.

Dataset 4

Steps:

  • Firstly, open a new worksheet and select any cell, in this case, the B4 cell is chosen.
  • Then, go to Data > Consolidate.

How to Do Consolidation in Excel for Multiple Worksheets

  • Now, a dialog box appears, where you have to click on the Reference
  • Next, select the dataset in the B4:C13 range from the Sales Data 2020 worksheet and click Add.

How to Do Consolidation in Excel for Multiple Worksheets

  • Similarly, choose the dataset for the Sales Data 2021 spreadsheet and press OK.

How to Do Consolidation in Excel for Multiple Worksheets

Eventually, the outcome should look like the picture shown below.

How to Do Consolidation in Excel for Multiple Worksheets

Admittedly, I have skipped the process of consolidating data from multiple workbooks which you may explore if you wish.


How to Do Consolidation of Data with Link to Source

Excel allows us to link our consolidated report to the source. Simply put, if we make changes to the source data, the report will be updated automatically. Just follow along.

Steps:

  • Similar to Case 2, select any cell and go to Data > Consolidate.

How to Do Consolidation in Excel for Multiple Worksheets

  • In turn, add the range of datasets from both worksheets.
  • Additionally, check the Create links to source data box as shown below.

How to Do Consolidation in Excel for Multiple Worksheets

  • Just like before, this generates the consolidated report.
  • Moreover, the Level Tabs (1 and 2 ) and the Toggle Buttons (+ and – ) appear.

How to Do Consolidation in Excel for Multiple Worksheets

Here, the Level Tabs allow us to drill down into the report while clicking the Toggle Buttons expands the groups.

How to Do Consolidation in Excel for Multiple Worksheets

Read More: How to Create a Linked Consolidation


Download Practice Workbook

You can download the practice workbook from the link below.


Conclusion

To conclude, I hope this article helped you understand how to do consolidation in Excel. If you have any queries, please leave a comment below.


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.
Eshrak Kader
Eshrak Kader

Eshrak Kader is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He boasts a rich background in Microsoft Office Suite, and over the past year, he has authored over 120 insightful articles for the ExcelDemy. Currently, Eshrak leads a team of 5 in the ExcelDemy Video project and enjoys problem-solving and making videos on Excel. Eshrak’s passion for continuous learning underscores his commitment to excellence in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo