Hey friends, this blog is in continuation of the blog series of Accelerate your analysis with pivot table. If you are beginner to the pivot tables, I would suggest looking over to How to insert a Pivot table first.

Recommended Articles:

How to convert dates into months in pivot table?
How to change layout of the pivot tables?

When you can insert a pivot table and analyse the data easily, your hunger for analysis may rise to top. Sometimes, you need to present your analysis. It may be before top-level management or a simple presentation or discussion with friends, presenting in a better way with graphs and charts does no harm. Presentation always matter!

Excellent. The good news is you can use timeline chart, graphs, pie chart, or something like that on a pivot table too. So that it looks goods, feels good and improves your analysis and productivity.

Now let us get back into gear and back to topic: Pivot table Slicer

What is Slicer?

Slicer as the name suggest slices the data as per your requirement. Assume there is a large data in the pivot table and you can slice your data by use of slicer with very pleasant user interface.

Slicers can be very much of use while presenting data or while analyzing data quickly as you can filter, the data by just one click or touch.

How to insert a Slicer in a pivot table?

Slicers can be inserted in normal table as well as in pivot tables also.

Let us insert slicer into a pivot table.

Pivot Table Slicers:

  1. Insert a pivot table
  2. Have some data in the fields of pivot table.
  3. Select any cell of pivot table.
  4. Choose Insert Slicer from Analyse ribbon of Pivot table tools.
    Pivot Table Tools >> Analysis >> Insert Slicer
  5. Choose the column for which you want to insert slicer.
  6. Press ok and boom!

Let us understand the same with one practical example to understand that how you can use slicers in pivot table very effectively.

Here we have Sales Register for F.Y. 2016-17 containing Date, Branch, Customer, Item sold, Quantity and Selling Price.

Here is the file of Sales Register, download it and continue with me step by step. Slicer Pivot table Sales Register

 

Sales Register

  1. Insert a pivot table.
  2. Take two or three items in row label and one or two items in values. (You can take any number of items in any field. Here for the sake of explanation , I am taking Branch and Customer to Rows label and Amount in Values)
  3. If you do not like the default layout of Pivot table i.e. Compact layout. Learn about different types of layout and how to change the layouts of pivot tables? I am changing the layout to tabular layout.
  4. Select any cell of pivot table.
  5. Choose Insert Slicer from Analyse ribbon of Pivot table tools.
    Pivot Table Tools >> Analysis >> Insert SlicerInsert Slicer
  6. Select the columns for which you want to insert a slicer from the “Insert Slicer” box.
  7. Press Ok and you will have your slicers besides the pivot tables. Enjoy!Pivot table Slicer

How to use Slicers in Excel?

Select the name or place or the thing you want to filter out from the pivot table.

  1. Let us check out total sales to Gautam Mishra. Just select Gautam Misra from slicer and filter will be applied to pivot table.
  2. Let us check Sales made to Gautam Mishra from Indore. Select Indore from slicer and then select Gautam Mishra.

Got the basic idea about the slicer!! Now as the slicer is having more importance for presentation, let’s format slicer buttons, their size and colour.

Formatting Slicer:

How to change number of columns for slicer buttons?

If your slicer buttons are too many and not visible in one screen, you might consider this as a best option. i.e. to have two or more columns of buttons of slicer.

  1. Select the slicer
  2. When you select the slicer, one additional ribbon of options will be visible.
  3. When you select it, you will have will see Buttons option on the right hand side. Change the columns to 2 instead on 1 in the buttons.

How to change the size of slicer buttons?

You can locate that option under column of Button of options.

  1. Select the slicer.
  2. Go to options ribbon.
  3. Change the height and width of slicers from buttons.

How to sort the data of Slicer?

You can sort the data of slicer in ascending or descending order from slicer properties. (Default slicer is arranged in ascending order)

  1. Select the slicer.
  2. Go to options ribbon.
  3. Go to Slicer Properties located on extreme left side.
  4. Select the type of sorting you want to do.

Here is the file in which all the steps are applied.If you had any problem, check this file Pivot table Slicer or comment.

 

Stay Connected to know

  1. How to insert slicer into multiple pivot tables or tables?
  2. How to insert Chart/Graph into Pivot table?
  3. How to connect Slicer to Chart/ Graph so that selection of slier changes chart/graph.

Don’t forget to share and subscribe our blog via email, if you loved post.
Have any suggestion/ recommendation? Leave a comment.