• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Symbols in Excel Slicers

You are here: Home / Excel PivotTables / Symbols in Excel Slicers
July 11, 2017 by Mynda Treacy

Did you know you can display symbols in Excel Slicers? That’s right, Slicers aren’t just limited to listing boring old items from your PivotTables or Tables.

In the animated image below I’ve got a Slicer that displays currency symbols. As you click on a currency symbol in the Slicer, the chart converts to the selected currency.

Symbols in Excel Slicers

Of course, this isn’t limited to use with charts and in fact this isn’t even a PivotChart. Let your imagination run wild…why not use this technique to convert a whole dashboard into different currencies.

Watch the Video

Subscribe YouTube

Download the Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Interactive Chart with Symbols in Excel Slicers

There are just 4 components to this example so let’s step through them and then we’ll look at some other examples of symbols in Excel Slicers.

Step 1: Currency Table

tbl_currency

The table above, called tbl_currency, contains the list of currencies and their exchange rate.

The Symbols are found on the Insert tab > Symbol:

insert symbols

Note: Symbols using Wingding fonts require special treatment. More on that later.

Step 2: Insert a PivotTable & Slicer

insert a pivottable and a slicer

The source data for the PivotTable in cells B20:C21 is tbl_currency.

A Slicer for the PivotTable Symbol field allows the user to specify which currency is displayed in the chart and filters the PivotTable to only display one currency. How to insert and use Slicers.

Step 3: Sales Table for the Chart

sales table for the chart

The table above (called tbl_chartData) contains the sales by month. Columns F and H feed the chart.

a. Column H converts the sales into the currency based on the symbol selected in the Slicer, the rate for which is in cell C21 of the PivotTable. Notice that the formula uses Structured References ( [@Sales $]] ) because this is an Excel Table. More on Tables and Structured References here.

b. Cell H3 (above the table) contains a custom label for the chart title that picks up the currency symbol selected in the Slicer. How to insert custom chart labels.

Note: If multiple currencies were selected in the Slicer it would convert it to the first currency selected. Any other selected currencies would be ignored because the formula in column H is only interested in the rate in cell C21.

Step 4: Insert Chart and Format Slicer

insert chart and format slicer

a. Position the Slicer beside the chart. Remove the Slicer header (right-click Slicer > Slicer Settings > uncheck ‘Display Header’).

Tip: Slicers can be on a different worksheet to the PivotTable.

b. Insert the chart; select column F and H of the table (hold CTRL to select non-contiguous cell ranges) > Insert tab > Charts - select a line (or column) chart.

c. Link the Chart Title to the custom label in cell H3. Tutorial on Custom Chart Labels.

Summary

There are just 5 skills required to create this interactive chart:

  1. Excel Tables and formulas using Structured References (this is recommended, but optional. A regular table would work too).
  2. Excel PivotTables
  3. Slicers
  4. Inserting Charts (nothing complicated, just use the charts on the Insert tab)
  5. Custom Chart Labels

So, you see that even with some quite basic Excel skills you can create something clever. In my Excel Dashboard course I aim to equip you with lots of skills that you can combine to create powerful reporting solutions to wow your users.

Other Symbols in Excel Slicers

There are tons of different symbols available in the Insert Symbol dialog box, but you must watch out for symbols that require a special font. This is because Slicers have a default font and if you want something special then you have to create a custom Slicer Style (more on that later).

Here are some examples:

Other Symbols in Excel Slicers

  1. The weather symbols use Wingdings. We need to set up a custom Slicer Style for these to render properly in the Slicer. More on that in a moment.
  2. The Cards are fine, they just use Arial font. Note: if you don't see these symbols in the Symbols dialog box, you can enter them using the Windows Character Map program.
  3. The last example uses Bell MT. You can see that the apple symbol doesn’t render in the Slicer but the other two do. Again, this Slicer will require a custom Slicer Style if you want to use these symbols.

Caution: If you require a custom Slicer Style then you can only choose symbols from the same font family, because you can only set one font for the Slicer buttons.

Creating Custom Slicer Styles

Creating a Custom Slicer Style is easy and it travels with the Excel file:

  1. With a Slicer selected go to the SlicerTools: Options tab > right-click an existing Slicer Style that you like the colour scheme of > Duplicate:
  2. go to the SlicerTools

  3. In the Modify Slicer Style dialog box (image below), we’ll set the font for the Slicer

  4. a. Select the ‘Whole Slicer’ element

    b. Click the ‘Format’ button.

    c. Go to the Font tab

    d. Choose the font to match your symbols

    e. Give your Slicer Style a name if you want.

    modify slicer style

    Bonus tip: if you want your Slicer header to display in a regular font, like Arial, choose the ‘Header’ Element and change the font back to something readable.

  5. Apply your custom Slicer Style to the Slicer: Select the Slicer > SlicerTools: Options tab > in the Slicer Styles gallery click on your custom style. Tip: It should be the first one in the list:

custom slicer style

Thanks

I stumbled upon this post by Rob Collie on using Wingdings in Slicers for Power Pivot, which was the inspiration for this post.

More Slicers Posts

Slicer Controlled Interactive Excel Charts

Slicer Controlled Interactive Excel Charts

Slicer Controlled Interactive Excel Charts can open up a huge range of possibilities and the best part is you can use Slicers to control regular charts too.
slicer selection in formula

Use Excel Slicer Selection in Formulas

How to use Excel Slicer selection in formulas to control charts and tables. Download the workbook and follow along.
Excel Slicer Trick

Excel Slicer Trick

In this Excel Slicer Trick I show you how to use a Slicer to display a drilled down view of your data in a PivotChart. Download the file and follow along.
Excel Slicers for Fiscal Years

