Retrieve values from many workbooks

Retrieve values from many workbooks

7 April 2017

This article explains how to consolidate data from numerous Excel files using Power Query.

The situation:

  • A folder contains hundreds of Excel files.
  • New workbooks can be added to the folder, or some workbooks can be removed.
  • Workbooks do not contain the same number of worksheets.
  • Worksheets may be added or removed at any time.
  • There is no naming convention for the worksheets.
  • Each worksheet may contain one or more values to retrieve.
  • The data needs to be consolidated often.

The solution: Use Power Query to import and consolidate the data automatically.