Slicers are one of my favorite feature in Excel. And here is a quick demo to show why they are my favorite.
Slicers – what are they?
Slicers are visual filters. Using a slicer, you can filter your data (or pivot table, pivot chart) by clicking on the type of data you want.
For example, let’s say you are looking at sales by customer profession in a pivot report. And you want to see how the sales are for a particular region. There are 2 options for you do drill down to an individual region level.
- Add region as report filter and filter for the region you want.
- Add a slicer on region and click on the region you want.
With a report filter (or any other filter), you will have to click several times to pick one store. With slicers, it is a matter of simple click.
See this demo:
Getting started with Slicers – Video
Here is a quick 5 minute video tutorial on Slicers. If are just getting started with this AWESOME feature, you must watch the video, NOW. See it below or head to my YouTube channel.
Download Slicer Examples Workbook
This post is very long and has many examples. Please click here to download slicer examples demo workbook. It contains all the examples shown in this post and a fun surprise too.
How to insert a slicer?
Note: Slicers are available only in Excel 2010 and above.
Adding a slicer in Excel 2010:
In Excel 2010, you can add a slicer only to pivot tables. To insert a slicer, go to either,
- Insert ribbon and click on Insert Slicer
- or Options ribbon (PivotTable Tools) and click on Insert Slicer
Adding a slicer in Excel 2013 / 2016 / 2019 / 365:
In Excel 2013 and above, you can add a slicer to either pivot tables or regular tables.
Adding slicers to regular tables:
When you add a slicer to regular Excel tables, they just act like auto-filters and filter your table data. To add a slicer to regular table, use Insert ribbon > Insert Slicer button.
Adding slicers to Pivot tables:
To add a slicer, you can do either of these things:
- Right click on pivot table field you want and choose “add as slicer”
- Use either analyze or insert ribbon to add the slicer.
Single vs. Multi-selection in Slicers
You can select a single item or multiple items in slicers. To multi-select,
- If the items you want are together, just drag from first item to last.
- If the items you want are not together, hold CTRL key and click on one at a time.
- You can also click on the “checkbox” icon in slicer header to multi-select items in slicers.
Creating interactive charts with slicers
Since slicers talk to Pivot tables, you can use them to create cool interactive charts in Excel. The basic process is like this:
- Set up a pivot table that gives you the data for your chart.
- Add slicer for interaction on any field (say slicer on customer’s region)
- Create a pivot chart (or even regular chart) from the pivot table data.
- Move slicer next to the chart and format everything to your taste.
- And your interactive chart is ready!
Demo of interactive chart using slicer:
Here is a quick demo.
Linking multiple slicers to same Pivot report
You can add any number of slicers to a pivot report. When you add multiple slicers, each of them plays a role in telling the pivot table what sub-set of data to use for calculating the numbers.
Linking one slicer to multiple pivot tables
You can also link a single slicer to any number of pivot reports. This allows us to build very powerful, cross-filtered & interactive reports using Excel.
To connect multiple pivot tables to single slicer, follow these steps.
- Optional: Give names to each of the pivot tables. To name the pivot tables, click anywhere in the pivot, go to Analyze ribbon and use the pivot table name field on top-left to give it a name.
- If you don’t name your pivot tables, Excel will give them default names like PivotTable73. This can be confusing once you have more than a few pivot tables.
- Right click on the slicer and go to Report Connections (in Excel 2010, this is called as PivotTable connections).
- Check all the pivot tables you want. Click ok.
Now both pivot tables will respond to the slicer. See this demo:
Linking slicers to more than one chart
You can use the same approach to link one slicer to more than one chart (pivot chart or regular one).
See this demo:
You can examine this chart in detail in the Slicer Examples workbook.
Capturing slicer selection using formulas
While slicers are amazing & fun, often you may want to use them outside pivot table framework. For example, you may want to use slicers to add interactivity to your charts or use them in your dashboard.
When you want to do something like that, you essentially want the slicers to talk to your formulas. To do this, we can use 2 approaches.
- Dummy (or harvester) pivot table route
- CUBE formulas route
Dummy pivot table route
This is the easiest way to capture slicer selection into a cell. Using a dummy pivot table, we can find out which items are selected in slicers and use them for some other purpose, like below:
The process is like this:
- Let’s say you want to know which profession is picked up in the slicer (so that you can use it in some formulas or charts).
- Create another pivot table.
- Add the profession field to row labels area.
- Link the slicer to this new pivot table as well (using report connections feature of slicer)
- Now when you click on the slicer, both original pivot and this new dummy pivot change.
- Access row labels like regular cells in your formulas to find out which slicer item is selected.
See this illustration to understand how to set up the formulas:
CUBE Formula approach:
This is relevant only if your slicers are hooked up to a data model thru something like Power Pivot, SAS Cubes or ThisWorkbookModel in Excel 2013 or above.
To find out slicer selection, we need to use CUBERANKEDMEMBER() Excel formula like this:
=CUBERANKEDMEMBER(“ThisWorkbookDataModel”, Name_of_the_slicer , item_number)
Let’s say you have a slicer on Area field, and its named Slicer_Area (you can check this name from Slicer properties)
To get the first item selected in the slicer, you can use CUBERANKEDMEMBER formule like this:
=CUBERANKEDMEMBER(“ThisWorkbookDataModel”, Slicer_Area, 1)
This will return the first item selected on slicer. If there is no selection (ie you have cleared the filter on slicer), the Excel will return “All”.
Bonus tip: You can use =CUBESETCOUNT(Slicer_Area) to count the number of items selected in slicer.
Bonus tip#2: By combining CUBESETCOUNT and CUBERANKEDMEMBER formulas, you can extract all the items selected in the slicer easily.
Please download Cube Formula Slicer Selection example workbook to learn more about this approach.
Note: this file works only in Excel 2013 or above.
Formatting slicers
Slicers are fully customizable. You can change their look, settings and colors easily using the slicer tools options ribbon.
Here is a quick FAQ on slicer formatting:
Q. I have too many items in slicer. How to deal with this problem?
Simple. See if you can set up your slicer in multiple columns. You can also adjust the height and width of slicer buttons to suit your requirements. If your slicer is still too big, you can adjust the font size of slicer by creating a new style.
Q. I don’t like the blue color of slicer. What do I do?
You can switch to another color scheme. Just go to Slicer Tools Options ribbon and pick a style you want.
Pro tip: You can create your own style to customize all aspects of a slicer.
Q. I don’t like the title on slicer. Can I get it rid of it?
Yes you can. Right click on the slicer and go to “Slicer Settings”. Uncheck display header option to remove the header & clear filter button.
Q. My slicer keeps showing old products (or categories etc.) that are no longer part of data after refresh. What do I do?
Simple. Right click on the slicer and choose “Slicer settings”. Check Hide items with no data option.
Q. I want to make my slicers look good. But I don’t know where to start…
Here is an inspiration for you.
Slicers vs. Report Filters
In a way slicers are like report filters, but way better. (Related: Introduction to Pivot Table Report Filters)
There are few key differences between both.
- Report filters are tied to single pivot tables. Slicers can be linked to any number of pivots.
- Report filters are clumsy to work with. Slicers are very easy to use.
- Report filters may not work very well in a touch screen environment. Slicers are great for touch screen UIs.
- Report filters take up one cell per filter. Slicers take up more space on the worksheet UI.
- Report filters can be automated with simple VBA. Slicers require a bit more code to automate.
- You can access report filter values using simple cell references. Slicer values can be extracted using either dummy pivot tables or CUBE formulas, both of which require extra effort.
Slicers vs. Timelines:
If you have a date field in your data, you can also insert a “timeline”. this is a special type of slicer, that works only with date values.
Here is a quick demo of Timeline slicer.
You can also customize the look & feel of Excel Timelines.
The download workbook has an example of timelines.
Slicers & Compatibility
Slicers are compatible with Excel 2010 & above versions of Excel. You can also use Slicers with Excel Online.
If you create a workbook in Excel 2010 (or above) with slicers and email it to a friend using Excel 2007, they will see an empty box where slicer should be.
Slicers work on desktop & web versions of Excel in the same way.
Download Slicer Examples Workbook
Please click here to download slicer examples demo workbook. It contains all the examples shown in this post and a fun surprise too.
Also download the Cube formulas approach for slicer selection extraction workbook to learn that technique.
Additional Resources to learn about Slicers
If you like slicers and want to learn creative ways to use them in your work, check out below examples:
- Create a fully dynamic dashboard using Pivot Tables & slicers
- Use slicer as scenario selection mechanism
- Slicers + charts for awesome user experience – case study & one more
- Related: Introduction to Excel Relationships & Data Model
- Related: Introduction to Excel Pivot Tables
- Related: Introduction to Excel Report Filters
- Related: Advanced Pivot Table Tips & Tricks
Do you use Slicers? What are your favorite tips about slicers?
As mentioned earlier, slicers are one of my favorite features of Excel. I use them liberally in my dashboards, charts & workbooks.
What about you? Do you use slicers? When do you use them? What are your favorite tips when it comes to using slicers? Please share in the comments area.
39 Responses to “11 very useful excel keyboard shortcuts you may not know”
You asked about a favorite keyboard shortcut: I often right click the navigation arrows at the bottom of an Excel workbook to get a list of the worksheets. I can click the one I want without having to scroll left or right.
I regularly use the networkdays(x,y,z) function to show the number of working days between two given dates. To exclude public holidays I reference a list of dates ("z" in the above reference) which I periodically update to reflect upcoming non-working days. To keep the sheet looking tidy for other users, I like to hide this column when I'm done, and then unhide it when I update the sheet.
With 40 separate workbooks to edit, these shortcuts make it a breeze...
? Hide selected column: CTRL+0 [zero]
? Unhide hidden column(s) within selection: CTRL+SHIFT+) [closed parenthesis]
If an "Autofit Selection" keyboard shortcut (not just a key sequence) existed, I'd be as happy as a clam!
you can use Alt+o+c+a to autofit selection
Is there a way to auto arrange the column width as you enter the data. Without a macro
You may use alternative key of hide the selected cell by ---print + H key
Unhide---prnt+U keys
You may use alternative key of hide the selected cell by ---print + H key
Unhide---prnt+U keys
[...] 11 very useful excel keyboard shortcuts you may not know [...]
[...] 11 very useful excel keyboard shortcuts [...]
[...] an Excel Conditional Formatting Rock Star 11 very useful excel keyboard shortcuts 73 Free Designer Quality Excel Chart Templates Tracking mutual fund / Stock portfolios using Excel [...]
Hello Chandoo, thanks for sharing this information. With some of the shortcuts I seem to have difficulties as they do not seem to work on the German keyboard.
Can you maybe verify that those combos are only working with the keyboard setup you are using? What would be a good source to lookup combinations for other layouts?
Regards, 1xoid1
@1xoid1 ... Thanks for visiting PHD and taking timeout to ask your question. Unfortunately all my German can be summarized to one phrase: "guten tag".
I wont be able to help you, but I can request other readers to respond. So if you know German or use German keyboard and can answer 1xoid1's question, then you get a free donut.
Guten Tag 🙂
To Sam Krysiak.
Shortcut to Autofit Selection (assuming you mean autofit columns). If you right click toolbar, click customise. From Commands tab/Categories select Built-in Menus.
In Categories window scroll down and select Columns and drag drop it onto toolbar. Then click the new toolbar Columns button and drag drop Autofit button onto your toolbar (note Autofit for Columns will no longer appear in your menus, only on toolbar).
Remove Columns button from toolbar (if you want to keep clutter down) drag and drop it off of your toolbar.
Close Customise box.
Now to Autofit columns just press "Alt" then "A".
Comment 8 correction - 2nd paragraph should read
"In Commands window...
@1xoid1:
Read the following text as follows: The key ,[;] is the one right to the M on the German keyboard. Here are the differences you have to know when using a German keyboard:
2. Press strg .[:] for inserting the current date (and strg shift .[:] for inserting current time)
3. Press strg ,[;] to copy values from cell above
8. Press strg shift –[_] to apply an outline border
10. Press strg-shift S to activate the font drop down (Schriftgroesse)
11. Press strg-shift G to activate the font size (Groesse)
Number 10 and 11 do not work with Excel 2007 anymore, but strg-shift-P shows the font tab of the cell format dialogue in Excel 2007.
All other shortcuts should work on a German keyboard exactly as Chandoo described them.
More information needed? Download a complete list with all shortcuts for Microsoft Excel in German (for free):
http://www.freeware-download.com/downloaddetails/5655.html
@Chandoo: please do not send a donut, unless you are able to attach one to an email. Otherwise the donut might be able to walk by itself, when it arrives here in Germany...
I forgot to mention:
For all readers using an English keyboard: Chip Pearson offers a comprehensive list of Excel shortcuts on the English keyboard:
http://www.cpearson.com/excel/ShortCuts.aspx
[...] your own keyboard shortcuts in Excel 2007, knowing a few keyboard shortcuts in excel is a huge help. Lyte Byte describes a nifty way to create your own key board shortcuts in [...]
[...] Select a bunch of cells and click on the Sigma symbol on the standard tool bar. Alternatively you can use Alt+= keyboard shortcut. [...]
I do some data entries column A,column B ,Column C , A and B have 10 to 12 digit codes , C has the names ; Kindly suggest me a format or formula for excel to avoid duplication of entries in all the the three columns.
Regards,
Prashant
@Prashant... You can use conditional formatting to highlight duplicate entries in the three columns. That way whenever you type a dupe value in a cell the formatting would highlight the values so that you can avoid the error.
check this post for more on using this way to handling duplicates: http://chandoo.org/wp/2008/03/13/want-to-be-an-excel-conditional-formatting-rock-star-read-this/
If you are looking for a way to remove duplicates from an existing range, you can try one of the various techniques we have described here. Try these tips:
http://chandoo.org/wp/2008/11/06/unique-duplicate-missing-items-excel-help/
http://chandoo.org/wp/2008/08/01/15-fun-things-with-excel/
[...] good alternative (although manual) is to use keyboard shortcuts CTRL + ; or CTRL + : to insert current date and time in the active cell. Since this places the [...]
Your site doesn't correctly work in safari browser
Hi GesyimmeliA: Can you tell me which version of Safari on which OS has this problem. I use Macbook at home and loaded the site quite often in Safari and never seen any layout or content issues. Are you facing any script issues while posting comments or somethings like that ?
Hey Chandoo. Great site. Learning lots.
My favorite Excel shortcut has got to be Alt+Down when over an autofilter drop down. Learning that changed my life. That was one of the last things I needed to use a mouse for. Changed my life.
[...] are a big advocate of keyboard shortcuts. I think learning a handful of keyboard shortcuts can improve your productivity tremendously, [...]
My favourit keyboard shortcut is control and 1 (use the 1 above the letters on the keyboard, not the number pad) for format cells.
Favourite shortcut: alt + shift + right/left arrow for grouping/ungrouping!
[...] Select a bunch of cells and click on the Sigma symbol on the standard tool bar. Alternatively you can use Alt+= keyboard shortcut. [...]
[...] clicking on these: excel keyboard shortcuts, excel mouse tips & tricks, excel productivity tips part 1 & part [...]
Martin Williamson thanks for the autofit tip! You rock.
In order to generate charts/bar graph with a single key:
1. Select the data
2. Press F11
3. Magic.... 🙂
F3 = Paste a Name or the entire list of Names
Crtl + F3 = Name Manager
Crtl + : = Inserts current time.
F12 = Save As
Mistake!
Crtl + Shift + : = Inserts current time.
SIR U R THE BEST PERSON WHO SHARES A WONDERFULL AND IMPORTANT TIPS IN EXCEL. THANKS AND KEEP ROCKING.
How do i hide / unhide a work sheet using the keyboard.
Great!
Other useful excel shortcuts
http://webtutorialplus.blogspot.com/2012/01/excel-quick-tip-useful-encoding-hotkeys.html
input in one cell 1a23bc output in two cell one of 123 and other one is abc how to possible, please help me.
Hey cool shortcuts but excel have more shortcuts then you listet.
i find a big database of supportet shortcuts for Excel 2007 here
http://www.veodin.com/excel-2007-shortcuts/
[...] INTEREST Date with my sheet – 10 tips on using date / time in excel http://chandoo.org/wp/2008/07/29/excel-keyboard-shortcuts/ [...]
Great article with some very useful follow-up comments and tips.
One simple question … how do you vertically align the drop-down filter button in a cell with a larger than normal height?
It’s easy enough to do so with a cell’s contents but the drop-down filter button stubbornly remains at the bottom and I need it at the top!
I’ve looked everywhere and haven’t located an explanation to what I am sure is considered an Excel basic.
Cheers