🥳 GOSKILLS TURNS 10: Get 10 days of free access with code 10YEARS

GoSkills
Help Sign up Share
Back to course

Basic Sorting

Compact player layout Large player layout

Locked lesson.

Upgrade

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

About this lesson

Basic sorting of row and column headers in 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.

Basic Sorting.xlsx
197.3 KB
Basic Sorting - Completed.xlsx
198.5 KB

Quick reference

Basic Sorting

Basic sorting of row and column headers in a PivotTable.

Where/when to use the technique

When you’d like to control the order items are displayed in a PivotTable.

Instructions

Sorting based on row labels

  • Right-click one of the labels you wish to sort by > Sort
  • Choose the order you’d like to sort by:
    • Z at the top:       Choose Sort Largest to Smallest
    • A at the top:       Choose Sort Smallest to Largest
  • The sort will be applied to all fields that are part of that row label
  • Subsequent sorts can be applied to sort within subtotals

Sorting based on column labels

  • Right-click one of the labels you wish to sort by > Sort
  • Choose the order you’d like to sort by:
    • Z on the left:      Choose Sort Largest to Smallest
    • A on the left:      Choose Sort Smallest to Largest
  • The sort will be applied to all fields that are part of that row label
  • Subsequent sorts can be applied to sort within subtotals

Manual sorting

  • Drag the row/column fields into the order you’d like to see them
  • Be aware that this turns automatic sorting off for that label level (only)

Restoring Automatic Sorting

  • Right-click the label field that is in manual mode > Sort > More Sort Options
  • Change the manual sort to Ascending/Descending as appropriate
  • Click OK
Login to download
  • 00:04 In this video, let's look at how to sort values in a pivot table.
  • 00:08 And you'll notice that I've got a very simple pivot table built up here so far.
  • 00:11 We've got week across the top.
  • 00:13 We've got our sales dollars across the first row here, but
  • 00:16 we don't have any row context whatsoever.
  • 00:19 So I'm going to go and add one, we're going to grab Class and slide into Rows.
  • 00:23 And what you'll notice is that as soon as I add my Class in here,
  • 00:26 it sorts the Class in alphabetical order with Alcohol first and Food second.
  • 00:31 And if I go to Category and
  • 00:32 slide that under Class, it's also going to sort those in alphabetical order as well.
  • 00:38 But what if I want Food to come before Alcohol?
  • 00:41 It's no problem.
  • 00:42 We'll right-click on one of those two items, either Alcohol or Food,
  • 00:46 we'll go to Sort, and we'll choose to sort it from Z to A, and
  • 00:49 this will actually put Food up top.
  • 00:51 The nice thing here, if I hit the Refresh button on the pivot table,
  • 00:54 it will always go through and sort these into reverse alphabetical order.
  • 00:58 If I get a new category like say beverages,
  • 01:00 it would then go food, beverage, alcohol.
  • 01:03 So it's automatically going to put those in place.
  • 01:05 Notice though that it did not sort the fields for my categories.
  • 01:10 They're still in alphabetical order, not reverse alphabetical order.
  • 01:13 Can I change that?
  • 01:14 Sure, I can right-click, sort, Z to A,
  • 01:18 and you'll notice now that it doesn't just sort the categories under Food,
  • 01:23 it sorts the categories under Alcohol as well.
  • 01:26 And this is a key thing here, is that this sorting is done at a field level,
  • 01:30 it is not done at a specific group level within a particular sub-total.
  • 01:34 What if I decide though that for whatever reason, I want to reorder this stuff and
  • 01:39 I want to put Draft Beer right down near the end under Bottled Beer?
  • 01:42 Well, that's obviously not alphabetical in any way.
  • 01:46 So the way I do that is I can click here, and
  • 01:48 when I move my mouse over the bottom or top of the border,
  • 01:51 you'll see that will get a very special arrow that actually comes up.
  • 01:55 It's this four pointed arrows with our air clicker pointing towards the middle of it.
  • 01:58 If we left-click and
  • 02:00 drag, you'll see that we can now move this into what's called a manual sort order.
  • 02:05 And the thing here,
  • 02:06 it's still going to maintain that order when we refresh things.
  • 02:10 The thing is, though, this field is now sorted manually.
  • 02:13 So at that point, if I do want to go back to A to Z or
  • 02:16 Z to A, I've actually gotta get it out of manual mode.
  • 02:20 How do I do that?
  • 02:21 I can click any of the items inside my category field,
  • 02:25 go to Sort > More Sort Options.
  • 02:28 Notice that it is in manual mode so we drag and drop our items to rearrange them.
  • 02:32 I can just change this back to A to Z and say OK, and now it's gone
  • 02:37 back into automatic mode, so we'll to automatic sorting as you would expect.
  • 02:42 Now, one more thing I want to show you is that this also works on our Columns.
  • 02:47 So if I right-click and say hey, I'd like to see this one sorted with the largest
  • 02:51 week first and the smallest week second, we can do that and
  • 02:54 we can now go 54231 instead of the other way around.
  • 02:57 So this is how sorting works in a pivot table, both for automatic A to Z, or
  • 03:01 Z to A, for ascending or descending order.
  • 03:04 As well as how to set up a manual sort when you
  • 03:08 need things showing in a specific method.

Lesson notes are only available for subscribers.

Conditional Formatting on PivotTables
04m:55s
Custom Sorting
05m:11s
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