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.
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.
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
*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.
- Comprehensive Guide to Slicers (Articles by Excel MVP Debra Dalgleish at Contextures Blog)
- How to use Slicers and Timelines on Tables in Excel 2013 (video by Excel MVP Zack Barresse)
- Timelines – cooler than sliced bread (article and links by Zack Barresse)
- How to list all slicers in the workbook with a VBA Macro (Excel Campus Library)
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!
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 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!
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/
😉
Hi dear Greg,
thank you very very much,let me for you thanks to persian language:
آقای گریگ از شما بخاطر آموزش اکسل متشکرم
Hi Mano, I’m glad you are enjoying the Excel training. 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 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!