Office 2010: Slice Excel Data Your WayApril 5, 2010 at 9:54 am | Posted in Microsoft, Technical Tips | Leave a comment
Tags: Excel, MCAS, MOS, Office 2010
Now that you’ve learned how to quickly summarize your Excel data, we’ll conclude the new features in Excel 2010 with the slicer.
The slicer is a filter you can apply to PivotTables and PivotCharts. Slicers can add or remove elements from a table display and be reused across multiple tables. Enough theory; enter the real-world.
Let’s say that you work for a big pharmaceutical company and you’ve got the following data (excerpted only).
So, to allow sales managers to parse the data easily, you create a PivotTable as follows:
But maybe a sales manager wants to filter the data dynamically by drug name or category value. What can you do?
This is where the slicer comes into play. Let’s create two slicers: one for the drug name and the other for the category. A sales manager can then filter the data to display only non-drug sales as follows.
If you select option(s) from the Category slicer, then only drugs from those categories are available in the Pharmaceutical slicer to filter. Only those options selected in these slicers will be displayed in the PivotTable. These slicers can then be applied to other PivotTables or PivotCharts.
The slicer highlights non-filtered fields and displays a lighter highlight for fields that do not match the filter criteria. You can even customize the color scheme and general appearance of these slicers to your heart’s content.
So, don’t be intimidated by manager requests for data filtering. Give them some slicers and let them do their own filtering with Excel 2010’s new interface.