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.

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.

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

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

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

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

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.

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.

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

Finally, the consolidated results appear beside the dataset.

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.

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.

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

Lastly, the result is shown in the picture below.

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.

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

Steps:

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

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

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

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

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.

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

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

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

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

Advanced Excel Exercises with Solutions PDF