How to Automatically Update One Worksheet from Another Sheet in Excel

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.

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.

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.

Method 5 – Update Different Sheet Using Pivot Table Reference

If you want to update one worksheet from another sheet using the pivot table reference, you will need Microsoft 365.

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.

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 Practice Workbook

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

Automatically Update One Worksheet from Another Sheet.xlsx

<< Go Back To Excel Link Sheets | Linking in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Sub UpdateDateInAllSheets() Dim ws As Worksheet Dim dateValue As Variant ' Change "Sheet1" to the name of the sheet where you will enter the date dateValue = ThisWorkbook.Sheets("Sheet1").Range("A1").Value For Each ws In ThisWorkbook.Worksheets If ws.Name <> "Sheet1" Then ws.Range("A1").Value = dateValue End If Next ws End Sub

12 Comments

Rudi Jan 26, 2022 at 8:42 PM

how do I update the formatting (i.e. colors / fonts / etc) to the 2nd sheet ?
- Reply
  Shahriar Aug 14, 2022 at 3:17 PM
  
  Follow Method 1. Paste Link Option. Just make a little bit of change. Select Paste Specials > Other Paste Options > Linked Picture.
John May 26, 2022 at 3:22 AM

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
- Reply
  Shahriar Aug 14, 2022 at 3:22 PM
  
  If your workbook is shared, anyone who has Write privileges can clear the read-only status.
Erica Nov 15, 2022 at 4:34 PM

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?
- Reply
  Shahriar Nov 16, 2022 at 4:40 PM
  
  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.
  - Reply
    Erica Nov 17, 2022 at 1:13 AM
    
    PERFECT! THANK YOU! This just saved me HOURS of doing everything manually, one item at a time!
    - Shahriar Nov 20, 2022 at 5:38 PM
      
      It’s my pleasure. I’m happy to be of service.
Imtiaz Jun 29, 2024 at 4:57 PM

I have excel workbook which has 14 spreadsheets. All 14 sheets are similar to each other, however values entered may differ and serial number of each sheets may be different. My query is that, if I change date in one sheet, it has to get updated in all the remaining spreadsheets, instead of me having to manually change in all the sheets. Is there any way to do.. please help..
- Reply
  Shamima Sultana Jun 30, 2024 at 12:27 PM
  Hello Imtiaz,
  
  To do so you need to use two VBA code in your Excel workbook. One in the Module to update date and another in the first sheet where you will update the date. Make sure to replace “Sheet1” with the actual name of the sheet where you will enter the date, and “A1” with the cell reference where the date is located.
  Copy and paste the following VBA code into the new module:
```
Sub UpdateDateInAllSheets()
    Dim ws As Worksheet
    Dim dateValue As Variant
    
    ' Change "Sheet1" to the name of the sheet where you will enter the date
    dateValue = ThisWorkbook.Sheets("Sheet1").Range("A1").Value
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Sheet1" Then
            ws.Range("A1").Value = dateValue
        End If
    Next ws
End Sub
```
  To automatically update the date whenever the date is changed, you need to use the Worksheet_Change event.
  In the VBA Editor, double-click the sheet where you will enter the date (e.g., Sheet1) in the Project Explorer window.
  Then, paste the following code:
```
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Call UpdateDateInAllSheets
    End If
End Sub
```
  Again, replace “$A$1” with the actual cell reference where the date is located.
  
  Now, whenever you change the date in the specified cell on your primary sheet (e.g., Sheet1), the date will automatically be updated in the corresponding cell on all other sheets.
  VBA code will assume that the date is located in the same cell on each sheet.
  
  Regards
  ExcelDemy
Imtiaz Aug 6, 2024 at 5:24 PM

Thank you for the guidance…appreciate it.. I would like to inform that as I am not savvy with using VBA codes in excel, not sure how to proceed….
- Reply
  Shamima Sultana Aug 6, 2024 at 9:03 PM
  Hello Imtiaz,
  
  You’re welcome! I understand that working with VBA may seems complex if you’re not familiar with it. Here’s a step-by-step guide to use the VBA code:
  
  To open the VBA editor press Alt + F11.
  In the VBA editor, go to Insert >> select Module. This will create a new module where you can paste the VBA code.
  Copy and Paste the First Code in the module.
```
Sub UpdateDateInAllSheets()
    Dim ws As Worksheet
    Dim dateValue As Variant
    
    ' Change "Sheet1" to the name of the sheet where you will enter the date
    dateValue = ThisWorkbook.Sheets("Sheet1").Range("A1").Value
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Sheet1" Then
            ws.Range("A1").Value = dateValue
        End If
    Next ws
End Sub
```
  Next, in the VBA editor, find Sheet1 (or the name of your sheet where you’ll enter the date) in the Project Explorer on the left side.
  You can also Double-click on Sheet1 to open its code window.
  Now, copy paste the 2nd code in the Sheet.
```
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Call UpdateDateInAllSheets
    End If
End Sub
```
  Make sure to replace “$A$1” with the actual cell reference where you will be entering the date if it’s different.
  Now, go back to your Excel sheet. Enter a date in the specified cell (e.g., A1) on Sheet1 and check if the date gets updated in the corresponding cell on all other sheets.
  
  Regards
  ExcelDemy

How to Automatically Update One Worksheet from Another Sheet in Excel

Method 1 – Using the Paste Link Feature

Method 2 – Utilize Exclamation Sign to Update Automatically

Method 3 – Apply IF Function to Automatically Update Data Based on Criteria

Method 4 – Utilize Drop-Down List to Update the New Sheet Automatically

Method 5 – Update Different Sheet Using Pivot Table Reference

Method 6 – Using Power Query Feature

Things to Remember

Related Articles

Leave a reply Cancel reply