How to Sort Pivot Table Field Values in Excel
With a Pivot Table, you have more options and more flexibility when it comes to sorting your field values than you do with a regular Excel data set. However, many of my clients and viewers seem to get “lost” when it comes to creating custom sorts for their Pivot Table Field Valuess. So, let me demonstrate four ways to sort fields to suit your needs.
Sort Pivot Table Fields
Sort the Order of Months
If you create a Pivot Table for a Fiscal Year or,as in this example a Water Year, you will want to sort the Month Fields to match the starting and ending months in your FY. There are at least three ways to do this:
-
Right click on the month that you want as your first month. Choose Move from the shortcut menu and select Move (in this case) Oct to Beginning.
-
Another way to relocate a field value is to select the bottom part of the cell. When you see the 4-heard black cross, just move the field value to the new location. You will see a Gray Horizontal Bar as you drag the field.
-
Most amazing way to move a field value - and seeing is believing – is to go to the cell where you want to place the field value and just “type in the name of the field value. Incredibly, Excel moves the field values to accommodate the value that you typed in! This only works with a Pivot Table.
Sort Using a Custom List
You can create a Custom List and use that for sorting the values in your Pivot Table Field. After you create your Custom List (I show you how in this video) you choose the Pivot Table Options Sort command and then choose the direction – Ascending or Descending.
In Pivot Tables, Excel remembers your custom Field Value Settings when you move – or Pivot – field from Rows to Columns for example.
After you watch this short five minute video lesson you will know how to sort field values in Pivot Tables to suit your needs.
Learn how to “Quickly Create Pivot Tables and Charts”
Related Excel Video Lesson
My YouTube Channel – DannyRocksExcels
Click here to watch this video in High Definition, Full-screen mode
March 6th, 2011 at 2:17 PM
[...] How to Sort Pivot Table Field Values in Excel [...]