How to Consolidate Two Sheets into One in Excel (3 Useful Methods)

When you need to consolidate or merge multiple sheets into one sheet in Excel, you should follow some steps and formulas to do that. In your practical life, you may face this issue again and again and you can solve this by studying how to consolidate two Excel sheets into one. In this article, we have tried to discuss various methods on how to consolidate two Excel sheets into one.

Download Practice Workbook

3 Methods to Consolidate Two Excel Sheets into One

We have made two datasets based on Sales in July and Sales in August of a random vegetables & fruits company in California. The datasets have data on Product, Sales Person, and Sales in USD. Sales in July dataset is like this.

And the Sales in August dataset is like this.

Now, we’ll discuss various methods of consolidating these two sheets into one.

1. Using Consolidate Feature to Consolidate Two Sheets into One in Excel

You can use the Consolidate feature when you need to consolidate sheets into one and the output values need to be the resulting values of a formula. Suppose you need to add the sales of July and August of every individual Sales Person. You need to follow the steps below.

• Firstly, go to Data > click on Data Tools > select Consolidate.

A Consolidate bar will appear.

• Secondly, put Sum in the Function box and give reference in the Reference You can put any function in the function box depending on your requirement.
• Thirdly, click Add

As a result, the reference of Sales in July sheet has been added to the All Reference box.

• Similarly, add a reference from the Sales in August
• Finally, click OK

Finally, you’ll see that the output from cells D5:D11 is in consolidated form in one sheet.

Similar Readings

• Consolidate Data from Multiple Columns in Excel (7 Easy Ways)
• How to Use Grouping and Consolidation Tools in Excel (5 Easy Examples)
• Consolidate Function for Text Data in Excel (with 3 Examples)
• How to Remove Consolidation in Excel (2 Handy Methods)
• Consolidate Data from Multiple Rows in Excel (4 Quick Methods)

2. Utilizing VLOOKUP Function to Consolidate Two Sheets into One in Excel

You can use the VLOOKUP function when you need to consolidate data from multiple sheets into one sheet without using any SUM function or any other functions. This function helps to show data in one sheet but individually. To do this you can follow the steps below.

• Firstly, you can make a sheet like this defining places for individual sales and final consolidated sales.

Secondly, in the D5 cell put the formula below.

B5 is the lookup value for Apple here and Sales in July’!B4:D11 is the table array where B4:D11 is found in Sales in July which is the name of the sheet, 3 is the number of the column where we want the data, and FALSE indicates an exact match.

Thirdly, after pressing ENTER, you’ll find the output as 2540. Using Fill Handle you’ll find all the data from cells D6 to D11.

You need to put the same formula in the E5 cell like this.

Here, the only change is the table array which is Sales in August’!B4:D11 that means this time values are entered from Sales in August sheet.

Eventually, press ENTER to get the output as 6197 and then use Fill Handle to get all the data from cells E6 to E11.

Finally, use the SUM function to add values to consolidate sheets. Put the below formula into cell F5.

Here, D5 & E5 are Sales of Apples in July & August respectively.

Similarly, by pressing ENTER and then using Fill Handle you find the output below.

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

3. Using INDEX and MATCH Functions

The objective of using INDEX and MATCH functions is the same as using VLOOKUP functions.

You need to write in cell D5 like this to use this formula.

B5 is the lookup value for Apple here and  Sales in July’!B5:B11 is the lookup array, 0 indicates an exact match, and Sales in July’!D5:D11 is the reference for the return range.

Secondly, press ENTER and use Fill Handle to get all the data in D5:D11 cells.

Similarly, using INDEX and MATCH functions for cell E5 and SUM function for cell F5 and then using Fill Handle individually you’ll get the output like below.

Read More: How to Consolidate Data in Excel from Multiple Workbooks (2 Methods)

Things to Remember

• You can use the VLOOKUP function and the combination of INDEX & MATCH functions for the same objective.
• While using the Consolidate feature you don’t need to apply another SUM function or any other functions to consolidate.
• More importantly, you should be careful about the absolute cell reference. Sometimes, you may find that the table array (e.g. B4:D11 cells of Sales in July sheet) is changing automatically. In that case, you need to use the Dollar ($) sign manually or press the F4 key after selecting the cells in the formula bar.

Conclusion

By studying this article you will definitely learn the methods to consolidate two sheets into one. For any further queries, please feel free to visit our official Excel learning page ExcelDemy.

Related Articles

• Consolidate Multiple Worksheets into One PivotTable (2 Methods)
• How to Consolidate Information in Excel (2 Simple Ways)
• Create a Linked Consolidation in Excel (2 Useful Methods)
• How to Build a Static Consolidation in Excel (2 Suitable Examples)
• Data Validation and Consolidation in Excel (2 Examples)