 |
| Excel Slicers |
Excel Slicers are a fun, friendly and powerful way to filter the data displayed for Pivot Tables, Pivot Charts, Power Pivot Tables and (for Excel 2013) normal Excel Tables.
These visual filters are an alternative to the field drop down lists and one Slicer can be connected to several different Pivot Tables or Charts to instantly apply a common filter.
These visual filters are an alternative to the field drop down lists and one Slicer can be connected to several different Pivot Tables or Charts to instantly apply a common filter.
Inserting Slicers
To insert a Slicer select a cell in the Table or PivotTable that you want the Slicer to control and then click the Insert tab of the ribbon, the Slicer control is in the Filters group. In Excel 2013 you can also right-click on the Field in the PivotTable Field List and choose Add as Slicer from the shortcut menu.
 |
| Inserting a Slicer |
Connecting Slicers to Multiple Pivot Tables and Charts
Once you’ve inserted your Slicer you can then choose which Pivot Tables or Charts you want it to control.
Right-click the Slicer and choose Report Connections from the short cut menu. This opens the dialog box where you can tick the boxes for the Pivot Tables you want your Slicer to control.
 |
| Multiple Connections |
A Slicer can only control Pivot Tables which share the same Pivot Cache. Typically Pivot Tables which reference the same data source share a Pivot Cache, but this is not always the case.
If the Pivot Tables that you want to connect to don’t appear in the list then it is because they have separate Pivot Caches.
If the Pivot Tables that you want to connect to don’t appear in the list then it is because they have separate Pivot Caches.
Filtering with Slicers
Slicers are easy to use and allow us to filter one or more items.
- Click one item to apply the filter for that area.
- Click and drag through items to select more than one or click the first item, hold down the SHIFT key and click on the last item in your range.
- Hold down the CTRL while clicking to select multiple items.
The Slicer displays the selected items in a different colour to give a visual indication of the applied filter. To remove all filters, click the red X at the top right corner of the Slicer.
 |
| Multiple filters |
Slicer Settings
Click the outer edge of the Slicer to display the Slicer Tools, Options tab on the ribbon. Here we can access all the Slicer Settings.
 |
| Slicer Settings |
- Change the Slicer name
- Turn the Slicer header on or off, or change the caption.
- Sorting settings.
- Choose how the Slicer should handle items with no data.
Slicer Styles
In the Slicer Styles group we can choose the colour and style, the colour options will be based on the Theme that you have selected for the workbook in the Page Layout tab of the ribbon.
 |
| Slicer Styles |
Buttons and Size
Use the Buttons and Size groups to set the number of columns, button height and width and the overall size of the slicer. You can also size the Slicer by grabbing and dragging the selection handles on its outer edge.
 |
| Sizing Slicers |
If the overall height of your Slicer is too small to show all of the filter values then a scroll bar is displayed.
 |
| Slicer scroll bar |
Formatting Slicers
 |
| Slicer Styles |
This opens the dialog where you can format each of the 10 Slicer elements exactly as you want. You can click the check box to Set as default slicer style for this document to use the style over and over again.
Copy and Modify Slicers
 |
| Duplicate the Slicer style |
To copy a style right-click the style in the gallery and click Duplicate. This opens the Modify Slicer Style dialog.
 |
| Multiple Slicers |
After duplicating your Slicer style you can apply it to your Slicers. If you have multiple Slicers you can select them by holding down the CTRL key as you click each one. Then click on your new style in the Slicer Style gallery.
No comments:
Post a Comment