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.

how to consolidate two excel sheets into one

And the Sales in August dataset is like this.

how to consolidate two excel sheets into one

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.

how to consolidate two excel sheets into one using Consolidate feature

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

how to consolidate two excel sheets into one using Consolidate feature

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

how to consolidate two excel sheets into one using Consolidate feature

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

how to consolidate two excel sheets into one using Consolidate feature

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

how to consolidate two excel sheets into one using Consolidate feature


Similar Readings


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.

how to consolidate two excel sheets into one using VLOOKUP function

Secondly, in the D5 cell put the formula below.

=VLOOKUP(B5,'Sales in July'!B4:D11,3,FALSE)

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.

how to consolidate two excel sheets into one using VLOOKUP function

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.

how to consolidate two excel sheets into one using VLOOKUP function

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

=VLOOKUP(B5,'Sales in August'!B4:D11,3,FALSE)

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.

how to consolidate two excel sheets into one using VLOOKUP function

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

how to consolidate two excel sheets into one using VLOOKUP function

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

=SUM(D5:E5)

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

 using VLOOKUP function

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

 using VLOOKUP function

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.

=INDEX('Sales in July'!D5:D11,MATCH(B5,'Sales in July'!B5:B11,0))

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.

Formula Breakdown:

  • MATCH(B5,’Sales in July’!B5:B11,0) → returns the row index number
    Output → 1
  • INDEX(‘Sales in July’!D5:D11,MATCH(B5,’Sales in July’!B5:B11,0)) becomes
    INDEX(‘Sales in July’!D5:D11,1)
    Output →
    2540

INDEX & MATCH functions

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

INDEX & MATCH functions

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.

INDEX & MATCH functions

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

Towhid
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo