Mrinmoy Roy
Excel offers an inbuilt function to pull data from multiple worksheets into a master worksheet. While consolidating data from multiple worksheets, you can perform various numerical operations such as addition, multiplication, subtraction, division, etc. But one pitfall regarding this feature is that it’s applicable only to numerical values. With that being said, in this article, you will learn 3 different methods to consolidate data in Excel from multiple worksheets with ease.
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
3 Ways to Consolidate Data in Excel from Multiple Worksheets
I will be using the following 3 demo monthly sales report to consolidate data in Excel.
These sales reports reside on 3 different worksheets.
The first worksheet, January, has the sales report for the month of January.
The second sales report is for the month of February.
And the third sales report is for the month of March.
We will consolidate data from these 3 worksheets into a master worksheet. So without having any further discussion, let’s dive straight into them.
1. Use the Consolidate Button to Combine Data from Multiple Worksheets in Excel
In this method, you will learn to use the Consolidate button to consolidate data from multiple worksheets in Excel. For that, all you need to do is,
❶ Copy the table format from any one of the other worksheets from where you want to consolidate the data.
❷ After that, select the very first blank cell of the copied table format.
❸ Then go to the Data tab.
❹ From the Data Tools group, choose the Consolidate option.
The Consolidate dialog box will appear.
❺ Select any function name from the Function box. I’m selecting Sum for this instance.
❻ In the Reference box, click on the up arrow icon.
This will allow you scopes to insert a cell range from where you want to pull data.
❼ Select the cell range of all the data tables. For this, select the range of the first data table and then click on the Add button. Then select the second data table and so on.
❽ In the “Use labels in” box, check the Left column option to add the left column of your numerical data.
If you want to create a link between your source data table and your destination data table, then check the Create links to source data option. This will automatically update the destination data table if you change anything in the source data table.
❾ After that hit the OK button.
Now you will see all the data from the 3 source data tables have consolidated into the final data table called, “Quarterly Sales Report”.
Read More: How to Combine Data from Multiple Sheets in Excel (4 Ways)
2. Use VBA Code to Consolidate Data from Multiple Worksheets in Excel
If you are looking for VBA codes to pull data from multiple worksheets into one worksheet, then go through the following steps.
❶ First of all, press ALT + F11 to open the VBA editor.
❷ Then go to Insert > Module to create a new module.
❸ After that copy the following VBA code:
Sub PullData()
Dim x1 As Long
Dim XRng1 As Range
On Error Resume Next
Worksheets.Add Sheets(1)
ActiveSheet.Name = "Consolidated Data"
For x1 = 2 To Sheets.Count
Set XRng1 = Sheets(1).UsedRange
If x1 > 2 Then
Set XRng1 = Sheets(1).Cells(XRng1.Rows.Count + 1, 1)
End If
Sheets(x1).Activate
ActiveSheet.UsedRange.Copy XRng1
Next
End Sub❹ Now paste and save the above code in the VBA editor that you have already opened.
❺ Now press the F5 button to run the VBA code.
After pressing the F5 button, you will see data from all the worksheets of your open workbook have been combined into another worksheet named “Consolidated Data”.
Read More: How to Merge Multiple Sheets into One Sheet with VBA in Excel (2 Ways)
Similar Readings
- How to Merge Multiple Excel Files into One Sheet (4 Methods)
- Combine Sheets in Excel (6 Easiest Ways)
- How to Combine Rows in Excel (6 Methods)
- Combine Columns into One List in Excel (4 Easy Ways)
- How to Merge Columns in Excel (4 Ways)
3. Use Power Query to Combine Data from Multiple Worksheets in Excel
If your worksheets contain Excel tables, then you can use Power Query to pull data from those Excel tables.
Now, to see the usage of Power Query follow the steps below:
❶ First of all, go to the Data tab.
❷ Then navigate to Get Data > From Other Sources > Blank Query.
❸ Go to the Home tab of the Power Query Editor.
❹ In the formula bar, insert the following formula:
=Excel.CurrentWorkbook()❺ Then hit the ENTER button.
Now all the tables in the current workbook will appear in a list.
❻ Double-click on the double arrow icon to see all the columns available to combine.
❼ Select your desired columns. For this instance, I’m selecting all the columns.
❽ Uncheck “Use original column name as prefix”.
❾ Finally hit the OK button.
After that you will get all the data consolidated into one worksheet as in the picture below:
Read More: Combine Multiple Excel Files into One Workbook with Separate Sheets
Things to Remember
- Excel consolidation works only for numerical values.
- Press ALT + F11 to open the VBA editor.
- To run the VBA code, press the F5 key from your keyboard.
Conclusion
To sum up, we have discussed 3 ways to consolidate data in Excel from multiple worksheets. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.
