How to Extract Data from Excel? (10+ Basic & Advanced Methods)

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 and save the Macro.

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.

Extract Data from Excel.xlsm

Source Data.txt

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

<< Go Back To Learn Excel

Get FREE Advanced Excel Exercises with Solutions!