If you are looking for some of the easiest ways to combine sheets in Excel, then you are in the right place. By following the procedures of this article, you will be able to combine data of multiple sheets into a single sheet easily.
Download Workbook
6 Ways to Combine Sheets in Excel
Here, we have three sheets named January, February, and March containing sales records of these months. In these data tables, the first two columns are the same for all of the sheets but the only difference is in the value of sales.
For creating the article, we have used Microsoft Excel 365 version, you can use any other versions according to your convenience.
Method-1: Using Consolidate Option in Excel to Combine Sheets
Here, we will use the Consolidate option, to sum up, the sales value of the three months of the three sheets January, February, and March for each product.
Steps:
➤ Select the cell D5 (where you want to have the consolidated data)
➤ Go to Data Tab >> Data Tools Group >> Consolidate Option
Then, Consolidate wizard will open up
➤ Select Sum Option (or any other option as per your needs) in the Function box
After that, we have selected the data of the Sales column of the January sheet in the Reference box
➤ Click Add
In this way, the reference will be added up in the All references box
Similarly, repeat this referencing task for the other two sheets February and March
➤ Press OK
Finally, you will get the sales values summed up for each product.
Method-2: Using Excel VLOOKUP Function to Combine Sheets
Here, to combine the data of the three sheets we have created a new sheet named VLOOKUP where we have pasted the common data of the Product and Sales Person column and now we will combine the sales data from the three sheets by using the VLOOKUP function.
Steps:
➤ Select the output cell D5
➤ Type the following formula
=VLOOKUP(B5,January!B4:D11,3,FALSE)
Here, B5 is the lookup value Apple, January!B4:D11 is the table array where January! Is the sheet name, 3 is the column number of which we want the values and FALSE is for an exact match.
➤ Press ENTER
➤ Drag down the Fill Handle Tool
Then, you will get the sales record of January month from the January sheet in the January column.
Similarly, you can get the sales record for February and March by using the following formulas
=VLOOKUP(B5,February!B4:D11,3,FALSE)
=VLOOKUP(B5,March!B4:D11,3,FALSE)
Method-3: Using INDEX-MATCH Formula to Combine Sheets
In this section, we will use the INDEX function and the MATCH function to combine the sales data of the three sheets in a single sheet.
Steps:
➤ Select the output cell D5
➤ Type the following formula
=INDEX(January!D5:D11,MATCH(B5,January!B5:B11,0))
Here, B5 is the lookup value Apple, January!B5:B11 is the lookup array where January! Is the sheet name, 0 is for an exact match, and January!D5:D11 is the reference for the return range.
- MATCH(B5,January!B5:B11,0) → returns the row index number
Output → 1
- INDEX(January!D5:D11,MATCH(B5,January!B5:B11,0)) becomes
INDEX(January!D5:D11,1)
Output → $4,629.00
➤ Press ENTER
➤ Drag down the Fill Handle Tool
In this way, you will get the sales record of the January month from the January sheet in the January column.
Similarly, you can get the sales record for February and March by using the following formulas
=INDEX(February!D5:D11,MATCH(B5,February!B5:B11,0))
=INDEX(March!D5:D11,MATCH(B5,March!B5:B11,0))
Method-4: Using INDIRECT-ADDRESS Formula
You can use the INDIRECT function and the ADDRESS function to combine multiple sheets into one sheet.
Steps:
➤ Select the output cell D5
➤ Type the following formula
=INDIRECT("January!"&ADDRESS(ROW(D5),COLUMN(D5)))
- ROW(D5)→returns the row number of the cell D5
Output→ 5
- COLUMN(D5)→returns the column number of the cell D5
Output→ 4
- ADDRESS(ROW(D5),COLUMN(D5)) becomes
ADDRESS(5,4)
Output→$D$5
- INDIRECT(“January!”&ADDRESS(ROW(D5),COLUMN(D5))) becomes
INDIRECT(“January!”&”$D$5”)→INDIRECT(“January!$D$5”)
Output→$4,629.00
➤ Press ENTER
➤ Drag down the Fill Handle Tool
Then, you will get the sales record of January month from the January sheet in the January column.
Similarly, you can get the sales record for February and March by using the following formulas
=INDIRECT("February!"& ADDRESS(ROW(D5),COLUMN(D5)))
=INDIRECT("March!"& ADDRESS(ROW(D5),COLUMN(D5)))
Method-5: Using Excel Power Query to Combine Sheets
In this section, we will use Power Query to combine multiple sheets into one sheet. But prior to that, we need to convert the data ranges of different sheets into a Table or named range as the following figures.
For this purpose, we have created three tables of the three sheets named January_month, February_month, and March_month.
Steps:
➤ Create a new sheet (here, it is Power Query)
➤ Go to Data Tab >> Get Data Dropdown >> From Other Sources Dropdown >> Blank Query Option
After that, a blank Power Query Editor will open up.
➤ Write the following formula in the formula box
=Excel.CurrentWorkbook()
➤ Press ENTER
Then, the name of the tables will appear
➤ Select the double arrow sign in the Content column
After that, a dialog box will pop up.
➤ Select the columns you want to have in your sheet and unclick the option Use original column name as prefix
➤ Press OK
Finally, you will get the combined data of the three sheets in the following table.
If you don’t want to change the combined data of this table after refreshing then you have to follow some more steps.
➤ Select the filter sign of the Name column
➤ Follow Text Filters Group >> Ends With Option
After that, the Filter Rows wizard will open.
➤ Type _month (the common portion of each table’s name) in the box besides ends with Option
➤ Press OK
Now, the task is completed and you can close this editor.
➤ Go to Home Tab >> Close & Load Dropdown >> Close & Load Option
In this way, you will be able to combine the data of multiple sheets into one sheet-like below.
Related Content: How to Merge Columns in Excel (4 Ways)
Method-6: Using VBA Code
You can use a VBA code for combining multiple sheets into one. For this purpose, we have created a sheet named Combined where we will have the combined data.
Step-01:
➤ Go to Developer Tab >> Visual Basic Option
Then, the Visual Basic Editor will open up.
➤ Go to Insert Tab >> Module Option
After that, a Module will be created.
Step-02:
➤Write the following code
Sub combinesheets()
Dim firstRow, firstCol, LR, LC As Long
Dim headers As Range
Set x = Worksheets("Combined")
Set wb = ThisWorkbook
Set headers = Application.InputBox("Select the Headers", Type:=8)
headers.Copy x.Range("A1")
firstRow = headers.Row + 1
firstCol = headers.Column
Debug.Print firstRow, firstCol
For Each ws In wb.Worksheets
If ws.Name <> "Combined" Then
ws.Activate
LR = Cells(Rows.Count, firstCol).End(xlUp).Row
LC = Cells(firstRow, Columns.Count).End(xlToLeft).Column
Range(Cells(firstRow, firstCol), Cells(LR, LC)).Copy _
x.Range("A" & x.Cells(Rows.Count, 1).End(xlUp).Row + 1)
End If
Next ws
Worksheets("Combined").Activate
End Sub
Here, we have declared firstRow, firstCol, LR, LC as Long and set the sheet name Combined as x.
firstRow and firstCol are set as the first row and column of the data tables and then we have run the FOR loop for the other sheets except for the Combined sheet as we will combine our data in this sheet.
LR and LC will determine the last row and column of each sheet and then a range is formed using the firstRow, firstCol, LR, LC; in this way, this loop copies the data of each sheet for this range and pastes it into the Combined sheet.
Finally, we will activate this sheet to see the result.
➤ Press F5
After that, the Input wizard will open up where you have to select the headers from any of the sheets.
So, we have selected the headers from the March (3) sheet.
➤ Press OK
Afterward, you will get the combined data in the Combined Sheet
Related Content: How to Merge Multiple Sheets into One Sheet with VBA in Excel (2 Ways)
Practice Section
Here, we have provided a blank sheet named Practice where you can try the ways to combine the sheets here. Do it Yourself.
Conclusion
In this article, we have tried to cover the easiest ways to combine sheets in Excel effectively. Hope you will find it useful. If you have any suggestions or questions, feel free to share them with us.