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
195.5 KB
Sorting Values - Completed.xlsx
195.5 KB

Quick reference

Topic

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
  • Change the manual sort to Ascending/Descending as appropriate
  • Click the drop down and select the values field
  • Click More Options…
  • Re-check the AutoSort checkbox
Login to download
  • 00:04 Now what we're gonna look at is sorting the values area of a pivot table.
  • 00:09 And this is slightly different than sorting the row or
  • 00:12 column labels, but equally valuable.
  • 00:15 We have two ways we can sort.
  • 00:16 We can sort vertically or we can sort horizontally.
  • 00:19 So if I wanted to sort vertically by the Grand Total column, what
  • 00:25 I would do is I would come here and say right-click > Sort > Smallest to Largest.
  • 00:31 And you'll notice that all the values,
  • 00:33 all the rows are re-sorted based on the Grand Total column, but notice also that
  • 00:38 even though I went smallest to largest, my sub totals did not re-sort.
  • 00:41 And that's good, because it allows me to sort within the sub total band.
  • 00:45 So, if I wanted to do this guy here, this is a different grouping level and
  • 00:49 a different sort level in our list.
  • 00:52 So, I can sort ascending by alcohol and I can sort descending
  • 00:58 within the actual category itself if I want to do that, which is kinda nice.
  • 01:04 I can also sort by a specific individual column.
  • 01:07 I don't have to use the Grand Total.
  • 01:09 Let's say that we wanted to sort the entire categories level
  • 01:14 in ascending order based on Week 3's numbers.
  • 01:18 So I select one of the values of Week 3 Sort > Smallest to Largest.
  • 01:22 It re-sorts the entire pivot table based on that logic.
  • 01:25 You'll notice this column looks all completely wonky.
  • 01:28 But this one is ordered very nicely, which is great.
  • 01:32 If I go to the very bottom and
  • 01:34 I right-click on one of these cells in the Grand Total row.
  • 01:37 And I do a Sort from largest to smallest, it actually sorts horizontally.
  • 01:44 It says, well, there's no point in sorting vertically because you're in
  • 01:46 the Grand Total row.
  • 01:47 So that may not be what you want.
  • 01:50 So we can sort smallest to largest, or largest to smallest rather or
  • 01:53 the other way around.
  • 01:54 Here's smallest to largest, where we're actually growing things up.
  • 01:56 Probably doesn't make sense if you've got week numbers across the top,
  • 01:59 but you never know.
  • 02:00 It might if you have some certain logic for doing that.
  • 02:04 What about trying to sort horizontally inside the values area?
  • 02:09 Maybe I want to see my burger sales so
  • 02:12 that my largest burger sale is on the left-hand side here and goes this way.
  • 02:16 That's the most important thing to me.
  • 02:19 Well, the way that we do that is we right-click and we go to Sort.
  • 02:25 We go to More Sort Options.
  • 02:26 This one's a little bit more hidden, but you'll notice that we can actually change
  • 02:30 this from left or set it to left to right, large to smallest.
  • 02:34 This is already left to right because I was in the Grand Total row, but
  • 02:38 if I now say left to right, there we go.
  • 02:41 All right, what about controlling automatic sorting?
  • 02:45 Right now you'll notice that Sandwiches is below Entrees.
  • 02:48 It's sorted in order of Grand Total.
  • 02:51 We'll just prove that right now.
  • 02:52 Sort > Large to Smallest.
  • 02:54 There we go.
  • 02:55 So if we went to our our data and
  • 02:58 we increased our sandwich revenue by 20,000 or to $20,000 and
  • 03:02 went back and re-sorted it, we would expect Sandwiches to jump above Entrees.
  • 03:07 And it does, so sorting is automatic right off the bat.
  • 03:10 What if I didn't want that?
  • 03:11 What if I wanted these values to not change?
  • 03:15 We can do that too, but we have to right-click on a row label to do this.
  • 03:20 So now we'll go to Sort, we'll go to More Sort Options.
  • 03:24 In this case, instead of doing descending by sum of amount,
  • 03:28 I need to click the More Options button and change it so
  • 03:31 that it does not sort automatically every time the report is updated.
  • 03:36 Now we can say OK.
  • 03:37 And then we can say OK again just to clear it in.
  • 03:40 And now, if I go back and reset my sales revenue back to $4.50,
  • 03:46 go back into the report.
  • 03:48 Right-click and Refresh.
  • 03:50 You'll notice that we are definitely not re-sorting our report, and that's great.
  • 03:56 But now I've decided, you know what,
  • 03:57 maybe I really do wanna sort this thing automatically every time it updates.
  • 04:00 So what I'm gonna do is I'm gonna right-click,
  • 04:02 I'm gonna say Sort, I'm gonna sort from largest to smallest, there we go.
  • 04:06 We've got our entrees in a good place, and now we're gonna go back and
  • 04:09 just check that it worked.
  • 04:11 So back to Sandwiches, back to 20,000,
  • 04:15 back to our report, and right-click and Refresh.
  • 04:20 Oh, we've got a problem.
  • 04:22 This did not re-sort, so it's still stuck in Manual mode.
  • 04:25 To get it out of Manual mode takes a little bit of work.
  • 04:28 In order to do this, we need to go back into the row label.
  • 04:34 Go back to More Sort Options.
  • 04:36 You'll notice that it's Manual.
  • 04:38 Now, we really wanna change this to descending,
  • 04:40 but we have to do something first.
  • 04:42 Before we do that, we need to go into More Options and
  • 04:45 we need to recheck the AutoSort button.
  • 04:48 Now we can say OK, now we can take it out of Manual mode,
  • 04:52 but it's not good enough to just click Descending,
  • 04:54 we have to change it to Descending by the sum of our amount.
  • 04:59 Now we can go back into More Options again and just check,
  • 05:01 yeah, it's doing it by the grand total, that's cool,
  • 05:03 it's not gonna be doing it by a specific column, we'll say OK and OK.
  • 05:09 It re-sorts for us automatically.
  • 05:11 That looks good.
  • 05:12 Let's go back and test it.
  • 05:14 We'll go back, put 4.50 in here.
  • 05:18 Back over to Report 2, right-click, and Refresh.
  • 05:22 Perfect, it's working, so setting it into Manual Sort mode is relatively easy.
  • 05:27 Getting it back out can be a little bit more work.

Lesson notes are only available for subscribers.

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

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