Tutorial Playlist
68 Lessons1. Excel Tutorial
2. Excel Worksheet for Practice
3. Google Sheets Vs. Excel
4. Excel Shortcut Keys
5. Excel Keyboard Shortcuts
6. Excel AutoSum Shortcut
7. Redo Shortcut in Excel
8. Charts in Excel
9. Pivot Charts in Excel
10. Excel Sum Formula
11. Excel Percentage Formula
12. Excel Age Calculation Formula
13. Excel Range Formula
14. Round Off Formula in Excel
15. VLOOKUP Formula in Excel
16. Excel Transpose Formula
17. Average Equation in Excel
18. How to Use DATEDIF Formula in Excel
19. IRR Formula in Excel
20. Standard Deviation Formula
21. Excel Age Calculation Formula
22. Excel MAX Function
23. Excel LEFT Function
24. Excel RIGHT Function
25. Trim Function In Excel
26. LookUp function in Excel
27. Columns in Excel
28. How To Sort Data In Excel
29. How To Freeze Panes in Excel
30. Page Setup in Excel
31. How to Recover an Unsaved Excel File
32. Concatenate in Excel
33. Count In Excel
34. IF Condition In Excel
35. H LOOK UP in Excel
36. How Do You Move Columns in Excel
37. Split Cells in Excel
38. Remove Blank Rows in Excel
39. How To Lock Cells in Excel
40. Data Validation in Excel
41. How to Insert Checkbox in Excel
42. How To Highlight Duplicates in Excel
43. Fill Series in Excel
44. How to Create Excel Drop-Down List
45. What-If Analysis in Excel
46. How to Use SUMIFS Function in Excel
47. INDIRECT Function in Excel
48. Pivot Table in Excel
49. Slicers in Excel
Now Reading
50. How to Create a Dashboard in Excel
51. Excel Data Cleaning
52. Data Analysis In Excel
53. Goal Seek in Excel
54. Solver in Excel
55. Power Query in Excel
56. Macros In Excel
57. How To Make Graph in Excel
58. How To Make Histogram In Excel
59. How To Convert PDF to Excel Without Software
60. Barcode Font For Excel
61. Gantt Chart in Excel
62. Excel RANK function
63. AND Function in Excel
64. How to Calculate NPV
65. Format Painter in Excel
66. Count Colored Cells in Excel
67. Amortization Schedule in Excel
68. Master Cell References in Excel
In analyzing data, we all know that there is no other like Microsoft Excel. But do you sometimes feel overwhelmed by the endless rows and columns of data in Excel like me? Let me tell you, a new trick and a few clicks can save your day.
Did you know there is a special filter called Slicers in Excel that can operate your data sheet without you hunting down the dropdown lists? Specifically designed for Excel tables and pivot tables, Slicers in Excel saves my time and mental strain everyday, and make my daily Excel tasks much more efficient.
Slicers were first introduced in Microsoft Excel with the release of Excel 2010. From this onwards, Microsoft Excel updated itself in data analysis. If you need to know more about Excel from scratch, head straight to the Excel beginner’s guide to learn the basics. Fear not, we all start from somewhere!
As I take charge in introducing you to slicers in Excel, let me start by explaining how you can add slicers in Excel.
While working with a large set of data, it is always best if you use Slicers to make your job easy. They are interactive visual filters specifically designed for Excel tables and pivot tables. The best part is, they are super user-friendly and time saving.
You can use slicers for both tables and pivot tables. Below, I have made an Excel slicer tutorial that will break down the steps to add slicers in your excel sheet for you:
So, let me use a sample data table to show you how to add slicers in Excel. Here, you have a list of sales of different products across various regions, listed in three columns. Here's an example table:
Source: MS excel
1. Adding Slicers to Your Worksheet
Select a Cell: Click on any cell within your data table. For example, click on the cell "B2" (Region: North).
Source: MS Excel
Insert the Slicer: In Excel insert Slicer by going to the Insert tab and locate the Filters section. Click the Slicer button.
Fields for Slicer: Here comes a window called ‘Insert Slicer’ will appear. This consists of all the columns of your table in checkboxes. Since we are focusing on filtering by region, select the checkbox next to the "Region" field. You can leave other checkboxes deselected for now.
Click OK: You now created your slicer and position it on your worksheet by default. You'll see a list of all the regions ("North," "South," and "East") displayed in the slicer.
2. Filter by Region through Slicers:
Click on a Region: Click on a specific region in the slicer, for example, "North." This will filter your data table to only show sales figures for products sold in the North region.
Multiple Selections (Ctrl+Click): Long press the Ctrl key while clicking on multiple regions (e.g., "North" and "South"). This will filter the table to show data for products sold in both North and South regions.
Let us take the reference from the last example, but here you need to make a pivot table of your previous data.
If you don't have one, create one:
Source: MS Excel
Your Pivot Table is ready to use. Learn more about the pivot tables and slicers to improve your excel knowledge. Next, in excel insert slicer by following this step by step guide:
1. Adding a Slicer:
Click in PivotTable: Click anywhere within your pivot table to ensure the slicer is linked to the correct data set.
Insert Slicer: Next, in Excel insert slicer by going to the ‘Analyze’ tab. In the Filter group, click on ‘Insert Slicer. Make sure your Pivot Table is selected, only then the Analyze tab will be visible.
Choose Fields for Slicer: The moment you click on the ‘Insert Slicer’ tab, a window named "Insert Slicers" will appear. This window consists of all the fields used in your pivot table. You can select the field(s) you want to use as slicer filters. Let's select the "Region" field here.
Click OK: This way, you are now successful to add slicers in Excel and place it on your worksheet. You can also drag and reposition it later.
2. Filter PivotTable Data:
Click on a Region: Your pivot table will automatically update to reflect the chosen region. For example, clicking on "North" in the slicer will show sales figures only for products sold in the North region.
Multiple Selections (Ctrl+Click): Hold the Ctrl key while clicking on multiple regions (e.g., "North" and "South"). This will filter the pivot table to show sales data for products sold in both selected regions. The pivot table will adjust accordingly.
Slicers do more beyond basic filtering in Excel. Unlike filters, it offers a variety of customization options to give you the best data exploration experience in Excel.
Slicers are not just functional; they can be visually appealing too. You can modify their size, color, border style, and font.
For example: You can make the slicer for the "North" cells under Region category blue. It indicates all the products that are sold in the northern region and might match with your regional sales map. It also helps to distinguish Northern regions from every other area in your Excel data.
Gone are the days of static filter menus. Slicers in Excel offer great layout flexibility. You can arrange them horizontally, vertically, or even stack them on top of each other. This allows you to create a well-organized dashboard and suits your best needs.
For instance, if you have a limited amount of horizontal space, you can stack slicers for "Region," "Product Category," and "Price Range" to save space and maintain a clean layout.
With slicers in Excel, you can choose individual items, and also can connect multiple slicers with slicer connections. It is particularly useful to analyze when you have large datasets.
For example, you have slicers for "Region" and "Sales Representative" in a sales report. By connecting these slicers, when you select a specific region, the "Sales Representative" slicer will automatically update to only show representatives working in that chosen region. This allows you to progressively filter your data.
Other than Regular Slicers, there is another type of slicer called Timeline Slicers. If you're working with data that includes dates or times, you can convert your slicer into a timeline slicer. This allows you to filter by specific date ranges, making it ideal for analyzing trends over time.
For example, you can use a timeline slicer to see how sales figures perform over the whole year.
If you hold the Ctrl key while clicking on slicer items, you can select non-consecutive options. This provides more individual control over your data view.
For example, you have a slicer for "Product Names" where you want to see sales data for "Shirt," and "Dress”. By using non-contiguous selection, you can quickly filter the data to include these specific categories.
In Excel, pivot tables and slicers allow you to control the data in your pivot table. This makes it easy to analyze different aspects of your data from a single pivot table.
For example, you have a pivot table containing the sales figures by region and product category. You can add slicers for both "Region" and "Product Category" to filter the pivot table and focus on specific areas of your interest, such as sales performance for Shirts in the North region.
In short, slicers in Excel have revolutionized data analyzing. Just like mine, these interactive visual filters can save your time too, unlike the traditional filter menus. They will effortlessly filter your data based on your specific interest, so you ace your data presentation every single time!
You can do a lot more than you can imagine with pivot tables and slicers in Excel. So, are you ready to be on top of your Excel game? Register yourself today to UpGrad’s free online Excel courses and make yourself pro at the field of data analysis today!
Both slicers and filters allow you to refine your data view in Excel. The difference is:
Slicers in Excel can be used with both Excel tables and pivot tables. They offer a more dynamic filtering experience compared to traditional filters in either format.
You can easily drag and drop slicers anywhere on your worksheet to arrange them for optimal viewing. You can also group related slicers together for better organization.
The slicer caption is the text displayed at the top of a slicer. It indicates the field it has filtered. You can customize the caption by:
The slicer vs filter excel is an old debate. Slicers are easier to use and provide a clearer view of filtering options. However, filters might be more suitable for simple filtering tasks with a limited number of options.
Slicers in Excel might feel faster to you sometimes due to their visual and interactive design, allowing you to quickly filter data with a few clicks.
You can add slicer in Excel for different data types, such as:
Here are some key reasons to add slicers in Excel:
The benefits of using slicers in Excel are:
Devesh
Passionate about Transforming Data into Actionable Insights through Analytics, with over 3+ years of experience working in Data Analytics, Data V…Read More
Talk to our experts. We’re available 24/7.
Indian Nationals
1800 210 2020
Foreign Nationals
+918045604032
upGrad does not grant credit; credits are granted, accepted or transferred at the sole discretion of the relevant educational institution offering the diploma or degree. We advise you to enquire further regarding the suitability of this program for your academic, professional requirements and job prospects before enrolling. upGrad does not make any representations regarding the recognition or equivalence of the credits or credentials awarded, unless otherwise expressly stated. Success depends on individual qualifications, experience, and efforts in seeking employment.
upGrad does not grant credit; credits are granted, accepted or transferred at the sole discretion of the relevant educational institution offering the diploma or degree. We advise you to enquire further regarding the suitability of this program for your academic, professional requirements and job prospects before enr...