Formulas

How to Sort and filter data in Pivot Table

Pinterest LinkedIn Tumblr

There are different ways to filter data in Pivot Table in Excel.

A filter is often utilized to focus on a smaller portion of your Pivot Table data for in-depth analysis. A sieve filters and returns the necessary ingredients, similarly filter in Excel retrieves only the data as per requirements. A general example would be, Filter sales based on this month alone.

Filter Data in Pivot Table

Slicer

A  Slicer enables you to filter data in Pivot Table. The difference between normal filter and slicers is it is an interactive type of filter.

  • Select any cell within the Pivot Table.
  • Go to PivotTable Analyze > Insert Slicer.
  • An alternate method is to click on Insert > Slicer.
  • The following dialog box pops up where you can select one or more field to filter based upon.
Filter data in Pivot Table using Slicer
  • Select the field and click OK.
  • The following window would pop up based on the field/category you have selected.
Filter Data in Pivot Table using Slicer

Select the options from the slicer to filter data in pivot table. You will notice the Pivot Table applies the filter and your data is returned. Slicers are way more interactive and informative compared to the regular filter option. You can also add multiple slicers.

Report Filter

A report or page Filter is a filter that operates on the entire pivot table within that pivot. To do so, simply drag a field into the filter section from the Pivot Table field pane. Below is an example depicting sector as the Report Filter.

Report Filter in Pivot Table

Below is a pictorial representation of how the filter appears in a Pivot Table. The highlighted portion shows that the filter has been activated. In the below image the Filter is based on Sector.

Report Filter - how to filter data in pivot table

Click the drop-down button next to (All) available on top of the pivot and filter as desired.

Row-Label Filter

  • Label Filter: This option will filter by the row name. In this case the name.
  • Value Filter: This option will filter based on the numeric value. For instance, you want a list of people with an experience above 5 Years.

To activate any of these filters, click on Row Label filter > Value/ Label Filter > Select as per requirement. Below is an image showing how to use Label or Value Filter.

Label and Value Filter

Value Filter offers the following options:

  • equals / does not equal
  • is greater than / greater than or equal to
  • is less than / less than or equal to
  • is between / not between
  • Top 10 (N)

Label Filter offers the following options:

  • equals / does not equal
  • begins with / does not begin with
  • ends with / does not end with
  • contains / does not contain
  • is greater than / greater than or equal to
  • is less than / less than or equal to
  • is between / not between

The power to filter data in Pivot table is enormous. It can be very helpful but be careful not to make use of too many filters at once . It gets complicated and could be risky.

Sort

Sorting is Pivot is a little different when compared to normally sorting your data. To sort data in Pivot Table follow the steps as follows

Click on the drop down in the Pivot Table and you get sorting options as below.

How to Sort and filter data in Pivot Table 1

The Sort A to Z and Sort Z to A can help in sorting based upon the first column. This means your pivot will be sorted based upon Name in the above example. Now if we wish to sort based upon the experience how do we go about The More Sort Options… can be used to sort pivot based upon other headers.

Once you click on the More Sort Options a window pops up as below.

How to Sort and filter data in Pivot Table 2

The three options are Manual, Ascending and Descending. Select the desired option, in this case, we select Descending and the moment we do so the drop-down list is activated as below. On using the drop-down you can select the desired header based on which you want to sort data. In this case we have selected Experience.

How to Sort and filter data in Pivot Table 3

Click OK and your data will be sorted.

ALTERNATIVELY

Click on any cell within the column you wish to Sort based upon.
Right-click and select Sort > Ascending/Descending.

How to Sort and filter data in Pivot Table 4

Note:Once you filter data in Pivot Table and wish to copy cell use the shortcut Alt + ; to copy only visible cells and then paste it.

Style your Pivot Table with fancy Design by using the link as follows.
https://excelinexcel.in/ms-excel/articles/how-to-design-pivot-table/
For video assistance on reporting and analyzing using Pivot Tables click here.

Related Posts

Write A Comment