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.
62 Responses to “Introduction to Financial Modeling using Excel [Part 1 of 6]”
Oooh! Chandoo - are you looking to outperform the Altman Z-Score?
I'm all ears 😉
Many thanks, Paramdeep. Looking forward to the remaining parts of the series
Thank you Paramdeep. I look forward to following the series.
Those interested in Financial Modelling may also find this site interesting:
http://www.fimodo.com/
Hi Chandoo,
Really looking forward for this awesome financial modeling journey 🙂
All the very best..
Regards
Rohit1409
Hi Chandoo,
it seems the future is not away when we will have a new software/spreadsheet as competitor to Microsoft Office/Linux etc.......Hats Off to you..
KEEP SMILING
GURU
@apex, clarity: Thanks...
@all: I do hope that you have started creating the model for the simple case given.. Lets see, what according to you all is the best way forward. So your views on the best practices for the template for valuation?
The planning process varies depending on the project and type of data I am looking at.
If I am doing a cost impact study based on actuals, verses a cost impact study to estimate future cost, verses reviewing historical data then creating a model, verse having to review historical data, apply it to a draft model, update the model based on the draft outputs and the presentation then sometimes repeat the process a few times.
Revisions are an absolutely necessary part of corporate financial modeling especially for the presentation sections. A good presentation will be revised multiple time. There are just to many elements which cannot be planned for, especially with ad hoc reports/models.
Revisions are leading cause of errors, and time spent tracking down errors. The more complex the model the more important revision control is. I would be interested in a section on revision control and good methodologies to determine the effect of changes on models. That is without having to due detail review of the whole model over again.
Hi Paramdeep,
I can't wait for the continuation of this series. You have done a great job in making technical terms appear simple and easy to understand. This article is also very useful in our line of business, in fact i will be featuring this article in our site. Of course the credits will go out to you.
Many thanks and keep it up!
Drew
[...] Introduction to Financial Modeling [...]
[...] Introduction to Financial Modeling [...]
[...] Introduction to Financial Modeling [...]
[...] Introduction to Financial Modeling [...]
@Drew: Thanks! Hope that you found the tutorials useful. Feel free to get back with any queries.
[...] you are building financial models or any other type of excel based decision models, chances are, there will be multiple scenarios in [...]
Great information, i also want to learn financial modeling, how to learn
[...] Here is a free financial modeling course to help you get started. Spread some love,It makes you awesome! Tweet [...]
[...] Introduction to Financial Modeling [...]
[...] (if you don’t want to join Financial Modeling School, we are still friends Here is a free tutorial on excel financial modeling.) [...]
chando bhai i m tired for looking financial modeling detail but i cant find it anyway. so plz kindly send me full detail of financial modeling from beginning levelo to final. where i learn complete modeling procedure n order to become professional
You noob, you won`t become a professional like that, you need to do it on your own and learn as you go
@Nadeem Bhai,
The 6 links to the tutorial at the top of this post, build a small integrated financial model step by step. We are also running a complete 8 weeks course on financial modeling (similar to excel school). But that is almost 50% complete right now. If you want to join, you can join the next batch (probably starting in Jan/ Feb)
Feel free to get back, if you have any queries regarding the model/ steps in creating the same
[...] you do not wish to join our course, we are still cool. Here is a 6 part training on financial modeling & 2 part training on project finance. They are free, fresh and [...]
I want to learn how to build a financial model
Dear Lulama,
You can use this tutorial to learn to build a financial model. We also run a course on financial modeling and project finance modeling. The details can be obtained from:
http://chandoo.org/wp/financial-modeling/
You can consider joining that as well.
I would strongly encourage you to join the newsletter to get the updates about financial modeling and the next batch starting for the course.
I want to learn thorough excel and financial modelling thing !!! I am a chartered accountant..CAn you please suggest a detail syllabus that should be learn by me. Also suggest any classes that would cover everything in excel and financial modelling related things.. I stay in mumbai, so please suggest a class in mumbai or any books..byyee.. thanks ..
[...] Financial Modeling using Excel – 6 part tutorial [...]
Hi Vivek,
We do conduct the financial modeling course online as well as in Mumbai. We start with the basics of excel and build a completely integrated financial model in the class. For details you can look at:
http://chandoo.org/wp/financial-modeling/
or
http://www.edupristine.com/courses/financial-modeling/prisitne-fm-offering
Please describe to me various type of financial modela and demonstrate how a model can be build.
Regards,
Dear Immanuel,
This series of articles does exactly that. If you go step by step through the articles and create the excel sheets on your own, you will understand how to create an integrated financial model.
Alternatively, if you are looking for video sessions, you can consider joining our course:
http://chandoo.org/wp/financial-modeling/
It is expected to reopen somewhere in mid july.
[...] Excel Financial Modeling – 6 part tutorial [...]
Thanks Paramdeep this is great!
[...] Few months ago, we learned how to create a project valuation model in Excel as part of our series on Financial Modeling in Excel. [...]
Paramdeep Hi! A bit ignorant here, but let's ask anyway. Can I access "recorded" video sessions apart from following the step by step articles?
Thanks!
@Marius: Yes, we do have a training program with recorded video sessions. If you are interested, you can enroll for the same. The details can be obtained from: http://chandoo.org/wp/financial-modeling/
i want to know about the scope of financial modeling.i also want to know after doing financial modeling course what are the job prospect, like where i can found job so plz send me full detail of financial modeling from beginning levelo to final.
I am interested to join your Financial modelling class cos am involved in a new business. But I have a Macbook Air with Excel for Mac installed. Would that work with your program?
Dear Charsiew,
The complete training program is built using Excel 2007 with MS windows. Though there is nothing specific to Microsoft in the training. I think a few people have joined the program and are using MAC. If you write an email to me on paramdeep @ gmail.com, I can send you the email ids of those participants and you can get to know their feedback and difficulties in using the program.
This is great, when does the next batch start?
Dear Karan,
The course is in recorded format and you can register for the same as soon as you want.
Paramdeep, thanks. I'm venturing into financial modelling for the first time and from the little I've read from your website already, I'm sure I'll find your resources very useful; thanks once again.
Dear Seun,
I am glad that you are finding the resources to be useful. Feel free to send in comments with your feedback and further questions.
hie ppl...I jst wantd a small favour cn smone help me out in calculating EBITDA, along with its working, from wipros annual report 2011-2012, link - http://www.wipro.com/investors/annual-reports.aspx
...PLZ plz... i am having a submission tommorow.
[...] [Related: Introduction to Financial Modeling - 6 part tutorial] [...]
Nice article.
In designing Excel Financial Models I design the user interface as friendly as possible and structuring the input variables in the workflow of the client's operations so that they can flow through the impact of the input variables in Excel using buttons and macros and giving the model transparency.
Cheers
Arthur
[…] 4) http://chandoo.org/wp/2010/07/21/financial-modeling-introduction/ Chandoo has a great 6 step guide which goes through financial modeling from start to finish. Includes loads of pdfs to download including cheat sheets and case studies. Definitely a good place to start learning how to get to grips with financial modeling and Chandoo is well known for its Excel tutorials so if anything doesn’t make sense about the terms used to talk about formatting etc. then the answer can surely be found elsewhere on the website (and often already has hyperlinks added to skip straight to those sections as they are talked about within the financial modeling article!) […]
[…] Financial modeling using Excel (6 part tutorial) […]
Excellent site. Lots of helpful info here.
I am sending it to a few buddies ans also sharing in delicious.
And certainly, thanks in your effort!
[…] Introduction to Excel Financial Modeling […]
Another step to build any financial model is to do a financial analysis beforehand. This normally helps to design and come up with good projections.
Wow great information even I would like to learn financial modeling thanks for sharing this.
Looking For Full details On financial modelling. thank you for sharing this part!
Superb site. Bunches of supportive information here.
I am sending it to a couple of mates and additionally partaking in scrumptious.
Undoubtedly, thanks in your exertion!
Thanks for sharing very useful information
I really appreciate the effort, specially the case study part. This is one piece of information that deserves appreciation.
Nice collection of article, so inspiring and easy to read, keep posting thank you for sharing
Have a Nice Day!
I have to agree with all your inclusions.such a piece of great information that will be very useful for students
I appreciate your effort of writing a value able piece of content.
I really appreciate the effort
This is really awesome, thanks for sharing.
Thanks for the share!
A debt of gratitude is in order for your article! You focused on a point we have just thought to be a couple of times. Our outcome in the past was a "light" adaptation from what you have proposed.
I have to agree with all your inclusions.such a piece of great information that will be very useful for students