Fix Excel Pivot Table Placing New Items at the Top
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.

- 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.
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.
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.
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'.
In the Sort dialog box, select the 'Manual (you can drag items to rearrange them)' radio button and click 'OK'.
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.
Click 'Refresh' on the PivotTable Analyze tab to ensure the new item remains permanently fixed in its manually assigned position.

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. Open Your Data: Launch WPS Spreadsheet and open your existing dataset or workbook.
- 2. Insert a Pivot Table: Navigate to the 'Insert' tab on the ribbon and click on 'PivotTable'.
- 3. Arrange Fields: Drag and drop your desired fields into the Rows, Columns, and Values areas in the side pane.
- 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.

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.




