Extract Data from Excel: Copy, Filter, or Power Query

Copy the cells you need

Select the range, press Ctrl+C, click the destination cell, and press Ctrl+V. The copy includes formulas. If you want the displayed numbers and text only, use Home, Paste, then Values. On a Mac, Copy is Command+C and Paste is Command+V.

A filtered list copies the visible rows. Rows hidden by the filter stay behind. If you hid rows yourself, with the right-click Hide command, select the range, choose Home, Find & Select, Go To Special, Visible cells only, then copy. Otherwise a normal copy can include the hidden rows.

Split one column

When a column holds several pieces in one cell, such as "Ada Lovelace, London", use Text to Columns or Flash Fill.

1. Select the column. On the Data tab, choose Text to Columns.
2. Pick Delimited, choose the comma or the character that separates the pieces, and set the destination so you do not overwrite a column you still need.
3. Finish. Excel writes each piece into its own column. This changes the sheet. Work on a copy if the original column must stay intact.

Flash Fill is the other path. Type the result you want in the next column for the first row. On the Data tab, choose Flash Fill. On Windows the shortcut is Ctrl+E. Check every filled cell. Flash Fill follows the example. It is not a formula, so it does not update when the source cell changes.

Pull matching rows with FILTER

In Microsoft 365 and Excel 2021, =FILTER(A2:D100,C2:C100="Open") spills the rows of A2:D100 where column C is Open. Put the formula on an empty area. If nothing matches, Excel returns #CALC!. Wrap it with IFERROR only when you want a message instead of that error.

FILTER is a formula. It updates when the source changes. It is not in Excel 2019 or earlier. Those versions can still use AutoFilter, then copy the visible rows.

Load another file with Power Query

Use this in Excel for Microsoft 365 when the rows live in another workbook or in a CSV.

1. On the Data tab, choose Get Data, From File, then From Workbook or From Text/CSV.
2. Pick the file. In the navigator, choose the sheet or the table, then Transform Data if you need to drop columns or set types.
3. Choose Close & Load. Excel writes the result to a new sheet. Close & Load To lets you pick the destination.
4. When the source file changes, choose Data, Refresh All. Excel runs the same steps again.

Power Query keeps the steps. It does not type the rows by hand. A query built on a desktop path fails on another computer if that path does not exist there.

Hand the sheet to another program

File, Save As, and the type CSV UTF-8 writes the active sheet as text. Formulas become the values they show. Other sheets are not included. A chart is not included. Keep the xlsx file. The CSV is the extract, not a full copy of the workbook.

One option: ask Elyx in the open file

Elyx is an AI agent inside Excel. You can ask it to copy the rows where Status is Open onto a new sheet. The trial lasts 7 days and does not ask for a card. Then Plus is €12/month and Pro is €29/month. There is no permanent free plan. Elyx does not run in Google Sheets.

Start the 7-day free trial, no credit card.

Direct answers

How do you extract data from Excel?

Select the cells, copy them, and paste. Choose Paste Values when you want numbers and text instead of formulas. File, Save As, CSV UTF-8 writes the active sheet for another program.

How do you extract only the rows that match?

Turn on AutoFilter and copy the visible rows. In Microsoft 365 and Excel 2021, FILTER spills the matching rows into a formula.

Does a CSV keep the whole workbook?

No. CSV UTF-8 writes the active sheet. Formulas become values. Other sheets and charts are not included.