Let’s say we have the following dataset in our source worksheet: Employee Name, Working Day, and Total Salary, and we want to automatically update any changes to this data in another worksheet in Excel. We can do this by using one of the six following methods.
Method 1 – Using the Paste Link Feature
The simplest way to connect and update one worksheet from another is to use the paste link feature in Microsoft Excel. Follow the steps below.
Steps:
- First, select the list from the table that you want to update in a new worksheet. Here we have chosen cell (B4:B13) and cell (D4:D13).
- Second, press CTRL+C to copy.
- Next, click the “New Sheet” icon below to create a new worksheet.
- In the new worksheet, choose a cell (B2) and click on the “Paste Link” under the “Paste” option.
- Your selected data will be copied into the new sheet.
- Let’s check if the data updates automatically or not. Return to the previous worksheet and select cell (D5:D7), then press DELETE on your keyboard.
- Now, if we go back to the new sheet, we will see the data is missing, which confirms an automatic update from the new sheet.
Read More: Transfer Data from One Excel Worksheet to Another Automatically
Method 2 – Utilize Exclamation Sign to Update Automatically
Sometimes, you might need to update one worksheet from another manually. To do so, use the exclamation sign (!) on your keyboard.
Steps:
- Select cells (B4:B13) and press CTRL+C to copy.
- Click SHIFT+F11 to open a new worksheet in the same workbook.
- Choose a cell (B2) in the new sheet and type the following formula:
=Employee!B4
- Press ENTER.
- Here, we will drag down the Fill Handle to fill all the cells.
- The column data will update from one worksheet to another sheet.
Read More: How to Link Two Sheets in Excel
Method 3 – Apply IF Function to Automatically Update Data Based on Criteria
The IF function allows you to update worksheets based on criteria being met and is another good method to update one worksheet from another in Excel automatically.
Steps:
- Select a cell (D5) and hit CTRL+C to copy.
- Click the “New Sheet” icon below to create a new worksheet.
- Inside the newly created sheet, choose a cell (B2) and apply the following formula:
=IF(Employees!D5>4000,Employees!D5,"")
- The IF Function will provide an output if the value in the cell Employees!D5>4000 is more than 4000. Otherwise, the cell will remain blank.
- Hit ENTER.
- You have now successfully updated the value from the previous sheet with the criteria.
Read More: How to Link Sheets in Excel with a Formula
Method 4 – Utilize Drop-Down List to Update the New Sheet Automatically
In the previous methods, we learned how to automatically update cells or columns from one worksheet to another. In this method, we will update the drop-down list from another sheet.
Imagine we have a drop-down list in our worksheet with employee names. Now, we will link this list from our source sheet with another sheet to update it automatically.
Steps:
- Create a new worksheet, select a cell (B2), and apply this formula:
='Employee List'!B4
- Press ENTER.
- Go back to the previous sheet and choose any name from the drop-down list. In our example, we selected “William.”
- In the newly created sheet, you will see the name is updated automatically.
Read More: Best Practices for Linking Excel Spreadsheets
Method 5 – Update Different Sheet Using Pivot Table Reference
Steps:
- Click on “Insert” followed by “Table” and select the list from a worksheet.
- Check the “My table has header” option and hit OK to continue.
- Select the table, click the “Table Design” option, and name your table.
- Create a new worksheet and type the following formula:
=Employees[#All]
- Hit ENTER, and your complete list will update from one worksheet to another within seconds.
Read More: How to Link a Table in Excel to Another Sheet
Method 6 – Using Power Query Feature
If you want to try another handy method to automatically update one worksheet from another sheet, try using the power query feature.
Step 1:
- Create a table from your dataset.
- Under “Data” select “From Table/Range”.
- A new window with the “Power Query Editor” will pop up.
- Hit the “Close & Load” option.
- The selected table will be created in a new worksheet momentarily. Let’s check whether it updates automatically or not.
Step 2:
- Select cells (C5:C13) and press DELETE on your keyboard.
- Switch to the newly created worksheet, and under “Data” click “Refresh All”.
- You have automatically updated one worksheet from another sheet in Excel.
Things to Remember
- In method 5, you won’t be able to use the pivot table reference without Excel 365.
Download this practice workbook to exercise while you are reading this article.
Related Articles
- How to Reference Cell in Another Excel Sheet Based on Cell Value
- Transfer Specific Data from One Worksheet to Another for Reports
- Linking Excel Sheets to a Summary Page
- How to Make Excel Look Like an Application
<< Go Back To Excel Link Sheets | Linking in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
how do I update the formatting (i.e. colors / fonts / etc) to the 2nd sheet ?
Follow Method 1. Paste Link Option. Just make a little bit of change. Select Paste Specials > Other Paste Options > Linked Picture.
Thanks- my colleague updates a spreadsheet daily. I have read only status. I would like to auto import update my sheet with the other. I only get read only access
If your workbook is shared, anyone who has Write privileges can clear the read-only status.
I’m looking for a way to take out very specific information from one spreadsheet to another.
I’ve got a file of products that I sell. The file comes directly from the wholesaler and I keep track of it all using *their* sku numbers. But one of the third-party selling sites I use only lets me list 500 items. Once I remove the other categories of things I don’t even work with, I’ve got over 2,500 items in the wholesale list. (Their initial list is over 9,000 products.)
I have an Excel file that I use specifically for that site with the 500 items listed. The wholesaler file also comes in Excel format. I want to be able to “update” the quantities on the 500 items that I listed, using the larger file from the wholesaler (they send it to me daily). But the wholesaler also doesn’t keep the same order for their list, so I can’t go by line number.
Is there a way I can have one sheet update from another one by searching and matching the SKU number?
Hello ERICA,
Here, I’m showing a way to solve your problem. I believe this will help you on this matter.
I’ve prepared a dummy file with 20 products. Assume it is the File from Wholesaler.
On the other hand, the following is your own Excel sheet that you maintain for the Site. For convenience, I kept the columns blank.
Here, I just gave some SKU code in Column B and the other data will be extracted from the File from Wholesaler. So, let’s see it.
• Firstly, open both files in Excel.
• Secondly, go to the File for Website.
• Now, select cell C5 and enter the VLOOKUP function.
=VLOOKUP(B5,
Here, B5 is the lookup_value that we want to search for.
• Then, move to the other workbook File from Wholesaler.
• Here, select the whole range of data. In this case, I selected data in the B4:E24 range. This is the table_array argument of the function.
• Afterward, we want to know the name of the Product corresponding to this code. And the Products are in the 2nd column of this table array. So, we wrote down 2 as col_index_num.
=VLOOKUP(B5,Wholesaler.xlsx!Product,2)
• Following this, press ENTER.
You can see the result in cell C5.
• Thenceforth, double-click on the Fill Handle.
• And get the full result in the following cells.
You can retrieve the value in other columns in the same method. Just you have to change the col_index_num in the formula. See how we wrote the formula to get the value of Sales.
=VLOOKUP(B5,Wholesaler.xlsx!Product,4)
So, I think this would be enough to help you. Otherwise, you can go through the article How to Link Two Workbooks in Excel to do the same task in multiple ways. Please follow our website, ExcelDemy, a one-stop Excel solution provider, to explore more.
PERFECT! THANK YOU! This just saved me HOURS of doing everything manually, one item at a time!
It’s my pleasure. I’m happy to be of service.