How to Sort a Slicer with Another Slicer for Quick Navigation + Video - Excel Campus
10

How to Sort a Slicer with Another Slicer for Quick Navigation + Video

Bottom line: Learn how to group text or number fields to create a slicer that can sort and filter another slicer.  This technique is great for slicers that contain a lot of items and are difficult to navigate.

Skill level: Intermediate

Filter or Sort Slicer with Another Slicer GIF

Last week’s post about how to add a search box to your slicer raised a lot of great questions on how to tackle a common problem.

The problem is that a slicer can be difficult to navigate through when it contains a lot of items.  The user has to scroll horizontally through a long list to find the item they are looking for.

I added some alternate solutions to that post that answer questions about how to view the selected items in the filter/slicer.

Video: Create a Slicer that Sorts Another Slicer

Watch video on YouTube (and hit the Like button!)

Download the File

Download the file to follow along.

Sort Or Filter A Slicer With Another Slicer.xlsx (356.1 KB)

How Does This Solution Work?

We are going to create a shorter slicer (with less items) that can filter down the long slicer.

In this example we have a long list of names in our Sales Rep slicer.  There are almost 200 items in that slicer.

The “1st Initial” slicer allows the user to first select the first initial of the name they are looking for.  The Sales Rep slicer will then be sorted to display a list of the names that start with that initial at the top of the slicer.

This makes it much faster for the user to navigate through the list by narrowing down their selection with the 1st Initial field (slicer).

Add a Parent Grouping Field to the Source Data

This technique is actually pretty easy to setup.  We first need to add a field to the source data that contains the first initial for each Sales Rep.

This can easily be accomplished with the LEFT function.  The left function returns a specified number of characters from a string or cell value, starting from the beginning of the text/number.

Source Data for Pivot Table Parent Grouping Slicer

If the Sales Rep name is in cell B2, then the following formula will return the first letter of the text in that cell.

=LEFT(B2, 1)

Copy the formula down the entire column of the data set and you now have a pivot table field for the first initial of each name.

This basically creates a parent level grouping for the Sales Rep names.  You can filter the 1st Initial column in your source data for “C” to see a list of all the data for all the reps that have a name starting with “C”.

Add a Slicer for the Parent Level Group Field

Now we just add a slicer to the pivot table for this new field (1st Initial).

It’s also best to disable the “Show items with no data last” slicer setting for the parent grouping slicer (1st Initial).  This will keep the letters in alphabetical order when an item is selected in either slicer.  You can get to the Slicer Settings menu by right-clicking the slicer and selecting Slicer Settings…

Slicer Settings - Disable Show Items With No Data Last

The Show items with no data option also sorts the second slicer (Sales Rep).  So you want to make sure this option is enabled for the child slicer (Sales Rep).  When an item is selected in the parent slicer, the related child items will be moved to the top of the slicer, and the items with no data will be listed below.

Create Parent Groupings for Text or Numbers

This solution also works for numbers.  In the example below I am using the same technique to create parent groups of area codes for phone numbers.

Create Parent Groups for Slicers with Phone Numbers and Area Codes

The LEFT function is used to return the first 3 characters from the phone number to create an area code field.

LEFT Function to return Area Code from Phone Number

This same technique could be used for all kinds of account codes or id numbers.

I explained a similar technique with dates and days of the month in a recent post on how to do month-to-date (MTD) comparisons with pivot tables.

How Will You Use This Technique?

It would be great to learn how you will use this technique to quickly filter and sort your slicers.  Please leave a comment below with questions or suggestions.  Thanks! 🙂

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 10 comments
Jeremy - February 22, 2017

Hi. This will work great on my pivot tables. Thanks. One question though, how can this be applied to Business Analytics since I do not have access to the actual data? Thanks.

Reply
Jim - January 26, 2017

What would be the formula if you wanted to do the first letter of the last name? Many thanks for your help Jon

Reply
    Jon Acampora - February 4, 2017

    Hi Jim,
    Here is a simple formula that will find the first space in the cell’s value, and return the entire string after the first space.

    =MID(A1,FIND(" ",A1)+1,LEN(A1))

    This will work for simple cases where you don’t have middle initials or multiple first names. I hope that helps.

    Reply
Muhammad Ishtiaq - December 27, 2016

Excellent solution Jon.

Reply
Prasad - April 27, 2016

Thanks for the such a clear and nice tutorials. I learned something new.

Reply
Jon Acampora - April 14, 2016

Thanks Trang!

Reply
Trang - April 13, 2016

Excellent simple solution.

thanks Jon

Reply

Leave a Reply: