Microsoft Excel is one of the most powerful data analysis tools today. Data analysis and visualization don’t always require complex formulas or functions. Excel Slicer is just one example of how your Excel dashboards can be more dynamic. And this dynamism can be achieved without having to learn complex functions or giving into complex tools.
Understand What an Excel Slicer Is and What It Does
Before we dive deeper into how to apply slicers on a table, let’s understand what a Slicer is. As the name suggests, a Slicer allows users the option to select certain fields in a large data set and slices the data to present only the fields desired by the user for data analysis.
One may wonder at this point if this functionality is somewhat like an Excel filter. Slicer can be considered as a more sophisticated version of a filter which also encapsulates better visual representation. In addition, it offers easy multi-level filtering based on data presentation requirements. You will understand more below when you see real-time data analysis and filtering using an Excel Slicer.
For example, let’s say we have a large Excel file with Sales data, but we are interested only in the performance of a certain product worldwide. The slicer can help slice out data only for that product and with the additional slicing options, can offer better visualization of how that product is doing across countries.
The best part is that you don’t need formulas or need to be proficient with complex functionality. This will all just happen with the click of buttons, making data analysis a breeze even for users who are not Excel-savvy.
How to Insert Slicers in a Table (Not Pivot Table)
When you click on a table, a new tab ‘Table Design’ will appear as shown in the screenshot below.
Under Table Design, you will find the option to ‘Insert Slicer’
Now, as in the example above, we want sales data for a certain product worldwide. So, we choose ‘Product’ and ‘Country’ as slicers on our table and click OK.
Once you do that, this is how the slicers will appear. You can click on any product name and any country name to see data for that product in that country. Yes, it’s really that simple. You will see your table moving around and slicing data based on the options you choose.
Removing an Excel Slicer
At any point, if you want to remove any slicing options, let’s say “Canada” from the Country list, just click on the red cross symbol on the right of the slicer window. The data will stop slicing based on a particular country and you will see all countries in the list highlighted as blue.
If you want to remove the slicing option altogether, just right click on the slicer and choose “Remove Country” and this slicing window will disappear.
How to Insert Slicers in a Pivot Table
When you click on any cell of a Pivot Table, a new tab ‘PivotTable Analyze’ will appear as shown in the screenshot below.
For this example, the Pivot Table contains Product Name, Sum of Gross Sales and Sum of Profit. In the similar manner as above, we find the “Insert Slicer’ option under ‘Pivot Table Analyze’ button tab and click on it.
From the options, let’s choose Country. After adding Country as a slicer, we can simply click on the country name. This will show the Gross Sales and Profits made on each product in that country. It’s really that simple.
You can also select Product as a slicer if you want the same functionality as above example. Using that, you can see the sales and profit for a particular product in a particular country.
After analyzing the data, the Excel Slicer can be removed from your Pivot Table data the same way as described above.
Conclusion
Excel Slicer adds a whole new level of convenience when it comes to professional yet easy representation of data. Creating reports can be so much easier when you just need to click a few buttons to move things around.
Wish to have Excel skills like a pro? Check out 10 Lesser Known Excel Features for Large Data Sets.
