Chart & Visualization Issues

WPS Spreadsheet: Sort an Excel Chart by a Drop-Down Category

Nimra Malik Nov 12, 2026 869 views

Question details

The user needs a chart to automatically rank items according to a category selected from a drop-down list.

Product: WPS Spreadsheet
Device & OS: not provided
Scenario: A worksheet contains albums or songs with scores in multiple categories, such as Vocals and Lyrics. The user wants to select one category and display the related items in descending order in a chart.
Observed behavior: The chart needs a separate sorted data range so that changing the drop-down selection updates both the ranking and the chart.

Before you start

Arrange the source data with one item column and one column for each scoring category, using clear headers such as Item, Vocals, and Lyrics. Reserve cells for the category selector and the dynamic sorted output range.

Solution 1Recommended

Create a dynamic sorted chart with a category drop-down

Use this method when the chart must refresh automatically whenever the selected scoring category changes.

Keep the original data unchanged and create a separate output range for the chart. The SORTBY formula orders the items by the score column returned for the selected header, so the chart always uses the current ranking.

Build the source table

In WPS Spreadsheet, place item names in column A, category headers such as Vocals and Lyrics in row 1, and the corresponding scores in columns B:D or another adjacent range.

Add the category selector

Select the selector cell, open Data > Data Validation, choose List, and set the source to the category header cells, such as B1:D1.

Create the sorted item output

In an empty output area, enter a formula such as =SORTBY(A2:A10,XLOOKUP($H$2,B1:D1,B2:D10),-1), replacing H2 with the drop-down cell. This returns the items in descending order for the selected category.

Create the sorted score output

In the adjacent output column, enter =SORTBY(XLOOKUP($H$2,B1:D1,B2:D10),XLOOKUP($H$2,B1:D1,B2:D10),-1) so the scores remain aligned with the sorted items.

Insert the chart

Select the sorted item and score output range, then choose Insert > Chart. After the chart is created, select another category from the drop-down to refresh the ranking and chart.

Create a dynamic sorted chart with a category drop-down

Keep the chart linked to the output range: Do not use the original unsorted score columns as the chart source. If your WPS version supports spilled ranges, use the spilled output; otherwise, include enough rows in the chart source to cover all returned items.

Solution 2

Use a helper range when dynamic formulas are unavailable

Use this fallback when the installed spreadsheet version does not support SORTBY, XLOOKUP, or dynamic array results.

Dynamic chart ranking in WPS Spreadsheet

Build Drop-Down Driven Ranking Charts in WPS Spreadsheet

WPS Spreadsheet lets you create a category selector, calculate a sorted output range, and link a chart to that range so rankings update when the selection changes. Its familiar spreadsheet tools support formulas, data validation, charts, and Excel workbook workflows.

1. Prepare the data table: Open WPS Spreadsheet and place item names, category headers, and scores in a structured table.
2. Create the drop-down: Select the category cell and choose Data > Data Validation > List, then use the category header range as the list source.
3. Generate the sorted range: Enter SORTBY with XLOOKUP or CHOOSECOLS in an empty area to return the selected category and sort it in descending order.
4. Insert the chart: Select the sorted output range and choose Insert > Chart to create the visual ranking.
5. Test the selector: Choose another category from the drop-down and confirm that the output range and chart update automatically.

Automatically rank albums, songs, or other items by the selected categoryUse Data Validation drop-downs and SORTBY-based output ranges in one worksheetCreate and update charts without changing the original source tableOpen and save Microsoft Excel .xlsx files with compatible spreadsheet formatting

Frequently Asked Questions

Which WPS Spreadsheet features are needed for a dynamic category chart?

Use Data Validation for the drop-down, SORTBY to rank the results, and XLOOKUP or CHOOSECOLS to return the score column for the selected category. Then create the chart from the sorted output range.

Why should the chart use a separate output range?

The original table may store categories in separate columns, while the chart needs one ordered item-and-score range. The helper output range transforms the selected category into the order required by the chart.

How do I make the chart sort from highest to lowest?

Set the SORTBY sort order argument to -1. For example, =SORTBY(A2:A10,XLOOKUP($H$2,B1:D1,B2:D10),-1) sorts the item names by the selected scores in descending order.

What should I do if XLOOKUP or SORTBY is not available?

Use a helper range with INDEX and MATCH to retrieve the selected category, then use RANK, LARGE, or manual sorting before creating the chart. Updating the selector will recalculate the helper formulas.

Excel & Data Problems