How to Create a Vertical List from Selected Excel Items
Question details
The user needs an Excel formula to extract specific selected items from a master list and return them into separate rows vertically instead of joining them into a single cell.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Filtering and extracting specific data entries based on user selection, such as linked checkbox TRUE/FALSE values, to dynamically build a new list.
- Observed behavior
- The selected items automatically populate downwards in a single column, with each chosen item occupying its own distinct row.
Ensure that your master data includes an adjacent indicator column that shows TRUE when an item is selected (often linked to form checkboxes). Verify that your spreadsheet version supports Dynamic Array formulas.
Use the Dynamic FILTER Function
The most efficient way to extract selected items into individual vertical rows is by using the dynamic array FILTER function.
The FILTER function allows you to effortlessly extract matching records from a data range based on a specified boolean condition. In this scenario, we filter the main item list based on whether the corresponding selection column equals TRUE.
Determine the exact data range of your master item list (for example, D2:D18) and the corresponding selection criteria range (for example, C2:C18, where TRUE indicates a selected item).
Click on the starting destination cell where you want the vertical list to begin (such as D21) and enter the formula: =FILTER(D$2:D$18,C$2:C$18=TRUE).
Press Enter. The formula will automatically spill the extracted results down the column into separate rows based on the number of selected items.
Use WPS Spreadsheet to Create Dynamic Vertical Lists
WPS Spreadsheet fully supports advanced dynamic array functions like FILTER. You can easily build dynamic vertical lists from selected data, exactly as you would in Microsoft Excel, while enjoying a smooth and lightweight interface.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the master list and boolean selection column.
- 2. Select the target starting cell: Click the blank cell where you want the top of your vertical list to be generated.
- 3. Apply the FILTER formula: Type =FILTER(D$2:D$18,C$2:C$18=TRUE) and press Enter to instantly spill the selected records down the column.
Frequently Asked Questions
Why am I getting a #SPILL! error when using the FILTER formula?
A #SPILL! error occurs when the destination range where the formula attempts to place the vertical list is blocked by existing data. Clear the cells below your formula to provide enough empty rows for the results to populate properly.
What happens if no items are selected in the master list?
If no items meet the criteria (i.e., no checkboxes are marked TRUE), the FILTER function will return a #CALC! error. You can cleanly handle this by adding the optional third argument to the formula: =FILTER(D$2:D$18,C$2:C$18=TRUE,"No items selected").
Can I filter data horizontally instead of vertically?
Yes. The direction the FILTER function spills depends entirely on the layout of your source data. If your master list is arranged horizontally across columns, the FILTER function will automatically spill the matched results horizontally.
Does WPS Office support Excel's FILTER function?
Yes, modern versions of WPS Spreadsheet fully support dynamic array functions, including FILTER, ensuring seamless compatibility with Excel documents that rely on array spilling behaviors.
