• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4611
  • Last Modified:

Sorting Excel pivot table

I've attached a file containing the data I'm working with. If you look on the Pivot Watch List tab you will see a column labeled "Sum of $ Change".

I would like to sort this column from lowest to highest and only see the top 10 values so that our marketing team will know which product groups are losing the most money from year to year.

When I try to sort the pivot table it gives me this error

"AutoSort and AutoShow cannot be used with custom calculations that use the positional references. Do you want to turn off AutoSort/Show?"

I click Yes but nothing happens. Any ideas on how to sort this list?
SalesMix-With-ItemDescriptions.xlsx
0
David11011
Asked:
David11011
1 Solution
 
Jerry PaladinoCommented:
The Sum of $ Change can be sorted if you remove the AutoSort from the Pivot Table.  It seems that replying YES to the dialog box it presents does not actually turn off the option.   It can be turned off manually as shown in the following screen shots.   Once sorted, you can see the Bottom 10 but using the TOP 10 filter option does not work with the positional calculated column.   An option is to hide the rows below the 10th item.   Sort DialogSort DialogSort DialogSort Dialog
0
 
David11011Author Commented:
Wow! thanks for the screenshots. nice reply. that fixed it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now