David has attended:
Excel Advanced course
Excel Advanced - Formulas & Functions course
Advanced Excel Dashboards course
Excel PowerPivot course
Pivot table , variant YOY, sort, decending order, chart
Hi,
I am creating a complaint dashboard using excel pivot tables.
One of the pivot tables /charts I am looking to create is a YOY variant .
I have managed to create this , but I now want to be able to sort the variation into descending order for visual purposes on the graph.
The pivot does not appear to give me an option to do this? The normal a-z auto sort does not work.
Can you advise if Variant Pivot tables can be sort in the manner I require?
RE: Pivot table , variant YOY, sort, decending order, chart
Hi David,
Thank you for the forum question.
I do not know if you use
Show values as -> Difference From
to get your YOY. If you then try to sort YOY Ascending or descending you get the error below.
"AutoSort and AutoShow cannot be used with custom calculations that use the positional references. Do you want to turn off AutoSort/Show?"
If you click yes to the question in the error above nothing will happen. The link below can help you turn off Auto sort.
https://www.experts-exchange.com/questions/28324629/Sorting-Excel-pivot-table.html
I am not sure without having the PivotTable in front of me if this will do the job. I am afraid that you will have to calculate YOY in a column in the source data to be able to do what you want.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
