Function Problems

How to Filter Active Rows and Return Selected Columns in Excel

Maira Mehtab Sep 27, 2026 870 views

Question details

The user wants to extract specific columns from a master worksheet while only displaying rows that are marked as active (e.g., containing a 1 in a specific status column).

Product: Excel
Device & OS: not provided
Scenario: Pulling selected data columns from a large master dataset based on a 1/0 active status indicator to create a clean, customized report.
Observed behavior: The goal is to dynamically output only the chosen columns for rows where the designated condition column equals 1, ignoring rows marked with 0.

Before you start

Ensure your master worksheet has clear column headers, and identify both the column containing your 1/0 status indicators and the specific numeric indexes of the columns you want to extract.

Solution 1Recommended

Use the FILTER and CHOOSECOLS Functions

Combine the FILTER function to extract active rows and the CHOOSECOLS function to specify exactly which columns to return from the array.

This dynamic array formula allows you to query a dataset in real-time. CHOOSECOLS isolates the desired columns by their numeric index, while FILTER handles the row criteria.

Identify your data ranges

Determine the full range of your master dataset (e.g., Sheet1!A2:T100) and the specific column containing the active status (e.g., Sheet1!P2:P100).

Select the destination cell

Click on the top-left cell in the new worksheet where you want the filtered data to begin.

Enter the combined formula

Type the formula: =CHOOSECOLS(FILTER(Sheet1!A2:T100, Sheet1!P2:P100=1), 1, 4, 7, 9) and modify the column numbers (1, 4, 7, 9) to match the columns you want to display.

Generate the results

Press Enter. The dynamic array will automatically spill into the adjacent cells, displaying only the selected columns for active rows.

Dynamic Updates: Because this relies on dynamic array functions, any changes made to the master worksheet's data or 1/0 status will automatically update your extracted results.

Solution 2

Extract Columns from an External Workbook

You can reference a master sheet located in a completely different workbook to keep your target report lightweight and separate from raw data.

Efficient Data Processing

Easily Filter and Extract Data with WPS Spreadsheet

WPS Spreadsheet fully supports dynamic array functions like FILTER and CHOOSECOLS, allowing you to seamlessly query master datasets and extract exact columns across workbooks without writing complex VBA code.

1. Open your dataset: Launch WPS Spreadsheet and open your master dataset workbook.
2. Select a blank area: Navigate to the sheet where you want to display the extracted report and click a blank cell.
3. Apply the formula: Type =CHOOSECOLS(FILTER(A2:T100, P2:P100=1), 1, 4, 7) to extract the 1st, 4th, and 7th columns of rows marked with 1.
4. Press Enter to spill data: Hit Enter, and WPS will automatically spill the filtered data seamlessly into the neighboring cells.

Fully compatible with Microsoft Excel formulas and functionsSupports dynamic arrays like FILTER and CHOOSECOLS nativelyLightweight application that handles large datasets smoothlyFree to use with an intuitive, tabbed interface

Frequently Asked Questions

What if my software version doesn't support the CHOOSECOLS function?

If you are using an older version of Excel that lacks CHOOSECOLS, you can achieve a similar result using the INDEX function combined with FILTER, or by utilizing Power Query to filter rows and remove unwanted columns visually.

Why does my formula return a #CALC! or #SPILL! error?

A #CALC! error typically means the FILTER function found no rows matching your criteria (no rows marked with 1). A #SPILL! error occurs when there is existing text or data blocking the cells where the formula needs to output the results. Clear the blocking cells to resolve it.

Can I filter by text conditions instead of just numbers like 1 or 0?

Yes, simply modify the condition part of the FILTER formula. For example, use Sheet1!P2:P100="Active" instead of Sheet1!P2:P100=1 to filter based on text strings.

Excel & Data Problems