Modifying PivotTable Styles to include grouped gridlines

Hi Ed,

Thanks for your reply. We've already tried that method but as I mentioned above "we can't figure out which Pivot element needs teh gridline settings. We've tried all combinations but to no avail. The closest we got was to add Inside and Outside gridlines to the whole table and then Outside gridlines to the Row Subheadings but that didn't work."

We know how to create a new PivotTable Style or to Duplicate/Modify a PivotTable Style. We're looking for the specific setting within the PivotTable Style format options that would put gridlines around the groups and subgroups all the way through to the values.

When you say "At this stage you can format each element of the PivotTable Style", do you know what the setting would be? I'm an Office MCT and I'm stumped on this one.

Thanks,

Vickie

Hi,

If all you want to do is change the gridline color then set the style to None and change the worksheet's gridline color - choose File, Options, Advanced, and locate the Gridline Color.

I might add, if I understand correctly, that if you only want the gridlines at the bottom and top of each group and subgroup then you will notice that none of Excel's built-in styles do that.  The implication then is that it can't be done using styles, even custom styles.

However, you could use VBA to do it.  Once you have written the code you will want to add it to a PivotTable_Update macro.

Unfortunately, the link in Ed's reply isn't working for me. 

I still have this issue.  Did you ever figure a way to get the gridlines without using a Macro or VBA code?

Thanks,

Bill MacLean

For what it's worth, and it sounds like you're trying to do what I just spent way-more-time-than-I-should-have-spent to figure this out, in Excel2010 on the Design Tab of the PivotTable Tools, in the PivotTable Style Options, just make sure all 4 (Row Headers, Column Headers, Banded Rows and Banded Columns) are checked. At least that's what got the auto-gridlines to show up for me in any of the Styles without having to add borders.

Thanks,

Matt

For what it's worth, and it sounds like you're trying to do what I just spent way-more-time-than-I-should-have-spent to figure this out, in Excel2010 on the Design Tab of the PivotTable Tools, in the PivotTable Style Options, just make sure all 4 (Row Headers, Column Headers, Banded Rows and Banded Columns) are checked. At least that's what got the auto-gridlines to show up for me in any of the Styles without having to add borders.

Thanks,

Matt

One way to achieve a substantial part of the goal is to "cheat" and use conditional formatting within the pivot table.

1) Click on the cell within the pivot table column you wish to format

2) Select conditional Formatting (Home -> Conditional Formatting)

3) Create a new rule

4) Select the 2nd button within the Apply Rule To:  radio button group representing the pivot column you wish to format

5) Select rule type "Use a formula to determine which cells to format"

6) enter "=True" (without the quotes) for the formula. This is the trick that will force Excel to format every cell in the column regardless of the value

7) Click on the "format" button and format as you see fit.

8) press OK to save the change

hope this helps.

Eric

PivotTable Tools > Design tab > Report Tab > enable Show in Tabular Form.

Easy Approach

Something I did was use conditional formatting on the section or the entire page (might want to do entire page since pivot tables can expand contrast. This is what I did: 

1. Pivot in Tabular format - As a general rule, I think Pivot Tables are best laid out in tabular format - Pivot Tables are sourced from tables, an I've found most people I've presented information to like seeing the Pivot's summary information in a similar way. To do this, go to the "Design" tab, at the far left, click on "Report Layout", then "Show in Tabular Format". For your purposes, you also want to select "Do not Repeat Item Labels" because the conditional formatting will basically look for blank cells.

2. Create your own Pivot Table - From the Design Style selection, choose "New Pivot Table Style" and from the design menu, chose "Whole Table" and make it so the whole table has borders (I prefer only horizontal ones, myself). Change anything else you prefer for the look like giving the Header and Grand Total rows a different color, but leave it so that the middle is no fill/white

3. Conditional format blank cells to have a white border - I selected all cells on the sheet, and made it so that any cells that were blank would have the TOP border white. What this does is make the row group look like a merged cell. Go to "Conditional Formating" on the Home tab, select "New Rule" then "Format only Cells that Contain", then make "Cell Value" "Equal to" then type in ' ="" ' (that's equal, quote quote - or basically equals blank). Then click on "Format", select the "Border" tab and then make it so the TOP border is White.

This gets you roughly what you're looking for. Though it won't band your rows, it will give the effect of grouping them.

More complicated approach

To band them, you could make a formula that basically alternates the words in the first column between 1 and 2, and conditional format on the 2 such that any row that has a 2 will be filled a color. Here you Would want the rows to repeat in the tabular format. So in this case, if the first column has groups A, B, and C. If you sort it so that Group A all appear together, then they would get a 1, the next Group (B) would get a 2, and C would get a 1 again. This needs a pretty specific formula to make it work both to get the 1 and 2s, And for conditional formatting, so if you'd like just message me and I can walk you through it, but the easier approach should suffice as a quicker solution.

Hope this helps,

Daniel