How to combine data from multiple sheets

by | Apr 19, 2018 | Excel Macros, Excel Tips & Tricks

How to combine data from multiple sheets 1

Although keeping the data on different sheets help organizing them in terms of look and feel, this approach may cause problems when you need to get data from all sources. VLOOKUP or INDEX functions can't really help here, and other methods to combine data can be challenging.

However; there is simple and better alternative for users with Excel 2010 and later: Power Query. This powerful tool has many tricks under its sleeve and is good place to start to get familiar with Microsoft Power Query (another very useful tool for manipulating data).

For download link and basics please see Power Query 101

Download Workbook

Steps

Let's say we have employee records that are separated into different sheets based on years. There are 7 sheets from 2011 to 2017, and we need to combine data in a single sheet.

  1. Begin by converting your data into Excel Tables by clicking INSERT > Table or pressing Ctrl + T. Excel automatically detects the entire table range if you've selected any cells in it.
  2. Next, click POWER QUERY > From Table/Range    
  1. Power Query window will pop up with your table data. This is the main page of Power Query where you can manage the data.
  2. Give the table a name and click Close & Load to create your first Power Query table.
  3. The previous step will create a new sheet in your workbook. The new worksheet will contain your queried data which is in the same format as the source, because there hasn't been any changes yet. To remove duplicate sheets and keep the connection, right click the connection on the right menu and click Load To.
  4. Select Only Create Connection and click Load. This step will clear  the new sheet but you will be able to see the connection on the right menu
    .
  5. Repeat steps 2 to 6 for all your sheets. After adding all sheets you need to consolidate by clicking POWER QUERY > Append.
  6. Next pop up window asks whether you want to append tables. Select the Three or more tables option and move all your tables to Tables to append section by selecting tables and clicking Add >> Click OK to proceed.
  7. You will see all your data combined in the Power Query window.
  8. Last step is to click Close & Load one more time to move the consolidated data into your workbook.

Note: Final data table is dynamically connected to your source sheets. When you update the source sheets, click Refresh All icon under DATA tab in the ribbon.