Compare Excel Tables, Highlight Prices Below Minimums, and Sort Totals in WPS Office Spreadsheets
Question details
The user needs to compare client prices against minimum selling prices, identify below-minimum values, highlight them, and sort clients by total billing in an Excel table.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user manages client pricing data and needs an audit-ready view that flags invalid prices and ranks clients by billing totals.
- Observed behavior
- Below-minimum prices must be identified and visually highlighted, and client totals must be sorted from highest to lowest billing.
Confirm that the source data is in a structured range or Excel Table with client rows and consistent price columns. Keep the minimum selling price table available in the same workbook so Power Query can merge it with the price data.
Use Power Query to Unpivot the Price Table, Compare Minimum Prices, and Sort Client Totals
Best for a repeatable, refreshable comparison when the source data may grow or change.
Power Query separates data transformation from formatting. It can identify below-minimum prices and sort client totals, but you still apply highlight formatting in the worksheet after loading the results.
Select any cell in the price table, go to Data > From Table/Range in WPS Spreadsheets, and confirm the table range.
In Power Query, select the client name column, right-click it, and choose Unpivot Other Columns to convert all price columns into Attribute and Value columns.
Go to Home > Merge Queries, choose the minimum price table, and match the product or Attribute column to the corresponding minimum-price column.
Add a conditional column that marks Value < Minimum Selling Price as invalid, then use Transform > Group By to sum Value by client for total billing.
Click Close & Load, then sort the loaded total billing column using Data > Sort or by sorting the total column inside Power Query before closing.
Use a Dynamic-Array Formula with Brackets and Conditional Formatting to Highlight Invalid Prices
Use this for a quick formula-based result in smaller workbooks when a refreshable Power Query setup is not required.
Compare Excel Tables and Highlight Below-Minimum Prices in WPS Office Spreadsheets
Use WPS Spreadsheets' Power Query workflow to merge price and minimum tables, flag invalid values, sort client totals, and apply conditional formatting for a clear audit view.
- 1. Load the source table: Open your Excel workbook in WPS Spreadsheets, select the price table, and go to Data > From Table/Range.
- 2. Prepare the comparison: Unpivot price columns, merge with the minimum selling price table, and add a conditional column for values below minimum.
- 3. Group and sort totals: Group by client to sum total billing, then sort the total column in descending order in Power Query or in the worksheet.
- 4. Highlight invalid prices: After Close & Load, select the invalid-price column and apply Home > Conditional Formatting > Highlight Cells Rules to mark below-minimum values.
Frequently Asked Questions
Why does Power Query identify invalid prices but not highlight them?
Power Query transforms and loads data; it does not apply worksheet formatting. After using Close & Load, select the price column and go to Home > Conditional Formatting > Highlight Cells Rules to format below-minimum values.
Can I refresh the comparison after changing the source price table?
Yes. Select any cell in the query result table, go to the Data tab or Query settings, and choose Refresh All, or right-click the query table and select Refresh.
How do I sort clients by total billing after merging the tables?
In Power Query, use Transform > Group By to create a total billing column and sort it descending before Close & Load. In the worksheet, select Data > Sort and sort the total billing column descending.
Does WPS Spreadsheets support the same Excel files and dynamic arrays?
WPS Spreadsheets opens .xlsx workbooks and supports common formula and Power Query workflows. For dynamic-array formulas, check that your WPS version supports spill range behavior.
What should I do if new client rows are not included in a formula-based result?
Extend the formula references to include the new rows or use an Excel Table and Power Query so the output updates through a refresh instead of manual formula changes.
