In MS Office 365 Excel, how do you prevent custom sorts from being lost during a pivot table refresh?

Question

In MS Office 365 Excel, how do you prevent custom sorts from being lost during a pivot table refresh?

Sandi Arnold 1

I am using Microsoft Office 365 (Excel) and I have a spreadsheet that I'm using as a Data Source for pivot tables on other tabs within the workbook. I have a need to resort my data regularly after I've updated a few cells here and there. That means I also need to refresh all of my pivot tables. However, once I do, my custom sort (which includes 3 fields) gets reset and I have to set up my sort every time. It's annoying. I see this was a problem in Excel 2013 too, but this is 2021 and I can't understand why this issue hasn't been resolved. Or maybe someone has come up with something and I simply can't find the solution because my searches are fruitless?? Can anyone help??

1 answer

Your answer

Answer 1

Emily Hua-MSFT 27,796

@Sandi Arnold
Do you set custom sort options like the article "Sort data in a PivotTable or PivotChart" says?

I suggest you right-click the down arrow besides first column header, select the correct field > More Sort Options > More Options button > check whether you tick the box of "Sort automatically every time the report is updated". You may untick it to have a check.

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Sandi Arnold 1 Reputation point

2021-04-07T05:53:58.267+00:00

@Emily Hua-MSFT

Thanks, but I'm not trying to sort the pivot table. I am trying to sort the actual data in the tab that is used as the data source for the pivot tables. You see, the data source contains multiple rows of the same "items" because they are offered in more than one "place" and there are calculated fields to show when and where the items are available and for how long. So, the calculated fields rely on the rows being in the proper order or the "availability" field will not be correct. So, the data source must be updated daily, then sorted, and then the pivots refreshed. The pivots were created to allow me to slice & dice the info the way I want...there are other columns (fields) that I use to look for info (category, status, provider, favorite, etc.)

Thanks!
Sandi

Share via

In MS Office 365 Excel, how do you prevent custom sorts from being lost during a pivot table refresh?

1 answer

Your answer