![]() Please leave a comment below with questions or suggestions. It would be great to learn how you will use this technique to quickly filter and sort your slicers. 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. This same technique could be used for all kinds of account codes or id numbers. ![]() The LEFT function is used to return the first 3 characters from the phone number to create an area code field. In the example below I am using the same technique to create parent groups of area codes for phone numbers. Once you are viewing the Custom Lists dialog window, you have a couple of options to create your list. Scroll all the way down to the bottom of the options list. Create Parent Groupings for Text or Numbers Pick the Advanced section in the left-side pane. 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. So you want to make sure this option is enabled for the child slicer (Sales Rep). The Show items with no data option also sorts the second slicer (Sales Rep). You can get to the Slicer Settings menu by right-clicking the slicer and selecting Slicer Settings… This will keep the letters in alphabetical order when an item is selected in either slicer. It's also best to disable the “Show items with no data last” slicer setting for the parent grouping slicer (1st Initial). Now we just add a slicer to the pivot table for this new field (1st Initial). Add a Slicer for the Parent Level Group Field 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”. This basically creates a parent level grouping for the Sales Rep names. If the Sales Rep name is in cell B2, then the following formula will return the first letter of the text in that cell.Ĭopy 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. The left function returns a specified number of characters from a string or cell value, starting from the beginning of the text/number. This can easily be accomplished with the LEFT function. We first need to add a field to the source data that contains the first initial for each Sales Rep. This technique is actually pretty easy to setup. Add a Parent Grouping Field to the Source Data This makes it much faster for the user to navigate through the list by narrowing down their selection with the 1st Initial field (slicer). 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. The “1st Initial” slicer allows the user to first select the first initial of the name they are looking for. There are almost 200 items in that slicer. In this example we have a long list of names in our Sales Rep slicer. We are going to create a shorter slicer (with less items) that can filter down the long slicer. Sort-or-Filter-a-Slicer-with-Another-Slicer.zip Download How Does This Solution Work?
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |