Sorting PivotTable Data with Multiple Fields in the Values Area

[Justin]

Hi all,

I'm working with a PivotTable and having trouble sorting -- I have an idea why it's not working but thought I would check.

Here is a replica of the sheet I'm working with (the data and fields are all fictitious but the structure is the same):

Pivot_Table_Sorting.xlsx

In rows we have sales reps with a forecast and actual revenue for each. The revenue field appears in the values area three times, calculated in different ways.

The first is a straight-up sum of all revenue for the year, so it appears for both forecast and actual.

The second two fields show the difference from forecast to actual for each sales rep -- so of course these columns only have values for "actual" since the forecast has zero variance from itself.

My challenge comes when I try to sort by either the % Diff. from Forecast or $ Diff. from Forecast fields. The PivotTable just won't let me do it and sorts instead by the Yearly Revenue field.

This happens both when the table is set up with its current structure as uploaded or when I put the Forecast/Actual field above the sales rep field. I've tried all the different ways of accessing the sort menu, but when I select in custom sort to sort sales reps according to % Diff. from Forecast descending, it just defaults to Yearly Revenue again.

My hunch is it won't let me do it because not every row has a value for that column and so it doesn't know how to sort it.

But it's quite inconvenient I think, as it doesn't allow you to easily see which reps have the biggest variance.

Am I missing something?

Thanks for any insight.

[Roger Govier]

Hi Justin
Sorry to be a while coming to this. Ken gave me a "nudge" to say that this question needed answering.

It can be done, as you will see in the attached file, but the method is far from intuitive!!!

I haven't bothered with your formatting, as you can deal with that for yourself.

I dragged Sales Rep to Row area
Forecast to Column Area
Revenue to Values
Revenue to Values as Difference from
Revenue to Values as 5 Difference from
I then hid columns E and G As they are the blank columns with zero difference from Forecast.

Then right click on any Sales Rep>Sort>More Sort Options
Select Descending>from the dropdown select Diff %
Click More options and select
Sort by>Values in selected column>$F$6 (the first %Diff value)>OK>OK

For some reason, it always defaults to $B$6, but if you change to $F$6 (or whatever column you want to sort by, then it works)

I would think that this is a bug and I will report it as such.

[Ken Puls]

Thanks for picking this up, Roger.

I stopped when I got to hiding the blank columns thinking... Really?

Always nice to get a second opinion to know I might actually not be missing something!

[Justin]

Hey Roger,

Thanks a lot for the help.

I tried to replicate your steps exactly, but I still can't get it to work for some reason.

You'll see in the screenshot below, when I go:

Click More options and select
Sort by>Values in selected column>$F$6 (the first %Diff value)>OK>OK

It says, "Sort Sales_Rep by % Diff in descending order using values this column: Actual" just beneath it.

But then when I press OK>OK it just seems to ignore it and still sort by the Revenue: Actual column ($B$6) instead. I tried this 3 or 4 times and no luck.

When I go to your pivot table that you added to the workbook and check out the sorting options to compare, it seems to indicate it has been sorted manually. Super weird...

Please let me know if you can see anything I'm doing wrong.



Many thanks.

[Roger Govier]

Hi Justin

Yep, it does say sorted Actual in the dialogue but ignore that - as I said there is a bug.
The only difference I can see in your screenshot, compared with mine (and I'm sorry I hadn't mentioned it), is I have un-checked the "Sort automatically every time report is updated" box.

Each time you do it, you have to go through the full procedure of first selecting Ascending or Descending, by the relevant field, as it will always default back to Manual.

It definitely does work, and I can happily switch back and forth between sorting by name, sorting Ascending by Diff%, sorting Descending by Diff $.

I did my original file with XL2010, but I have just confirmed that the outcome is identical under XL2007.

[Justin]

It worked!

That checkbox for "sort automatically" was the clincher. How satisfying to see it sort properly now

Just wondering, is it a good best practice to usually leave that box unchecked, or is it just in rare situations where it's necessary?

Thanks again.

Justin

[Roger Govier]

Hi Justin
In normal circumstances I wouldn't go anywhere near the More Options - so just leave the box to it's own devices.

As I said earlier, I believe this to be a bug in XL2007 and XL2010 and I will report it back to the Excel team.

[Justin]

Thanks Roger!

[randycoxclemson]

I am trying to do much the same thing, only finding no success with the steps mentioned in this thread (and in fact, that's what I tried first off).

I have a file with a field (dollars) for multiple dates. I filtered to select two dates and added a second copy of the dollars field only it is a "difference from" field based on the first date. Sorting on the difference produces random results. Here's an image:



Any ideas would be appreciated.

[Roger Govier]

Hi Randy

From your screenshot, I see you still have the Sort automatically box checked.
If you read through the thread, I said that I found that this needed to be unchecked.
Give that a try, and if it doesn't work, then upload a copy of your workbook and I'll take a look.