Slicers

If you are using pivot table reports for the 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 discussion, I will discuss 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?

Slicers 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.

Slicers in Excel

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

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

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

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.

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.

Slicer Selection

Slicer Selected Data 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 act like filters and it is very easy to handle.

If you need to display all the segments, then click on this clear filter button. All the buttons will be selected again.

There is a small button on the left side of the filter button, this is Multiselect 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 button, like this. You can use the method which is comfortable for 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 shows the data of Johnson only for Government business.

Let me show you a few more things which are very useful. I have these excel sheets; 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 must connect this slicer to other pivot tables. This is a very easy process.

You have to instruct the 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. 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 relates to 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 tables 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 a 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.

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.

This is a 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.

How to Make a Pie Chart in Excel #exceltech #exceltips #excelsolutions

Variance Charts in Excel Sales vs Targets or Actual vs Budget

What is Function in Excel ?

What is function?

The definition of Function is formulas that are predefined, user need to enter only the arguments to get the desired result.

Let us understand what Function is and what could be the proper definition of Function using simple example. Suppose we need to find out the maximum of any number series. We will use MAX function; we will type MAX and within the bracket, we will enter the number series and we will get one of the numbers which is the maximum number from these number series. Here MAX() is a function.

Excel has many functions, some of them needs to be feed by arguments and some are NOT.

Also, few functions don’t need to be feed with arguments but are also known as functions, like TIME() and NOW().

Generally, a function ended by opening and closing bracket where we put our arguments and we call them parentheses (brackets).

Therefore, the definition of function needs little modification, and we can define function as ‘function is formulas which is predefined and may or may not require arguments.’

Why use Function?

Human tendency is to reduce the repetitive and frequent jobs. Suppose we need to calculate the SUM of few numbers, to get the sum we have to write 10 lines of (programming) code. (A programming code is set of instructions to the computer, the code uses the compiler to converts them to zeros and ones, the language of computer and the computer CPU responds accordingly.)

So, every time we want SUM of two or more numbers, we have to write 10 lines of the same code. Now imagine, if we write these 10 lines of codes somewhere and name then as SUM and whenever require we can call SUM and which in turn will call those 10 lines of programming codes and we can avoid writing 10 lines of codes again and again.

We need to modify the definition of Function again.

The definition of Function should be like, “Functions are predefined formulas which are set of programming codes to evaluate some calculations using arguments which may be or may not be required.”

One more thing about functions, there are two types of Functions; inbuilt which comes with any application, like Excel and can also be created using programming tools like C, C++, JAVA etc. In  Excel, Functions can be created using VBA coding which we will learn soon.

Example of function which takes arguments are SUM(), COUNT(), IF() etc. and example of functions which may not require arguments in excel are TIME(), NOW(), DATE() etc.

I hope I am able to make you understand what function is.

Thank you for reading. Happy learning.

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.