How to filter Pivot table based on a cell value - Microsoft Excel

Today I am going to share the VBA code which helps in filtering a pivot table based on cell value in Microsoft Excel.

This method helps you to set a value of cell and then once you click apply the pivot is filtered based on this value.

We will be using cell "C1" as the cell which will store the value to filter "Pivottable1" in the below screenshot. Our data is a 2x2 simple table. Prepare a similar view in your excel sheet with sheet named as "Sheet1".

Once you start excel. hit Alt+F11 and double click on the sheet in which you would like to filter the pivot table.

Now on the right hand side, paste the below code and save the file.

Sub Pivot_filter()
    Application.ScreenUpdating = False
  

    Dim pt1 As PivotTable
    Dim pf1 As PivotField
    

    Set pt1 = Worksheets("Sheet1").PivotTables("PivotTable1")

    Set pf1 = pt1.PivotFields("X")
    

    pf1.CurrentPage = ActiveSheet.Range("C1").Value
    Application.EnableEvents = True

Exit Sub

End Sub

Lets understand the code little more.

• Application.ScreenUpdating = False: This line ensures the user doesn't see the "real work" code is doing in the backend.
• Dim pt1 As PivotTable, Dim pf1 As PivotField: Here you are defining the pivottable and pivotfiled objects so that the macro understands them when used later.
• In the below to lines we are setting the actual names of Pivot Table and the corresponding pivot field which needs to be filtered by the code.
• Set pt1 = Worksheets("Sheet1").PivotTables("PivotTable1")
• Set pf1 = pt1.PivotFields("X")
• pf1.CurrentPage = ActiveSheet.Range("C1").Value: In this line we are pointing the code the cell where it needs to take the value and filter the pivot table.

Thanks for visiting. If you need a copy of the excel file readymade, please email me at askme.kkhelps@gmail.com

How to show values in Millions using custom format - Microsoft Excel

Converting an absolute value to Millions view is task unless you that it is possible to do in a easier way.

Below are two methods:

1. Using concatenation and division.

2. Right click on the value and go to format cells, select custom on left hand side. Now enter #0,"M" in the text box under Type header on right hand side. 

The difference between the first and second method is in the first method the formula bar doesn't show the value instead shpws the formulae used to convert to Millions.

In the second method, the original value is shown as is and doesn't affect user experience.

Additionally, you can use the custom formatting to show text which doesn't show in formula bar like below.

Formula Bar:

Custom Format used: