When formatting colour of cell or text
Make sure “All” is selected on all the pop-down boxes before you do the formatting or else it loses formatting when you change selection
General tip
Right-click table -> table options -> deselect “AutoFormat table”
Also check “Preserve formatting”
Cell borders – problem!
Format of cell borders is NOT retained when you change the layout or refresh a PivotTable.
(http://support.microsoft.com/kb/162028/en-us?spid=2512&sid=298)
Solutions:
1) select every horizontal item in turn and create a bottom border
& select every vertical field in turn & create a right border
or
2) Macro to give light grey borders to all cells in pivot – courtesy of
http://windowssecrets.com/forums/showthread.php/112055-pivot-table-gridlines-(Excel-2003-SP2)
When you refresh the pivot it will apply light grey border to all cells
- Right-click the sheet tab of the worksheet that contains the pivot table.
- click View Code
- Paste the following:
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
With Target.TableRange1.Borders
.LineStyle = xlContinuous
.ColorIndex = xlColorIndexAutomatic
.Weight = xlThin
.ColorIndex = 15
End With
End Sub
