Excel : Filter pivot data dynamically using macro

In this post I would like to explain very easy method to filter pivot data dynamically using macro.

To demonstrate this, I have used Adventureworks database AdventureWorksDW2008.  Query used is to find the sales amount in each sales territory for each product in every year.

CREATE view [dbo].[v_Exceldashboard]

AS     SELECT EnglishProductName,
               CalendarYear,
               d.SalesTerritoryGroup,
               SalesTerritoryRegion,
               SalesAmount,SalesAmount - TotalProductCost as profit ,
               EnglishMonthName
     FROM dbo.FactInternetSales a
     INNER JOIN dbo.DimProduct b ON a.ProductKey = b.ProductKey
     INNER JOIN dbo.DimDate c ON a.OrderDateKey = c.DateKey
     INNER JOIN dbo.DimSalesTerritory d ON a.SalesTerritoryKey = d.SalesTerritoryKey

Connect Excel to this database and retreive the result of the above query into one sheet and we can create pivot data out of this result set as shown in Fig 01.

Fig 01

Above pivot data can be filtered by territory as per the user wish but I have received change request from the client to filter the pivot data by typing the required value in one text field rather than selecting from the pivot filter area as shown in Fig 02.

Fig 02

we can achieve this by implementing the below steps.

1. Create macro which capture the user entry from the field



Fig 03

2. Call this module at the change event of the field. Here E1 is the field where user can give the filter value.

Fig 04



When ever the change is happening to any of the filed in the worksheet happens this script also calls as it is given in the change event of the worksheet which is very performance degrade. So in order to avoid this situation we can add one more condition to check the change is happening to desired field or not by checking the address of the change event.

3. Final step is to hide the original filter so that user feel that only one option is there to filter data.