Filter Data in Pivot Tables in Excel

Hi,

I have a pivot table in which I have several filter options defined. The problem is that the filtering is done using "AND" conditions. I want to be able to filter where filter A value exists OR filter B value exists OR filter C value exists.

Is this possible without using the Advanced filter functionality?

Thanks,
Jason

I have several pivot tables that are updated daily with new data.

Currently, I have filters for "Creation Date" on each of the tables for each month's data. For example, I have a tab for October 2010, November 2010, etc. and on each tab, the pivot tables are filtered by Creation Date for the dates occurring in each month. Each time I add new data, however, I have to update each pivot table to include the new dates, but still exclude the old dates.

Is there a method for me to avoid having to update the filtered dates each time I add new data?

Many thanks!

When I click on the field header, go to filter, top 10. I have to 4 dropdowns in order. They are Top/Bottom, #of Items, Items/Percent/Sum, and the last one. The last dropdown which I think would contain the field name is blank. Any idea why this occurs. Perhaps I'm trying at the wrong part of the table?

Does anyone know a good way (in Excel 2010) to filter a pivot table to show only certain rows based on the values in an array of cells on another worksheet?

Say my pivot table has row labels of "SalesRep" and values of "count of Orders", and I want to only see the results for specific (and often arbitrary) SalesRep(s). Is there a way to have Excel filter the pivot table in a way other than unselecting all the values and then hand checking the box next to the SalesRep we want to report on?

Thanks (long time reader, first time poster),

Matt

Hello,
I have a pivot table with a 6 month list of days in the column section. I need to filter out though all the weekend and holiday dates. Can anyone think of a simple way to do this?
Thanks much in advance,
Nathan

Hello,

I have a pivot table issue with blank columns and rows in my pivot table. How can I remove this WITHOUT the filter in the pivot table?
the "-" indicate a completely blank cell. As you see, the column header and rows are blank.


Row Labels - Discussion of Concern - - - Steven MacLean Julie Payette Chris Hadfield inappropriate customer interaction 16-May-11 Marc Garneau Roberta Bondar Robert Thirsk misconduct 20-May-11


I grab the data from

Ops Manager Manager Agent Topic Action Date Steven MacLean Julie Payette Chris Hadfield inappropriate customer interaction Discussion of Concern 16-May-11 Marc Garneau Roberta Bondar Robert Thirsk misconduct Discussion of Concern 20-May-11

Hi guys,

I have a problem with VBA I need to overcome.

OK, so, I have three Worksheets:

Data_Sheet – this contains a table of data with three columns; Date, Sales, Cost
Pivot_Sheet – this contains a pivot table of the data within the Data worksheet
Controls_Sheet – This contains a parameter which I would like to use to control the pivot table

What I would like to do is to change the date value in Controls_Sheet!B2, then run a macro, which would then filter all date values except the value within Controls_Sheet!B2.

So for example, if these are my dates within the ‘Dates’ column of my Pivot Table:

31/07/2010
07/08/2010
14/08/2010
21/08/2010
28/08/2010
04/09/2010

Then I set Controls_Sheet!B2 equals ‘04/09/2010’ then run the macro, only the line for ‘04/09/2010’ will be showing in the pivot table.

I have tried many codes all which seem not to work, so I haven’t posted them here. I have posted the example work sheet.

I’m sure this is simple buy I’m struggling with it. Thanks in advance.

Hi

I am using Excel 2010 and have a spreadsheet containing just over 2000 rows, including a date field for each record. The date info is definitely in the correct date format because I can 'Filter' by year/ month etc when I put a filter on the header row. However, when I put the data into a pivot table, the 'Date Filter' option is greyed out.

Anyone any ideas how a activate the date filter option in the pivot table?

Hello there;
i have a pivot table with one variable on rows and one on column (this one represent month).

Now i want to display only the last 3 monthes from a value in a cell on the same sheet.
I want to do it with vba, because when the cell changes the row labels of the pivot table should automatically adjust to display the last 3 monthes.

I have the values of the last 3 monthes in vba within one array. this is the easy part.
now i would like to know how to have a procedure that takes thos 3 monthes and only allow them to be visible in the pv, (the rest should be invisible).

can someone help me please,
thanks a lot

Hello All,
I did a search and found a few helpful threads, but nothing to actually do exactly what I need.

I'm working on a spreadsheet for manager's use, and I have run into an issue. I have all the data on a tab, and am using a general sheet that the managers can enter in their employee number, to see all their employees current scores.

