This post contains a video and interactive guide (free download) on how to use slicers. You can use this if you are distributing a file that contains slicers, and want to provide instructions to your users.

This post contains a video and interactive guide on how to use slicers (free download).
You can use this if you are distributing a file that contains slicers, and want to provide instructions to your users.
Interactive Guide to Using Slicers in Excel

Video


Watch full screen or on Youtube

What's a Slicer?

Slicers were introduced in Excel 2010, and allow you to quickly filter a PivotTable or PivotChart with the click of a button.  They also serve as a visual reference that allow you to see what filters are currently set in the PivotTable.

Excel Slicer Screenshot

Basically, slicers do the same thing as filtering a PivotTable using the Filter drop-down menus.  However, users and consumers of your reports will really enjoy using slicers.  They are very easy to use and make your report more interactive.

Slicers can also greatly condense your file size.  For example, let's say you have a workbook with a lot of tabs, and each tab contains a similar report for each region, product, category, etc.  You could consolidate all of these reports into one sheet and use a slicer to allow the user to see different views (slices) of the same report.

Interactive Guide

The interactive guide (download below) is an Excel file that contains instructions on how to use slicers.  The entire guide is self contained in one worksheet, and you can copy this worksheet into your workbook before distributing to your audience.

Interactive Guide to Using Slicers in Excel

The guide covers the basics of clicking on the slicers and also shows some tips for working with multiple slicers connected to one Pivot.

The guide actually uses slicers to step through the instructions, so there is NO VBA or macros in the file.  This uses a technique by Bill Jelen (aka Mr. Excel) and you can checkout his YouTube video to learn more about it.  It's an awesome technique!

Download

Excel Slicers - Interactive Guide.xlsx (39.1 KB)

*Compatible with Excel 2010 and 2013.

Please leave a comment with any questions or suggestions on how to improve this guide.  Thanks!

Additional Resources

The guide above will show you how to use slicers.  Here are some articles and videos on how to create slicers and add them to your PivotTables and PivotCharts.

11 comments

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

  • Hi Jon,

    I have an issue, here is the facts:
    I’ve create slicer on my XLS file, the slicer is a list of dates, and when I click on a date I have all the tasks that are done during this day on a pivot table called “Tasks Overview”.
    The thing is we might have some “Canceled tasks” some days, so to show the exact number of the tasks that are done on my dashboard am using a simple equation that calculate the following:
    “Tasks done = total tasks – canceled tasks”
    So when I choose on my slicer a day with no canceled tasks I get “#REF!” as a result of my equation. on the other side if I choose a day with “Canceled tasks” I got the number of done tasks.

    Thanks in advance for your help 🙂

    • Hi Kamal,

      You might be able to use an IFERROR function to handle the error. It’s hard for me to tell without seeing the data. You could also add a slicer for the Task Status to filter out any canceled tasks. That would depend on how your data is structured. I hope that helps. Thanks!

  • Jon,

    You mentioned there was a workaround for slicers on Excel 2011 for Macbooks. Could you explain in detail or provide some links that explain the process?

    Also, have you had the chance to play with excel 2013 on Macbooks and know if they have an in depth slicing tool?

    Thanks

    • Hi Sachin,

      Sorry, that comment might have been confusing. Slicers are NOT available in 2011 for Mac. The workaround is to use the Excel WebApp, which is available to all users for free on OneDrive (formerly SkyDrive).

      Excel 2011 is the latest version for Mac at this point. I don’t know about the future version yet.

  • Hi dear Greg,
    thank you very very much,let me for you thanks to persian language:
    آقای گریگ از شما بخاطر آموزش اکسل متشکرم

  • Jon,
    I think slicers are an excellent way to build usable spreadsheets. However, I work in a company that has both Macs and PCs and it doesn’t seem that Excel for the Mac supports slicers. Do you know if this is true?

  • Hi Jon, great tutorial!

    How did you get the effect of changing images when a button is pressed? (Intro, Step 1, Step 2) I see that Step buttons are slicer, but I have never seen how to change images with slicers!?

    Thanks!

    • Hi Rolo,

      The effect of changing the images when the slicer button is pressed is based on a technique that Bill Jelen created. You can see more about it on his YouTube video here.

      http://youtu.be/P_6fBkFAaAQ

      Basically, the image is a Linked Image and the source of the image is a named range. The named range contains an OFFSET formula to display the image. This OFFSET formula references a starting cell, which is cell AC58 in the file, and then offsets based on the value that is displayed in the PivotTable in cell AG50. The value displayed in the PivotTable is based on the Slicer for the Steps (Intro, Step 1, Step 2, etc.).

      Sounds confusing? 🙂 It is a complex process and I recommend watching Bill’s video to help get an understanding.

      The nice part is that you can change any of the instructions in this guide by modifying the text boxes starting in cell AC59. You can also add/delete steps if needed.

      Please let me know if you have any other questions. Thanks!

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly

Macros and VBA Training Webinar