How to use Slicers in Excel: Video and Interactive Guide - Excel Campus
11

How to use Slicers in Excel: Video and Interactive Guide

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.

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.

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 11 comments
Kamal - October 10, 2016

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 🙂

Reply
    Jon Acampora - October 11, 2016

    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!

    Reply
Sachin - June 15, 2014

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

Reply
    Jon Acampora - June 15, 2014

    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.

    Reply
mano - February 14, 2014

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

Reply
TodoExcel - February 13, 2014

Jon, thanks for your rapid answer and delivery of the needed tip I asked!

Greg, Excel 2011 (MAC) seems to have some limitations. If you run complex Excel spreadhsheets with slicers and macros Excel 2011 for Mac is a pain.

There are 2 products which allows you to run windows on Mac (and then Excel 2010 or better Excel 2013 with no limitations):

http://www.parallels.com/products/desktop/
http://www.vmware.com/products/fusion/

😉

Reply
Greg - February 13, 2014

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?

Reply
    Jon Acampora - February 13, 2014

    Hi Greg,

    Great question. Unfortunately slicers are not available in Excel 2011 for Mac, but there is a workaround. The following link is a list of known issues for 2011, and slicers is the first one on the list.

    http://office.microsoft.com/en-us/mac-excel-help/known-issues-in-excel-2011-HA102928558.aspx

    Since the issue is listed first, maybe this means the next Excel for Mac version will contain slicers.

    Slicers are available in the Excel Web App. So you could upload your file to SkyDrive (soon to be called OneDrive) or SharePoint, and share the link with your Mac users. This would allow them to open the file in a web browser and use the slicers.

    Here is a link to open the Excel Slicers – Interactive Guide.xlsx file in the Web App.

    https://skydrive.live.com/redir?resid=3723CF33DB15C44F!794&authkey=!AI1x6W7VlrQXGuM&ithint=file%2c.xlsx

    Unfortunately, shapes are not supported in the web app yet, so you won’t be able to see the guide. But you will be able to see the slicers on the PivotTable and interact with them.

    I hope this helps. Let me know if you have any questions.
    Thanks!

    Reply
rolo - February 13, 2014

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!

Reply
    Jon Acampora - February 13, 2014

    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!

    Reply

Leave a Reply:

Take Your Excel Skills & Career to the Next Level

10 Excel Pro Tips eBook

Get my eBook & FREE weekly updates to help you learn Excel.

x