Excel workbooks

Question

I have excel workbook which has multiple tabs within. I have a number of these workbooks. I need to merge the data and information from each workbook and then merge the multiple workbooks into 1 excel workbook.

What is the best either AI tool or/& tool to use.

Kind regards,

Answer 1

Hi,

Watch this video - https://www.youtube.com/watch?v=yL11ugShdrk

Answer 2

Hi @Zahida Khan,

Thank you for posting your question in the Microsoft Q&A forum.

The suggestion provided by the other moderator to use Power Query is an excellent option, especially when your workbooks share a similar structure. As an alternative approach, you may also consider using Power Automate together with Office Scripts if you are looking to automate the consolidation process, particularly when new files are added regularly.

You can use a Power Automate flow to collect data from multiple workbooks and combine them into a centralized workbook. This approach can be useful when working with a large number of files or when you need a repeatable process that runs with minimal manual effort.

A few general recommendations:

• If all workbooks have the same column headers and similar worksheet layouts, Power Query remains the simplest and most robust solution.
• If the workbook structures vary and require additional processing, Power Query can still be used, but extra transformation steps may be necessary.
• If you need to automate the consolidation process across multiple files on an ongoing basis, Power Automate + Office Scripts may be worth considering.

You may refer to the following article for more details:

• Combine workbooks into a single workbook - Office Scripts | Microsoft Learn 

Thank you for your patience and understanding. I look forward to your update.

---

If the answer is helpful, please click"Yes". If you have extra questions about this answer, please click"Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread 

Answer 3

Use Excel's Power Query. It can import data from multiple worksheets across multiple workbooks, combine them into a single dataset, and load the results into one workbook. If your worksheets follow the same structure (same column names and layout), the merge is largely automatic and can be refreshed whenever the source files change.

If the worksheets have different structures, Power Query can still combine them, but you may need to transform or map columns before merging. For more complex scenarios, such as consolidating formulas, formatting, charts, or VBA code, a VBA macro may be a better option.

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin