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-
- 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.
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.
- 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.
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.
- 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-
- 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?
- Copy a Worksheet in Excel (5 Smart Ways)
- How to Protect Excel Sheet from Copy-Paste (4 Effective Ways)
- Copy and Paste in Excel Using VBA (7 Methods)
- How to Copy a Cell in Excel Using Formula(7 Methods)
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.
- 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.
- 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-
- 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.
- 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.
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.
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?
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.
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.
• 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.
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.