Pivot Table Issues

Fix Excel Pivot Table Placing New Items at the Top

Maira MehtabMaira Mehtab Nov 2, 2026 869 views

Question details

The user needs to prevent newly added data items from automatically appearing at the top of an Excel Pivot Table row or column field.

How to Fix Excel Pivot Table Placing New Items at the Top
Product
Microsoft Excel
Device & OS
not provided
Scenario
Adding new data to an existing source range and refreshing the Pivot Table.
Observed behavior
Newly added items are displayed at the very top of the row or column field instead of appending to the bottom.
Before you start

Ensure you have completely refreshed your Pivot Table so that all newly added items from your source data are visible in the layout before you attempt to rearrange them.

Solution 1Recommended

Change Pivot Table Sort Settings to Manual

Switch the sorting settings to manual to stop the Pivot Table from automatically forcing new items to the top, allowing you to drag them to your preferred position.

By default, Pivot Tables may apply automatic sorting rules based on alphanumeric values or custom lists. When you switch to manual sorting, you take full control over the exact placement of both existing and new items.

1
Open Sort Options

Right-click on any item within the affected row or column field in your Pivot Table, select 'Sort' from the context menu, and click on 'More Sort Options'.

2
Select Manual Sorting

In the Sort dialog box, select the 'Manual (you can drag items to rearrange them)' radio button and click 'OK'.

3
Reposition the New Item

Click the cell containing the newly added item, hover your cursor over the cell border until it turns into a four-sided cross arrow, and drag the item down to the bottom of the list.

4
Refresh and Verify

Click 'Refresh' on the PivotTable Analyze tab to ensure the new item remains permanently fixed in its manually assigned position.

Change Pivot Table Sort Settings to Manual
Automatic Sorting Overrides: If you leave the setting on 'Ascending' or 'Descending', any manual drag-and-drop adjustments you make will be overridden and reset the next time you refresh the Pivot Table.
Easy Pivot Table Management

Effortlessly Manage Pivot Tables with WPS Spreadsheet

WPS Office provides a highly compatible and intuitive spreadsheet tool that makes creating, formatting, and sorting Pivot Tables incredibly straightforward. You can easily manage data layouts and sort items exactly where you want them.

  1. 1. Open Your Data: Launch WPS Spreadsheet and open your existing dataset or workbook.
  2. 2. Insert a Pivot Table: Navigate to the 'Insert' tab on the ribbon and click on 'PivotTable'.
  3. 3. Arrange Fields: Drag and drop your desired fields into the Rows, Columns, and Values areas in the side pane.
  4. 4. Sort Manually: Right-click any row item to quickly access custom sort options, allowing you to manually place new data items exactly where they belong.
Fully compatible with Microsoft Excel (.xlsx, .xls) formatsIntuitive drag-and-drop Pivot Table builderAdvanced custom sorting and filtering without complex menusLightweight, fast, and completely free to download
microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel automatically put new Pivot Table items at the top?

This occurs because the specific field is subjected to automatic sorting rules (such as descending order or a specific custom list) or the default insertion behavior is conflicting with your dataset. Setting the field sort to manual resolves this.

Can I use a custom list to dictate where new items go?

Yes. You can create a Custom List in Excel's advanced settings and then configure your Pivot Table's 'More Sort Options' to sort using that specific list. This ensures items always display in a predefined sequence.

Why does my manually sorted item move back after I hit refresh?

If your Pivot Table field is set to 'Ascending' or 'Descending', it will automatically re-sort all items according to those rules every time the data is refreshed. You must switch the sort option to 'Manual' to preserve your custom drag-and-drop order.