Excel Slicers for Fiscal Years

Inseting Excel Slicers for Fiscal Years is easy but requires a bit of DIY date classification.
Excel Slicer rolling periods

Excel Slicers for Rolling Periods

Creating Excel Slicers for rolling periods is easy with this IF formula. Download the workbook and watch the video.
Single Slicer for Year and Month

Create a Single Excel Slicer for Year and Month

How to create a single Excel Slicer for Year and Month fields. Includes written instructions, video and workbook download.
Excel Slicers

Excel Slicers – Everything You Need to Know

How to insert and use Excel Slicers to create interactive charts and PivotTables. Includes video and step by step written instructions.
Sorting Excel Date Slicers

Sorting Excel Date Slicers

Slicers have some shortcomings when it comes to dates and sort order. This post explains a couple of ways to sort dates correctly in Excel slicers.

More Excel PivotTables Posts

Auto Refresh PivotTables

Auto Refresh PivotTables isn’t on by default, and the process differs depending on if your PivotTables is loaded to the data model or not.

Show Items with no Data in PivotTables

Show Items with no Data in PivotTables allows you to maintain a constant structure to your PivotTable or Pivot Chart axis when filtering.

Force Excel Slicers to Single Select

There's no build in way to force Excel Slicers to single select but we can use these clever warnings to persuade your users.
excel pivottable p&L

Excel PivotTable Profit and Loss

Creating an Excel PivotTable Profit and Loss Statement means you can use Slicers and Conditional Formatting and have the P&L automatically update.

Excel PivotTable Field List Tips

Customize the Excel PivotTable Field List to suit your needs. Find how to turn the PivotTable Field List on and off and other handy tips.

Hide Blanks in Excel PivotTables

Hide blanks in Excel PivotTables caused by empty cells in your source data. I’m talking about PivotTable cells containing the (blank) placeholder.
Excel Slicer Formatting

Excel Slicer Formatting

Excel Slicer Formatting is essential because they’re big and chunky. In this tutorial I show you the tricks to make Excel Slicers small.
Excel PivotTable Quick Explore

Excel PivotTable Quick Explore

Drill down into data hierarchies using PivotTables and Pivot Charts with Excel PivotTable Quick Explore. New in Excel 2013 onward.
excel online pivottables

Excel Online PivotTables

Excel Online PivotTables are now available from the Insert tab of the ribbon. There are some limitations that are covered in this post.

Excel PivotTable Error Handling

Excel PivotTable error handling and why you can’t calculate the percentage change when the prior period is zero or blank.


Category: Excel PivotTablesTag: slicers
Previous Post:Excel Chart Secondary Axis Alternatives
Next Post:Power Query Unpivot Scenarios

Reader Interactions

Comments

  1. Kent Burkhardt

    February 18, 2022 at 5:30 am

    Mynda, another great post! I enjoyed it so much that I wanted to re-create it in PowerBI. Got hung up on converting sales to the filtered currency. Out of curiosity, how would you set it up in PowerBI? So simple in Excel, but seems a lot harder in PowerBI.

    Seems like another table is required that is able to be changed dynamically based on the currency selected, and use that exchange rate and multiply it by the selected currency. Just not sure how to set that up.

    Any suggestions?

    Kent

    Reply
    • Mynda Treacy

      February 18, 2022 at 9:19 am

      Thanks, Kent! One way is to use a disconnected table to switch currencies, like I used to switch aggregation methods.

      Reply
  2. Ken McMillan

    February 18, 2022 at 4:05 am

    Thanks for your videos. They are very good!

    Reply
    • Mynda Treacy

      February 18, 2022 at 9:16 am

      Thanks so much, Ken!

      Reply
  3. Bernice

    July 18, 2017 at 3:10 pm

    Hi Mynda, thank you for the post. Three odd things. Maybe just me having a brain bubble. 1. I am assuming that you have to manually type the conversion formula in the “Sales Converted….” column and 2. For the life of me, I cannot get the Rupee symbol to display in the slicer. Displays every where else, including your workbook, but when I recreate, slicer just refuses. baaaaaahhh! 3. No matter what I do, I cannot get the pivot table to sort (and hence the slicer) in the same order as you.

    Otherwise, everything worked just dandy!
    Regards,
    Bernice

    Reply
    • Mynda Treacy

      July 18, 2017 at 8:33 pm

      Hi Bernice,

      The ‘Sales Converted to Currency’ is a formula that takes the rate from the PivotTable x the Sales $ amounts. The rates are entered in the Currency table manually.

      The Rupee symbol is an Arial Symbol. Have you tried creating a custom Slicer format and setting the font to Arial?

      The Slicer is sorted in alphabetical order. I’m not sure how Excel is determining this though and it also doesn’t correlate to the numeric code for the characters either.

      Mynda

      Reply
  4. Anand Kumar

    July 13, 2017 at 9:09 pm

    Great One…………Thanks ☺.

    Reply
    • Mynda Treacy

      July 13, 2017 at 9:50 pm

      Thanks, Anand 🙂

      Reply
  5. crystal (strive4peace)

    July 13, 2017 at 6:59 am

    very nice, Mynda

    respectfully,
    crystal

    ~ have an awesome day ~

    Reply
    • Mynda Treacy

      July 13, 2017 at 8:47 am

      Thanks, Crystal!

      Reply
  6. Peter Buyze

    July 11, 2017 at 11:21 pm

    Hey Mynda, another great post. I shared it on G+ here https://plus.google.com/+PeterBuyze/posts/A2xWYtSugJn

    Reply
    • Mynda Treacy

      July 12, 2017 at 10:34 am

      Thanks, Peter!

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.