How to Link Data Between Two Excel Workbooks (Auto-Update a Second Spreadsheet)
If you’ve ever maintained an employee list in Excel, you know how quickly version control becomes a problem.
🎥 Watch the Video Tutorial:
Before diving into the steps below, you can follow along with my full video walkthrough here. It shows each step in real time so you can see exactly how the formulas and links work.
How to Link Data Between Excel Files (Auto-Update Second Workbook)
HR keeps the master employee spreadsheet… payroll wants an active employee list… department leaders want only their employees… and before you know it, there are multiple copies of the same data floating around — and none of them match.
In this tutorial, I’ll show you how to link data between two Excel workbooks so you can maintain one master employee spreadsheet, and a second workbook (like an Active Employee list) will automatically update whenever:
a new employee is added, or
an employee’s status changes (Active, On Leave, Terminated)
This method eliminates copy/paste updates and saves a ton of time.
What You’ll Build (Real-Life HR Example)
Workbook 1: Master Employee Spreadsheet
This file contains your full employee database with columns such as:
Employee ID
Name
Status
Job Title
Department
Hire Date
Hourly Pay Rate
Statuses can include: Active, On Leave, or Terminated.
Workbook 2: Active Employee List
This is a separate workbook you can share with payroll or department leaders. It will automatically display a running list of Active employees only.
Excel Skills Used in This Tutorial
Convert data into an Excel Table
Create external workbook links
Use the FILTER function to return only Active employees
Keep formatting clean in the second workbook
Bonus: prevent broken links and refresh external data
Step-by-Step: Link Two Excel Workbooks and Auto-Update Active Employees
Step 1: Open Your Master Employee Workbook
Open your master spreadsheet (example file name: Demo Employee Database 26).
This is the workbook HR will maintain as the source of truth.
✅ Tip: Before linking anything, confirm your “Status” column contains consistent values such as:
Active
On Leave
Terminated
Step 2: Convert the Master Data into an Excel Table
To make sure your list expands automatically when new employees are added, you must convert your data range into a table.
Click anywhere inside your employee data
Press Ctrl + T
Confirm the box is checked: My table has headers
Click OK
Excel will apply table formatting to your list.
If you already have your own formatting and want to keep it, you can remove the default table style afterward.
Step 3: Name the Employee Table
Naming your table makes formulas much easier to manage.
Click inside the table
Go to the Table Design tab
Find the field labeled Table Name
Rename it to: MasterEE
Now your master employee data is stored in a table called MasterEE.
Step 4: Create the Active Employee Workbook
Next, open a new workbook (or an existing template) that will become your Active Employee List.
You can format the headers to match the master workbook, but the key is that this workbook will pull data automatically.
Step 5: Use FILTER to Pull Only Active Employees
In the Active Employee workbook, click in the first cell where you want the results to begin (example: A2).
Then enter a FILTER formula that:
pulls the full data table, and
includes only rows where Status = Active
Here is the format:
=FILTER('[Demo Employee Database 26.xlsx]MasterEE'[#Data], '[Demo Employee Database 26.xlsx]MasterEE'[Status]="Active", "")What this does:
[#Data] pulls only the employee rows (not the table headers)
[Status]="Active" filters the list to only Active employees
"" keeps the sheet clean if there are no results (instead of #CALC errors)
✅ Important: The FILTER formula will spill automatically and return all matching rows.
Step 6: Format the Output in the Active Employee Workbook
After the data spills into the second workbook, format the columns as needed:
Hourly Pay Rate (Currency)
Select the pay column
Choose Currency format
Hire Date (Date formatting)
Select the hire date column
Right-click → Format Cells
Choose Date → OK
This keeps your output workbook readable and professional for sharing.
Step 7: Validate That the Two Workbooks Match
A quick way to confirm everything is working properly:
In the master workbook, filter Status = Active
Select the Employee ID column
Check the count in the bottom status bar
Then repeat in the Active Employee workbook.
Step 8: Test the Auto-Update Link (Status Change)
Now for the fun part — proving it works!
In the master workbook, find an Active employee
Change their Status from Active to On Leave (or Terminated)
Go back to the Active Employee workbook
✅ That employee should disappear from the Active list automatically.
Step 9: Test Adding a New Employee
To test automatic expansion:
Scroll to the bottom of the table in the master workbook
Add a new employee record in the next blank row
Select Status = Active
✅ The new employee should immediately appear in the Active Employee workbook.
Shortcut: Insert Today’s Date
If you need to add today’s date quickly:
Press Ctrl + ; (Control + semicolon)
Bonus Tips (Recommended for Real-World Use)
Tip 1: Save Both Files in the Same Folder
To avoid broken links:
✅ Save both workbooks in the same folder before sharing.
This makes Excel’s file path links much more stable.
Tip 2: Refresh Linked Data If Needed
If your external links don’t refresh automatically:
Go to the Data tab
Click Refresh All
That forces Excel to update the external workbook links.
Watch the Full Video Tutorial: If you prefer to follow along visually, watch the video tutorial here
This is a simple but powerful way to link data between two Excel workbooks so you can:
✅ maintain one master employee spreadsheet
✅ automatically generate an Active Employee list
✅ eliminate copy/paste updates
✅ prevent version control issues
It’s perfect for HR workflows where multiple departments need access to subsets of employee data.
