Pivot Table Issues

How to Sort Pivot Table Results in a Custom Order in Spreadsheets

Anum Mushtaq Oct 21, 2026 870 views

Question details

The user needs to display product descriptions in a pivot table in a predetermined, custom order rather than the default alphabetical order, ensuring this applies automatically to the data.

How to Sort Pivot Table Results in a Custom Order in Spreadsheets

Product: Spreadsheets
Device & OS: not provided
Scenario: Organizing and presenting sales and return data in a pivot table according to a specific company product hierarchy or custom sequence.
Observed behavior: By default, the pivot table automatically sorts product descriptions alphabetically, which disrupts the required custom layout.

Before you start

Prepare your desired product sequence by typing the items in the exact order you want them to appear into a blank column in your spreadsheet, so they are ready to be imported as a custom list.

Solution 1Recommended

Sort Using a Custom List

Define a custom sequence in your spreadsheet settings and apply it directly to the pivot table sorting options for an automated, reusable order.

Creating a custom list is the most efficient method because it only needs to be set up once. After it is saved to the application, you can apply it to any pivot table or standard data range in the future.

Create the custom list

Go to the application settings or options menu, locate the 'Custom Lists' feature (often found under Advanced settings), and type or import your desired product order, then click 'Add'.

Access pivot table sort options

Right-click on any product description item inside your existing pivot table, select 'Sort', and then choose 'More Sort Options'.

Apply the custom list to the pivot table

In the sort dialog box, click on 'More Options', uncheck the box that says 'Sort automatically every time the report is updated', and select your newly created custom list from the 'First key sort order' drop-down menu. Click OK.

Persistent Settings: Once the custom list is configured, refreshing the pivot table with new sales or return data will automatically respect this custom product order.

Solution 2

Add a Numeric Helper Column to Source Data

Assign a specific numeric value to each product directly in the source data and use this column to drive the pivot table order.

Advanced Spreadsheet Management

Easily Sort Pivot Tables with WPS Spreadsheet

WPS Spreadsheet provides powerful pivot table functionalities, allowing you to quickly define custom lists and sort your data exactly how you need it without complex workarounds.

1. Open your data file: Launch WPS Spreadsheet and open the file containing your sales and return data.
2. Create a custom list: Navigate to 'Menu' > 'Options' > 'Custom Lists'. Type your predetermined product order and click 'Add'.
3. Insert the pivot table: Select your source data, go to the 'Insert' tab, click 'PivotTable', and place it in your desired worksheet.
4. Sort by your custom list: Right-click the product field in the generated pivot table, select 'Sort', navigate to the custom sort options, and choose your newly added list.

Fully compatible with Microsoft Excel (.xlsx and .xls) formatsIntuitive pivot table creation and customization toolsBuilt-in custom list manager for advanced data sortingLightweight, fast, and free to use

microsoft office alternative - wps office

Frequently Asked Questions

Why does my pivot table sort alphabetically by default?

Pivot tables are programmed to automatically organize text-based row and column labels in alphabetical order (A-Z) and numeric data sequentially to make unstructured datasets instantly readable.

Will the custom list order update automatically if I add new data?

Yes. Once a custom list is applied to a specific pivot table field, any new matching items added to your source data will automatically fall into their designated place in the custom sequence when you refresh the pivot table.

Can I manually drag and drop items to sort a pivot table?

Yes. For quick, one-time adjustments, you can select a row label in the pivot table, hover over its border until a four-sided arrow cursor appears, and drag it to your preferred position in the list.

Excel & Data Problems