pivot table SORT in data source order

[drom]

Hi and thanks in advance!

I am using a table with only 2 columns

• Columna A: COMPANY
  • Can contain X companies (usually over 150)
• Column B Sort order

In this table if I have most of the time many companies, but usually we work 90% of the time with 10-15 companies
This is why I have the Sort Order Column, If I want to see first CocaCola, and 2nd PEPSI I am gonna put close to CocaCola on rhe Sort to order column a number say eg: -50 and for PEPSI -25 and them I will sort this table in ascending order by this 2nd column

• This works PERFECT when working with Data validation List cells attached to the Company Column

But with my 1st Columna has many companies I do not want to use data validations cells in my CRM's.

• So I use a pivot table with only 1 field ( the Company ) on the Filters position,
• I don't allow multiple selection and I add a Slicers as well
  • If the end user selects >1 company using the slicers a conditional formatting shows RED colour on the Pivot table Filters Fileld

My problem

• Despite this pivot table has SORT in data source order the Filters keeps showing everything A-Z
• And I can Sort the country Slicer for this Pivot table in data source order

Ant idea how can I fix this??

Please I do not want to use FORM Controls

 

[Misca]

Try using Power Pivot instead of old fashioned pivot tables. Use Power Query to add an index column to your dimension table and sort the items by that column. If you don't want to use a separate dimension table you can add the sort by column directly to the fact table, which is a bit more tricky.

If Power Pivot / Power Query is out of the question you could sort your data by a custom list. This is useable if you keep using the same items over and over again and there's only a handful of items in your list.