How to Automatically Create Monthly Lists in WPS Excel from a Payment Table
Question details
The user wants to generate separate monthly lists of people who paid from a payment table, with the lists updating automatically when source data changes.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A payment table contains monthly payments by person, and the user needs per-month payer lists generated automatically.
- Observed behavior
- No automatic per-month list exists yet; the user wants formulas or Power Query to create one that updates when names or payments change.
Make sure the payment data is in a consistent tabular layout with a column for person names and one column per month, with no merged cells or empty header cells. Turning the range into an Excel Table first will make formulas and Power Query refresh more reliable.
Use an Excel Table and the FILTER Function to Generate Monthly Lists
Best for a formula-driven result that updates immediately when source values change.
This method works when the payment table has a name column and separate month columns with payment amounts. Storing the data as a formatted table makes column references stable and easier to read.
Open the workbook in WPS Spreadsheets, select the payment range including the header row, then click Home > Format as Table and confirm My table has headers.
Go to the Table Tools > Design tab, or click the table name box, and set the table name to PaymentTable.
Click the plus icon next to the sheet tabs to create a new worksheet, then rename it Monthly Lists.
In A1 type January Paid. In A2 enter =FILTER(PaymentTable[Name],PaymentTable[January]<>"","No payments") and press Enter.
Use the same formula in separate columns or blocks, replacing January with the actual month column name from the payment table.
Use Power Query to Unpivot the Payment Table for Flexible Monthly Lists
Use when the payment table layout changes often or when you want a clean month/name/value table to connect to PivotTables and slicers.
Create Automatic Monthly Payer Lists in WPS Spreadsheets
Use WPS Spreadsheets to turn a payment table into an Excel Table and add dynamic FILTER or Power Query outputs, so monthly payer lists refresh automatically in a familiar .xlsx workflow.
- 1. Open the payment workbook: Launch WPS Spreadsheets and open your .xlsx payment table file.
- 2. Make the source range an Excel Table: Select the payment range, click Home > Format as Table, and confirm the header row.
- 3. Create the monthly formula list: On a new Monthly Lists sheet, enter =FILTER(PaymentTable[Name],PaymentTable[January]<>"","No payments") and press Enter.
- 4. Expand to all months: Repeat the formula for each month column, or use Data > From Table/Range to build a refreshable Power Query output.
- 5. Save and maintain: Save the workbook as .xlsx and refresh formulas or queries after adding new payment rows.
Frequently Asked Questions
Which formula shows all names that paid in a given month?
Use =FILTER(PaymentTable[Name],PaymentTable[January]<>"","No payments") after converting the payment range to a formatted Excel Table; replace January with the month column header.
Will the monthly lists update when I add new payments?
Yes. If the source is an Excel Table and formulas or Power Query reference that table, WPS Spreadsheets updates results automatically or after Data > Refresh All.
Can I create the same monthly lists without Power Query?
Yes. Use the FILTER function or IFERROR/INDEX/SMALL array formulas on a separate worksheet; the FILTER method is simpler for most payment tables.
How do I stop blanks or zeros from appearing as paid names?
In the FILTER formula use the condition PaymentTable[Month]<>"" and add zero checks if needed; in Power Query remove blanks and zero values before loading.
Will the workbook still work in Microsoft Excel?
Save as .xlsx and avoid features unsupported by your Excel version; FILTER requires Excel 365/2021, but the table and Power Query output remain compatible.
