Excel merges multiple worksheet data and implements synchronous update skills

How can data from multiple worksheets be merged into one table? Don't tell me you're still copying and pasting one by one. It's a waste of time. Here, I'd like to share with you a good way.

1. Table data that needs to be merged

As shown in the following figure, here I have prepared some random data to merge the multi-worksheet data into a single table.

2. PQ merges data

Here, we use the Power Query feature. Note that this feature is not available in the Excel2010~2013 version. You need to download the Power Query plug-in from the official website before you can use it, only in version 2016 or above.

First of all, we create a new workbook, and then go to "data"-"get and transform"-"New query"-"from File"-"from Workbook" to find our table storage path and import it.

02. We select the table, click "convert data" below, select the "Data" column, and click "manage columns"-"Delete columns"-"Delete other columns". Click the button next to "Data", uncheck "use original column name as prefix", and make sure.

03. At this point, we can see that there are "names" and "sales" between the merged data. How can we cancel them? Click "convert"-"use the first line as title". Then click the drop-down button of "name", find the "name" in it, and uncheck OK.

Finally, we click "close"-"close and upload"-"close and upload". Now we have merged all the worksheet data into one worksheet.

3. The merged table can update the data synchronously.

This is the power of Power Query! After we merge all the Sheet into one table, if we modify and add new content, or add more Sheet, our merged table will be updated!

Let's take a look at it. I'll add some content to the original table and save it.

02, back to the merged table, right-select any cell, click the "Refresh" button, see, the data is automatically updated!

This article comes from the official account of Wechat: Word Alliance (ID:Wordlm123), author: Wang Wangqi