Category Archives: Functions

In my last post I talked about identifying pivot table’s Values field, if it had one. That function plays a part in this post, which is shows two functions for determining if a pivot field has visible subtotals. As with the last post, I didn’t find much about this on the web. I even asked my first Excel question on Stack Overflow. After a bunch of experimentation I came up with a function that seems to always work. And then, whaddaya know I came up with a better one. I use the second function in my improved per-item conditional formatting utility, which I will post about soon.

What Do I Mean by “Visible Subtotals?”

In the picture below the pivot table is set to show subtotals for every field. However subtotals are actually visible only for the Region field. There’s none for the Items field, which makes sense since Items is the rightmost field, and its subtotals would just be a repeat of the individual item values:

The VBA Subtotals Property Does Half the Job

The first thing you might try in VBA is checking the pivot fields Subtotals property. However below you can see that it returns True for both fields. The issue is the same as above: Subtotals are turned on but they don’t show for the rightmost field:

My First Attempt

So, I wrote some code that:
1. Checks if a field’s subtotals are turned on. If not, the function returns False.
2. Checks if any fields with the same orientation as the field we’re checking is a Values field
3. Tests if the field we’re checking is in the last position for its orientation (including the Values field). If not, then subtotals are on and the function returns True.

A Better Way – PivotCell to the Rescue

The above seems to work fine, but it’s got kind of a feel-your-way-in-the-dark aspect to it. I would much rather just have some code that examines the actual pivot table and figures out whether a given field is currently showing any subtotals. Happily, I have found a way to do this.

It’s based on the Range.PivotCell object and its PivofField and PivotCellType properties, all of which go back to Excel 2003, according to this MSDN page. They allow you to cycle through a pivot table’s cells checking for ones with a PivotCellType of xlPivotCellSubtotal (or xlPivotCellCustomSubtotal ) and, if so, checking what PivotField the subtotals belong to. I’ll discuss this some more after the VBA.

The Code

How it Works

The code above actually only checks the pivot table’s ColumnRange and RowRange. These ranges are highligthed in the picture below. The code checks this area for cells with a PivotCellType of subtotal or custom subtotal. There are 10 PivotCellTypes, nine of which can be found in the ColumnRange or RowRange areas (the data area of the pivot table consists just of the xlPivotCellValue type.

The picture below highlights the cells with a PivotCellType of either xlPivotCellSubtotal or xlPivotCellCustomSubtotal. The custom subtotals are ones such as Min, Max and Average. These can be set in the field options menu. If the code finds a cell whose PivotCell.PivotCellType property is one of these two it then checks the cell’s PivotCell.PivotField object for a match with the field passed to the function.

I Like PivotCells

I’ve used the Range.PivotTable object quite a bit over the years. But it’s just recently that I’ve delved into the Range.PivotCell property. Hopefully I’ve given you some ideas for how you could use it to poke around in pivot tables.

Have you used the Range.PivotCell property? If so, leave a comment (I also love comments, especially the ones that add to my knowledge and don’t require me to do anything but say “thanks”).

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

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

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:

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:

(1 and 2 return the two other data fields)

Furthermore, if you check the

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,

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

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