How to Create a Custom Sort List in Excel Pivot Table?

Sharing is caring!

Are you looking to create a custom sort list in an Excel pivot table? Pivot tables are a powerful feature in Microsoft Excel that allow you to quickly summarize and analyze large amounts of data. One useful capability is the ability to define a custom sort order for the rows or columns in your pivot table, rather than relying on the default alphabetical or numerical sorting. In this article, we’ll walk through the steps to set up a custom list for sorting a pivot table field in Excel.

What is a Custom Sort List in Excel?

In Excel, a custom list is a user-defined list of items in a specific order. Custom lists are helpful when you have data that needs to be sorted in a non-standard way, such as days of the week, months of the year, or a list of product sizes like small, medium, and large.

When you apply a custom list to a pivot table field, Excel will sort the items in the field according to the order defined in your custom list, rather than alphabetically or numerically. This gives you more control over how the data is arranged and presented in your pivot table.

Why Use a Custom Sort List in a Pivot Table?

There are a few key benefits to using custom sort lists in pivot tables:

• Arrange data in a logical, meaningful order
• Quickly spot trends and patterns
• Improve readability of the pivot table
• Customize the presentation of your data

For example, let’s say you have sales data for products in different sizes, and you want to analyze the sales by product size in a pivot table. By default, Excel would sort the sizes alphabetically as “Large”, “Medium”, “Small”. But with a custom sort list, you could define the order as “Small”, “Medium”, “Large” to better align with the natural progression of sizes.

How to Create a Custom List in Excel

Before you can use a custom list to sort a pivot table, you first need to create the custom list in Excel. Here’s how:

1. Go to File > Options
2. In the Excel Options window, click on Advanced in the left sidebar
3. Scroll down to the General section and click the Edit Custom Lists… button
4. In the Custom Lists dialog box, click on NEW LIST in the left pane
5. Type your list entries in the List entries box on the right, with each entry on a separate line
6. Click Add to save the new custom list
7. Click OK to close the Custom Lists dialog box, then click OK again to close Excel Options

Your custom list is now defined and ready to be used for sorting.

Sorting a Pivot Table Field Using a Custom List

Now that you have a custom list created, you can apply it to sort a field in your pivot table. Follow these steps:

1. Select any cell inside the pivot table
2. Go to PivotTable Analyze > Fields, Items & Sets > More Sort Options
3. In the Sort dialog box, choose the field you want to sort from the Sort by dropdown
4. Select More Options…
5. In the More Sort Options dialog box, select Custom List under AutoSort options
6. Click the Custom Lists… button
7. In the Custom Lists dialog box, select the custom list you want to use from the left pane
8. Click OK to close the Custom Lists dialog box
9. Click OK to close the More Sort Options dialog box
10. Click OK to close the Sort dialog box

The selected pivot table field will now be sorted according to your custom list order. You can confirm this by looking at the row or column labels for that field in the pivot table.

Using a Custom List for Multiple Pivot Table Fields

If you have multiple pivot tables where you want to apply the same custom sort list to a certain field, you don’t have to repeat the sort process each time. Instead, you can set a default custom list for that field. Here’s how:

1. Right-click the pivot table and select PivotTable Options
2. Go to the Data tab in the PivotTable Options dialog box
3. Click the AutoSort button
4. In the AutoSort dialog box, choose the field you want to set a default sort order for
5. Select Custom List under AutoSort options
6. Click the Custom Lists… button and select the custom list to use
7. Click OK to close the Custom Lists dialog box
8. Click OK to close the AutoSort dialog box
9. Click OK to close the PivotTable Options dialog box

Now, any new pivot tables you create using that data source will automatically apply your custom sort list to the specified field.

Tips for Working with Custom Sort Lists

Here are a few tips and best practices to keep in mind when using custom sort lists with pivot tables:

• Keep list entries short: Custom list entries should generally be kept concise, ideally just a word or two each. Longer entries can make the list harder to read and maintain.
• Use a consistent format: Make sure all entries in your custom list follow the same format and capitalization. For example, use all lowercase or title case for each entry.
• Update custom lists as needed: If the items you need to sort change over time, make sure to go back and update your custom list definitions to reflect the changes. Outdated lists can lead to sorting errors.
• Use custom lists sparingly: While custom sort lists are useful, they can also make your workbooks more complex. Only set up custom lists for fields where a non-default sort order is truly needed.

Troubleshooting Custom Sort Lists

If your pivot table isn’t sorting as expected based on a custom list, here are some things to double-check:

By troubleshooting issues like these, you can ensure your pivot tables are sorted correctly using your custom lists.

Summary

Custom sort lists provide a handy way to take control of how pivot tables organize and present your data in Excel. By defining a custom sorting order, you can arrange rows or columns in a more meaningful, intuitive way. The steps to create a custom list and apply it to sort a pivot table field are straightforward:

1. Create a new custom list from the Advanced Excel Options
2. Apply the custom list to sort a pivot table field from the Sort dialog box
3. Optionally set a default custom list for a field in the PivotTable Options

With custom lists in your tool belt, you can take your pivot table analysis to the next level and glean better insights from your data. By following the tips and troubleshooting steps outlined here, you’ll be able to create, use, and maintain custom sort lists with confidence.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.