. Select any cell in the pivot table. On the PivotTable Tools Analyze tab ( PivotTable Tools Options tab in Excel 2007/2010), in the Filter group ( Sort & Filter group in Excel 2007/2010), click the Insert Slicer command (and then select Insert Slicer in Excel 2007/2010).
Click any cell inside the pivot table. On the Analyze tab, in the Filter group, click Insert Slicer. Check Category and click OK. For example, click Fruit to only show the fruit exported to each country.
In the Insert Slicers dialog box, check the field or fields (Yes, you can create multiple slicers at once!) to use to 'slice' your data, then click OK. To use the Slicer, simply select one of the fields in the Slicer to filter your data by that field. Related Video.
Sometimes you may want to present your data simply as a range, but would like to make use of Slicers (available in Microsoft® Excel® 2010 and 2013) to be able to quickly filter data. Commonly, slicers are applied only to data that is presented in Tables, Pivot Tables and Pivot Charts – not non Pivot data, but there is a way around that, which is what we will show you in this tip. Note: Download the to practice this exercise Applies to: Excel 2007, 2010 and 2013 We start by inserting a Pivot Table using the cost centers. Select any cell within the Cost Centre table. Select the Insert tab then Pivot Table. Add the Pivot Table to the existing worksheet in cell C16 and select OK 4.
Place the Cost Center to the rows area. Drag the Key field to the values area. Select the Pivot Table. From Pivot Table Tools, select Options.
Select Insert Slicer. Select Cost Center. Right click your Slicer and select Slicer Settings. Uncheck the Display header box. Select Sales on the Slicer.
Select cell G1 and enter the following formula: =INDEX(B1:F13,$D$17) Note: The reference for D17 should be typed in not selected. Press the F4 key to make this cell absolute.
The INDEX function returns the value in a table at the intersection of a given row and column number. B1:F13 is the data range. There is no row number hence returning, (two commas).
The column number is returned by the value in $D$17(1). Copy the formula down to G17 (the values for Sales or the selected cost center will be copied down). Select the data range A1:A13 and G1:G13 (Hold down the control key to make your selection).
Select the Insert tab. Select the Column chart under the Charts group. The chart data and the values in G1:G13 will change based on the selected Cost Center from the slicers list as can be seen the data in the range B1:F13 can be filtered with a slicer without inserting a Pivot Table.