Function Problems

How to Automatically Number Filtered Excel Records for Mail Merge in WPS Office

Chanuka GeekiyanageChanuka Geekiyanage Nov 13, 2026 868 views

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.

How to Automatically Number Filtered Excel Records for Mail Merge in WPS Office
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the source workbook

Launch WPS Office and open the Excel workbook that contains the data used by the FILTER formula.

2
Select the cell for the spill result

Click the top-left cell where you want the automatically numbered filtered list to appear.

3
Enter the dynamic numbering 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
Verify the record numbers

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 LET, FILTER, SEQUENCE, and HSTACK to Add Automatic Record Numbers
Formula ready for mail merge: The first column of the spilled result contains a unique sequential number for each filtered record, so you can use the spill range as the data source for your Word mail merge.

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. 1. Open WPS Office Excel: Launch WPS Office and open the workbook that contains the source data for the filtered list.
  2. 2. Select the top-left result cell: Click the cell where the auto-numbered filtered list should start.
  3. 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. 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. 5. Check automatic updates: Change the source data or FILTER criteria and verify that the record numbers and filtered rows update automatically.
Add automatic record numbers to filtered Excel data with LET, FILTER, SEQUENCE, and HSTACKKeep full compatibility with Microsoft Excel .xlsx files and formulasUse the dynamic spill range directly as a Word mail merge data sourceEnjoy a free, familiar spreadsheet experience in WPS OfficeApply the same formula technique to other filter criteria and source ranges
microsoft office alternative - wps office

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.