Data Extraction is the approach of gathering particular data from a source or replicating the data from somewhere else.

In this Excel tutorial, you will learn how to extract data from Excel cells, columns, rows, another sheet, or even from another workbook.

Suppose we have a dataset of Order Management. Now we will get the Order ID and Sales Amount ($) for a particular Sales Person using a dynamic formula in Excel. The dynamic formula will allow you to get newly added data and update itself automatically.

This content covers different methods of data extraction in Excel, including:

- Extraction of particular data manually
- Filtered data pulling off
- Gathering specific data from a single-column
- Extraction of data from a cell
- Data extraction based on a partial match
- Use of formulas for data extraction from worksheet
- Application of Excel formula for dynamic dataset
- Data combination with the Data Consolidate tool
- Extraction of data from external sources with Power Query
- Pulling off data from the selection using the VBA Macro tool

*📒Note:**We have used*

**Microsoft 365**while preparing the dataset for this tutorial. You can apply the mentioned methods in versions from Excel 2007 onwards.⏷What Is Data Extraction in Excel?

⏷Preparing Excel Worksheet Before Extracting Data

⏷Possible Methods to Extract Data

⏷1. Extracting Particular Data Manually

⏷2. Extracting Filtered Data from Sheet

⏵2.1. Using Filter Tool for Data Extraction

⏵2.2. Using FILTER Function to Extract Data Based on Criterion

⏵2.3. Using Advanced Filter to Extract Data Based on Multiple Criteria

⏷3. Extracting Specific Data from a Column

⏷4. Extracting Data from Cell

⏷5. Extracting Data Based on Partial Input

⏷6. Using Formula to Extract Data Based on Lookup Value

⏵6.1. Using VLOOKUP Function

⏵6.2. Inserting INDEX-MATCH Formula

⏵6.3. Using OFFSET and MATCH Function

⏷7. Data Consolidation from Another Worksheet

⏷8. Extracting Information from Dynamic Dataset

⏷9. Using Pivot Table to Extract Data

⏷10. Applying VBA Macro to Extract Data

⏷11. Using Power Query for Data Extraction

⏷How to Extract Specific Number of Characters?

⏷How to Extract Month and Day from Date?

⏷How to Extract Same Cell from Multiple Sheets into a Master Column?

⏷Which Method Is Best Among All for Data Extraction?

## What Is Data Extraction in Excel?

Data Extraction is the process of pulling off specific data from an extensive dataset. Often it can be the process of gathering data from CSV (Comma-Separated-Values file), and plain text file (.txt) to an Excel Spreadsheet. Also, it is the process of bringing out data from two or multiple datasets and worksheets.

## How to Prepare Your Excel Worksheet Before Extracting Data?

Ensuring the best possible shape is essential when you extract data in Excel. The presentation of data plays a vital role in the successful extraction process. Some invaluable techniques that might help you in cleaning, organizing, and structuring your data before you begin extraction:

**Data Cleaning:**

- Identify and remove duplicates.
- Address and handle missing-incomplete information.
- Ensure consistency formats (Such as date formats, numerical values, text capitalization).
- Correct data inconsistencies and entry errors.

**Data Organization:
**

- Group similar data into categories.
- Create dictionaries to describe the meaning and format of each column and variable.
- Arrange data by sorting and filtering.

**Data Structuring:**

- Reduce data redundancy and maintain integrity.
- Summarize and aggregate data with Pivot tables.
- Establish relationships among datasets and tables.

## What Are All Possible Methods to Extract Data from Excel?

Data extraction is the process of extracting specific portions of information from an extensive dataset in Excel. Depending on specific needs and situations, there are several methods that allow you to perform this.

