How to Link Two Excel Workbooks and Keep Data Updated Automatically

You can link two Excel workbooks so one file shows selected data from a master workbook. This tutorial uses an Excel table and the FILTER function to keep a separate active employee list updated.

This works best when both files stay in the same folder and the master workbook is available when the second workbook refreshes.

Turn the master data into an Excel table, then use FILTER in the second workbook to pull only the rows you need. In this example, the second workbook shows employees where Status equals Active. Save both files in the same folder and use Refresh All if the linked data does not update.

The master workbook contains an employee database with Employee ID, Name, Status, Job Title, Department, Hire Date, and Hourly Pay Rate.

The second workbook contains the same column structure, but it only displays active employees. Payroll or department leaders can use that smaller file without filtering the full employee list.

Use a version of Excel that supports the FILTER function.

Keep both workbooks available. The linked workbook needs access to the master workbook to refresh correctly.

Use a clear Status column. In this example, statuses include Active, On Leave, and Terminated.

Open the workbook that contains the full employee database.

Check that each column has a header. The example uses Employee ID, Name, Status, Job Title, Department, Hire Date, and Hourly Pay Rate.

Make sure the Status values are consistent. The filter will only return rows that match the exact status you choose.

1. Click anywhere inside the employee dataset.
2. Press Ctrl + T.
3. Confirm that My table has headers is selected.
4. Click OK.
5. Rename the table. The employee example names it masterE.

Open a new workbook for the active employee list.

Set up the same column structure as the master workbook. This lets the filtered results line up with the expected fields.

Select the first cell where the linked data should appear.

Use FILTER to return rows from the master workbook where the Status column equals Active.

If the exact workbook and table references are hard to read, build the formula by selecting the source table and source Status column with your mouse. Excel will insert the external workbook references for you.

=FILTER(source_range, status_range="Active")

The FILTER result should spill into nearby cells. If the formula only returns one value, check whether Excel added fixed references that stop the spill.

Copy the formula across the needed columns only if the workbook setup requires one formula per output column. Make sure each formula still filters by the Status column.

Apply the right format to each output column after the data appears.

In the employee example, the Hire Date column is formatted as a date, and the Hourly Pay Rate column is formatted as currency.

1. Filter the master workbook to Active employees and count the records.
2. Count the records in the active employee workbook. The counts should match.
3. Change one employee from Active to On Leave. That employee should disappear from the active employee workbook.
4. Add a new employee to the master table and set the Status to Active. The new employee should appear in the active employee workbook.
5. Delete a test employee from the master table. The employee should also disappear from the linked list.

If the active list does not update, open the Data tab and choose Refresh All.

Keep both workbooks in the same folder when possible. Moving or renaming files can break external workbook links.

If new employees do not appear, make sure the master data is still an Excel table and the new row is inside that table.

If inactive employees appear, check the Status spelling and the FILTER condition.

Linked workbooks depend on file paths. If someone moves a workbook, renames it, or sends only one of the files, the link may stop working.

This method is useful for a controlled Excel workflow. It is less reliable when many people download, rename, and resend versions of the same file.

This setup works well when a small team controls both files. It becomes harder when people regularly download reports, rename files, move them between folders, combine several systems, or depend on someone remembering to refresh the workbook.

At that point, the real problem is not the formula. The problem is the manual process around the files.

A more automated workflow can pull data from the source system, apply filtering rules, validate records, keep a central dataset updated, and send only exceptions to staff.

If your team is manually updating or linking the same files every week, I can review the workflow and identify which parts can run automatically.