How to Automatically Number Filtered Excel Records for Mail Merge in WPS Office
Question details
The user needs to add an automatically generated sequential number to each row returned by a FILTER formula in WPS Excel so the filtered list can be used as identifiable records in a Word mail merge.

- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- A FILTER formula creates a dynamic list of records for a Word mail merge, and each filtered entry must be assigned a record number.
- Observed behavior
- The expected result is a spilled range whose first column contains a unique sequential number for each filtered record, and the numbers update when the filter result changes.
Open the workbook that contains the source data and confirm that your WPS Office version supports the LET, FILTER, SEQUENCE, and HSTACK functions. Check that the source range and FILTER criteria return the correct records before adding the numbering formula.
Use LET, FILTER, SEQUENCE, and HSTACK to Add Automatic Record Numbers
Use this solution when you need a dynamic spill range that numbers only the records returned by the FILTER formula for a Word mail merge.
The formula stores the FILTER result in a variable with LET, counts the filtered rows with ROWS, creates a matching sequential list with SEQUENCE, and places the numbering column next to the filtered data with HSTACK. Because the result is a spill range, the numbers are recalculated whenever the source data or the criteria change.
Launch WPS Office and open the Excel workbook that contains the data used by the FILTER formula.
Click the top-left cell where you want the automatically numbered filtered list to appear.
Type =LET(filtered,FILTER(B2:C8,C2:C8="a"),r,ROWS(filtered),auto_num,SEQUENCE(r),HSTACK(auto_num,filtered)) and press Enter.
Confirm that the first spilled column contains a unique sequential number for each filtered record and that the numbers update when the filter criteria or source data changes.

Use WPS Office Excel to Number Filtered Records for Mail Merge
WPS Office Excel can create the automatically numbered filtered list directly with LET, FILTER, SEQUENCE, and HSTACK. Enter the formula once, and the spill range will update dynamically for your Word mail merge without a paid Office subscription.
- 1. Open WPS Office Excel: Launch WPS Office and open the workbook that contains the source data for the filtered list.
- 2. Select the top-left result cell: Click the cell where the auto-numbered filtered list should start.
- 3. Enter the dynamic formula: Type =LET(filtered,FILTER(B2:C8,C2:C8="a"),r,ROWS(filtered),auto_num,SEQUENCE(r),HSTACK(auto_num,filtered)) and press Enter.
- 4. Use the numbered list in Word mail merge: Open WPS Writer or Microsoft Word, start a mail merge, and select the WPS Excel workbook or spilled range as the data source.
- 5. Check automatic updates: Change the source data or FILTER criteria and verify that the record numbers and filtered rows update automatically.

Frequently Asked Questions
Can I use this formula in an older .xlsx workbook in WPS Office?
Yes, if your WPS Office version supports LET, FILTER, SEQUENCE, and HSTACK. Save the workbook as .xlsx to maintain compatibility with Excel.
How do I use the numbered filtered list in a Word mail merge?
Open WPS Writer or Word, start the mail merge, select the workbook as the data source, and choose the sheet or named range that contains the spilled result. The first column will contain the record number.
Will the record numbers update when the FILTER criteria change?
Yes. The SEQUENCE function is based on the row count of the FILTER result, so the record numbers recalculate automatically whenever the filtered list changes.
What can I do if HSTACK is not available in my WPS Office version?
If HSTACK is not supported, place the SEQUENCE formula in one column and the FILTER formula in adjacent columns to create the same numbered layout manually.




