Calculation Issues

How to Keep Excel Subtotal Rows Fixed When Sorting a Table

Huda Qurayshi Sep 27, 2026 869 views

Question details

The user wants to sort a table containing song sets without moving the subtotal rows out of their fixed positions.

How to Keep Subtotal Rows Fixed When Sorting a Table in Excel

Product: Excel for the web
Device & OS: not provided
Scenario: Sorting an Excel table that includes both standard data rows and subtotal rows, specifically organizing sets of data.
Observed behavior: Standard sorting moves the subtotal rows out of their intended positions, mixing them with regular data rows instead of anchoring them at the end of their respective sets.

Before you start

Ensure your dataset has a dedicated column for defining the sort order (such as a 'Set Order' or 'Row Number' column), as this is essential for keeping specific rows anchored in place.

Solution 1Recommended

Use Decimal Sort-Order Values for Subtotal Rows

Assigning decimal numbers to your subtotal rows ensures they stay exactly where you want them relative to the whole numbers used for your standard data rows.

By utilizing decimals, you can slip the subtotal rows between integer groups. For instance, if your first set of data rows goes from 1 to 10, assigning 10.1 to the subtotal row forces it to remain strictly after row 10 but before row 11.

Assign Decimal Values to Subtotals

Locate the sort order column for your subtotal rows. Enter a decimal value that falls just after the last item of the set (for example, type 10.1 in the cell next to your first subtotal, and 29.1 for your second subtotal).

Convert Text to Numbers (If Necessary)

If your numbers are formatted as text, they will not sort properly. Select the sorting column, go to 'Data' > 'Text to Columns' in the ribbon, and simply click 'Finish' to force Excel to recognize them as numeric values.

Sort the Table

Select your entire table, navigate to the 'Data' tab, click 'Sort', and choose to sort by the 'Set Order' column in ascending order.

Use Decimal Sort-Order Values for Subtotal Rows

Data Sorting: Using decimal values effectively anchors your subtotal rows while allowing the integer-numbered rows within the sets to be reordered freely.

Efficiently sort complex tables

Use WPS Spreadsheet to Sort Tables with Subtotals

WPS Office offers a robust Spreadsheet application that allows you to easily sort, filter, and manage complex tables with subtotal rows without losing your data structure.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the table and subtotal rows.
2. Assign decimal values: In your sorting column, enter decimal numbers (like 10.1 and 29.1) for the subtotal rows to lock their relative positions.
3. Sort the dataset: Select the data range, go to the 'Data' tab, click 'Sort', and sort the column in ascending order.

Easily sort data by multiple columns and custom decimal criteria.Fully compatible with Microsoft Excel formats (.xlsx, .xls).Built-in Text-to-Columns feature to quickly fix data formatting issues.Free, lightweight, and user-friendly interface for powerful data analysis.

Frequently Asked Questions

Why do my numbers with decimals sort incorrectly in ascending order?

If decimals sort incorrectly, the spreadsheet application might be reading the column as text instead of numbers. Select the column, go to the Data tab, select Text to Columns, and click Finish to convert them to proper numeric values.

Can I use a built-in Subtotal feature instead of sorting manually?

Yes, if your data is organized properly, the built-in Subtotal feature under the Data tab automatically inserts subtotal rows whenever a specified column's value changes. This can often eliminate the need for manual decimal sorting.

Will this method work if I add new rows to the data sets later?

Yes, as long as you assign an appropriate integer to the new data rows (e.g., 1 to 10) and ensure the subtotal row retains a decimal value (e.g., 10.1) at the end of the group sequence, the sorting will remain accurate.

Excel & Data Problems