MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Sort Slicers

November 13, 2017 - by Bill Jelen

Sort Slicers

Slicers in Excel tend to be arranged alphebetically. This article will show you how to arrange them in another sequence.

Watch Video

  • How to change the sequence of items in a pivot table slicer
  • Old method involved using the "Sort by" feature in Power Pivot
  • New method: Add a custom list with the correct sequence
  • The slicer will respect the custom list
  • The last custom list in the dialog will "win"

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode 2069
  • has sorting slicers hey welcome back to
  • mr. Zell Metcalfe I'm bill John I've
  • shown a webinar for Australian New
  • Zealand yesterday and a question came
  • across that I cringed at they said hey
  • look our fiscal year runs July through
  • June how can you reorganize these
  • slicers and I remembered that there's a
  • way to do this in PowerPivot you have to
  • take the data to the data model create
  • another table with the correct sequence
  • like the month names and the the numbers
  • select you would have some like this one
  • too I'm just going to hold on the ctrl
  • key there and drag that down and you
  • would add this little table link this
  • table to the original table and then in
  • PowerPivot so you would sort by this
  • column sort this field by this column
  • alright and then put this field on the
  • slicer and one of the comments came in
  • from Bjorn who lives in New Zealand
  • Bjorn says no no you don't do that just
  • use a custom list I'm like custom lists
  • I know that works in a pivot table but
  • I'm sure it used to not work in a slicer
  • but check it out it works in a slicer so
  • here's what you do you type the list in
  • the order that you want them to appear
  • like that and select that list go into
  • file Excel options Excel options and
  • then go to advanced in the advanced
  • dialog this is a long dialogue you want
  • to scroll 83% of the way down right
  • there a button called edit custom lists
  • you see this is where they store Sunday
  • Monday Tuesday and chance at more but
  • I'll have to change that one it's grayed
  • out those four are protected but we're
  • going to import our new list like that
  • and here's the beautiful thing okay so
  • July is in this list and July is in this
  • list the list at the bottom wins right
  • so this list is now going to become the
  • prevailing list for the sequence click
  • OK click OK alright and then what we're
  • going to do is we're insert a new slicer
  • to analyze insert slicer based on date
  • click OK and it helps you're saying the
  • right sequence
  • cool all right that's great now of
  • course in my slashers I like to
  • rearrange them here a little bit make
  • them a little bit shorter so they all
  • fit in one nice little area I'm even
  • going to hide those two two little
  • stupid ones down there for the greater
  • than and less than alright so there we
  • go a cool cool trick from your Inn in
  • New Zealand and I'm setting an Excel
  • guru mission patch if you learned for
  • that great idea I was hand these out of
  • my live seminars I figure what the heck
  • I can do it in a webinar - boy hey they
  • mail easier than the old Excel Excel
  • guru enamel pencils got broken every
  • time I mail this one I think we'll
  • survive alright so how to change the
  • sequence of items in a pivot table
  • slicer the old method involved using the
  • sort by feature in PowerPivot new method
  • add a custom list with a correct
  • sequence and then we recreate that
  • slicer it will respect the custom list
  • if you have July and two different lists
  • the one that is last in the dialog box
  • will win XP learn for that for setting
  • that tip in and thanks to you for
  • stopping by we'll see you next time for
  • another net cast from MrExcel

Download File

Download the sample file here: Podcast2069.xlsm

Title Photo: Hans / Pixabay