Hide Icons in Pivot Table Grant Total

[firstredpepper]

Hi There,

This forum has been an amazing help in the past few years learning excel. I now a challenge that has been giving me alot of trouble recently and have not been able to found an answer here.

How do you tell excel not to show icons (conditonal formatting) in the grand total row? I can't remove the grand total row because there are other columns that need to be summed up. I thought having a new conditional formatting rule: if the adjacent cell contains "Grand Total" show no icon, but i can't get the formula to work. Any suggestions?

Thank you!
Dave

 

[General Ledger]

firstredpepper,

It seems your logic of entering a condition that "If cell in column A = Grand Total, then no icon" should work. Are you sure this condition is being evaluated first in the list of conditional formats? And that Stop if True check box is checked?

You might also try to put as a first condition that "If the current cell's value = Maximum value in column, then no icon". Theoretically, the row with the maximum value is the row with the total. Of course this assumption could be wrong if the individual rows contain negative amounts.

Best of luck,

G/L

 

[firstredpepper]

Hi there, Thank you for the quick reply! I thought the logic made sense too, and it works well if i do regular conditional formatting using a formula (ie: creating a rule with a rule type of: Use a formula to determine which cells to format), but when I use an icon set i cant seem to get the formula to work. What would you type? It would be nice in the regular conditional formatting the option to add icons because then this would be very easy.

Hopefully we can figure this out!

firstredpepper,

It seems your logic of entering a condition that "If cell in column A = Grand Total, then no icon" should work. Are you sure this condition is being evaluated first in the list of conditional formats? And that Stop if True check box is checked?

You might also try to put as a first condition that "If the current cell's value = Maximum value in column, then no icon". Theoretically, the row with the maximum value is the row with the total. Of course this assumption could be wrong if the individual rows contain negative amounts.

Best of luck,

G/L

 

[General Ledger]

firstredpepper,

I tried with Excel 2013 and it seems fine.

I have a PivotTable where column A has row field names and row 4 has column field names. Column B has summed results for January and a Grand Total in row 14. I selected cells B5:B14 and applied two Conditional Formatting rules:
1. =A5="Grand Total", no format set is applied. I checked the box for Stop If True.
2. =Icon Set of five arrows with Type set to Number and Values I entered.

I then hid some rows of the PivotTable using the filter for column A. This reduced the number of rows displayed in the PivotTable and moved the Grand Total row up. The Grand Total row continued to not have Conditional Formatting applied.

If you are still having trouble, maybe try changing the criteria for the icons. As I said, I used Number. You may have used the default of Percent. Also, are your cell references in the Conditional Formatting formulas referring to the correct cells?

G/L

 

[firstredpepper]

Thank you!! It worked!!! I must have had a different understanding of Stop If True. You are awesome!!

I have one more question - which may be more appropriate to start a new thread but here it goes anyway:

I have 4 option buttons (radio buttons) in a row. They control a calculation done to a cell beside the last button. The user is to fill out some cells in the row, then select a button which does the calculation. The user then goes to the next row and does the same thing. To give some context, this is a budgeting sheet.

Which would be the best option:

1. Manually create these 4 option buttons for the most likely maximum number of rows (probaly 50), each button with its own vba script (so 50x4=200 vba scripts)?
2. Have the buttons dynamically created when a cell in that row has something entered in it and only have one script?

Without a doubt Option 2 would be better, but I do not know how to do something like that (ie: create buttons and have them linked to cells in the same row). Is this complicated?

Thank you!!

firstredpepper,

I tried with Excel 2013 and it seems fine.

I have a PivotTable where column A has row field names and row 4 has column field names. Column B has summed results for January and a Grand Total in row 14. I selected cells B5:B14 and applied two Conditional Formatting rules:
1. =A5="Grand Total", no format set is applied. I checked the box for Stop If True.
2. =Icon Set of five arrows with Type set to Number and Values I entered.

I then hid some rows of the PivotTable using the filter for column A. This reduced the number of rows displayed in the PivotTable and moved the Grand Total row up. The Grand Total row continued to not have Conditional Formatting applied.

If you are still having trouble, maybe try changing the criteria for the icons. As I said, I used Number. You may have used the default of Percent. Also, are your cell references in the Conditional Formatting formulas referring to the correct cells?

G/L