How to Automatically Update One Worksheet from Another Sheet in Excel

Provided that you have prepared a worksheet on your workbook and you want to utilize it in another sheet. In this situation, you can unconsciously use a copy-pasting tool. But it will not update automatically if any change happens in the main sheet or first sheet. In that case, what should you do? Is it looking difficult to update one worksheet from another sheet in Excel automatically?

In this article, I’ll demonstrate how to update one worksheet from another sheet in Excel automatically.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

6 Quick Methods to Automatically Update One Worksheet from Another Sheet in Excel

In the following, I have shared 6 simple and easy methods to automatically update one worksheet from another sheet in Excel.

Suppose we have a dataset of some Employee Name, Working Day, and Total Salary in a worksheet. Now we will automatically update this data to another worksheet in Excel. Stay tuned!

1. Using Paste link Feature

The simplest way to connect and update one worksheet from another is by using the paste link feature in Microsoft Excel. You just have to copy the cell or list and open a new sheet paste using the paste link. That’s it. Follow the instructions below-

Steps:

• First, choose the desired list from the table which you want to update to a new worksheet. Here I 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.

• Thereafter, in the new worksheet choose a cell (B2) and press the “Paste Link” feature from the “Paste” option.

• Thus, the chosen data will be pasted inside the new sheet.

• Now, let’s check if the data updates automatically or not. In order to do so, come back to the previous worksheet select cell (D5:D7), and click DELETE from the keyboard.

• After that, if we go back to the new sheet we will see the data is missing inside the new sheet which confirms an automatic update from the new sheet.

Read More: How to Copy a Cell in Excel Using Formula(7 Methods)

2. Utilize Exclamation Sign to Update Automatically

Sometimes you might need to update one worksheet from another manually. For that, you can use the exclamation sign (!) from your keyboard.

Steps:

• Let’s start with, choosing cells (B4:B13) and pressing CTRL+C to copy.

• Now, click SHIFT+F11 to open a new worksheet in the same workbook.

• Hence, in the new sheet choose a cell (B2) and type the following formula down-

• To finish, press ENTER.
• Here, we will drag down the “Fill Handle” to fill all the cells.

• In summary, we will get the column data updated from one worksheet to another sheet.

Read More: Copy and Paste Multiple Cells in Excel (7 Quick Ways)

3. Apply IF Function to Automatically Update Based on Criteria

What if I tell you you can update data from one worksheet to another based on criteria? Well, it might seem impossible but it’s not. Using the IF function you can update the condition from one worksheet to another at the same time.

Steps:

• Presently, choose a cell (D5) and hit CTRL+C to copy.

• Similarly, click the “New Sheet” icon below to create a new worksheet.

• Inside the newly created sheet, choose a cell (B2) and apply the formula down-

Where,

• The IF Function will provide an output if the value in the cell –Employees!D5>4000 is more than 4000. Otherwise, it will display blank inside the cell.

• Gently, hit ENTER.
• As you can see we have successfully updated the value from the previous sheet with the criteria. Simple isn’t it?

Read More: Copy and Paste in Excel Using VBA (7 Methods)

Similar Readings

• Paste Comma Separated Values into Excel (in Different Orders)
• Copy a Worksheet in Excel (5 Smart Ways)
• How to Protect Excel Sheet from Copy-Paste (4 Effective Ways)
  
• How to Paste a List of Emails into Excel (2 Easy Ways)
• [Fixed!] Paste Special Not Working in Excel (4 Possible Solutions)

4. Utilize Drop-Down List to Update Automatically into New Sheet

In the previous methods, we learned how to automatically update cells or columns from one worksheet with another. In this method, we will update the drop-down list with another sheet.

Imagine we have a drop-down list in our worksheet with some Employee Name. Now, we will utilize the list with another sheet so that it updates automatically.

Steps:

• Similar to the previous methods, create a new worksheet and select a cell (B2) and write the formula down-

• Simply, press ENTER.
• Thereafter, coming back to the previous sheet, choose any name from the list. Here I have chosen “William” from the drop-down list.

• Thus, in the newly created sheet, you will see the name is updated automatically.

5. Update Different Sheet Using Pivot Table Reference

For this method, you will need Microsoft 365. In this method, I will show you how you can update one worksheet from another using the pivot table reference.

Steps:

• First, choosing the list from a worksheet presses the “Table” feature from the “Insert” option.

• Just checkmark the “My table has header” and hit OK to continue.

• Within a blink of an eye, our table will be created. Now again selecting the table go to the “Table Design” option and provide a name for the table.

• In the same fashion, create a new worksheet and type the following formula below-

• Hence, hit ENTER and here we have our total list updated from one worksheet to another within seconds.

6. Using Power Query Feature

Well, if you want you can also utilize the power query feature to automatically update one worksheet to another sheet. This is quite handy as it updates and refreshes automatically. Below I have explained the processes-

Step 1:

• Just like the previous method, create a table from the dataset and select “From Table/Range” from the “Data” feature.

• A new window will pop up named “Power Query Editor”.
• Then, hit the “Close & Load” option from the left corner.

• The selected table will be created within a new worksheet within a moment. Let’s check whether it updates automatically or not.

Step 2:

• Choose cells (C5:C13) and press DELETE from the keyboard.

• Without wasting time, let’s visit the newly created worksheet and click “Refresh All” from the “Data” option.

• Finally, we 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. You can download it from this link.

Conclusion

In this article, I have tried to cover all the methods to update one worksheet from another sheet in Excel automatically. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.

Related Articles

• How to Copy Visible Cells Only in Excel (4 Fast Ways)
• Copy the Same Value in Multiple Cells in Excel (4 Methods)
• How to Copy and Paste in Excel Without Changing the Format
  
• How to Use Paste Name Dialog Box In Excel (3 Ideal Examples)
• How to Enable Paste Option in Excel (3 Suitable Ways)
• How to Copy WhatsApp Group Contacts to Excel (with Easy Steps)