- Copying and moving particular data manually
- Pulling off filtered data with
**Filter**tool,**FILTER**function, and**Advanced Filter**. - Gathering specific data from a single column
- Extraction of data from a cell with the
**FIND-LEFT**formula - Using the
**FILTER-ISNUMBER-SEARCH**formula to extract data based on partial text - Using formulas for single data extraction from a worksheet
- Applying Excel formula for dynamic dataset
- Combining data with the
**Data Consolidate**tool - Using
**Pivot table**to extract summarized data - Extracting data from external sources with
**Power Query Editor** - Pulling required data from the selection using the
**VBA Macro**.

Here is our dataset. We will apply the above-mentioned methods in the following dataset.

## 1. Extracting Particular Data from Excel Manually

In this section, we will show you how to extract particular data manually from an Excel Spreadsheet. Suppose you need to extract the top 10 data from a worksheet. You can do so manually by copying and pasting them into another sheet.

- Select the
**B5:F15**range and press**Ctrl + C**to copy the first 10 orders.

- Now go to the new worksheet and paste them by pressing
**Ctrl + V**keys. - Thus you will be able to move the data into a new worksheet.

## 2. Extracting Filtered Data from Excel Sheet

Now you will learn how to extract filtered data from an Excel spreadsheet. There are 3 basic methods:

- Applying
**Filter**Tool - Using
**FILTER**function - Applying the
**Advanced Filter**Tool

### 2.1. Using Basic Filter Tool in Excel to Extract Filtered Data

The Basic **Filter **tool is the most frequently used and convenient Excel tool to extract the filtered data. By applying the Filter tool, there will be a drop-down box on each heading. You can filter out any data with the filter drop-down.

- Select the
**B5:F5**range. - Go to
**Home**=>**Editing**=>**Sort & Filter**=>**Filter**.

- Select Region
**Filter drop-down**box => Check**West**only =>**OK**.

- Therefore, you will be able to gather all the data of the West region.

*Note:**The*

**Filter**tool doesn’t update data automatically once data changes.### 2.2. Using Excel FILTER Function to Extract Data Based on Criterion

The **FILTER **function is the dedicated function to extract the filtered data based on criteria in Excel. Unlike the previous Filter tool, it updates the outcome if data changes. Now we will extract the data for the west region with the **FILTER **function.

*Note:**The*

**FILTER**function is only available on Excel 2021 and Microsoft 365. In the earlier versions, it is unsupported.- Insert the following
**FILTER**formula in the**B28**cell.

`=FILTER(B6:F25,D6:D25="west")`

- As a result, you will get all the information of the region “West” with an array format.

### 2.3. Using Advanced Filter to Extract Data Based on Multiple Criteria

Now we will show you how to extract data from a table or range based on multiple criteria in Excel. Suppose we want to pull the data for the west and after the 2/6/2023 order date. To apply these two criteria, we must use the **Advanced Filter **tool. The Advanced filter extracts data based on multiple criteria in Excel.

- First, select the entire data range.
- Then, click accordingly,
**Data**=>**Sort & Filter**=>**Advanced**.

- Next select Copy to another location from the
**Action**field. - Select List range
**$B$5:$F$25**, Criteria range**$C$27:$D$28**and output location**$B$30**.

- Thus we will be able to gather all the data of the west region and the order data of after 2/6/2023.

*Note:**Like the Filter tool,*

**Advanced Filter**doesn’t update data automatically when data changes.## 3. Extracting Specific Data from a Column in Excel

In this segment, you will learn to extract specific data from a column in Excel. We will extract the Order IDs of the East region by combining **INDEX**, **SMALL**, **IF**, **IFERROR**, **ROW**, and **ROWS **functions.

- Write down the following formula in the
**H9**cell and press**Enter.**

`=IFERROR(INDEX($B$6:$B$25, SMALL(IF($D$6:$D$25=$H$6, ROW($D$6:$D$25)-ROW($D$6)+1), ROWS($E$6:E6))), "")`

- Thus all the Order Id 11001 from the East region will show up. Further, using the
**Fill Handle**tool you will be able to pull off all the Order IDs.

## 4. Extracting Data from an Excel Cell

