Wingdings and Other Symbolic Fonts in Slicers!

 
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:  http://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 :)

16 Responses to Wingdings and Other Symbolic Fonts in Slicers!

  1. SimonLid says:

    Nice post, there is no need to download a character map. Press the windows button and type charmap and press enter and you will get into Character Map

  2. Chris says:

    Did you check that the fonts render in Excel Services?

  3. Ed Gregory says:

    Thanks Rob, and Simon. Another great feature to pack away in an over-packed brain. But something like this is one of those nice “differentiation” features.

  4. Ed Gregory says:

    “Stretchy” column-width or page width?

    Just curious why you seem to have chosen a template with hard-coded page width. I’m one of those who view on different machines, monitors, with a wide variety of resolutions. Sometimes, I have to change resolution temporarily to view your site as (I think) you intended.

  5. Michael Talbot says:

    Thanks for all your helpful posts.
    I read somewhere that this change of style is local to the Microsoft excel on the PC you are using, not saved in the spreadsheet itself.
    If you open the spreadsheet on another computer, the style is missing, thus the orginal (default) style is used.

    Michael

    • powerpivotpro says:

      Hi Michael, thanks for the kind words :)

      Custom styles DO get saved in the workbook. In fact, transferring a workbook with a custom style in it across computers is the workaround for “sharing” a custom style.

  6. Laurent says:

    “You CANNOT mix and match characters from different fonts! … 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.”

    I am a little confused about the scenario you are referring to.

    You can very well have different fonts on the slicer for different states and/ or for differents parts (header / data items).

    Do you mean you would like to be able to use differents fonts for the same data item? If yes, then how does it work for a pivot table … ?

    • powerpivotpro says:

      Yeah, I struggled with the wording of that, too.

      Let’s say you want a slicer that offers three choices to the user – night, morning, and day.

      And you find a great symbol for night, and also one for day, in the original WingDings font. But you *don’t* find a good one for morning.

      If you find a good symbol for morning in another font, like WingDings 3 for example, I don’t know of a way to use WingDings for two of the slicer tiles and WingDings 3 for the third slicer tile. All tiles in a single slicer have to come from the same font, since the only way to set font is via the Styles dialog, and the Styles dialog doesn’t let you specify font for first tile, second tile, etc.

      Let me know if you find a way, I would be pretty stoked.

      • Laurent says:

        It is now clear what you meant.

        Regarding pivot tables, it occurred to me you were referring to the formatting of individual cells on the pivot table body. (I was focusing too much on pivot styles, which would not let you do that either.)

        Unfortunately, I have no workaround for this, besides creating a new font and distributing it along with the workbook.

  7. Bill Jelen says:

    Rob – this is an awesome trick! Thanks for figuring it out. I never even realized you could right-click and duplicate a slicer style (and it works with the new Timeline Style). Massive amounts of time-wasting in my near future…

  8. Jeff Weir says:

    WHAT! No Arial Unicode??? Pity, because there’s some pure golf tucked away in there that would make any management report shine
    – A very happy snowman at 9731 (or Arial Unicode MS no. 2603)
    – The skull and crossbones at 9760 (or Arial Unicode MS no. 2620)
    – The old soviet hammer and sickle at 9773 (Arial Unicode MS no. 262D, ironically placed right next to the Peace symbol at 262E)
    – Great cleavage at 5729

    • Jeff Weir says:

      Whoops, I meant “pure gold” not “pure golf”.

      • powerpivotpro says:

        OK that makes a TON more sense :)

        Jeff if you’ve found some symbols lurking in other fonts and want to compile some more examples to supplement those that I posted, feel free to send them my way. I’ll post them to the blog with attribution to you.

  9. David Wetton says:

    FYI, Following the info in this posting (and also some tips in the new Alchemy book) I added a new video on my EXCEL BI training channel in Youtube on how to create a wingding slicer: http://www.youtube.com/watch?v=ROrXPFmHExg

    I’m not sure that using “IF” statements is the best way to do this, but it works.

    As noted above, custom slicers do stay with the Excel file so if you share the file the user will get the customized slicers. I find that most of the default slicers are a bit pale, and the ones with the white fonts are horrible.

    I’m planning to build a “template” with my favorite customized slicers and use that file when starting a new project so that I’ll have all those custom tools available for immediate use. You can spend a lot of time creating some very unique slicers so I want to be able to recycle those in other files. I;m not sure what the limit is in regards to customized slicers, but if I hit the ceiling I’ll be sure to post that info here.

Leave a Comment or Question