Function Problems

How to Extract Table Data for a Printable Form Using Excel Formulas

Olivia Miller Oct 10, 2026 872 views

Question details

The user needs to dynamically extract specific nonblank data (such as location, parts, quantities, and descriptions) from a table into a printable form based on a drop-down list selection.

How to Extract Table Data for a Printable Form Using Excel Formulas

Product: Microsoft Excel
Device & OS: not provided
Scenario: Setting up a printable form that automatically populates relevant records from a master inventory or parts table based on a specific cubicle or equipment name selected in a drop-down list.
Observed behavior: Dynamic extraction requires specific functions. When using the FILTER function in unsupported older Excel versions, it results in a #NAME? error and an _xlfn. prefix. Additionally, merged destination cells prevent the results from populating correctly.

Before you start

Verify your Excel version, as dynamic array functions like FILTER are only available in Microsoft 365 or Excel 2021. Additionally, inspect your printable form's destination area to ensure all cells are unmerged, as merged cells will block data from spilling properly.

Solution 1Recommended

Use the FILTER Function in Microsoft 365 or Excel 2021

Use the dynamic array FILTER function to instantly return matching, nonblank rows based on your drop-down selection.

The FILTER function is the most efficient way to extract data into a printable form dynamically. It evaluates a source table and spills the matching results directly into your form.

Because FILTER is a dynamic array function, the destination cells must be entirely clear of existing data and must not be merged. Merged cells will cause a #SPILL! error or prevent the array from populating.

Unmerge destination cells

Highlight the area on your printable form where the data will appear. Go to the Home tab and click 'Merge & Center' to unmerge any combined cells.

Enter the FILTER formula

Select the top-left cell of your destination area. Type =FILTER( to begin the formula.

Define the array and criteria

Select your source table data as the 'array'. For the 'include' argument, select the column to evaluate and set it equal to your drop-down list cell (e.g., Table1[Cubicle]=$A$1).

Filter out blank rows

To ensure nonblank parts are extracted, multiply the criteria with a nonblank condition, such as (Table1[Cubicle]=$A$1)*(Table1[Parts]<>"").

Press Enter to spill the data

Press Enter. The extracted parts, quantities, and descriptions will automatically spill into the adjacent rows and columns to complete your form.

Use the FILTER Function in Microsoft 365 or Excel 2021

Automatic Updates: Whenever you change the selection in your drop-down list, the FILTER function will automatically update the extracted data for immediate printing.

Solution 2

Use Alternative Formulas for Older Excel Versions

If you are using an older version of Excel and encounter a #NAME? error, use backward-compatible functions like INDEX and MATCH.

Advanced Formulas in WPS Office

Extract and Format Table Data Easily in WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array functions like FILTER, making it simple to create dynamic printable forms. It offers seamless compatibility with Microsoft Excel formulas, ensuring your data extraction works flawlessly.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the source table and printable form.
2. Ensure destination cells are unmerged: Select the form area and verify cells are unmerged using the Merge & Center button on the Home tab.
3. Input the FILTER function: Click on the target cell and type =FILTER() to link your drop-down list to the source table, specifying your inclusion criteria.
4. Press Enter to extract data: Hit Enter to instantly spill the matching location, parts, and quantities into your form.
5. Preview and print: Navigate to the Menu or use Ctrl+P to access Print Preview, ensuring your form is perfectly formatted before printing.

Free, lightweight, and fast office suiteFull compatibility with Microsoft Excel formulas and file formatsNatively supports dynamic array functions like FILTER for instant data extractionBuilt-in Print Preview makes formatting and printing professional forms a breeze

microsoft office alternative - wps office

Frequently Asked Questions

What does the _xlfn. prefix mean in my Excel formula?

The _xlfn. prefix indicates that the formula uses a function (such as FILTER) that is not supported by your current version of Excel. Excel adds this prefix to preserve the formula structure, but it will not compute correctly and usually results in a #NAME? error.

Why is my FILTER function returning a #SPILL! error?

A #SPILL! error occurs when there is not enough empty space for a dynamic array function to output its results. This commonly happens if the destination cells contain other data, or if the cells within the spill range are merged. Unmerge the cells and clear the surrounding area to resolve this.

How can I exclude blank rows when extracting data for my form?

When using the FILTER function, you can add an additional condition to the 'include' argument using multiplication. For example, setting the criteria to (Table[Location]=A1)*(Table[Part]<>") ensures that it only extracts rows that match your location and have a nonblank part entry.

Excel & Data Problems