Now we will display you the approach of extracting data from an Excel cell using the **LEFT****–****FIND** formula. Suppose we have a dataset of service lists. Now, we like to extract keywords from each cell, and the keyword appears in the first word.

- To get the first word from every cell, Input the following formula in the
**E6**Then, drag down the**Fill Handle**tool.

`=LEFT(B6,FIND(" ",B6)-1)`

## 5. Extracting Data Based on Partial Input from Excel Cell

In this portion, we will show you how to extract data with partial text from cells in Excel. Let’s assume, we like to bring out all the data containing air in the cell. We extract data with a partial text by combining **FILTER**, **ISNUMBER**, and **SEARCH **functions.

- In the
**E8**cell, insert the below**FILTER-ISNUMBER-SEARCH**formula.

`=FILTER(B6:C18, ISNUMBER(SEARCH(F5, B6:B18)), "None")`

- Thus all the information containing the partial text “Air” will show up with an array format.

## 6. Using Excel Formula to Extract Data Based on Lookup Value

In this segment, we will extract data from Excel worksheets with Excel formulas. To do so, we will introduce 3 methods:

- Application of the
**VLOOKUP**function - Use of
**INDEX-MATCH**formula - With the help of the
**OFFSET-MATCH**formula

### 6.1. Using VLOOKUP Function

Using the **VLOOKUP** function, you can extract data for a lookup value from an Excel worksheet. For example, we will extract the Order Date for each Order ID.

- By applying the following
**VLOOKUP**formula, we obtain the Order Date in the**I6**cell.

`=VLOOKUP(H6,B6:F25,2)`

- You will get the Order Dates automatically for each Order ID with the
**Fill Handle**tool.

*Note:**When you apply*

**VLOOKUP**in a dataset containing multiple columns, it searches for values in the leftmost column of a range/table and returns the value of the same row of another column to the right. Besides, The function returns only 1st and single value from a column.### 6.2. Inserting INDEX-MATCH Formula

You can extract data for a particular value from a worksheet by applying the **INDEX-****MATCH** formula. For instance, we will pull off the Sales Person for each Order ID.

- By using the following
**INDEX-MATCH**formula in the**I6**cell, we obtain the Lily.

`=INDEX($B$6:$F$25,MATCH(H6,$B$6:$B$25,0),4)`

- Next, using the
**Fill Handle**tool, you will get the Order Dates automatically for each Order ID.

### 6.3. Using OFFSET and MATCH Function

You can extract data for a particular value from a worksheet by employing the Excel **OFFSET****-MATCH** formula. Likewise, we will extract the Sales Person for each Order ID. You can also consider the formula as a duplicate use of the **INDEX-MATCH** formula.

- We obtain the Lily by inserting the following
**OFFSET-MATCH**formula in the**I6**cell.

`=OFFSET($B$6, MATCH(H6, $B$6:$B$25, 0) - 1, 3)`

- After that, you will get the Order Dates automatically using the
**Fill Handle**tool for each Order ID.

## 7. Pulling Data from Worksheet Using Data Consolidate Tool

In this section, you will learn to bring out data from two datasets of a worksheet with Data Consolidation. Suppose, we have two datasets in a worksheet. Now, we will bring out these two datasets into one using the **Data Consolidate** tool.

- First, select the
**B5**cell. - Then, Go to
**Data**=>**Data Tools**=>**Consolidate**.

- Next, choose
**Sum**from the**Function**field. - After that, add references from the
**Dataset 2**worksheet - Further, check the
**Top row**and**Left column**from the**Use labels in**field. - Finally hit the
**OK**command.

- As a result, we’ll bring out the data together in a dataset.

## 8. Extracting Information from Dynamic Dataset in Excel

The previous methods have common shortcomings. They don’t update themselves when we add new rows below a dataset. To alleviate the issue we introduce a dynamic formula that works for any condition. So the addition or removal of rows is no longer an issue. We will extract data from a dynamic dataset in Excel combining **IFERROR**, **INDEX**, **SMALL**, **ROWS**, and **ROWS **functions.

