There are multiple ways to pull data from one Excel spreadsheet to second Excel spreadsheet. It is up to Excel file author to decide which way to use. Present here is a way to pull data through external source data. External data source gives users control on when to update External data.

Steps to setup Excel as external data source

  1. Create an Excel data source file (use here for demonstration) 001 - Source Excel file
  2. Go to “Data” tab and select “From Other Sources” and click “From Microsoft Query”002 - Select from other Sources in Destination Excel
  3. Select “Excel Files”  in “Databases” tab003 - Select Excel Files
  4. Select source Excel file004 - Select Source Excel File
  5. Select all options in “Table Options”. If some options are not checked, it may should show up in the “Available tables and columns” area005 - Select Sheet and columns to import
  6. Filter data if required.006 - Fine Tune Data with Filter
  7. Sort Data as required007 - Fine Tune Data with Sort
  8. Select “Return Data to Microsoft Excel”008 - Import Data to Destination Excel
  9. Select location for result data009 - Enter destination locaiton
  10. Data are displayed on destination Excel spreadsheet010 - Data Imported

That is it, powerful but easy.