copy live table data from multiple tabs

New Contributor

I want to have a tab in my spreadsheet that contains the data in columns in other tables on other tabs. For example, we have the same table on 5 tabs for each team with columns like title, status, percent complete and the like. I want a master table containing all the data from all 5 tabs / tables in one big table.   I'm sure I am making it harder than it is... please help?


Thanks for any help you can give! 

3 Replies



I am going to suggest that you reverse the process. Reverse the thinking behind your current design.


Start with a combined table that contains all of the data currently in five separate tabs, merely adding a column that differentiates or identifies which team the rest of the row refers to. You'd accomplish this right now by simply copying all of those tables onto a singe tab, adding the aforementioned column.


Once you've done that, it will be easy to extract the data for a given team, for reporting and status report purposes. You could also extract all completed projects (or whatever it is that is being tracked) for each team, sorted in whatever order.


This is very easy if you have the most current version of Excel, the version that contains the FILTER and other dynamic array functions. Here's a video that explains those functions.

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, bu...
best response confirmed by Grahmfs13 (Microsoft)


You may use Power Query to combine them and load result to master sheet.