It was requested that they have access to the data for each employee. I decided to just make a simple pivot based off their employee ID, that would update when they entered in their ID. This would allow them to open up the detail on a seperate tab by double clicking the pivot.

This is the code I'm currently using, yet I need it to refresh the pivot once the ID is entered into cell B2.

Code:

Sub Pivot1()
ActiveSheet.PivotTables("ManagerData").PivotFields("REVIEW_EMP_MGR_ID").CurrentPage = Range("B2").Value
End Sub

Any help would be greatly appreciated.



The below is the code I'm using to update another pivot table on a different tab, which is actually used to pull in each employees scores and such.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub
On Error Resume Next
Application.EnableEvents = False
Sheets("Manager PIVOT DATA").PivotTables("PivotManager").PivotCache.Refresh
Application.EnableEvents = True
End Sub

Unless there is some magical way to link a cell to a pivot table that will allow the manager to just click the individual employee ID to pull that specific data.

Any help would be greatly appreciated.

Hi,

Working in excel 2007. I have set up a pivot table to view sales figures.

I download the data from an sql server and then have to run a text to columns operation so that the date is in the correct format and an can be sorted.

When I built the pivot table from scratch, when I chose to filter by 'Creation date' there was no issue and I could select data from this year, last month, this week-no problem. I test once or twice pasting in the data and seemed to have no issue. When i tried this week, the date filter is no longer possible, I can now just see label filters.

I have tried, formatting the source data in other ways but to no avail. I can filter the souce data by date with no issue but for some reason the date filters are not working on the pt.

Please help.

I'm using Excel 2007 to analyze sales data for my territory managers. My database is 21 columns by 39,078 rows (in case this matters.)
My pivot table will not allow me to use the value filters - all options are greyed out except "top 10". I need to be able to exclude the accounts with zero sales dollars. How can I get the value filter option to work for me?

Hello,

Have a table of sales people,

Want to set rewards program for top sales people based on n# of sales that exceed $X.

so for the source range that feeds the pivot table, I have a vba decision derived decision column title "Huge Sale" that is applied to each sale (does this sale exceed $x, if true then put "yes" in decision column, else "no").

Now, what I am trying to figure out, on my pivot table, I want it only to show salespeople who have had at least n# of "Huge Sales". Is there a way I can set a filter on my pivot table to show only row groups where Count of Huge Sales exceeds n?

