Vba working with slicers in Excel

Slicers are visual filters using which data can be filtered with a simple click.

Slicers can be used to filter data on Pivot table,Pivot chart or table.

Apart from filtering, slicers also display the current state of the filter, which is useful in understanding the data displayed.

The Slicers are available from Excel 2010 and above.

Create a Slicer

Let us take the following pivot table for illustration.

excel vba working with slicers

The following steps demonstrates the creation of a slicer

1.Click inside the pivot table.

2.Goto Analyze Tab.

excel vba working with slicers

Note that, the Analyze tab will be visible only if the pivot table is selected.

3. Select "Insert Slicer".

excel vba working with slicers

The following dialog box appears showing all the columns, which can be filtered of our choice.

excel vba working with slicers

In this example, let us choose Men,Population and Age<30 columns and apply filter.

Once done, the following slicers will be displayed.

excel vba working with slicers

Now, clicking on any button within each slicer, will filter the data of the pivot table accordingly.

excel vba working with slicers

selecting these rows gives you this

excel vba working with slicers

 

You can find similar Excel Questions and Answer hereunder

1) I have to make an investment decision - I have the cashflow numbers. In Excel, how do I calculate Internal Rate of Return (IRR)?

2) I frequently use a Macro - is there a way to quickly access the Macro in the Excel Ribbon?

3) How can I get input from user through a prompt and assign user's input to a cell?

4) How can I generate random numbers between 2 limits?

5) How can I ensure that user enters only certain acceptable values in an input cell?

6) Here an explanation about frames and how to control frames in Excel VBA

7) I have several pictures that are misaligned - manually aligning them is very laborious. Is there an automatic alignment option?

8) How can I display list of NAMES in a worksheet?

9) What is a recordset in Excel and how to use it. Here are some basic explanations

10) How can I hide a sheet completely from users (the sheet should not even appear in Unhide dialog box)?

 

Here the previous and next chapter