Microsoft Office 365, Excel Pivot table sort

Hello Experts,

Have spent some time Chinese engineering a pivot table.
Finally got it to format the audience is used to seeing it
except for one sort.

In Chris_report_c the fourth column marked 'Total'
displays the IRFR (incremental rate from revenue)
from smallest to largest.

Allens_report does not sort the totals.

Tried just a straight up Sort Z to A but Excel complains
that it cannot sort because the data is in a pivot table.

So the Total columns is right-clicked and Value Field
Setting is chosen and at the Show Values As tab
In the Show Values As dropdown the Rank Smallest to Largest
option is chosen but this returns '##'.

Have tried a dozen other combinations and screen printed
all eight or so dialogue boxes to try and find the setting
that will sort the column. No luck.

Can someone show me how to get that column to sort?


Allen in Dallas

PS Have attached a copy of the file in case an empirical approach is preferred.
Allen PittsBusiness analystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShumsExcel & VBA ExpertCommented:
Hi Allen,

You mean like attached?
  1. Click on Header Cell of Payer's ID Col
  2. Click on More Sort Options
  3. Click on Ascending (A to Z) by: From the DropDown, pleas select Total
  4. Press OK
  5. Click on Header Cell of Pricing Rule Col
  6. Click Sort A to Z

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
peter mostmansIT Developer & AnalistCommented:
That indeed does the trick.  The wierd thing is that it doesn't work when positioning in the Total column and try to do the sort there.
ShumsExcel & VBA ExpertCommented:
Which means provided solution was helpful?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

peter mostmansIT Developer & AnalistCommented:
Hi, Shums, that you should ask the original poster, but I tried it myself and it seemed to work for me :)
Allen PittsBusiness analystAuthor Commented:
Hello Shums and Peter,

Thanks for your replies.
The solution offered sorts the worksheet on the tab marked 'Payer Summary' by PAYER_ID.
If one looks at the jpg called Chris_report_c it is seen that the worksheet is sorted by the last
column, the dollar totals, not the PAYER_ID.

That's what is needed: sort by the column with the dollar totals from smallest (or greatest negative)
to largest.

Tried the six steps provided above. I get a dialogue box titled 'Sort by Value' but is also
says 'Summary     Sport PRICING_RULE by total in ascending order'. This is not what is required
the needed is by the column just to the right of the PRICING_RULE column, that is the column
with the dollar figures.

Thanks again.

Allen in Dallas
peter mostmansIT Developer & AnalistCommented:
Well, Allen,

That's exactly what it does, see attachment.
Allen PittsBusiness analystAuthor Commented:
Still not sorting by dollar value but your effort is appreciated. Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.