Hello,

I am currently trying to filter out a 'Work Schedule' for multiple people into seperate auto-updating tables.

To do this, I have created a pivot table based on the original table as this is the best idea I could come up with. My main problem is that now I have filtered into a pivot table, I am unable to sort the pivot table by number under one of the columns labeled 'Work In'. The only option that appears is sort 'A to Z' and visa versa. On top of this, once I try this option, my data gets grouped together. Is there anything I could do to fix this? Should I be using a different method?

I do not use pivot tables very often so any help would be appreciated.

Many Thanks,

Brockhurst

Hello,

A pivot table works with hierarchies. If you drag Field1 into the Rows area and then drag Field2 into the Rows area, you will not be able to sort the pivot table by the values of Field2, because the the main sort order is established by Field1.

In that case, you may want to change your approach. Do not put Field1 into the Row area but into the Filter area instead.

Then drag Field2 into the Row area. Now Field2 is the top level of the hierarchy and can be sorted. You can now use the Filter of the Pivot Table to select the "Field1" value that you want to inspect. 

Hope that makes sense.

Hi Brockhurst,

I have few questions that will help us to answer.

1. Which version of Excel do you use?
2. Can you share the fields you use?
3. Did you format the "Work In" field as a number?
4. Are you familiar with the option "More Sort Options"? It allows you to filter by additional fields that are used by the PivotTable.