How to Extract Data from Excel Sheet (6 Effective Methods)

If you are looking to extract data from an Excel sheet to another sheet, then you are in the right place. You just have to follow along with this article and practice your own Excel file or you can download our practice book. In this tutorial, you are going to learn 6 easy and effective methods to extract data from an Excel sheet.

How to Extract Data from Excel Sheet: 6 Effective Methods

Here, we have a data set containing 5 columns and 9 rows including headings. Our mission is to extract data from an Excel worksheet to another worksheet.

Now, let’s discuss the methods one by one.

1. Extract Data from Excel Sheet Using VLOOKUP Function

The VLOOKUP function seeks for a given data in the leftmost column of a given dataset and then extracts a value in the same row from a specified column.

Steps: 

Suppose we need to extract the salaries of ID no. 103, 106, and 108 from sheet 1 to sheet 2.

1. Enter the following formula in Cell C13 of Sheet 2.

2. Drag the Fill Handle to the range you need.

Here is the output.

Read More: How to Extract Specific Data from a Cell in Excel

2. Pick Data from Excel Sheet Using INDEX-MATCH Formula

INDEX-MATCH combo is a powerful and popular tool in MS Excel to extract data from a particular portion of the table. By applying this combined formula, we can extract data from sheet 1 to sheet 3 based on criteria. For this, you just need to follow the steps below.

Let’s suppose, you want to find the salary for a particular ID. We will use the combo of INDEX and MATCH functions to do so.

Steps: 

1. In cell C13, enter the following formula

2. Press ENTER.

3. Drag the Fill Handle to the range you need.

Here is the output,

Read More: How to Extract Data Based on Criteria from Excel

3. Extract Data from Excel Sheet Using Data Consolidation Tool

In many cases, there is a simpler way to extract data from an Excel sheet using Data Consolidation than VLOOKUP or INDEX-MATCH. I am using two datasets in the same Excel worksheet (Consolidation 1) as input. The result of the consolidation will be shown on a different worksheet (Consolidation 2).

Now, follow the steps below.

Steps:

1. Go to the Consolidation 2 sheet >> Select a Cell (Cell B4 in this example) where you want to put your consolidated result.

2. Then, go to the Data tab >> the Data Tools group >> Click on the Consolidate icon.

A Consolidate Dialog box will pop up.

3. Select the Function you need, then one by one select each table including the headings from the “Consolidation 1” sheet in the Reference box, and click Add.

4. All selected tables from Consolidation Sheet 1 will appear in the All References box. Ensure the Tick mark (top row and left row) in the Label box. Click OK.

Here is the result,

Read More: How to Extract Data From Table Based on Multiple Criteria in Excel

4. Extract Data from Worksheet Using Advanced Filter

You can extract data from an Excel sheet to a different sheet using an Advanced Filter. Follow the written instructions below. In this illustration, the data is on Sheet 5 and will be extracted from Sheet 6.

Steps:

1. Go to Sheet 6 >> Select a Cell (Cell B4 in this illustration)>> Data tab>> click Advanced.

An Advanced Filter window will be opened.

2. Select Copy to Another Location.

3. Click on the List Range box >> Select Sheet 5 and select the entire table with the headings.

4. Choose the criteria range.

5. Then, in Copy to Box, select the cell on sheet 6 (Cell B4 in this example).

6. Click OK.

Here is the result, 

Read More: How to Extract Data from Cell in Excel

5. Pull Data from Another Sheet in Excel with the Help of Name Box

To extract a cell from one Excel sheet to another, you just need to know the sheet’s name and the cell’s name. Then, by linking them together with an exclamation sign you could copy it. When you need to change the data in one worksheet, the other worksheet where you copied it will be changed automatically.

Suppose we have two worksheets named NameBox1 and NameBox2. We want to extract data from NameBox1 to NameBox2.

Now, follow the steps below.

Steps: 

• In any cell in NameBox2 (Cell B4 in this example), just enter =NameBox1!C9 >> Press ENTER and you will get values from Cell C9 in your new worksheet.

Here is the result,

Or,

• Type ‘=’ in any cell from NameBox2, then click the NameBox1 sheet and select the cell you need and press ENTER.

6. Extract Data from Excel Sheet with INDEX Function

The INDEX function does the opposite action of the MATCH function and somewhat acts like the VLOOKUP function. You need to tell the function what column and row of data you need, then it will tell you the value of what’s in the cell. Suppose we have two sheets named INDEX 1 and INDEX 2. In INDEX 2 sheet, we will set the Row and Column no. of the data from the INDEX 1 sheet.

Now follow the steps below.

Steps:

• In Cell D5, enter the following formula.

• Press ENTER.

Here is the output,

Download Practice Book

Download the following Excel file for your practice.

Conclusion

In this tutorial, I have discussed 6 easy methods on how to extract data from an Excel sheet. I hope you found this article helpful.

Please, drop comments, suggestions, or queries if you have any in the comment section below.

Related Articles

• Excel Formula to Get First 3 Characters from a Cell
• How to Extract Data from a List Using Excel Formula
• How to Extract Month and Day from Date in Excel
• How to Extract Month from Date in Excel
• How to Extract Year from Date in Excel

<< Go Back To Extract Data Excel | Learn Excel