PowerPivot:  Now with Wingdings and Other Symbolic Fonts in Slicers!

Slicer Fonts Gone Wild!

PowerPivot Dreaming.  On Such a Summer’s Day.

Someone planted an idea in my head the other day:  “hey can we use the Wingdings font in slicers?”

Me:  “No.  Wait.  Maybe…  50/50.  Hmmm…  actually, I’m gonna say 90% likely.  Yeah, in fact, why WOULDN’T it work?”

If it had ended there it wouldn’t have cost me any sleep.  But of course, I was up into the wee hours last night, poring over every symbolic font in Windows.  Installing freeware character maps so I can view the “extended” characters in those fonts.  Writing macros (more on macros later).

And then I dreamt all night about it, and woke up two hours early to resume.

What Fonts Can We Choose From?

Here are the symbolic fonts that I found installed on my Windows Vista, Office 2010 computer:

  1. Wingdings
  2. Wingdings2
  3. Wingdings3
  4. Symbol
  5. Bookshelf Symbol 7
  6. MS Reference Specialty
  7. Webdings
  8. Marlett

(MS Outlook and MT Extra are also apparently symbolic, but contain few if any useful characters).

Partial Snapshot of the Character Sets

Wanna see all the characters you can use from each?

Wingdings 1 through 3 Fonts (Typeable Characters Only)

    Wingdings 1 through 3 Fonts (Typeable Characters Only)

    Use in Slicers:  Symbol, Webdings, and Bookshelf Symbol 7 (Again, Typeable Chars Only)

    Symbol, Webdings, and Bookshelf Symbol 7 (Again, Typeable Chars Only)

    Use in Slicers:  Marlett and MS Reference Specialty

    Marlett and MS Reference Specialty

    Extended Characters

    Some of those fonts contain characters that you cannot type without using the ALT key and a numeric identifier.  None of those are included in the images above.

    In order to browse those, you need something like the old Character Map that was in older versions of Windows.  I downloaded a freeware replacement last night:

    I never thought I'd use Character Map with PowerPivot But...

    I downloaded it from here:  https://www.softpedia.com/progDownload/Character-Map-Download-52314.html 

    (Careful, don’t use the big download buttons, those are ads.  You want the links on the right).

    Some Rob-Harvested Favorite Sets

    Using that tool, I pored over all eight fonts looking for sets of characters that would be potentially useful on slicers.

    You CANNOT mix and match characters from different fonts!  If we could, that would be even more amazing.  But you have to stay within one font on a given slicer.  You can use different slicer fonts in a single pivot, but still only one per slicer.

    More on this later, in the how-to.

    Anyway, here are some sets that I found interesting:

    Wingdings Use in PowerPivot Slicers  Wingdings2 Use in PowerPivot Slicers  Webdings Use in PowerPivot Slicers

    Some Notes:

    1. Wingdings contains clock symbols for all 12 hours
    2. The two characters from Symbol would be useful in some geeky workplaces to denote “Total” and “Percent Change” (but really who works in a place like that?)  You would use that with branching measures that switched between total and % change in response to slicer click.
    3. Webdings line 3 would be useful to denote Sectors, like Residential, Government, and Commercial.
    4. Webdings line 4 – Suburban / Urban / Rural
    5. Webdings line 8 – more weather symbols than you would ever need.
    6. Webdings last linePlanes, Trains, and Automobiles.  (And the unmade sequel, Buses, Bikes, and Boats!)

    image

    Coming Soon to a Slicer Near You

    How Do I Do This?

    Well I first made a worksheet with a bunch of my favorite sets pasted into columns:

    image

    Symbol Sets Copied Into Excel, in Columns

    Then I copied them and pasted them into the PowerPivot window using the Paste New button:

    image

    And I repeat until I have them all pasted over as new tables:

    image

    Wingdings and Other Fonts Pasted Into PowerPivot Show Up as Regular Fonts!

    Now Add Slicers

    When I add slicers on those fields, I get:

    image

    What Happened to the Symbols???

    That’s right, slicers don’t use the right font by default, so you are seeing the equivalent characters from Calibri, the default font in Excel.

    Gotta switch those fonts…

    Changing Slicer Fonts

    This is a bit trickier than you might think, but still not bad.

    First, you need to make a copy of your slicer style, using the Duplicate command.  Select a slicer, then right click in the ribbon…

    image

    Duplicate a Slicer Style so You Can Change Font

    You CANNOT change the default slicer styles!  That’s why you have to make a new style first, because custom styles can be modified.

    image_thumb[1][1]

    Just Give Your New Slicer Style a Name and Click OK

    VERY important…  you now have to switch to use that new style!  The slicer you had selected is still using the old, built-in, non-modifiable style!

    image

    Important Step!  If You Skip This Part it Won’t Work

    NOW Modify the Custom Slicer Style

    Right click your custom style, choose Modify:

    image

    Now you get the following dialog:

    image

    Lucy, You Have Some Clicking to Do – Yes, All Eight of Those

    If you don’t change the font in all 8 of those, your symbolic font won’t show up all the time.

    This is tedious, but it’s the right decision on Microsoft’s part.  While I never would want different fonts when I hover, I may want different colors, or maybe bold vs. italic etc.

    Results

    image

    Voila!

    One Custom Style Per Font

    If you plan to use more than one symbolic font in a single workbook, you will need to define a custom style for each font by repeating the steps above.

    Turning Off Header? 

    In some cases the symbols are clear enough that you may not need the header on the slicer:

    image

    If that’s true you can turn it off here:

    image

    Note however that also removes the “clear filters” button.  Tradeoffs.  I recommend using this option carefully, as it may not always be clear to the report consumer what is going on.

    Macros!

    Whenever you find yourself doing tedious work like modifying all 8 slicer item style elements, and then doing it all over again for other fonts, it may be time for a macro.  So I wrote one.  I will share that on Thursday 🙂