How to Keep Selected Excel Columns Updated in Another File
Question details
The user needs to copy specific columns from a source Excel sheet to another file and have them update automatically when new records are added or changed, without exposing confidential information from the source file.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Sharing selective, dynamic data across different Excel workbooks while maintaining a live connection to the source.
- Observed behavior
- Standard copy-pasting or basic cell linking fails to dynamically include new rows and risks exposing hidden confidential columns to unauthorized users.
Ensure your source data is formatted as an Excel Table (press Ctrl+T) and that both files are saved in a stable, accessible location such as a local drive or shared cloud folder to prevent broken data connections.
Use Power Query to Import Specific Columns
Power Query allows you to extract specific columns from a source file securely, automatically fetching new rows and data updates without exposing the entire source file.
Power Query is the most robust method for this scenario. It creates a one-way data pipeline from your source workbook to your destination workbook. By applying transformation steps, you can filter out confidential columns entirely before the data ever loads into the destination file.
Open your destination Excel file. Navigate to the 'Data' tab on the ribbon, click on 'Get Data', choose 'From File', and then select 'From Workbook'. Browse to find your source file and click 'Import'.
In the Navigator window, select the specific sheet or table containing your source data. Instead of clicking 'Load', click 'Transform Data' to open the Power Query Editor.
In the Power Query Editor, hold the Ctrl key and click the headers of the specific columns you want to keep. Right-click any of the selected headers and choose 'Remove Other Columns'.
Click the 'Close & Load' button in the top-left corner. The selected columns will now be placed into a new table in your destination workbook.
Use Advanced Formula Linking with FILTER (Microsoft 365)
If you are using a modern version of Excel, dynamic array formulas like FILTER or CHOOSECOLS can link specific columns across workbooks.
Easily Link and Manage Data Across Files with WPS Office
WPS Spreadsheet provides powerful data connection tools and dynamic array formulas that make linking separate workbooks incredibly simple. You can efficiently pull specific columns, protect confidential information, and keep your data perfectly synchronized.
- 1. Create Your Destination File: Launch WPS Spreadsheet and open a new workbook where you want the updated data to reside.
- 2. Import External Data: Navigate to the 'Data' tab and select 'Import Data' to securely connect to your source workbook.
- 3. Select Specific Columns: Use the import wizard to specify exactly which columns you wish to retrieve, successfully excluding any sensitive or confidential data.
- 4. Configure Auto-Refresh: Adjust the connection properties within WPS Spreadsheet to ensure the linked columns automatically update whenever the source file changes.
Frequently Asked Questions
Why doesn't my linked Excel data update automatically when the source changes?
Data links might not update if your Excel calculation options are set to manual, if the source file location was moved or renamed, or if background refresh is disabled. You can manage these settings by navigating to Data > Queries & Connections > Properties and enabling automatic refresh.
Can I link Excel data without opening the source file?
Yes, using Power Query (Get & Transform Data) allows you to connect to an external workbook and refresh the data in your destination file without ever having to open the source file manually.
How do I ensure confidential columns aren't exposed when sharing the destination file?
By using Power Query to explicitly select and import only the columns you want to share, the confidential columns are completely left behind. When you share the destination file, users only have access to the imported data table, not the original source structure.
