Function Problems

How to Create a Filtered Excel Table from Selected Columns

Phi Hung Vo Sep 30, 2026 869 views

Question details

The user needs to extract specific columns and filter rows by a condition (such as a date), then format the results as a standard Excel table to create a dynamic chart.

How to Create a Filtered Excel Table from Selected Columns

Product: Excel
Device & OS: not provided
Scenario: Extracting a subset of data from a large master dataset for specific charting and reporting purposes.
Observed behavior: Using functions like FILTER and CHOOSECOLS returns a spilled dynamic array, but Excel does not allow a spilled formula result to be directly converted into a standard Excel Table.

Before you start

Check your Excel version to ensure it supports dynamic array functions (like FILTER and CHOOSECOLS) or verify that Power Query is available in your Data tab for advanced data transformations.

Solution 1Recommended

Use Power Query to Output a Refreshable Table

Power Query is the recommended method because it allows you to filter data, extract specific columns, and load the final result as a standard, format-ready Excel Table.

Since standard Excel Tables cannot overlap with dynamic array spill ranges, Power Query provides a robust alternative. It extracts the data in the background and outputs it cleanly into a structured Table.

Load data into Power Query

Select your source data range. Go to the Data tab on the ribbon and click 'From Table/Range'. If prompted, confirm your data range to open the Power Query Editor.

Select and remove columns

In the Power Query Editor, hold the Ctrl key and click the column headers you want to keep. Right-click one of the selected headers and choose 'Remove Other Columns'.

Filter the rows by condition

Click the drop-down arrow on your Date column (or the column you wish to filter by). Use the Date Filters to select the specific date or criteria you need.

Load to an Excel Table

Click 'Close & Load' in the top-left corner. Choose 'Close & Load To...', select 'Table', and choose where you want the new filtered table to be placed. You can now use this Table to insert your chart.

Use Power Query to Output a Refreshable Table

Automatic Updates: When your source data changes, simply right-click the Power Query output table and select 'Refresh' to instantly update your filtered records and chart.

Solution 2

Use Dynamic Array Formulas (FILTER and CHOOSECOLS)

If you only need the data for immediate charting and do not require a formal 'Table' object, you can combine FILTER and CHOOSECOLS to spill the exact data dynamically.

Powerful Data Processing Tool

Easily Filter and Extract Data with WPS Spreadsheet

WPS Spreadsheet offers powerful data handling capabilities, including advanced filtering, robust functions, and intuitive charting tools to help you process complex datasets effortlessly.

1. Open data in WPS Spreadsheet: Launch WPS Office and open your dataset in WPS Spreadsheet.
2. Apply an AutoFilter: Navigate to the Data tab and click 'AutoFilter'. Use the drop-down arrows on your column headers to filter rows by your target date.
3. Copy selected columns: Highlight the specific columns you need from the visible, filtered data. Press Ctrl+C to copy them.
4. Paste and format as Table: Paste the copied data into a new worksheet. Highlight the pasted range, go to the Insert tab, and click 'Table' (or press Ctrl+T) to convert it into a standard table for charting.

Fully compatible with Microsoft Excel file formats (.xlsx) and functions.Built-in advanced filtering tools to easily extract rows based on dates or custom criteria.Lightweight architecture ensures fast calculation even with large data arrays.Seamless process for converting extracted data into formal Tables for charting.

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I convert a spilled dynamic array formula into a standard Excel Table?

Standard Excel Tables (created using Ctrl+T) require a fixed structure and do not support dynamic array spill behaviors. The automatic resizing of a dynamic array conflicts with the rigid, predefined boundaries of an Excel Table object.

Can I build a chart directly from spilled formula results without a Table?

Yes. You can select the dynamic array output and insert a chart normally. To ensure the chart updates dynamically when the array size changes, you can define your chart data series using the spill operator (for example, by referencing `$A$1#` in the Name Manager).

What is the CHOOSECOLS function used for?

CHOOSECOLS is a dynamic array function that returns specified columns from a given array or range based on the column index numbers provided. It is highly useful for reordering or extracting non-adjacent columns from a large dataset.

Excel & Data Problems