Wingdings and Other Symbolic Fonts in Slicers!

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?

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 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:

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 measuresthat 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 line – Planes, Trains, and Automobiles.  (And the unmade sequel, Buses, Bikes, and Boats!)

How Do I Do This?

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

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

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

Now Add Slicers

When I add slicers on those fields, I get:

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…

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

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!

NOW Modify the Custom Slicer Style

Right click your custom style, choose Modify:

Now you get the following dialog:

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

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:

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

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 🙂