Slicers in Pivot Table - Quickly Filter Data

 If you are using pivot table reports for management or even for your own analysis and, you are not using Slicer then, you are not fully utilizing the pivot table report.

In this article I will discuss about slicers in pivot table. How to insert slicer, how to customize slicer, where and when you need to use slicer.

We can use slicer both in Pivot tables and Tables.

But what is slicer?

Slicer is a tool which provides buttons, that you can click to filter tables, or PivotTables. Slicers are a powerful new way to filter pivot table data.

It's very easy to add Slicer in your pivot table. Let understand this tool in this pivot table.

Slicer in Pivot Table or Table

To insert slicer, go to the Analyze menu in the ribbon, then, click the button Insert Slicer. A new small Insert Slicer window appears.

Slicer Menu

If you notice carefully then, you will find that all the table headers are showing in this window with check boxes in beginning.

Let suppose, we choose Segment. 

A new small floating window appears, and all the segments are showing in this window, like, Channel Partners, Enterprise, Government etc. These are all buttons. 

Now, if you click on any button, say for example, Channel Partners then all the relevant data regarding Channel Partners will be shown and the rest data will be hidden.

Slicer in Pivot Table Selecting One Button

Now, if you need enterprise, click Enterprise button the data will show the related data. I hope you have understood the working of slicer in pivot table.

Let us check this drop-down list, it is showing something is selected from the entire list. Click on this you will note that Government segment is selected and rest are deselected, therefore, slicers acts like filters and it is very easy to handle.

Slicer as Filter

If you need to display all the segments then click on this clear filter button with delete sign, top right corner. All the buttons will be available for selected again. 

Clear Slicer Selection

There is a small button in the left side of the filter button, this is Multi-select button. If you want to use multiple buttons from slicer, then use this button to activate multi select. Let us do it.

Click any one button first then click this multi select button to activate, and then click on another button, both these buttons will be selected.

Please remember, firstly you need to select one button from all the slicer buttons then click on the Multi-select button, otherwise this will deselect the buttons you click.

Alternatively, you can use my method, I select any one of the buttons then I hold press the control button and then select the next buttons, like this. You can use the method which is comfortable to you.

You can use multiple slicers, more than one slicer. 

Select any cell in pivot table, then go to Analyze menu and again click on the Insert Slicer button. Insert slicer window will appear again. Select as per your need. Here I will select Name.

For example, if you want to show the performance of Johnson, click Johnson from the Name slicer, all the relevant data related to Johnson will be displayed, from this if you want to show only Government segment data, you only need to click on the Government button from the Segment slicer.

The pivot table will show the data of Johnson only for Government business.

Let me discuss few more things which are very useful in slicer. I have few excel sheets in the same workbook; these are copied from the same pivot table.

Now in this pivot table, we have already selected Johnson and Government but these sheets are not reflecting the changes made by slicers in Sheet 1.

If you want to affect the changes in Sheet 2 or Sheet 3 or Sheet 4, you have to connect this slicer to other pivot tables. This is very easy process.

You have to instruct slicer that these sheets are to be linked by a connection.

Click on any slicer and you will find a new menu as Option at the last of the menu items. Click on this button, Report Connection.

Slicer Report Connection

A new window, Report Connections and within bracket your slicer name. Here you will see the list of your sheets along with the pivot table name, in our case pivot table name is PivotTable1. Tick the checkbox of Sheet 2. Click OK. Now your slicer is connected with Sheet 2. Whatever selection you made in Sheet 1 slicer it will display the same selection in Sheet 2.

Remember, it is not mandatory that all the pivot table should be of same structure to use slicer to control multiple tables. You can design pivot tables as per your need, filters will be applied as per the slicer.

Now I will discuss few basic settings in slicer. You can change the color of slicer from slicer styles, preformatted styles are available in the Slicer Style group, choose any, the color will change.

Slicer Styles

If you need to show the slicer items in more than one column, you can do it through Format slicer. Right click on the slicer and select the second last option Size and Properties option. Format slicer will open, from position and layout option you can increase the number of columns, you can fix the position of the slicer and many more.

You can change the caption of the slicer, select any slicer and click Slicer Settings from this button or you can get it from the pop-up menu after right click.

Format Slicer

This is very good tool in pivot table and Table. Please use this tool in your pivot table report, it will increase the value of your report and the navigation in the report will be easy.

I hope you have learned how to use slicer in pivot table, please feel free to ask any question in the comment section. I will respond to your question.

Thank you for reading.

End Date of The Month : EOMONTH Function

Suppose we have to get the number of days from a certain days given few months and the day will be end of the last month.

Like the image below.

EOMONTH Function

If we break our problem,

            1.  We need the end date of the month or the last date of the month

            2.     The last month will be current month and the number of months provided.

            3.     Next step is to get the difference between the Start date and the ‘Month end Date’

In this scenario, EOMONTH function needs to be used.

 

Excel has a function which will give the last date of the month, supplied number of months.

 

The EOMONTH function is a built-in function in Excel which is a Date/Time Function.

 

Syntax :

 

The syntax for the EOMONTH function in Microsoft Excel is

 

=EOMONTH( start_date, month )

 

Parameters or Arguments

 

start_date : A valid date to return the day.

This function can work after the start date or before the start date.

Month : Count of months in number

Returns : The EOMONTH function returns a serial date value

Therefore, please remember to format the cell or range of cells as date.

In this above example, we need to type the formula in the C3 cell.

 

The formula is =EOMONTH(A3,B3).

 

EOMONTH Function

You can notice one month is given negative sign. It will calculate the date before the start date.

 

Thank you for reading...