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

Sandi Arnold 1 Reputation point
2021-04-06T05:27:18.733+00:00

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??

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,639 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Emily Hua-MSFT 27,526 Reputation points
    2021-04-06T08:49:50.367+00:00

    @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.