Log In
Register
Home
September 28, 2018
Offline
Hi,
I have a Pivot table on the dashboard. Dashboard background is green. When pivot table is filtered, background behind the Pivot table changes from green to grey. How can i make sure that Pivot table filtering doesn't change the dashboard background behind the Pivot table?
Thanks a lot
Nadia
July 16, 2010
Offline
Hi Nadia,
I would use Conditional Formatting for PivotTables to apply the background colours so that it automatically updates with change to the PivotTable.
Mynda
September 28, 2018
Offline
Hello Mynda,
Thanks for your response. I tried to change Pivot table formatting but still unable to arrive at the result i want. Could you please take a look at the file when you get a chance? i attached the example
Thanks a lot
Nadia
July 16, 2010
Offline
Hi Nadia,
You haven't tried to apply any conditional formatting to that file as I recommended and linked to above, so I'm not sure what you've tried.
1. Select cell A1
2. Then click on the triangle in the top left of the grid between the row number 1 and column letter A to select the whole sheet.
3. Home tab > Conditional Formatting > New Rule > 'Use a formula to dtermine which cells to format'
4. In the Rule Description field enter the formula below (note that the cell reference to A1 is relative, not absolute):
=ISBLANK(A1)
5. Click on the 'Format' button > set the cell fill to the light blue colour
6. Click OK and OK again.
Mynda
September 28, 2018
Offline
Hello Mynda,
Sorry, what i initially tried is to format Pivot table itself using "format only cells that contain" Blanks, so this didnt work (i also tried that contains zero)
I followed your instructions and used =ISBLANK formula for the entire sheet, not just the Pivot table area. Filtered Pivot table for 2 states and the background was blue. yay! However, when i cleared the filter and selected 2 new states, formatting rule no longer worked and the background was white again
Is there a way for the Worksheet to remember the formatting?
Thanks a lot for your help, it is VERY much appreciated!
July 16, 2010
Offline
Hi Nadia,
Looks like the CF 'Applies to' range is being disrupted when the PivotTable changes shape. Not much you can do about this, it's just a "feature" of CF. You have two options:
1. abandon the blue background. It's unneccesary and only ads to the file size for little/no benefit.
2. Find a VBA solution that automatically applies the blue fill after the PT refresh/update. Downside, file must be saved as a macro enabled workbook with the extension .xlsm and when opened, Macros will need to be enabled by the user.
Mynda
1 Guest(s)
