GoSkills
Help Sign up Share
Back to course

Sorting Values

Compact player layout Large player layout

Locked lesson.

Upgrade

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

About this lesson

Sorting the values area of a PivotTable.

Lesson versions

Multiple versions of this lesson are available, choose the appropriate version for you:

2016, 2019/365.

Exercise files

Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.

Sorting Values.xlsx
199.1 KB
Sorting Values - Completed.xlsx
199.4 KB

Quick reference

Sorting Values

Sorting the values area of a PivotTable.

Where/when to use the technique

When you’d like to order the values in a PivotTable in order (either ascending or descending).

Instructions

Sorting column values (including grand totals)

  • Right-click a cell in the column you wish to sort by  > Sort
  • To have the largest numbers at the…
    • Top:                     Choose Sort Largest to Smallest
    • Bottom:               Choose Sort Smallest to Largest
  • The entire PivotTable value area will be re-sorted into the order of the selected column

Sorting grand total rows

  • Right-click any value in the grand total row (except the bottom right cell) > Sort
  • To have the largest numbers at the…
    • Left:                     Choose Sort Largest to Smallest
    • Right:                   Choose Sort Smallest to Largest

Sorting values from left to right

  • Right-click a cell in the row you wish to sort by  > Sort  > More Sort Options
  • Change the Sort direction to Left to Right
  • To have the largest numbers at the…
    • Left:                      Choose Sort Largest to Smallest
    • Right:                   Choose Sort Smallest to Largest

Toggling automatic sorting

Turning off automatic sorting
  • Right-click one of the row label fields > Sort > More Sort Options > More Options…
  • Uncheck the AutoSort checkbox
Restoring automatic sorting
  • Right-click one of the row label fields > Sort > More Sort Options
  • Click More Options…
  • Re-check the AutoSort checkbox
  • Change the manual sort to Ascending/Descending as appropriate
  • Click the drop down and select the values field
