How to use custom sort on slicer buttons in Microsoft Excel

Microsoft Excel arranges the sliders in alphabetical and numerical order. Luckily, you can add a custom sort list to sort the buttons in a more meaningful way, as needed.

If you do a lot of group analysis and summaries, you can build your PivotTables and slicing tool market share. When you place the slicer on one of the fields in the PivotTable, Microsoft Excel Arrange the buttons in alphabetical or numerical order. Most of the time that will be appropriate. Sometimes you’ll be working with items that don’t represent well in the traditional way. When that happens, create a custom sort list to sort the cutter buttons.

In this tutorial, I’ll show you how to create a custom list of t-shirt sizes and then sort the cutter buttons by size rather than alphabetical order.

UNDERSTAND: Windows, Linux, and Mac commands everyone needs to know (free PDF) (TechRepublic)

I’m using Microsoft 365 on Windows 10 64-bit systems, but you can use earlier versions of Excel until 2010. Excel for the web supports slicers, but you cannot create custom sort lists in the web version.

How to Add a PivotTable in Excel

Assume that you track t-shirt sales orders using a Table named TableSales, shown in Picture A. The t-shirt comes in four sizes: Small, Medium, Large, and X Large.

PivotTables are a great way to differentiate the number of t-shirts each customer buys by size. Let’s quickly add a PivotTable to show the total amount that each customer purchased.

Picture A

To insert a PivotTable, do the following:

1. Click anywhere inside the TableSales Table.
2. Click the Insert tab, and then click PivotTable in the Tables group.
3. In the resulting dialog box, click the Existing Sheet option. You can add it to a new sheet, but this option allows me to display everything on the same sheet.
4. Click inside the Position control, and then click somewhere in the worksheet to specify where you want the PivotTable to be. I chose F2.
5. Use Figure BUT as a guide to creating a PivotTable via the PivotTable Field List.

Figure BUT

With the PivotTable in place, add a slicer that filters it by t-shirt size.

How to Add a Slicer in Excel

Now suppose you want to add a slicer to filter the PivotTable by t-shirt size. To do so, do the following:

1. Click anywhere inside the PivotTable.
2. Click the Contextual PivotTable Analysis tab.
3. In the Filters group, click Insert Slicer.
4. Click Buyer (SIZE) and click OK.

SIZE

Visualization

As you can see in Visualization, the cutter arranges the buttons in alphabetical order. Sometimes that arrangement is appropriate, but in this case it’s a bit counter-intuitive. Users will want to see the size buttons in the following order: Small, Medium, Large, and X Large. You may hear complaints from users that they first clicked the button expecting to see Small purchases but received Bulk instead.

Luckily, you can give them what they want by adding a custom type.

How to Add a Custom Sort in Excel

We can add a custom sort to the workbook and then arrange the slicer according to that style. To create a custom type, do the following:

1. Select the File tab and click Options in the left pane.
2. Click Advanced in the left pane.
3. In the General section, click the Edit Custom List button (it’s near the bottom).
4. Add the sizes in the traditional order: Small, Medium, Large, and X Large. Press Enter between each item to create a list that shows up in Figure E.
5. Click OK twice to return to the worksheet.

Figure E

The cutter buttons do not update automatically.

How to update button sort order in Excel

Microsoft Excel won’t automatically arrange buttons – it’s not a dynamic feature. To get the buttons to update the sort order, right-click the slicer and click Refresh. Doing so will force the buttons to sort and they will choose a new custom type, as shown in Figure Fthat we created in the previous section.

Figure F

If by chance you add a new size, such as Petite, simply add Petite to the custom sort list. You don’t need to completely recreate it. Then refresh the cutter.

You won’t hear any complaints from users if you give them a cutter that arranges the buttons in the expected order.