Excel: Manually Re-sequence the Order of Data in a Pivot Table

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Problem: By default, a pivot table organizes data alphabetically. For the Region field, this means the data is organized with Central first, East second, and West third. My manager wants the regions to appear in the order East, Central, West. After unsuccessfully lobbying to have the Central region renamed Middle, I need to find a way to have my table sequenced with the East region first.

  1. Central, East, West is the alphabetical sequence.

    Strategy: It is amazing that this trick works. Try it:

    1. Select cell B4 in the pivot table.
    2. In cell B4, type the word East.

  2. Go to the Central cell and type a new heading.
    1. When you press Enter, Excel senses what you are trying to do. All the data from the East region moves to Column B. Excel automatically moves the Central region heading and data to column C.

  3. East and Central switch! Never try this outside a pivot table.

    You can easily use this trick to re-sequence the fields into any order as necessary.

    Additional Details: This technique will only change the Region sequence in a single pivot table. If you would like to change the sequence in all future pivot tables, you need to create a custom list with the regions in the proper sequence. See "œHave the Fill Handle Fill Your List of Part Numbers" on page 59. Any pivot tables created will follow the custom list sequence.