Login to download
  • 00:04 Now we've already seen how we can sort based on our row labels or
  • 00:07 our column headings.
  • 00:09 But what if we want to sort based on values.
  • 00:11 For example, maybe we want to see our largest sellers at the top of the list.
  • 00:15 As you'd expect, this is pretty easy to do, you can just right click and
  • 00:18 say Sort > Largest to Smallest on the Grand Total column.
  • 00:22 And notice that it sorts both our food and
  • 00:24 our alcohol into the largest to smallest groups.
  • 00:27 So that's pretty easy, that's the Grand Total column.
  • 00:31 What happen if we did Grand Total row?
  • 00:33 Let's go right-click over here, and say Sort > Large to Smallest.
  • 00:37 Notice that it's now changed the order of our sales to the largest sales
  • 00:41 on the left-hand side based on our Grand Total.
  • 00:43 And we've moved our weeks into the order 3, 4, 2, 5,
  • 00:46 1 because week three has the largest sales.
  • 00:50 That's interesting.
  • 00:51 But what if we wanted to do something different?
  • 00:52 I'm going to resort this back into the proper week order
  • 00:55 by going to smallest to largest, we'll toss the previous sort.
  • 00:58 And now what I'd like to know is can I sort my data within these subcategories so
  • 01:03 that's actually showing the largest of sales based on week 1 sales.
  • 01:07 So let's go right-click, Sort > Largest to Smallest.
  • 01:11 And notice that it's no longer sorted in the Grand Total order.
  • 01:14 It's now sorted in the order for that individual column for both Food and
  • 01:18 for Alcohol.
  • 01:19 What if I now wanted to do something slightly different?
  • 01:23 I can see that Burgers had the largest sale in week 2.
  • 01:26 What if I wanted to sort my data for my weeks in order of my burger sales?
  • 01:31 Well, the challenge is, when I go right-click and I do something like a Sort
  • 01:34 > largest to Smallest, it's going to sort vertically, not horizontally.
  • 01:39 So what I need to do for that is go to More Sort Options.
  • 01:43 And inside the More Sort Options, you'll notice it is sorted largest to smallest,
  • 01:47 but in here, I can also change it to go from left to right.
  • 01:50 And at that point, it sorts it into a different order.
  • 01:53 Now, at this point, what you would expect to happen is if I were to change something
  • 01:57 in the underlying source data and I were to refresh the pivot table, it would
  • 02:00 automatically go back and apply the sorts to get everything organized correctly.
  • 02:05 So for example, let's go back and say, let's sort this largest to smallest.
  • 02:09 So now we have our Burgers sorted and our order for
  • 02:12 our different categories over here and we're largest to smallest.
  • 02:15 But if when I update this thing,
  • 02:17 I don't want to see the order of these categories change.
  • 02:20 I know what they were, and even if the values over on the side here change so
  • 02:25 that something else, maybe sandwiches, pops to the top, I don't want it to.
  • 02:28 I want it to stay in this particular order.
  • 02:30 How do I lock that down?
  • 02:32 To do that,
  • 02:33 what we're going to do is we're going to actually right-click on our category.
  • 02:37 And we're going to go to Sort and we're going to choose More Sort Options.
  • 02:42 Inside here we're going to now move into More Options.
  • 02:46 And what you'll see is that we have the ability to actually turn off the automatic
  • 02:50 sorting of the pivot table.
  • 02:52 So the key thing here is we can set everything and
  • 02:54 get it all sorted the way that we want to begin with.
  • 02:56 And then we can turn off the automatic resorting algorithm.
  • 03:00 So now I can say OK, and I can say OK.
  • 03:03 And at this point, you're going to see that something strange happened.
  • 03:07 My Food stayed sorted the way I expected it to, but my Alcohol changed.
  • 03:11 This is the only way that you can actually go now and say, I'd like to see this
  • 03:17 one sorted from smallest to largest and show Food largest to smallest.
  • 03:21 Alcohol in the other order, which is pretty interesting.
  • 03:26 Now, at this point if I were to go and update something in my data,
  • 03:30 let's say that I was to come back over here and we're going to go and
  • 03:33 put in 40,000 items inside our coffee here.
  • 03:36 Or 400,000, we're going to come back and we're going to refresh things.
  • 03:40 Well, we can see is that our sales have not resorted the pivot table.
  • 03:43 So we're happy with the way that's actually working because we wanted to
  • 03:46 keep things in this particular order.
  • 03:47 But then at some point, you may look at this and go, wait a minute, but
  • 03:50 now I want to get it back to where I expect it to be.
  • 03:52 How do I reset this so that the automatic sorting does take place?
  • 03:57 Unfortunately, it's not as easy as you might think.
  • 03:58 You can't come back and right-click and say Sort, go into your More Sort Options,
  • 04:03 and then just say let's go with ascending.
  • 04:05 It doesn't work that way.
  • 04:06 Unfortunately, we need to go back first into More Options,
  • 04:10 turn off the sort automatically, or turn it back on, rather.
  • 04:14 And then what we need to do is, in this case,
  • 04:17 we want it descending based on the actual sales dollars.
  • 04:20 At this point, it will now put it back into the manual sort order and
  • 04:23 you'll notice that it's both applied them to both the Food and the Alcohol again.
  • 04:27 So it's no longer separate.
  • 04:29 So that's the tricks on how we play with both sorting vertically and horizontally.
  • 04:33 There's lots of different options as well as locking it down to say, don't update
  • 04:37 when the values go, and restoring it back to automatic updates as well.

Lesson notes are only available for subscribers.

Custom Sorting
05m:11s
Report Filters
04m:37s
Share this lesson and earn rewards

Facebook Twitter LinkedIn WhatsApp Email

Gift this course
Give feedback

How is your GoSkills experience?

I need help

Your feedback has been sent

Thank you

Back to the top

© 2023 GoSkills Ltd. Skills for career advancement