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

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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.

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

`=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.

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.

`=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.

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.

`=SUM(D5:E5)`

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.

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

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.

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

#### Shajratul Alam Towhid

Hello, myself Shajratul Alam Towhid. Basically, I am a Naval Architect who wants to expand knowledge in the field of Microsoft Excel. I wish all of my articles will be beneficial for the readers.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF