ExcelInsider Team
Microsoft Excel allows us to sort our pivot tables using some automated methods. Those usually follow a format like alphabetical or numerical sorting. However, sometimes we need to sort the items according to our own order. Using a custom list, we can sort the data in an order that does not follow any rules. It can be useful if you have data that you have to sort using a unique sequence, like the sales region or product categories.
In this article, we will learn how to sort a pivot table by a custom list. We will go through the step-by-step on how to do it, and by the end, you will be able to use custom lists to sort your pivot table at ease.
➤ Go to File > Options > Advanced and select Edit Custom Lists from the General section.
➤ Write the custom list entries in the edit box at the right, and hit Add. Click OK to close the dialog box.
➤ Head back to the pivot table, and sort the data by right-clicking on a related cell, and selecting Sort > Sort A to Z.
Creating custom lists may seem like a lengthy process, but it has advantages. Once you configure a custom list for your Microsoft Excel, you won’t have to do it for any other worksheets. Microsoft Excel will remember it and apply it to your future sheets as well. Follow the guide to learn to sort a pivot table by a custom list.
Steps for Sorting a Pivot Table in Excel by a Custom List
We have chosen a dataset with customer feedback to demonstrate the process. There are customer names, their locations, the satisfaction scores ranging from 1 to 10, and feedback types. The feedback types are basically what the customers provided the satisfaction scores against. There are three types of feedback: product, service, and support.
The feedback types are shown in columns. Microsoft Excel has sorted from A to Z. However, we want the Service to be at the beginning, Support afterwards, and Products at the end. We are going to accomplish that sorting using a custom list. Follow the steps below to do so:
Step 1: Prepare to Create a Custom List
➤ Let’s write the sort order beside the table first. In G4:G6, write the new order.
➤ From the ribbon at the top, go to File > Options. A new window will open. These are the global Excel options that we have to modify.
➤ From the left, select Advanced to see the advanced options.
➤ Scroll down and find the General section.
➤ Click on Edit Custom Lists.
Step 2: Add the List Entries
➤ In the custom lists window, there are essentially two methods to add entries to the custom list. First, we can use the list entries that we have written in the G4:G6 cells of the worksheet before. To do that, click on the small arrow that points upwards.
➤ Now select G4:G6 using your mouse. It will show up as absolute references in the small Options window. You can also write $G$4:$G$6 manually. Press the downwards pointing arrow afterwards.
➤ Click Import to import the entries, and click OK to close the window.
➤ That was one method. Another method to do this would be to directly write the entries in the List entries section and hit Add.
Step 3: Sort the Data
➤ Go to the B4 cell of the pivot table, and right-click on that.
➤ Select Sort > Sort A to Z.
➤ Now the column will be sorted using the custom list we defined earlier.
➤ We can clear G4:G6 now; they serve no more purpose.
Note:
The order we put the list items in Step 2 is the A to Z order for Excel. If we want to do the reverse, we need to select Z to A.
Frequently Asked Questions
Is it possible to sort a pivot table?
Yes. Although sorting a pivot table is not as straightforward as a normal table, it is possible to sort a pivot table as long as you know what you are doing. Remember, pivot tables sort data according to each category (row), instead of the full table.
Can you sort a PivotTable in sheets?
Yes, the rows and columns of a pivot table in Google Sheets can be sorted. Find the Edit button under the pivot table, and click on it. If you want to sort the rows, click the arrow under the “Order” or “Sort by” labels under the “Rows”. If you want to sort the columns, go to the “Columns” section to find those labels instead.
How to rearrange columns in a PivotTable?
If there are multiple fields in the Columns section of the pivot table, you can drag and drop the columns to rearrange them. If you want to rearrange the individual headings, you can sort them using the Sort option from the Data tab. You can also move them manually by right-clicking the heading and selecting the options under Move.
What is the default sort order of a PivotTable?
Usually, Microsoft Excel sorts the pivot table alphabetically. However, this can change based on the source data and the order you import the data into the pivot table. Remember, pivot tables automatically sort the data again every time you select/deselect a field to be included in the table.
What is a slicer in a PivotTable?
Slicer allows you to filter data in a pivot table. You can select different fields in a slicer to make them show up in the pivot table. Slicers also show what filters are currently applied so that no one gets confused looking at the pivot table.
Wrapping Up
In this article, we have learned to sort a pivot table by a custom list. We hope that we have been able to teach the process properly. If you face any issues following the guide, write it below the article, and we will reply as soon as possible. Stay tuned for more Excel tutorials.