If I can do that, user inputs what a huge sale is ($ threshold value) and how many sales before reward (# threshold). And the table would just spit out the sales people to reward.

Any ideas?

Current Row labels a
Huge Sale (show "yes" only)
Salesperson

Data:
Count of huge sale

Hi guys,
I'm having a weird issue with my beloved pivot tables. In my spreadsheet I have a pivot table which looks up aroud 500 rows of data and that is filtered on a few defined account numbers. I am refreshing the source data daily, and every day I find myself checking that the pivot is still filtering on the correct accounts. Every time I update the data (through a macro) and refresh all pivot tables (through a macro), there are a few more accounts that get added to the filter.
Do you have any idea of how I can lock the filters in my pivot table?

Thanks in advance and hapy christmas

G

I have a pivot table. It is based on a data table located on another worksheet. I am trying to set up a filter on the table (have put a separate filter on the relevant column, although the column being filtered is not a part of the data being displayed in the pivot table). Now, I want this filter to be automatically selected based on the value selected elsewhere in the sheet.

So for example, if the filter is based on a color: yellow, green, blue, red etc and this determines what gets displayed in the pivot table; I have a text box elsewhere on the sheet to which a user will enter a value and that automatically should apply the filter to the pivot table. so the user might enter red in the text box, and this should automatically filter the data in the pivot table... can this be done using a macro?

Dates are part of a 60,000+ row datatable I'm using in a Pivot Table. The datatable is a huge time record database: Employee Name; Date; Hours worked. I have selected the Date field as a Report Filter in my Pivot Table. When I pull down the Date field in the Pivot Table, I'd like to select a date range for the report. Instead, each unique date in my datatable is listed, and I merely have the option to click on/off each specific date. The table is 2 years of data, so I don't want to click 365 dates if I want to report the other 365 dates.

Is there an easier way to filter the Pivot Table using a date range variable?

Dave

I often use pivot tables in MS Word reports. Those pivot tables often use filters in the column headers. In past versions of Excel, I was able to highlight the pivot table's content on Excel, copy it, and paste as enhanced metafile into the word document, and the filter arrows were not included in the resulting graphic. I can't use the regular paste function - I am often using charts that have to fit into multi-column pages, so putting the chart in as an embedded worksheet doesn't work with the size restraints of the document. I can't figure out how to do a cut-and-paste of a graphic version of the spreadsheet into Word without having the arrows show up in Excel 2010.

Is this possible? Or did they remove the ability to hide the arrows?

i have this code that works for filtering a pivot table to the cell value in b3, I am trying to figure out a way to filter it to (blank) if the cell value in B3 is not in .PivotFields("MGR").because rigth now it just leaves the previous values that the pivot was filtered to when the selected b3 cell value is not included in the list of items for ("MGR")... HELP please

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub
On Error Resume Next
Application.EnableEvents = False
Sheets("MainPage").PivotTables("PivotTable4").PivotCache.Refresh
With Worksheets("MainPage").PivotTables("PivotTable4")
.PivotCache.Refresh
.PivotFields("MGR").CurrentPage = Target.Value

End With

Application.EnableEvents = True
End Sub

Hi,

I am looking for a way in VB to select and unselect items in the list for filters generated in a pivot table.item list.

Ho do I do that?

Is there any way to Sort TOP 5 Values in PIVOT Table?

I know there is an option to filter TOP 10 but how abt TOP 5?
Do we need to write a macro? If Yes please suggest

Hi,

Is it possible to show the values in a pivot table filter?

When I filter by one item, it shows what item I'm filtering on (say "A" for example).

When I filter by more than one item, it shows "(Multiple Items)".

I would like to show what those multiple items are on the sheet (say "A" and "B" for example).

This would make things a lot easier when I distribute reports.

Is this possible?

Thanks in advance.

Hi Everyone,

Is it possible to filter a PivotTable field using a "contains" keyword from another cell?

I tried recording a macro and got results below:

Code:

Sub CatFilter()
'
' CatFilter Macro
'
'
ActiveSheet.PivotTables("PivotTable6").PivotFields("Category"). _
ClearLabelFilters
ActiveSheet.PivotTables("PivotTable6").PivotFields("Category").PivotFilters. _
Add Type:=xlCaptionContains, Value1:="Green"
End Sub

I'd like to replace Value1:="Green" to reference by cell G25 where the keyword is listed. Attached is a screenshot of what I attempting to do.

Thank you in advance for your help!!!

How can I put an automatic filter in pivot table so that it only shows last 12 months data?

If today is Oct'10, filtered data will show between Oct'09 to Oct'10.
If today is Nov'10, filtered data will show between Nov'09 to Nov'10. This way I won't need to update the filter each month.

How can I do that?

Thanks.

Good morning,

I have a predefined Pivot Table report where users use a userform to change the report filter. The textbox on the userform populates a hidden cell in the workbook which then triggers a worksheet change event to update the report filter cell. If the user enters a wrong value, I get a "No item of this name exists in the pivot table report" message which allows you to hit OK to rename the report filter. Can I write code to intercept this message with my own message box that allows the user to hit OK and start again?

Thank you in advance

I have a pivot table grouped by sales groups, under each sales group there is a list of their clients. Values, sales for this quarter, are given for each client. I want to filter the pivot to show only the sales groups whose total sales (a subtotal created by the pivot - this number is not in my data) are greater than a specific value.

Is this possible?

The data looks something like this:

Q1 sales Sector 7 G Star-bellies 26 Whos 77 Total Sector 7 G 103 Moe's Mulberry Street 50 Cats in Hats 25 Total Moe's 75

My goal would be to filter out, let's say, the entire Moe's group because their total is less than 100.

The sales groups whose total sales are greater than the threshhold should still be visible, and the list of their clients and this quarter's sales figures cannot be hidden.

Setting a filter for column A to text containing total and then using value filters on column B is not a solution - lines are hidden. For some reason, everyone I'd talked to give this solution.

In a perfect world, if my overlords came to me and asked to re-filter based on another value, that would be a few clicks.

FWIW, the pivot table I am working with is more than 9,000 lines.

Is it possible to automate the report filter on a pivot table to say, a week number, or the contents of another cell?

Long story short - Big spreadsheet covering results from inspection, I need to run week number based analysis on the results. I can do this by manually changing the pivot report filter to the relevant week. My issue is that there are 25 pivot tables in the sheet and I want to hand it over to the production guys. They will not purposely break it but I know I will be back and fourth to repair it for the rest of my natural life!!!!

Any advice appreciated....

Stu

This is my first post, so I would first like to say that all the information I have found on this board has been extremely helpful. Thank you.

The issue:

I have a workbook that gets data from a text file, that data then populates a pivot table. The pivot table has one filter critiera (portfolio). One the filter is selected, the data populates an output tab with various formulas, values, extra. That output gets copied and pasted the various tabs within the workbook. Go back to pivot table and select different filter, rinse and repeat for 168 critiera (portfolios)

I currently have macros for all the steps in this process except selecting the pivot table filter critiera. I only need to select the filter critiera one at a time (no multiple values).

I am not a programmer, I have a minimumlistic scope when it comes to vba, however my non-vba excel knowledge is expansive. Thank you for your assistance.

-Matt

Good morning folks
I'm hoping for some advice please, on how to get my pivot table to filter out blanks when refreshed.
I have an ever expanding range of values being evaluated in a simple pivot table that resides on the same sheet as the data range. The values are also pretty simple, just week no, a reject code and a quantity. I found some code to update the PT each time data is added to the range 'On worksheet selection change', but what I'd like to do is not have blanks displayed. I tried just deselecting them in the PT filter, but for some reason then any new reject codes I add, that have not been previously entered, also get deselected and do not display in the PT. If I do not filter at all then everything works OK except for the fact that I have a row and column for 'blank'. I guess that is due to the PT updating prior to the new data row being completed, so it is seeing blank data momentarily. Might there be a bit of simple code I could include with my refresh code, to switch off the filter whilst PT is updated and then re-filter after update? Or will that just loop?
Sorry, I'm a newbie on vba and pivot tables for that matter.
Hope that makes some sense.
Thanks folks

Code I'm using to refresh the PT is:

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim iP As Integer
Application.DisplayAlerts = False
For iP = 1 To ActiveSheet.PivotTables.Count
   ActiveSheet.PivotTables(iP).RefreshTable
Next
Application.DisplayAlerts = True

End Sub

I am trying to come up with a way to fitler a pivot table by date the same way i can with the filter dropdown. I have a table that filters using the TODAY feature in the filter dropdown undet date filters. I would like to assign a vba to a pair of buttons for easy switching between the "Today" date filter and the "Yesterday" feature so i no longer have to pull it up from teh filter dropdown.

I have a spreadsheet that has multiple work sheets which each have a pivot table with Year and Month Report Filters.
I'd like to be able to select the Year and Month via Data Validation Lists on the front sheet (my dashboard) and automatically have the Pivot Tables update in the other work sheets.
How would this be possible? Via VBA only?

Hi all,

I'm using a Pivot Table which is generated from OLAP. Certain fields are multi-dimension and can be "drilled down".

For example, the field "Customer" has "[Customer].[Customer group]" i.e. from Customer Group it can be drilled down to Customer.

I wish to create a macro that can filter a single entry.

I tried this code to choose the customer group AA - HAPPY HUISDIER

Sub Choose_Pivot()
With ActiveCell.PivotTable.PivotFields("[Customer].[Customer Group]")
For i = 1 To .PivotItems.Count
If Not .PivotItems(i).Name Like "AA - HAPPY HUISDIER" Then
.PivotItems(i).Visible = False
End If
Next i
End With
End Sub

When I run it, it stops where the red code above is. It gives the error message:

Run-time error '1004':

Unable to set the visible property of the PivotItem class.

But if I try this similiar code on a normal pivot table (not from OLAP), it works:

Sub Choose_Pivot()
With ActiveCell.PivotTable.PivotFields("Name")
For i = 1 To .PivotItems.Count
If Not .PivotItems(i).Name Like "Paul van Rijn" Then
.PivotItems(i).Visible = False
End If
Next i
End With
End Sub

Can anyone give me some guidance on solving this problem? Many thanks

Here's a challenge for you.

When working in a pivot table I want to use the label filter on one of the fields. Problem is I want to use it for multiple text strings on the same field.

For instance, I want to grab items that have 74130 OR 74135 or 73265 in them.

I can't seem to make anything but one string of text work here. I would think there's got to be a way to do this with some sort of specific command.

Help?

Hi All

I am looking to link the filter options on 4 pivot tables (excel 2007) (all on the same worksheet) to a single drop down box, so that when i select an item in the drop down box it changes the filters to reflect my selection.

Eg if my pivot table filter contains a list of months and i select january in a seperate drop down box then all my pivot tables change to the january filter.

I have checked the board but the answers i see dont seem to be working for me.

many thanks

Stu

I'm trying to filter the "Blank" cells in my pivot table as a "page" filter,
but I'm not getting any success. On the contrary of showing the "blank" cells
the Pivot Table shows all the values. I've already tried to format the cells
in the source table as "number", but it didn't work. Any suggestions?

I have a pivot table created from a data table with three columns: Date, Sales, and Customers. I have the Date column in the Report Filter and I want to change the date based upon a value in a cell range named Date, of all things. The pivot table is located in another worksheet and the range Date is changed by a spin button in the active worksheet.

My code is:

Sub ptDate()

Dim pt As PivotTable
Set pt = Worksheets("Pivot Tables").PivotTables("PivotTable1")

pt.PivotFields("Date").ClearAllFilters
pt.PivotFields("Date").CurrentPage = Range("Date")

End Sub

This doesn't work. Any help would be greatly appreciated.

I need help writing a macro which is supposed to change the report filter (which contains dates up to the current date) of a Pivot table.
I want to be able to enter a date on a second sheet in cell A1 and the macro should basically change the report filter to the date I entered there. The macro button will be on the second sheet, too, so I won't have to go back to the Pivot table at all to change the filter.
I appreciate any suggestions and thoughts! Thanks!

Hi Everyone,

Is it possible to filter a PivotTable field using a "contains" keyword from another cell?

I tried recording a macro and got results below:


-----------------
Sub CatFilter()
'
' CatFilter Macro
'

'

ActiveSheet.PivotTables("PivotTable6").PivotFields("Category"). _
ClearLabelFilters
ActiveSheet.PivotTables("PivotTable6").PivotFields("Category").PivotFilters. _
Add Type:=xlCaptionContains, Value1:="Green"
End Sub
------------


I'd like to replace Value1:="Green" to reference by cell G25 where the keyword is listed. Below is a screenshot of what I attempting to do.

Thank you in advance for your help!!!

I have a pivot table with a filter. The report is formatted differently than the default formatting (i.e. column width made smaller, color shading for columns, light grey dotted lines for cell borders, wrapping text). The option "Preserve Cell Formatting on Update" and "Classic Pivot Table Layout" is used.

When I change the value of the filter, the Pivot Table format does not change.

Then, when selecting "Show Report Filter Pages...:", the worksheets are created correctly for each person but the formatting is not totally preserved (cell width is not preserved, the color coding, wrapping, and cell borders are preserved). I would have to re-format all of the tabs every time I run this daily report.

How do I get the worksheets generated from the "Show Report Filter Pages" option to use the same column width as the original? I'm stumped!

MSExcel Office 2007

Hello, When I create a pivot table and click the dropped for my Row Labels the Date Filter option is grayed out. The Data I am selecting for the the row label is shown as dates. I have attached a screen shot to show you what I am seeing. Any help in resolving this issue will be greatly appreciated.
Screen shot.jpg

Hello,
I have a pivot table wherein one column called Country is in Filter option. Can you please help me how can I select more than one options in VBA coding?
I have enabled the "EnableMultiplePageItems = True" and then I am unable to select two options which I needed.
Please help me.
Thanks

Dear all,

I see a pivot table through a list box. The problem is that I can not see the pivot filtered in the list box, the box load all the items that the field contains. How can I filter the pivot directly in the list box?

Many thanks in advance

I am using Excel 2007 against SSAS 2005. I am using a pivot table to query a cube. That works well. However, instead of using contants for the filter values, I would like to use cells. My goal really is to be able to change the query by changing cells.

Please, can you tell me how I can do this? Perhaps there are multiple ways?

Thank you.

I have a pivot table that has a dynamic data connection and want to set a filter so that I can filter and view any dates in the respective date format column that are 14 days earlier and onwards from the current date.

The only way I can think of doing this is with an advance filter, which I have tried on a normal table with the following formula to no success.

= "

Hi, thanks for reading this and any help you can give. I reference this site quite a lot but can't find what i'm looking for (Because i'm not sure)

I have a Pivot with a Userform containing 3 cascading Listboxes, each listbox fills down to the next. What i am trying to do is have the result of the listboxes to filter the Pivot table.

Keeping it simple for a moment, in listbox1 user has a list of Departments and clicks "Liquor" then the Pivot should only show items within the "Liquor" departments. How do i achieve this?

Also when looking at other treads and seeing the code offered, should i be trying to filter the Pivot Table field in the Page or Row area?

Thanks

Hi there-

I have a pivot table on the left hand side of my worksheet

I have columns to the right of that pivot table that I use to add comments to data in the pivot table.

How can I apply an autofilter to the pivot table so that when I change the row criteria in the pivot table the data in the columns to the right of the pivot table sort/hide/show along with the pivot table?

I've seen settings where an autofilter is applied to the pivot table, but I cannot figure out how to do that...