Identify a Pivot Table’s Values Field

Identify Pivot Table Values Field

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:

Values Field by years then values

In the next example I’ve dragged the Value field up and now the data area grouping is for years within data fields:

Values Field by values then years

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:

Values Field Column Labels

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:

Values Field by values called Frodo

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

EDIT:

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”:

? ActiveCell.PivotTable.ColumnFields("Values").Name

And so does this:

? ActiveCell.PivotTable.DataFields("Values").Name

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?

The Function

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
    GoTo exit_point
End If
 
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
        Exit For
    End If
Next pvtField
Set GetValueField = ValueField
 
exit_point:
End Function

Boom! Let me know if you’ve got a better way, anything to add, etc. And, as always, thanks for dropping by.

8 thoughts on “Identify a Pivot Table’s Values Field

  1. Very handy to know, Doug. I have an awful lot of code that checks whether a DataField is called “Values”, as I didn’t know that users could change it. Now I know better, and how to avoid the issue. Not to mention that there’s one more thing that some non-English install won’t balk at.

    Instead of testing each non-data field for a data field with a matching name, I’d do it the other way around: test each data field for a non-data field with a matching name. Quicker. Now…what to do with the millisecond I just saved?

    • I do it in that order, because I can limit to just the DataFields. Otherwise I’d have to go through just the Column and Row fields, or go through all the PivotFields and check their orientation. So I settled for readability over speed.

  2. There’s a potential problem with your code: In the unlikely event that the source data has a field called ‘Values’ in it that happens to be in the Pivot as a Row or Column, then your function returns nothing. You can see this if you add the following line above exit_point:
    If GetValueField Is Nothing Then Stop

    That’s my surname with a ‘d’ on the end: Weird.

  3. Although I like your intricate thought process ;), I’m using Office 2010 and simple DataPivotField (“PT.DataPivotField.Name”) works for me..
    It’s possible that it might not be so in all Office versions…

    • Hi Petra. Thanks! I think you’ve pointed the way to a simpler method. The big caveat is that PT.DataPivotField is always something (Not Nothing) even if the Values field isn’t actually visible/used in the pivot table, i.e., a pivot table with just one data field. For example, create a pivot table from a two-cell range and you’ll see that it still has a PivotDataField named Values.

      So, to determine if the Values field is visible you need to test whether it has a Range, e.g.,  Set TestRange = .DataPivotField.DataRange. Wrap that in On Error statements and then test if TestRange is nothing. Still much simpler than my lovely, intricate code. At least that’s my verdict after five minutes of testing. What do you think?

      • Hi Doug, you’re right, I wasn’t that thorough with my thinking. (I never am ;))
        But then again, let me suggest a simpler method – you can determine whether the field is visible or not by testing .DataPivotField.Orientation – zero means the pivotfield is not visible. No evil On Error wrapping necessary 😉
        Does that work for you or is there any issue I overlooked again? 🙂

Speak Your Mind

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>