- In the
**E8**cell, write the below dynamic formula.

`=IFERROR(INDEX($B:$B, SMALL(IF($E:$E=$I$5, ROW($E:$E)-ROW($H$1)+1), ROWS($H$1:H1))), "")`

- Then drag down the
**Fill Handle**tool till you get values.

- Similarly, input the following dynamic formula in the
**I8**cell.

`=IFERROR(INDEX($F:$F, SMALL(IF($E:$E=$I$5, ROW($E:$E)-ROW($H$1)+1), ROWS($H$1:H1))), "")`

- Therefore, we’ll get a total of 5 Order IDs and Sales Amount ($) for Simon.

- Here you’ll find “Mili” in the
**Sales Person**drop-down list once we add information about “Mili” in the 26th and 27th rows. - Therefore, by selecting Mili from the drop-down, you will be able to extract data for Mili as well.

## 9. Using Pivot Table to Extract Data in Excel

Now, We will show you how to use the **Pivot Table** for data extraction in Excel. The **Pivot Table** sums up the overall data automatically and provides summarized data.

*Note:*

**Pivot Table**doesn’t update automatically. So, you must hit the**Refresh All**command to update every time once data changes.- To convert a range into Pivot Table, click as follows:
**Insert**=>**PivotTable**=>**From Table/Range**. - Thus,
**PivotTable from table or range**dialog box shows up. - Now input
**Table/Range range**=> Pivot table**location**=>**OK**.

- After that, check
**Region**,**Sales Person**, and**Sales Amount ($)**from the**PivotTable Fields**pane. - Thus you will be able to show the Sum of Sales for each person in the separate region.

## 10. Applying VBA Macro to Extract Excel Data

By applying VBA Macro we can extract data based on our selection from an Excel worksheet. Unfortunately, the above-mentioned tools and functions are unable to work for any random selection, rather you must develop formulas and apply tools based on the data range. However, you will be able to apply the VBA macro in any worksheet range as well as custom selection. For instance, we will select the input data range, criterion, and output location with our developed VBA code.

- Write the following
**VBA**code in the**Module**

```
Sub Extract_Data_from_Excel()
'Developed by MD Tanvir Rahman, ExcelDemy
Dim Inp As Range, Cri As Range, Out As Range
Dim out_row As Long
Set Inp = Application.InputBox("Select input range:", Type:=8)
Set Cri = Application.InputBox("Select Sales Person:", Type:=8)
Set Out = Application.InputBox("Select Output Location:", Type:=8)
out_row = 1
For i = 1 To Inp.Rows.Count
If Inp.Cells(i, 4) = Cri.Value Then
Out.Cells(out_row, 1) = Inp.Cells(i, 1).Value
Out.Cells(out_row, 2) = Inp.Cells(i, 5).Value
out_row = out_row + 1
Else
End If
Next i
End Sub
```

- Now select as follows sequentially:
**Developer**=>**Macros**=>**Extract_Data_from_Excel**=>**Run**. - Select the
**input range**,**criteria**, and**output range**. - Thus you will get the Order ID and Sales Amount ($) for Lily.

## 11. Applying Excel Power Query to Extract Data

Suppose, we have a dataset in a plain text file separated by comma delimiters. In this section, we will show you how to extract data from a text file and import it to an Excel worksheet with the **Power Query** tool.

- To import data from a plain text (.txt) file, first, select
**B5**cell and click sequentially,**Data**=>**From Text/CSV**.

- Then, select the
**Source Data**file =>**Open.**

- Next, select Comma
**Delimiter**=>**Transform Data**.

- After that, click on the
**Region drop-down**box => Chcek**East**=>**OK**.

- Further, select
**Close & Load drop-down**=>**Close & Load To**.

- Therefore, the
**Import Data**dialog box appears. - Now select
**Table**=>**Existing worksheet**=>**$B$5**=>**OK**, Since we want to import data into a table format in the existing worksheet.

- Thus you will be able to bring out all the data containing the East region in an Excel worksheet.

