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

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 Pitts
Allen Pitts
  • 3
  • 2
  • 2
2 Solutions
ShumsDistinguished Expert - 2017Commented:
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
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.
ShumsDistinguished Expert - 2017Commented:
Which means provided solution was helpful?
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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