by contextures.com
In some of the pivot table articles that I post here, there is sample code that you can use in your own files. Sometimes I show the code sample here, and you can copy and paste it into your workbooks. Other times, I give a link to a file that you can download, and copy the code from that.
If you’re not an Excel programming expert, here are a few tips for copying the Excel VBA programming code to your workbook.
When you create a pivot table, Excel automatically makes the value fields either a SUM or a COUNT, depending on the type of data in that field. You’re not stuck with that function though — if you don’t like the summary function that Excel selected, you can change it.
Unfortunately, you can’t add hyperlinks to a pivot table, even if there are hyperlinks in the pivot table’s source data. In the screen shot below, the Hyperlink command is not available, because a pivot table cell is selected.
Continue reading “Create Fake Hyperlinks in Excel Pivot Table”
One of the advantages of summarizing your data in a pivot table is that you can filter the results, to focus on specific items.
After you create a pivot table, you can add or remove fields by using the check boxes in the field list. Text fields are automatically added to the Row Labels area, and numeric fields go into the Values area.
After you set up a pivot table, you might want to prevent other people from making changes to the way that it is set up. With a bit of programming, you can restrict what happens to a pivot table.
In Excel 2010, slicers were added to Excel, and they make it easy to select one or more items in a pivot table’s Report Filter.
With a pivot table’s Report Filters, you can select one or more items, and see the summarized results for those items only. For example, you might want to see the sales in one region, or on a specific date.
You can also click on “(All)”, at the top of the items list, to see the overall results.
In some pivot tables, you might want to prevent people from selecting the "(All)" option in a Report Filter.
In this example, the worksheet shown below has GetPivotData formulas that refer to the Report Filter selection in cell B1. If (All) is selected in cell B1, the formulas show a message, instead of the dollar amounts.
Unfortunately, you can’t remove the (All) option from the report filter’s drop-down list, but you can use Excel VBA to block users from selecting it.
The code shown below will undo the report filter change, if (All) is selected or if someone checks multiple items in the filter. They’ll also see a message that explains what to do — "Please select a single date."
This code is stored on the pivot table’s worksheet module, and can be activated by selecting from the filter drop down list.
If someone selects “(All)” from the OrderDate report filter’s drop-down list, they’ll see the warning message.
To download the sample file, which contains the pivot table and VBA code, please visit my Contextures website: Pivot Table Report Filters VBA. The zipped file is in Excel 2007/2010 format, and you will have to enable macros when you open the file.
_________________
When you add a field to the Row Label or Column Label area of the pivot table, its heading cell shows a drop down arrow. When you click the arrow, you’ll see Sort and Filter options, for the selected field.
In the screen shot below, you can see the sort and filter options for the Product field, and the check boxes for manually filtering the list.
If you want to prevent manual filtering, you can hide the drop down arrows, by using a bit of Excel VBA programming.
The following code will hide the drop down arrow for every field in the first pivot table on the active worksheet.
Sub DisableSelection()
Dim pt As PivotTable
Dim pf As PivotField
Set pt = ActiveSheet.PivotTables(1)
For Each pf In pt.PivotFields
pf.EnableItemSelection = False
Next
End Sub
After you hide the arrows, you will still be able to sort the pivot items, by using the commands on the Ribbon.
You’ll also be able to use the Label Filters and Value Filters, if you right-click on an item in the disabled field.
After you hide the arrows, you can use similar code to show them again — just change the EnableItemSelection setting to True.
The following code will show the drop down arrow for every field in the first pivot table on the active worksheet.
Sub EnableSelection()
Dim pt As PivotTable
Dim pf As PivotField
Set pt = ActiveSheet.PivotTables(1)
For Each pf In pt.PivotFields
pf.EnableItemSelection = True
Next
End Sub
To see the different types of filters – label, value and manual – please watch this short video tutorial.
Or watch on YouTube: Apply Multiple Filters to Pivot Table Field
________________
When you create a pivot table, it is based on a pivot cache, with all of the records from the source data table. You can’t see the pivot cache, but with some programming, you can get information about the cache.
In the pivot table shown above, you can see the total quantities for all the records in the source data. To see how many records are in that source data, you can create a User Defined Function.
To create the User Defined Function, copy the following code, and paste it into a regular code module.
Function GetRecords(rngPT As Range) As Long
'pivot table tutorial by contextures.com
Dim pt As PivotTable
Set pt = rngPT.PivotTable
GetRecords = ActiveWorkbook _
.PivotCaches(pt.CacheIndex).RecordCount
End Function
This creates a function named GetRecords, and it requires a cell reference as its argument. If the referenced cell is in a pivot table, the function will show the record count for that pivot table’s pivot cache.
Note: If you save this file, make it a macro-enabled file type.
Then, on a worksheet in that workbook, enter a formula that uses the function name, and refers to a cell in the pivot table. For example, enter the following formula in cell B1, referring to the pivot table in cell A3:
=GetRecords(A3)
The formula result is 825, which is the number of records in the source data. You can see the last record in the screen shot below. It’s in row 826, and if you subtract 1 for the heading row, that is record number 825.
______________