Combine Values and Status Comments and Summarize by Month in WPS Spreadsheets
Question details
The user needs to restructure monthly value and status data in WPS Spreadsheets so payments, billed items, and unbilled items can be summarized by site and month.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A user has wide monthly columns containing values and status comments for different sites and wants to normalize the data into Site, Status, Month, and Value fields for monthly summaries.
- Observed behavior
- The source data contains separate month columns, making it difficult to summarize by site, status, and month without reshaping the table.
Make sure your monthly source table has consistent month column names, such as Jan, Feb, and Mar, and a separate Status column. If you plan to combine values and comments in one cell, prepare to enable Wrap Text after adding line breaks.
Use Power Query to Unpivot Monthly Data into Site, Status, Month, and Value Fields
Use this when you need to reshape wide monthly data into a normalized table that a PivotTable or SUMIFS can summarize by site, month, and status.
Unpivoting converts month columns into rows. This keeps the original data intact and creates a clean table for reporting paid, billed, and unbilled amounts by site and month.
Click any cell inside the source table, go to the Data tab, and choose From Table/Range to open Power Query.
In Power Query, select the Site and Status columns, then click Transform and choose Unpivot Other Columns.
Rename the Attribute column to Month and the Value column to Value so the table has clear Site, Status, Month, and Value fields.
Use Transform > Replace Values to change any inconsistent status entries to Paid, Billed, or Unbilled before loading the table.
Click Close & Load to place the normalized table in a new worksheet, ready for PivotTable or SUMIFS summaries.
Combine Values and Status Comments in One Cell with a Line Break
Use this when you want to display a value and its status comment together in a single cell, such as an amount and its paid or billed note.
Summarize the Normalized Table by Site and Month with a PivotTable or SUMIFS
Use this after creating the normalized table when you want monthly totals by site and status without manually copying values.
Simplify Monthly Payment Summaries with WPS Spreadsheets Power Query
Use Power Query in WPS Spreadsheets to unpivot monthly columns into a tidy Site, Status, Month, and Value table, then summarize with a PivotTable or SUMIFS. This makes it easy to track payments, billed items, and unbilled items by site and month.
- 1. Download and install WPS Office: Go to the WPS Office download page, download the installer, and open WPS Spreadsheets.
- 2. Open the monthly data workbook: Open the workbook that contains the monthly value and status data in WPS Spreadsheets.
- 3. Unpivot the source table: Click Data > From Table/Range, select the Site and Status columns, and choose Transform > Unpivot Other Columns.
- 4. Load and summarize the normalized table: Click Close & Load, then create a PivotTable or SUMIFS formulas to report paid, billed, and unbilled amounts by site and month.
Frequently Asked Questions
How do I unpivot monthly columns in WPS Power Query?
Click Data > From Table/Range in WPS Spreadsheets, select the Site and Status columns, then choose Transform > Unpivot Other Columns. Rename the Attribute column to Month and the Value column to Value.
Why does my combined value and comment not show on two lines?
The line break only appears if you use CHAR(10) between the values and enable Wrap Text. Select the cell, go to the Home tab, and click Wrap Text.
Can I summarize the normalized data without formulas?
Yes. Select the normalized table, go to Insert > PivotTable, drag Site to Rows, Month to Columns, Status to Columns or Filters, and Value to Values.
Does WPS Spreadsheets support TEXTJOIN and FILTER?
WPS Spreadsheets supports TEXTJOIN in many versions, but dynamic array functions such as FILTER may depend on the version. For simple value and comment combinations, use =A2&CHAR(10)&B2 with Wrap Text.
