Pivot Table Background Cell Color

[nms2130]

I have selected all cells and made them white. I insert a pivot table with a report filter. When one of the filters is selected the pivot table changes size. When this happens, cells with no formatting appear beneath the pivot table. Is there a way to make sure that every cell not part of the pivot table retains its color formatting regardless of filters?

Thanks in advance.

[mbwd]

This thread was never responded to, but I have the same exact problem. Does anyone know how to stop the Pivot Table from changing the formatting of the cells that it touches when a filter is applied and the list extends and contracts?

In my case, I have the entire sheet with a color background fill. I also have a user-defined Pivot Table style of the same background, with no lines. So everything looks great until I apply a filter with a long result list -- and then go back to another filter with a short list. The result is that the cells below the short list, where the long list was, are now "white" or "no formatting."

THANKS!

[mbwd]

I thought a few images would help illustrate the problem:

Before filtering:

Capture2.JPG

After filtering:

Capture.JPG

[Mothergreen]

Give this a try and see if it works.

1. Make sure your PivotTables are completely expanded no filters selected.
2. Next highlight either the entire visible window or specific cells that the PivotTables fall on.
3. Go to Conditional Formatting and click on create a New Rule.
4. Select Format only cells that contain and under Format only cells with next to Cell Value change Between to Equal To
5. place in the final cell: =""
6. Go to Format and select the color you would like as your default background.

Note:: if you don't place an = at the beginning excel will think you are specifically looking for quotation marks. Also make sure that your PivotTable is void of blanks as this conditional formatting may effect them as well.
Either filter blank cells out or go into the PivotTable Options and indicate something for empty cells. Should be under the Layout & Format.

I hope this helps.

[ThatMathGuy]

I know this thread at this point is old, but generated a report with a dashboard and faced this exact issue. I wanted the fill for each cell to be gray so that my pivot table styles would stand out cleanly against the background. My pivot table was limited to the top 10 of a certain metric, we'll call it sales $, but some criteria would only produce say, 2 or 3 results. So I had a case where I could position my dashboard layout and create these interactive tables with slicers, but the fill issue was nerveracking to say the least. The conditional fill tip posted by mothergreen I think is a good start, but only works for the row label parts of the pivot, and anything that goes in as a value remains an issue (where the fill color is deleted once the pivot table expands and then contracts). I'm considering making a Workbook_Change macro that finds the pivot table range and addresses any affected cells, but if anyone has other suggestions for this please help! I'm using Excel 2010, and I can't help but think that with the addition of slicers in tables in newer versions that this may be taken care of already, but still. Helps us old school guys out!

[xlnitwit]

Hi,

If you alter the Normal cell style to use a gray fill colour, that should fix the issue, though you will need to apply a manual 'no fill' (or a new custom style) to cells in the other sheets.