As an Excel power user one of my favorite tricks is setting up automatic updates between worksheets. Imagine you have key data points scattered across multiple sheets that all need to sync up. Instead of manually copying and pasting values between sheets whenever something changes you can automate this tedious process!
In this post I’ll walk through exactly how to automatically update one Excel worksheet from another with some handy formulas and links. This will change your spreadsheet game and make your models seamlessly pull data from any sheet in any workbook. Let’s get to auto-updating!
Step 1: Open Your Worksheets
First things first – open up the Excel workbooks containing the sheets you want to connect. For this example, I have:
- Workbook 1 with a ‘Sales Data’ sheet tracking daily revenue by product.
- Workbook 2 with a ‘Dashboard’ sheet summarizing key metrics.
My goal is to auto-populate the revenue figures from the ‘Sales Data’ sheet into my ‘Dashboard’ for real-time reporting.
![Open Excel workbooks][]
Having both sheets handy in separate windows will allow us to set up the connecting formulas.
Step 2: Select the Data Source
Now we’ll select the cell range in the source worksheet containing the latest data we want to link into the other sheet.
In my ‘Sales Data’ sheet, I’ll select cells A2:B6 containing my daily revenue by product figures. This is the data set I want to relay to my ‘Dashboard’.![Select data source range][]
Try to keep your source range relatively simple – no formulas or merging cells. Just the raw figures you need extracted.
Step 3: Copy the Formula
With our source data selected, head up to the formula bar and copy the full cell reference. This will look something like: ='[Workbook1.xlsx]Sales Data’!A2:B6
This references those exact cells from the source sheet. We’ll need this to paste into the destination sheet.![Copy cell reference][]
You definitely want to copy-paste this reference rather than typing it out manually. Much easier!
Step 4: Paste to the Destination
Now flip over to the destination worksheet – in my case the ‘Dashboard’ from Workbook 2. Select the area where you want the extracted data to be populated. Make sure it’s the same size as your source selection.
With the target cells selected, right click and select Paste Special.![Paste special][]
This opens up additional paste options. Select Paste Link and the cell reference will be linked into the destination sheet.![Paste link][]
The revenue figures now get dynamically pulled from the source worksheet! Super cool automation!
Step 5: Add Criteria (Optional)
The examples above simply link all the source data cells into the destination. But you can get more advanced by filtering specific criteria.
For instance, I can tweak my formula to only pull ‘Book’ revenue figures from the source data: =FILTER(‘Sales Data’!A2:B6,’Sales Data’!A2:A6=”Book”)![Add criteria][]
The FILTER function lets me specify which rows to include based on a logical test. This adds another layer of automated connections!
Step 6: Refresh Automatically
Currently the linked values only update when you manually trigger a recalculation. To enable automatic refreshing:
- Go to File > Options > Formulas.
- Under Workbook Calculation, select Automatic
This makes the linked values recalculate every time the source sheet changes – automating the entire process!![Automatic calculation][]
No more manual refreshes needed! The destination sheet will stay up-to-date.
Step 7: Save Your Workbooks
Finally, don’t forget to save your Excel workbooks once you’ve finished setting up the links!
I like to save source workbooks with the actual data as .xlsx files. For any destination summary sheets, I use .xlsm macro-enabled files. This allows me to easily add more automation like triggers, notifications, etc.
But the basic file format doesn’t matter as long as you save both the source and destination workbooks to retain the formulas.![Save both workbooks][]
Awesome! Now your cross-sheet formulas and links will work seamlessly next time the files are opened.
Customizing Your Spreadsheet Linkage
The steps above provide a simple automation workflow between 2 sheets. Here are some additional tips for customizing and extending your worksheet connections:
- Reference multiple sources into one destination using multiple
FILTERformulas. - Use dynamic named ranges so your references auto-adjust when new data is added.
- Include IF statements in your linked formulas to handle errors gracefully.
- Build a relational data model with VLOOKUPs and INDEX/MATCH rather than direct cell links.
- Use PivotTables for powerful aggregation transforms on extracted data.
- Enable macros to code routines for refreshing the links on demand.
- Add buttons to trigger the refresh macros with one click.
As you can see, connecting Excel sheets can evolve from simple to quite advanced. Start with the basics above and then explore customizations as needed for your use case.
The flexibility of spreadsheets lends itself perfectly to workflow automation. Hopefully these tips help you eliminate repetitive manual copy-paste tasks between your Excel worksheets. The potential time savings are massive, allowing you to focus on data analysis vs. data wrangling.
