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
- Create an Excel data source file (use here for demonstration)
- Go to “Data” tab and select “From Other Sources” and click “From Microsoft Query”
- Select “Excel Files” in “Databases” tab
- Select source Excel file
- Select all options in “Table Options”. If some options are not checked, it may should show up in the “Available tables and columns” area
- Filter data if required.
- Sort Data as required
- Select “Return Data to Microsoft Excel”
- Select location for result data
- Data are displayed on destination Excel spreadsheet
That is it, powerful but easy.