Actually I know how to select the data after applying the data filter but the issue is I am not able to exclude the header row and give the target range as used (non-blank) rows only!!
Filter grayed out excel code#
I’m sure there is a way to brute force some code to expand each filter value into a table in a new worksheet and rename that worksheet appropriately, but that kind of defeats the purpose of creating a “BI Tool for the Masses” which is the goal of most of my Pivot Table blogs.I am looking for the code to select the visible data after applying a data filter. When I then opened the Options dropdown menu from the Analyze ribbon of the PivotTable Tools group as I did before, I was surprised by the fact that the option to Show Report Filter Pages was greyed out as you can see below.Īpparently, this option is not available when displaying the data through the Power Pivot model, but is available from the basic Pivot table model. I then defined a Pivot Table from the data model using the same fields as before. So using the same data, I attempted to add the original data to the Power Pivot Data Model by using the Add to Data Model option in the PowerPivot ribbon
Filter grayed out excel series#
It also makes it easier to simply print a series of the pages to your printer if hardcopy is necessary. If you give a copy of the workbook to someone who might not be as familiar with pivot tables as you are, you can expand out the pivot table by the filter values and then lock the workbook so they cannot accidentally make changes to it. This option makes it easier to go back and forth between views of the pivot table with different filter values applied. In this case, I only have a single filter on the Channel field so I select that filter and click the OK button.Įxcel then generates a separate worksheet for each of the filter values and labels the worksheet tab with the filter value so you can easily click on a table to view the pivot table with the selected filter value applied. When I select this option, Excel displays a dialog that let me pick the filter I want to expand. Then select Show Report Filter Pages… from the Options dropdown menu as shown in the image below. To do this, I can go to the PivotTable Tools menu group and select the Analyze ribbon. However, Excel provides another way to display the pivot table showing the pivot table on a series of pages with each page representing a different filter value. With only four possible values, it would not seem to be a big deal to view each of the channels one at a time. However, by using the dropdown, I can view the pivot table with one or more of the filter values at a time.
However, I also added channel as the filter.īy default, after adding a filter, the pivot table still displays all values for the filter in the table. I then added the dimensions for store and product as my horizontal and vertical dimensions generating a reasonable pivot table. Being a numeric value, Sales Amount makes a good choice as a measure for a pivot table. I added the Sales Amount field to my Values area. Then I click on the Pivot Table command from the Insert ribbon to create a basic pivot table. In the following example, I use a version of the FactSales table ripped from Contoso and I load it directly into Excel as one of the worksheets. In regular pivot tables, you might want to create a pivot table and use one of the dimensions as a filter. Standard pivot table functionality still exists in Excel 2013 and serves as an alternatively tool for simple pivot tables that only require a single data source and less than a million rows of data. One of the interesting things to see when working with Pivot tables is to look at the differences between regular pivot tables that Excel has been able to create since about 1997 with Excel 97 (actually Excel 5 in1993 had basic pivot table functionality, but no wizard yet) and Power Pivot tables which has been a relatively recent addition (Excel 2010 with an add-in).
0 kommentar(er)
