Over the next few posts I plan to delve into a couple of functions I’ve written to identify areas in a pivot table. I also want to do a quick post on a pivot quirk I noticed recently. I then plan to roll it up into a post on my new-and-improved per-pivot-item conditional formatting tool. It’s good to have plans, right? Anyways, let’s get started with a function to identify a pivot table’s Values field.
I deduced the following just by messing around – I couldn’t find anything on the web about identifying a Values field. If I got something wrong, or if you have a better way to do this, please leave a comment.
What is a Values Field?
The Values field is the one that appears when you have more than one data field. Its location in the Rows or Columns area of the pivot table dialogs controls the grouping of those data fields. In the following example, I’ve grouped the data area by data fields within years. In other words, the two summing data fields appear side-by-side for each year:
In the next example I’ve dragged the Value field up and now the data area grouping is for years within data fields:
Some pivot table layouts, such as the one below, don’t show the word “Values” anywhere in the pivot table, but it still shows in the pivot table dialog:
Like all pivot fields, the Values field can be renamed. Note that though I changed it to “Frodo” in the pivot table, it still says “Values” in the dialog:
Everything I’ve said about the Columns area of the pivot dialog applies to the Rows area. The Values field behaves the same way there.
Identifying the Values Field in VBA
In the comments below Petra identified a much faster way using PivotTable.DataPivotField. DataPivotField contains the Values field, whether or not it’s visible. So,
If DataPivotField.Orientation <> 0 tests whether the Values field is present.
So, anyways, I wanted a VBA function that returns a pivot table’s Values field if it has one. When figuring out how to do this I asked myself:
Is the Values field a PivotTable.DataField or a PivotTable.ColumnField/RowField?
The answer is both, kind of. So, for instance, in the examples pictured above typing the following into the immediate window returns “Values”:
And so does this:
So it looks like the Values field is both a data and column (or row) field. To further confirm this, note that this statement returns True:
? ActiveCell.PivotTable.DataFields("Values").Orientation = xlColumnField
So, even though it’s both a Data and Column (or Row) field it looks like it’s a bit more of a Column field (I’m going to stop saying “or Row” now). This is backed up by the fact that you can’t refer to it’s Data personality using an index. In other words, the following returns an error:
? ActiveCell.PivotTable.DataFields(3).Name (1 and 2 return the two other data fields)
Furthermore, if you check the
DataFields.Count for the example above the count is only two.
Cutting to the Chase
In addition to the above, I’ve got one more informational tidbit: if you change the name of the Values field to “Frodo,” both its Data and Column selves refer to themselves as “Frodo.” So even though, as we’ve seen above, the dialog box continues to use the word “Values” to refer to this field,
? ActiveCell.PivotTable.DataFields("Values").Name gets you a runtime error 1004.
This means that you can’t just refer to the values field using “Values” in either its DataField or ColumnField version. If you do and a user changes its name you’re out of luck.
Fortunately, this has an upside, and it’s not just that I have something to blog about. It means that a Values field name is the only field name in the pivot table that can be repeated for a Data field and a Column field. Usually two fields can’t have the same name. For example, in the examples above if you try to rename “Year” or “Values” to “Sum of Unit Cost” you’ll get a “Field name already exists” error. But in the case of a Values field both its Data and Columm/Row references will be the same name.
This means you can identify a pivot table’s Value field by finding a row or column field that has the same name as a data field. Cool, eh?
Function GetValueField(pvt As Excel.PivotTable) As Excel.PivotField
Dim pvtField As Excel.PivotField
Dim TestField As Excel.PivotField
Dim ValueField As Excel.PivotField
'If there's only one data field then there won't be a Values field
If pvt.DataFields.Count = 1 Then
For Each pvtField In pvt.PivotFields
On Error Resume Next
'test each non-data field for a data field with a matching name
Set TestField = pvt.DataFields(pvtField.Name)
On Error GoTo 0
If Not TestField Is Nothing Then
'if there's a match then you've got the Values field
Set ValueField = pvtField
Set GetValueField = ValueField
Boom! Let me know if you’ve got a better way, anything to add, etc. And, as always, thanks for dropping by.