question

SandiArnold-3267 avatar image
0 Votes"
SandiArnold-3267 asked SandiArnold-3267 commented

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

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

office-excel-itpro
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

emilyhua-msft avatar image
0 Votes"
emilyhua-msft answered SandiArnold-3267 commented

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


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·