How to Extract Data from Excel – 10+ Basic & Advanced Methods

Sample dataset for extract data in Excel.

This is a dataset of Order Management. And this is our sample dataset.

Method 1 – Extracting Data from Excel Manually

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

Copying first 10 data

Go to the new worksheet and paste them by pressing Ctrl + V.

Data is moved into a new worksheet.

Pasting top 10 data into another worksheet.

Method 2 – Extracting Filtered Data from an Excel Sheet

2.1. Using the Basic Filter Tool to Extract Filtered Data

Getting Filter tool from Home tab.

Unchecking west from the filter drop down list.

Data of the west region is displayed.

Extracting all filtered data in Excel.

Note: The Filter tool doesn’t update data automatically.

2.2. Using the Excel FILTER Function to Extract Data Based on Criteria

Insert the following FILTER formula in B28.

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

All information on the “West” region will be displayed in array format.

Extracting all filtered data with an array format in Excel.

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

Select the entire data range.
Click Data =>Sort & Filter =>Advanced.

Selecting Advanced Filter tool in Excel.

In Action, choose Copy to another location.
Select List range $B$5:$F$25, Criteria range $C$27:$D$28 and output location $B$30.

Selecting input, criteria and output locations.

All data of the west region with order dates from 2/6/2023 onward will be displayed.

Extracting all filtered data in Excel.

Note: Advanced Filter doesn’t update data automatically.

Method 3 – Extracting Specific Data from a Column in Excel

Enter the following formula in H9 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))), "")

Order Id 11001 from the East region will be displayed. Drag the Fill Handle tool see all Order IDs.

Extracting data from a column.

Method 4 – Extracting Data from an Excel Cell

Enter the following formula in E6 and drag the Fill Handle tool.

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

Extracting Data from an Excel Cell

Method 5 – Extracting Data Based on Partial Input from a Cell

Enter this FILTER-ISNUMBER-SEARCH formula in E8.

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

All information containing the text “Air” will be displayed in array format.

Extracting Data Based on Partial Input from Excel Cell

Method 6 – Using an Excel Formula to Extract Data Based on the Lookup Value

6.1. Using the VLOOKUP Function

Enter the following formula in I6.

=VLOOKUP(H6,B6:F25,2)

Drag the Fill Handle to see the Order Dates for each Order ID.

Using VLOOKUP Function to extract data based on a lookup value.

6.2. INDEX-MATCH Formula

Enter the following formula in I6 to see the Sales Person (Lily).

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

Drag the Fill Handle to see the Order Dates for each Order ID.

Using INDEX-MATCH function to extract data based on a lookup value.

6.3. Using OFFSET and MATCH Function

Enter the following formula in I6 to see the Sales Person (Lily) .

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

Drag the Fill Handle to see the Order Dates for each Order ID.

Using OFFSET-MATCH function to extract data based on a lookup value.

Method 7. Pulling Data from a Worksheet Using the Data Consolidate Tool

There are two datasets in a worksheet.

Sample datasets in a worksheet for extracting data in Excel.

Selecting Data Consolidate tool.

Adding references in <a href= Data Consolidate dialog box." width="488" height="352" />

Data is displayed together in the dataset.

Data consolidated in a worksheet.

Method 8 – Extracting Information from a Dynamic Dataset in Excel

Enter the formula below in E8.

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

Drag the Fill Handle across the cells you want to fill.

Using Excel formula to extract Order ID from dynamic worksheet.

Enter the following formula in I8.

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

The total of 5 Order IDs and Sales Amount ($) for Simon will be displayed.

Using Excel formula to extract Amount ($) from dynamic worksheet in Excel.

Information was added to Sales Person list. By selecting Mili from the drop-down list, you will also be able to extract data.

Method 9 – Using a Pivot Table to Extract Data in Excel

Note: A Pivot Table doesn’t update automatically. So, you must click Refresh All to update it whenever data is changed.

To convert a range into Pivot Table, click Insert =>PivotTable =>From Table/Range.
In the PivotTable from table or range dialog box, choose Table/Range range => Pivot table location =>OK.

Selecting range and converting PivotTable.

Check Region, Sales Person, and Sales Amount ($) in PivotTable Fields.
The Sum of Sales for each person in separate regions will be displayed.

Selecting components from PivotTable Fields.

Method 10. Applying a VBA Macro to Extract Data

Enter 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

Writing a VBA code and Saving the macro.