How to Auto Populate from Another Worksheet in Excel

While working with multiple sheets in Excel, it’s very usual to feel the necessity to auto-populate data from another worksheet. There are a number of easy and simple ways to meet this objective. In this article, you’ll learn how you can auto-populate data from another worksheet with proper steps and illustrations.


Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.


3 Suitable Approaches to Auto Populate from Another Worksheet in Excel

1. Auto Populate by Linking Worksheets in Excel

In the following picture, Sheet1 is representing some specifications of a number of smartphone models.

Auto Populate by Linking Worksheets in Excel

And here is Sheet2 where only three columns from the first sheet have been extracted. The Price column has not been copied yet as we’ll show different methods here to pull out the price list from the first sheet. We have to maintain some rules that will auto-update the price column if any change is made in the corresponding column in the first sheet (Sheet1).

Auto Populate by Linking Worksheets in Excel

Now let’s have a look at how we can link between these two worksheets so that data in one worksheet (Sheet2) will be auto-populated based on another worksheet (Sheet1).

📌 Step 1:

➤ From Sheet1, select the range of cells (F5:F14) containing the prices of the smartphones.

➤ Press CTRL+C to copy the selected range of cells.

Auto Populate by Linking Worksheets in Excel

📌 Step 2:

➤ Go to Sheet2 now.

➤ Select the first output cell in the Price column.

➤ Right-click the button of your mouse and choose the Paste Link option as marked within a red square in the following screenshot.

Auto Populate by Linking Worksheets in Excel

The Price column is now complete with the extracted data from the first sheet (Sheet1). Now we’ll see how a change of data in the primary worksheet (Sheet1) auto-populates data in the second worksheet (Sheet2).

📌 Step 3:

➤ In Sheet1, change the price value of any smartphone model.

➤ Press Enter and go to Sheet2.

Auto Populate by Linking Worksheets in Excel

And you’ll find the updated price of the corresponding smartphone in Sheet2. This is how we can easily link between two or multiple worksheets to auto-populate.

Auto Populate by Linking Worksheets in Excel


2. Auto Update Data by Using Equal Sign to Refer Cell(s) from Another Worksheet

Now we’ll apply another method where we won’t have to copy and paste anything from one worksheet to another. Rather, we’ll use the cell reference(s) from another worksheet to auto-populate data.

📌 Step 1:

➤ In Sheet2, select Cell D5 and put an Equal (=) sign.

Auto Update Data by Using Equal Sign to Refer Cell(s) from Another Worksheet

📌 Step 2:

➤ Go to Sheet1.

➤ Select the range of cells (F5:F13) containing the prices of all smartphone models.

➤ Press Enter.

Auto Update Data by Using Equal Sign to Refer Cell(s) from Another Worksheet

Now in Sheet2, you’ll find an array of prices in Column D ranging from D5 to D14. If you change any data in the Price column in Sheet1, you’ll also see the updated price of the corresponding item in Sheet2 right away.

Auto Update Data by Using Equal Sign to Refer Cell(s) from Another Worksheet


3. Use of INDEX-MATCH Formula to Auto Populate from Another Worksheet in Excel

We can also combine the INDEX and MATCH functions to auto-update data from a worksheet to another in Excel.

📌 Step 1:

➤ Select Cell D5 in Sheet2 and type the following formula:

=INDEX(Sheet1!$B$5:$F$14,MATCH(Sheet2!$C35,Sheet1!$C$5:$C$14,0),MATCH($D$4,Sheet1!$B$4:$F$4,0))

➤ Press Enter and you’ll get the first extracted price of the smartphone from Sheet1.

Use of INDEX-MATCH Formula to Auto Populate from Another Worksheet in Excel

📌 Step 2:

➤ Now use Fill Handle to autofill the rest of the cells in Column D.

After extracting the price list for all smartphones in Sheet2, you can now easily auto-update any price of the corresponding smartphone in Sheet2 based on a price change in Sheet1.

Use of INDEX-MATCH Formula to Auto Populate from Another Worksheet in Excel


Concluding Words

I hope all of these simple methods mentioned above will now help you to apply them in your Excel spreadsheets when necessary. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.


You May Also Like to Explore

How to Autocomplete Cells or Columns From List in Excel

How to Reference Worksheet Name in Formula in Excel

Use of VLOOKUP in VBA to Find Values from Another Worksheet in Excel

How to Pull Data from Multiple Worksheets in Excel (4 Ways)

How to Update One Excel Worksheet from Another Sheet Automatically

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo