I have an exciting news & massive post for you.
As of Jan 19, 2011, our little blog has registered our 20,000th RSS Subscriber. While this is not a huge achievement or anything, It certainly calls for celebration. I am so happy to see our mission to make people awesome in Excel is reaching out to more people everyday. Thank you.
To celebrate this milestone, I am doing a massive post with 220 Excel tips, tricks, tutorials & templates.
Share on Facebook
Tweet This!
Join our FREE Newsletter
Add Chandoo.org RSS Feed to your Newsreader
These 220 tips are broken down in to following areas. Click on the links to access them.
- Formulas [52 tips]
- Formatting & Conditional Formatting [36 tips]
- Charting [60 tips & templates]
- Tables & Pivot Tables [15 tips]
- Using Excel [47 tips]
- Free Downloads [5 downloads]
- Recommended Resources [5 resources]
Formulas [52 tips]
Learn 5 tips on how to use IF formula, CHOOSE Formula in Excel. [link]
Find out how you can sum or count values that meet a criteria. A must have tool for any analyst or manager using Excel. [link]
Lookup formulas are famous for a reason. In this article, you will learn how to use them to find anything, well, almost! [link]
While VLOOKUP is awesome, it can-not go left. That is why you must learn how to use INDEX+MATCH combination. [link]
OFFSET formula becomes very important if you ever plan to make an excel dashboard. So go ahead and learn. [link]
I just love SUMPRODUCT formula. And read this to find out why. [link]
SUMIFS & COUNTIFS formulas are like Swiss army knifes. They can do pretty much anything and still look slick. [link]
Find out how you can use ROWS or COLUMNS formulas to generate sequential numbers [link]
Learn how to calculate moving average using Excel. [link]
Learn how to write formulas to test Between condition. [link]
Calculate weighted average using Excel formulas [link]
Learn to write either-or (XOR) conditions using excel formulas [link]
SUBTOTAL is a powerful & low profile formula. In this article we explore the possibilities and show you some cool examples. [link]
If you use Excel to do anything more than counting your chickens, chances are you use VLOOKUP quite often. Read this to learn 6 important VLOOKUP tips. [link]
15 Formulas for Advanced Users
Anyone can write a SUM or IF formula. But how well do you know some of the powerful formulas in Excel. Read this to know 15 very useful formulas in Excel. [link]
10 Formulas & Tips for working with Date & Time in Excel
Dealing with dates is not something only for young people. Analysts and managers work with dates & times all the time. In this article, we explain 10 powerful ways in which you can work with Excel Date & Time values. [link]
Formatting & Conditional Formatting [36 tips]
16 Paste Special Tips & Tricks
Paste special is one of my favorite features in Excel. In this article, we explore various ways paste special can save us time. [link]
5 Ways to become Awesome using Conditional Formatting
Conditional formatting is your way of asking excel to change a cell’s formatting when a certain condition is met. You can use it do some pretty awesome things. In this classic chandoo.org article, learn 5 ways to use conditional formatting to become awesome. [link]
15 Fun things you can do in Excel in less than 15 seconds
How well do you know Excel? See this list to find out 15 simple but fun things you can do in less than 15 seconds. [link]
Charting [60 tips]
Thermo-meter Chart – Excel Tutorial & Free Template
Prepare a thermo-meter chart to compare actual performance with targets. [link]
Bullet Graph – Excel Tutorial & Free Template
Learn how to make bullet graphs using Excel. [link]
Comparison chart – Excel Tutorial & Free Template
Learn how to make an interactive comparison chart to compare one value with several others. [link]
Pareto Chart – Excel Tutorial & Free Template
Pareto analysis is very important in quality control, decision making etc. In this tutorial learn how to create pareto charts using Excel. [link]
Water Fall Chart – Excel Tutorial & Free Template
Water-fall charts are a good way to visualize how various components contribute to the overall outcome. Like how net profit is arrived etc. Know how to make these charts using Excel. [link]
Panel Charts – Excel Tutorial & Free Template
What are panel charts and how to make them using Excel? [link]
Gantt Chart – Excel Tutorial & Free Template
Gantt charts are an excellent way to plan for a project and track it. Learn how to use Excel’s conditional formatting, cell grid structure etc. to make Gantt charts. [link]
Project Timeline Chart – Excel Tutorial & Free Template
Timeline charts are a great for showing project progress and macro level picture. In this article, we show you how to make such milestone / timeline charts using Excel. [link]
Burn Down Chart – Excel Tutorial & Free Template
Burn down charts help you in visualizing how far the project has progressed. Learn how to create these charts using Excel. [link]
Dynamic Chart – simple example – Excel Tutorial & Free Template
Learn how to make simple dynamic chart using Excel data validation feature. [link]
Dynamic Chart with Checkboxes – Excel Tutorial & Free Template
Learn how to make a dynamic chart with checkboxes and interactivity. [link]
More Dynamic Charts – Excel Tutorial & Free Template
Get more examples, templates & tutorials on Dynamic charts using Excel [link]
In-cell Charts – Excel Tutorial & Free Template
Get more examples, templates & tutorials on incell charts using Excel [link]
Make compelling, effective charts by following these 5 simple rules. [link]
5 Chart formatting tips that cost you nothing and make your charts look cool. [link]
Come what may, do not make any of these 6 types of charts. They are ugly. Period. [link]
Charting Lessons from Optical Illusions
We come across optical illusions all the time. But do you know they also teach valuable lessons on chart formatting & Selection? [link]
Pie charts are ineffective in all but few scenarios. But how do you show composition of several values? Read this post to find 10 alternatives to pie charts. [link]
Back in 2009, we ran a contest on how to visualize budget vs. actual data. You can see 14 different charting ideas in this article. [link]
Tables & Pivot Tables [15 tips]
Excel tables are a new and powerful feature introduced in Excel 2007. Ever since I learned them, I have been much more productive while making dashboards or reports. Read these 10 tips to learn how to use Excel tables better. [link]
Pivot tables are a great way to analyze data and make reports. In this post, learn 5 powerful pivot table tricks & tips. [link]
Using Excel [47 tips]
Learn how to use Double click feature on your mouse to save time and become awesome in Excel. [link]
Learn how to use Mouse to become even more productive in Excel [link]
10 Ways to make your Excel Workbooks Boss-proof
While not all bosses are like Dilbert’s boss, we do realize that they like to press things and test. So it is a good idea to make your workbooks boss-proof. Learn 10 tips to do so. [link]
15 Excel Productivity Tips & Tweaks
Do you know that you can customize excel to make yourself more productive? You can turn off annoying features and set default formatting options to save precious time and make better worksheets all the time. Read this post to learn 15 such customizations & productivity tips. [link]
12 Rules for Making Better Spreadsheet Models
It is a good idea to follow a set of principles while designing a complex workbook model or dashboard. In this post you can find 12 rules for making better excel workbooks. [link]
Free Downloads [5 tips]
Using this handy one-pager, you can quickly remember the syntax for the most used formulas. Also find out what to do if there is an error. [link]
Get this e-book (available only on subscription to my newsletter) to enjoy 95 excel tips & tricks. [link]
Learn how to use Excel 2007 Ribbon
Learn how to use Excel 2007’s ribbon based user interface using this handy guide. [link]
12 Rules for Better Spreadsheets – Poster
Larry’s spreadsheet rules in a simple poster. Very useful if you work on spreadsheet models often [link]
Use this cheat-sheet to write better VLOOKUP formulas [link]
Recommended Resources [5 tips]
Join my Excel school program to learn Excel in a step-by-step fashion and become awesome. We have 23 hours of instruction on Excel & 8 hours of lessons on Dashboards. Click here to learn more. [link]
Excel Project Management Templates
Get a copy of my Excel Project Management templates so that you can save time and become a better project manager. The template pack has 24 templates to take care of various key areas of project management like planning, status reporting, tracking, timesheets etc. [link]
Learn 75 Excel Formulas [e-book]
In this easy to understand e-book, I explain 75 very important Excel formulas in plain English. Get a copy today and learn something new. [link]
Jon Peltier’s Charting Utilities
I recommend Jon’s charting utilities. You can make some of the custom charts that are not part of Excel charting gallery in minutes using his tools. They can save you lots of time and money. [link]
Excel Everest Training Kit for Learning Excel
I recommend using Sean’s Excel Everest if you are an Excel beginner. This is an interactive excel workbook designed to teach you various features of Excel. You can read my review. [link]
Thank you
Thanks for your support to Chandoo.org. Without you this milestone or anything else that we achieve is meaningless. I wish you even more awesomeness and knowledge in the months and years to come.
Share on Facebook
Tweet This!
Join our FREE Newsletter
Add Chandoo.org RSS Feed to your Newsreader
49 Responses to “Introduction to Slicers – What are they, how to use them, tips, advanced techniques & interactive reports using Excel Slicers”
Great article!
If you want to learn a bit more about using slicers in VBA, head over here:
http://jkp-ads.com/articles/slicers03.asp
Hi
I downloaded cube-formula-slicer-selection.xlsx.
Why is 'Report Connections' grayed out?
Great article!! Thank you very much... This post is one of the most helpful for my job!
Great Introduction. Thanks very much.
Wow! trying to use this on the reports that I have now. I really liked that Quantity and Amount Bar graph used on the pivot-multi tab, but for the life of me, I can't seem to replicate it from scratch. Help please?
[…] http://chandoo.org/wp/2015/06/24/introduction-to-slicers/?utm_source=feedburner&utm_medium=email… […]
This is awesome! I will favorite this page in my blog, http://www.exceltoxl.com
Since I've known slicers about 2-3 yrs ago, I've pretty much used them in every damn report I do. Everyone that sees it for the first time is like "This is the best thing ever. Did you do that using excel or something else?" 😀 My bosses are so used it that when they see a report from someone else that doesn't have slicers they send it to me to redo it :).
Couple of tips:-
Tip 1:
If for lack of space or say you want ability to search within a filter due to numerous values being present but still want it to connect to multiple pivot tables or charts then
1. Setup a pivot table with just the report filter
2. Create a slicer with the same field and tie that to all the pivot tables/charts that you want.
3. Just place it some out of sight.
Now you have a dropdown with all your values with search option plsu it is also connected to all your charts and pivot tables.
TIP 2:
In Excel 2013, slicers can be used with just plain tables as well. Not limited to pivot tables.
Congrats!
Nice content : )
Very comprehensive. Explained in an extremely simple way. I have been using Slicers for a while, but still learnt new things from this post. Thanks for sharing. Best wishes.
Awesome Explanation !!
I have joined this blog recently. Brilliant tools are available that I started using in my day to day work. Brilliant site. Thanks heaps.
[…] Read the full article here: Introduction to Slicers – What are they, how to use them, tips, advanced techniques & interact… […]
Oh wow. I've only just started using Excel 2010 and had no idea this even existed. It makes dynamic charts so much easier!
You are my Hero! I am working with PowerPivot due to the huge amount of data I have and could not use my usual tricks to get the scatter chart title to change. For some reason the CUBE function wouldn't work (who knows why, I don't have time to dig into it now) but your "dummy" solution did.
thankyouthankyouthankyou!
Clare
On a normal PivotTable filter, you can choose whether to allow multiple items to be selected or not. Is that possible with slicers (in Excel 2010)? I've had a look through the options and not found a way to do it yet!
Hi Stevie... this is not possible with slicers.
Just hold down control when you're choosing them...can then either click another (without control) and it will show only the new one, or click the filter with the red 'x' to revert back to all options.
Not a limitation that can be placed on the slicer but still a potential workaround depending on your needs.
Very comprehensive note on slicer. I haven't yet used ms excel 2010, but learnt Slicer tool very well
How should I apply Slicer in excel 2010 version, not able find options
as directed, could you please tell me that step by step
@Arif
In Excel 2010 slicers could only be applied to Pivot tables/Charts not Regular tables
@Arif
In Excel 2010 slicers could only be applied to Pivot tables/Charts not Regular tables
I have a longitudinal line graph with the count of exams scored at each level(1-4). I need a longitudinal line graph that shows the percentage for each level. I made my pivot with the count in the field settings with a calculation of % of row total. This works great until you add a slicer fo that you can look at one level at a time. When I do this, it shows as 100% because it seems to lose the rest of the row calculations. How can I set it up to show the percent. I do not have the option of adding it to my data table. I am using straight Pivot, not PowerPivot.
@Mary
I'd suggest asking the question in the Chandoo.org Forums http://forum.chandoo.org/
Attach a sample file with an example of what you are after, even hand drawn
Hi, thanks for these tips. Is it possible to link a slicer to *different data sets*? All my data sets have a "year_opened" and "month_opened" fields, and I'd like do a single filter and update everything at once. Is that possible?
Hi,
Can someone tell me how to format a date field in a slicer to tell July 2016 instead of 07/31/2016?
Thanks in advance.
Great post - easily explainable for non excel whiz.
Thanks for the slicers post. I'm knew to this feature so don't be to harsh on me 🙂
In the example bar chart graph: "Quantity breakup by Customer Profession and & Product category" you get a different picture depending on which area is chosen "East, Middle, North, South, West". That part I get. But the graph itself doesn't specify which region you are in.
Is it possible to put the filtered criteria into the Chart title. For example if I chose West, the title would read "Quantity breakup by Customer Profession and & Product category - West".
Is that possible? Just curious. Thanks
It is possible...I have this on a number of my reports.
1) create a pivot table with just the column your slicer is set on
2) assign the slicer to that pivot table
3) create a string in cell B3 (or wherever):
="Quantity breakup by Customer Profession & Product Category- "&A3
(assuming that A3 is the cell that the chosen region appears in)
4) click (once) on the graph title, then in the formula bar type =B3
As you change the slicers, B3 will update as will the chart title.
Couple of tips:
1) if you need to have a new line for the title, use CHAR(10) e.g.
="Quantity breakup by Customer Profession & Product Category"&CHAR(10)&A3
(this will have the region on a new line)
2) if multiple regions will be chosen, I've added in an IF statement
=IF(COUNTA(A3:A10)>1,"Multiple Regions",A3)
(I'm sure there are ways to concatenate the strings but for mine it could get up to 20 and that just gets ridiculous for the graph heading)
Just Wow
I am trying to create a duplicate dashboard using data in one workbook and creating a new workbook to place in a shared file for my coworkers. I have created a separate worksheet in the original workbook for the new pivot charts and slicers I want to use in the new workbook/dashboard. I don't want all of the source data in the new workbook, as it is very large. I am having trouble making new slicers work. They work in the original workbook, but when I copy them to the new workbook they don't work. Am I going about this the right way or is there an easier way?
Very good post! Helped a lot. Keep up the good work!
how can you prevent multiple selection in a slicer box? In short, in any slicer box, only one entry is allowed and not multiple entries.
Fairly new to forum's, hoping I'm not breaking a rule here, but I found this forum which seems to provide a solution:
https://wessexbi.wordpress.com/2014/03/17/just-one-slice-please/
I have 2 files. (1. .xlsx 2. .xlsm)
1 file contains all the pivot tables and charts. its also macro enabled.
2nd file contains the source data which is a .xlsx file.
but I am unable to run slicer on my 1st file.
can anybody help me out?
chandoo.org: one of my favourite Excel sites for years.
Slicers tutorial: excellent as usual.
Animated gifs: sorry, but REALLY distracting!! Especially with two on the same screen. Is there any way they can be activated only when we click on them, or something?
Hi Team,
I have inserted a slicer to a pivot table with 4 fields...I need to add another field for the same slicer...help me with this..
First of all I would like to say terrific blog!
I had a quick questio in whiich I'd like to ask if you don't
mind. I was intereested to know how you center yourself and clear your head
before writing. I've had a hard time clearing my mind in getting my ideas out there.
I do enjoy writing however it just seems like the first 10 to 15 minutes are generally lost simply just tryying to figure out how
to begin. Any recommendations oor tips? Many thanks!
Hi All
Im trying to connect a slicer to 2 pivot tables with different sources
Both data tables have been sorted and have duplicates
ie
Table 1
Name Week FTe
A 1 7.2
A 2 7.3
B 1 7.3
B 2 7.3
Table 2
Name Month Fte
A Jan 2.6
A Feb 3.2
A Mar 4.4
B Jan 2.2
B Feb 6.4
B Mar 2.2
etc
I have created 2 pivot tables and have sorted it out the way i want with charts etc
Now all i want is to connect the Name Slicer to be connected to both of those pivot tables but problem is they have duplicates and are from different tables/sources
how can i connect/add this to a data model and connect to my name slicer?
Im sure it maybe something simple but minds not with it
So in short 1 to connect 1 slicer to 2 different pivots from different sources but not all pivots (There are dups in both) - as shown in the example
Thank You
Hi H
This is how you can do it. Create a third table with all slicer options (in this case it would be Name column) with one row per unique value. Now add this table to your source list. Then link all two tables via this third table thru Data ribbon > Manage relationships feature. Finally add a slicer on this third table column and link the slicer to both pivot charts.
Please note that you need to construct the tables and charts after data model is created.
See this page for more explanation on how to use relationships - https://chandoo.org/wp/introduction-to-excel-2013-data-model-relationships/
Hi,
Using Cube Value with Slicers is great. I am new to cube value, but it is so powerful. I am stuck on an issue where I want to filter on a slicer for all values except 1 and the slicer has thousands of values. I get #N/A in the results, when trying to do this. Any ideas on how to do an exception calc or how to get around this with the multi select slicer functionality?
Thanks in advance.
Cyleste
@Cyleste... thanks for your comments and welcome to Chandoo.org. You can use DAX to calculate such things as Excel pivot tables alone cannot function like the way you want. You can use DAX formula EXCEPT() to achieve this. For example,
=CALCULATE(SUM(data[sales]), EXCEPT(ALL(data[filter_column]), VALUES(data[filter_column]))) can tell you the sum of [sales] column in the data table by ignoring slicer selected values.
Hope that helps.
Hi Chandoo,
Thank you for your quick reply. I am not familiar with DAX but it sounds like I won't be able to apply the calculation you provided after converting the power pivot to excel formulas via OLAP.
Cyleste
Thanks Chandoo, I like yours tricks & always I use slicers. Regards from México.
Hi Chandoo,
I have a lot of text in the slices (Pivot table). The text is not completely visible. What should I do?
Please Help
Thanks
Hi Girish,
Slicers are useful only for items with short text, for ex: categories, product names etc. For longer values, you are better off using form controls for interaction - Here is an overview of form controls Form Controls – Adding Interactivity to Your Worksheets
Thanks so much for this, it's brilliant! I think it's almost there - I've actually followed the steps on the example linked in my post. I just can't get it to filter properly; it just returns 0 when I add a date into Cell O2. Should I be doing it differently?
slicers dont work with non-admin roles in OLAP Pivot Tables