How to combine worksheets data using Power Query in Excel. No VBA required.
Often we need to bring together data from multiple worksheets for further analysis. This data could be data of different months of sales or expenses. The user may want to create a report from data in cells in many worksheets by creating a new master worksheet.
Watch the video below to learn how assemble data from different worksheets without the use of VBA:
Let’s see how this is done. We have sales data from Quarter1 to Quarter4 in four Excel worksheets. We have about 50 rows of data in each worksheet.
We click the Data Tab and then click on ‘Get Data’, ‘From Other Sources’ and finally we select ‘Blank Query’.
The Power Query Editor opens. In the formula bar we write ‘=’ and select ‘Excel.CurrentWorkbook’ and place a bracket ‘()’ after the formula. When we press enter we get two columns with the headers ‘Conent” under which 4 tables are listed and another column with the header ‘Name’ with the four items ‘SalesQ1’, ‘SalesQ2’, ‘SalesQ3’ and ‘SalesQ4’ listed under it. This represents the four worksheets whose data is arranged in tables with the names ‘SalesQ1’, ‘SalesQ2’, ‘SalesQ3’ and ‘SalesQ4’.
Now we click on the ‘double outward pointing arrow’. A new window pops up. We deselect the checkbox ‘Use original column names as prefix’. Before clicking on ‘OK’ we click on the ‘Load More’ link to check whether any more columns are present in our tables.
Now a new table opens up with the headers ‘Product’, ‘Amount’ and ‘Store’. Below these columns all the data from the worksheets is also present. The fourth column with the header ‘Name’ has all the table names from which the data has been brought to this query.
Now we right-click on the ‘Name’ column and select ‘Remove’. The ‘Name’ column is removed.
On the right-hand side of the screen under the heading ‘Applied Steps’ you will see a list of actions done by the user.
We now observe the headers ‘Product’, ‘Amount’ and ‘Store’ and note that their data type is ‘text’. We want the data type for ‘Amount’ to be a ‘whole number’. So we right-click the header ‘Amount’ and from the displayed options we select ‘Whole Number’.
Next we click on ‘Group By’ in the Ribbon, select the option button ‘Basic’, select Product from the drop-down menu below it, give the ‘New column name’ as ‘TotalSalesByProduct’, select ‘Sum’ under ‘Operation’ and under the ‘Column’ header’ we select ‘Amount’. Now we are left with two columns in our query window with the column headers ‘Product’ and ‘TotalSalesByProduct’.
We can next transfer this data to our worksheet by clicking ‘Close and Load To…’ in the ‘Home’ tab. In the ‘Import Window’ that fires up we check the option buttons next to ‘Table’ and ‘New worksheet’. There are other options like ‘Pivot Table’, ‘Pivot Chart’, etc also available for our use. Once we click ‘OK’ we get our data into a new worksheet called Sheet1. We can rename Sheet1 to TotalSalesByProduct.
On the right-hand side of the screen under the header ‘Queries & Connections’ we notice that our query name is ‘Query1’. We click on the white icon to the right of the query name and in the Quer1 window that pops up we select ‘Edit’ in the footer. The Power Query Editor window opens. On the right hand side under ‘Properties’ and ‘Name’ we can change the name of our query from ‘Query1’ to ‘TotalSalesByProduct’. Now we can click on ‘Close & Load’ to come back to our worksheet ‘TotalSalesByProduct’.
If we now edit our data in the other worksheets, come back to our ‘TotalSalesByProduct’ worksheet and click on ‘Refresh All’ our data will be updated.
In this way we have combined the data from multiple worksheets into one master worksheet and have also calculated the total sales of each product in the four quarters by using the ‘Group’ feature of the Power query.