Using Slicers with Non Pivot Data

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 sample workbook to practice this exercise

Applies to: Excel 2007, 2010 and 2013

We start by inserting a Pivot Table using the cost centers.

tip-1

 

 

 

1. Select any cell within the Cost Centre table.
2. Select the Insert tab then Pivot Table.

tip-2

 

 

3. Add the Pivot Table to the existing worksheet in cell C16 and select OK

tip-3

 

 

 

 

 

 

 

4. Place the Cost Center to the rows area.
5. Drag the Key field to the values area.
6. Select the Pivot Table.
7. From Pivot Table Tools, select Options.
8. Select Insert Slicer.

tip-4

 

 

 

9. Select Cost Center.
10. Select OK.

tip-5

 

 

 

 

 

 

11. Right click your Slicer and select Slicer Settings.
12. Uncheck the Display header box.

tip-6

 

 

 

 

 

 

13. Select OK.
14. Select Sales on the Slicer.
15. 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).

16. Copy the formula down to G17 (the values for Sales or the selected cost center will be copied down).
17. Select the data range  A1:A13 and G1:G13 (Hold down the control key to make your selection).
18. Select the Insert tab.
19. Select the Column chart under the Charts group.

tip-7

 

 

 

 

tip-8

 

 

 

 

 

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.