Sort Pivot Table Using Two Columns

Introduction

This is a short but, I am sure, a valuable page for those of you using Pivot Tables. In this page I will explain how it is possible to sort a Pivot Table using TWO columns at the same time.

English Premier League Table

I just decided a couple of days ago, as I accidentally opened a page in which they showed all of the EPL results for the current season to date, that I would program the league table using Excel.

I have done this before, just using ordinary cells and basic functions and formulas. This time, however, I decided to use Power Query and whatever else it took to achieve my ambition of creating a template that will work not only for the EPL but for any sporting competition that presents or that can present, the results in the way that I have been given.

PPP: Paper, Pencil, Plan

I broke my own rule as I started this exercise and did not use PPP: paper, pencil, plan. In other words, I didn't really think this through at all: I just switched on Excel and started typing. Actually, I got near to the end of my task and then realised I had built in several inefficiencies so I scrapped everything and started again. That cost me a morning's work!

Then I achieved what I wanted until it came to this:

My EPL Table that doesn't sort fully

I read around and didn't really find a solution to this problem. Then I tried to create a calculated field but it didn't work. Finally, I searched again and I came across Helgi on www.stackoverflow.com and she gave me the answer: I have edited Helgi's answer to make it more readable but it is simple to follow and to implement.

I used  a calculated field in the Pivot Table to represent the order that I wanted. I created a formula for the calculated field that gave me the correct order value. In my case it was tournament standings where points and goal difference decided the order. My formula was points * 100 + goal difference. I CALLED IT GDRank

Then in the Value Fields Settings for that field I selected  Show Value as ... Rank Largest to Smallest. That calculated field then showed the values 1, 2, 3 in my Pivot Table ... which was the correct rank of the teams in the tournament. Finally I changed the name of the calculated field  to Rank and sorted it largest to smallest and  the table now in the order I wanted.

Here is my EPL league table now:

The Rank column, via its calculated field, helps to sort the table into the right order

This file is not ready for downloading yet as I am still testing it with other data but if you need to sort a Pivot Table using two columns, this is how to do it!

Duncan Williamson

3rd March 2020