How to use a custom sort on slicer buttons in Microsoft Excel

Microsoft Excel sorts slider buttons alphabetically and numerically. Fortunately, you can add a custom sort list to sort the buttons in a more meaningful way, when necessary.

If you do a lot of analyzing and summarizing by groups, you probably build your fair share of PivotTables and slicers. When you base the slicer on one of the fields in the PivotTable, Microsoft Excel sorts the buttons alphabetically or numerically. Most of the time that will be adequate. Occasionally, you’ll be working with items that aren’t well represented by a traditional sort. When that happens, create a custom sort list to sort the slicer buttons.

In this tutorial, I’ll show you how to create a custom list of t-shirt sizes and then sort slicer buttons by sizes instead of alphabetically.

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

I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use earlier versions of Excel through 2010. Excel for the web supports slicers, but you can’t create a custom sort list in the web version.

How to add a PivotTable in Excel

Let’s suppose that you track orders for t-shirt sales using the Table named TableSales, shown in Figure A. The t-shirts come in four sizes: Small, Medium, Large and X Large.

A PivotTable is a great way to discern how many t-shirts each client purchases by size. Let’s quickly add a PivotTable to display the total t-shires purchased by each client.

Figure A

To insert the 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, click the Existing Sheet option. You can add it to a new sheet, but this option allows me to show everything on the same sheet.
4. Click inside the Location control and then click somewhere in the sheet to identify where you want the PivotTable. I chose F2.
5. Use Figure B as a guide to build the PivotTable via the PivotTable Fields List.

Figure B

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

How to add a slicer in Excel

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

1. Click anywhere inside the PivotTable.
2. Click the contextual PivotTable Analyze tab.
3. In the Filter group, click Insert Slicer.
4. Click Buyer (Figure C) and click OK.

Figure C

Figure D

As you can see in Figure D, the slicer sorts the buttons alphabetically. Sometimes that sort will be adequate, 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 might hear complaints from users that they click the first button expecting to see the Small purchases but get Large instead.

Fortunately, you can give them what they want by adding a custom sort.

How to add a custom sort in Excel

We can add a custom sort to the workbook and then sort the slicer by that sort. To create the custom sort, 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 Lists 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 the list shown in Figure E.
5. Click OK twice to return to the sheet.

Figure E

The slicer buttons don’t automatically update.

How to update the button sort order in Excel

Microsoft Excel won’t sort the buttons automatically — it isn’t 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 pick up the new custom sort, as shown in Figure F, that we created in the last 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 have to completely recreate it. Then, refresh the slicer.

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