Power Query Problems

Sort Customer Plan Blocks and Build Summary Tables in WPS Office Spreadsheet

Chanuka Geekiyanage Nov 13, 2026 868 views

Question details

The user needs to sort a yearly plan table by customer while keeping each customer’s complete plan together and automatically summarize plan categories.

Sort Customer Plan Blocks and Build Summary Tables in WPS Office Spreadsheet

Product: WPS Office Spreadsheet
Device & OS: not provided
Scenario: A yearly plans sheet has customer names only in the first row of each block, with blank cells below, which prevents normal sorting and automatic summaries.
Observed behavior: Sorting by the customer column splits customer blocks or sorts only the named rows, and there is no automatic customer/category summary.

Before you start

Confirm that the plan table has consistent columns for Customer, Plan, and Plan Category, and insert a blank helper column next to the Customer column before sorting.

Solution 1Recommended

Sort Customer Plan Blocks with a Helper Column in WPS Office Spreadsheet

Use this method when you need to sort complete customer blocks without changing the original plan layout.

WPS Office Spreadsheet does not require Power Query for this task. A helper column copies each customer name down through blank rows, so the customer block can be sorted as one unit.

Open the yearly plans sheet and add a helper column

In WPS Office Spreadsheet, open the yearly plans workbook and insert a blank column next to the Customer column.

Fill customer names down through blank rows

In the first data row of the helper column, enter =IF(A2<>"",A2,C1) and drag the fill handle down to the last plan row.

Convert the helper formulas to static values

Select the filled helper column, right-click, choose Copy, then right-click and choose Paste Special > Values so the customer names remain fixed during sorting.

Sort the full block by customer

Select the entire table, go to the Data tab, click Sort, set Sort by to the helper column, choose A to Z, and click OK.

Remove the helper column

Delete the temporary helper column after sorting. The customer blocks now stay together.

Sort Customer Plan Blocks with a Helper Column in WPS Office Spreadsheet

Customer blocks stay together: The helper column makes the blank customer cells sortable, so each customer’s complete plan block remains together.

Solution 2

Create an Automatic Category Summary with a PivotTable in WPS Office Spreadsheet

Use this method when you need a summary table that counts plan categories per customer and can be refreshed.

WPS Spreadsheet direct solution

Sort Customer Plan Blocks and Summarize Categories with WPS Office Spreadsheet

WPS Office Spreadsheet can sort complete customer blocks with a helper column and build a refreshable category summary with a PivotTable, so you do not need Excel Power Query.

1. Download WPS Office and open the workbook: Download WPS Office Free, launch WPS Spreadsheet, and open the yearly plan workbook.
2. Fill customer names down: Add a blank helper column, enter =IF(A2<>"",A2,C1) in the first data row, and drag the fill handle through all plan rows. Copy the column and paste it as values.
3. Sort the complete customer blocks: Select the full table including the helper column, go to Data > Sort, sort by the helper column A to Z, and click OK.
4. Create the automatic summary: Select the filled table, go to Insert > PivotTable, choose New Sheet, and drag Customer to Rows, Plan Category to Columns, and Plan Category to Values.
5. Refresh after source changes: Right-click the PivotTable and select Refresh, or go to Data > Refresh All whenever the source plan table changes.

Keeps each customer’s full plan block together during sortingCreates a refreshable customer/category summary with a PivotTableOpens and saves .xlsx files with Microsoft Excel compatibilityFree, lightweight Office suite with familiar Data and Insert tab layoutNo Power Query add-in or advanced setup required

microsoft office alternative - wps office

Frequently Asked Questions

Can I use Power Query in WPS Office Spreadsheet for this task?

WPS Office Spreadsheet focuses on built-in sorting, formulas, and PivotTables. You can achieve the same sorted blocks and summary without Power Query by using a helper column and a PivotTable as described above.

How do I keep each customer’s rows together when sorting in WPS Spreadsheet?

Add a temporary helper column that fills the customer name into every row using =IF(A2<>"",A2,C1), paste it as values, include the helper column in the Data > Sort range, then delete it after sorting.

How can I show whether a customer has a specific plan category?

Build a PivotTable with Customer in Rows and Plan Category in Columns and Values. A count appears when the category exists; blank cells mean the customer does not have that plan category.

Will the PivotTable summary update automatically?

It updates when you refresh it. Right-click the PivotTable and select Refresh, or go to the Data tab and choose Refresh All after changing the source table.

Does this method work if customers have different numbers of plan rows?

Yes. The helper column fills downward only until the next customer name, so each customer block can be a different size and still sort as one unit.

Excel & Data Problems