## How to Extract Specific Number of Characters from an Excel Cell?

Now we will learn to extract specific data from an Excel cell. To do so, we will use the **LEFT **function. Let’s say, we have a dataset of Customer ID containing Order Code and Andress joining with an underscore (_). We will extract the Order Code from each Customer ID.

- Using the following formula we obtain the Order Code in the
**H6**cell.

`=LEFT(B6,5)`

- Further, use the
**Fill Handle**tool to fill automatically.

## How to Extract Month and Day from Date in Excel?

Now we will show you how to extract month and day from a date in Excel. Likewise, we will show the month and day of each Order Date using the **TEXT **function.

- You will get the month “January” when you insert the following formula in the
**F6**cell.

`=TEXT($C6,"mmmm")`

- Further, apply the
**Fill Handle**tool to autofill similarly.

- Similarly, you will obtain the order day “Sunday” when you input the following formula in the
**G6**cell.

`=TEXT($C6,"dddd")`

## How to Extract Same Cell from Multiple Sheets into a Master Column in Excel?

We have two separate worksheets such as **Dataset 2** and **Dataset 3**. Now we will pull the same cells from multiple sheets into a master column in Excel. Therefore, we will bring out information for each sales person with Salary for January and February.

- Select
**Sum**from the**Function**field. - Add references from
**Dataset 2**and**Dataset 3**worksheets. - Check the
**Top row**and**Left column**from the**Use labels in**field. - Finally hit the
**OK**command.

- As a result, we combine data from multiple worksheets with the
**Data Consolidate**tool.

## Which Method Is Best Among All for Data Extraction in Excel?

We have learned to use several **Excel tools**, **functions**, **Power Query**, and **VBA Macro**. The suitable method varies from user to user based on the requirement, goal, and application for data extraction.

- You can use the
**Filter**tool and**FILTER**function to get filtered data. The Filter tool is an excellent choice when you extract data quickly that is not recurring. - On the other hand, it is better to use the
**FILTER**formula if you are likely to filter the data permanently. It also updates data automatically if data changes within the range. - If you need to add or remove data continuously, then the dynamic formula will be beneficial to you.
- In another context, the use of the
**Power Query**tool will be fruitful if you have CSV (Comma-Separated-Values file), and plain text file (.txt). - Lastly,
**VBA Macro**requires programming knowledge. In the meantime, it allows versatile applications.

**Download Practice Workbook**

Download the Practice workbook and practice yourself.

**Concluding Words**

Throughout the content, we elaborated on how to extract data from Excel. To filter data you can use the Filter tool. In addition, the FILTER function and Advanced Filter tool support criteria. You can use the LEFT-FIND formula to extract the first word from a cell. The use of the FILTER-ISNUMBER-SEARCH formula allows you to get data based on partial text. You can pull off data for particular data by applying VLOOKUP, INDEX-MATCH, and OFFSET-MATCH formulas. Besides, you can also get updated data when you add or remove rows by using a dynamic formula. Data Consolidation can be done using the Data Consolidate tool. VBA Macro is fruitful for applying to any selection. Finally, The Power Query tool helps to import data from a text or CSV file.

Feel free to leave your insights, suggestions, as well as, queries in the comment section.

## Extract Data Excel: Knowledge Hub

- Excel Formula to Get First 3 Characters from a Cell
- Extract Data from a List Using Excel Formula
- Extract Month from Date in Excel
- Extract Year from Date in Excel
- Pull Data From Another Sheet Based on Criteria in Excel
- Get Data from Another Sheet Based on Cell Value in Excel
- Extract Filtered Data in Excel to Another Sheet
- Pull Values from Another Worksheet in Excel
- Pull Data from Multiple Worksheets in Excel
- Extract Data from One Sheet to Another Using VBA in Excel
- Pull Data from Multiple Worksheets in Excel VBA
- Excel Macro: Extract Data from Multiple Excel Files

**<< Go Back To Learn Excel**