When I look at the info in the conditional formatting rule for that table (or any of them that I have) the original data range of $L:$Q then changes to $L:$L;$Q:$Q.
To get it to stay you must conditional format each part of the pivot table separately, one conditional rule for the rows section (the labels on the left) and one for the values section (on the right). I am confused as I do not have an option tab in Excel 2013, so I can go no further than this step.4. My actual problem is, when I refresh the data, my "Row Labels" disappear. If you are still having issues after these tips try removing all filters - set the formatting and check the box to preserve - then add the filters back at the end. In summary, it looks like there are two things to do to get Excel to keep number formats in a Pivot Table: First, right-click in the Pivot Table and in Pivot Table Options select "Preserve cell formatting on update." Second, click on the field name cell in the top row of the table (NOT on the Excel column header).The conditional formatting is set to change the entire line a certain color based on the % in Col Q.The conditional formatting works until the data is updated for new month.Clicking the black down arrow so that specifically the pivot table was selected and THEN following those steps caused the formatting to stick as intended.Not great to do it on lots of different tabs in the same spreadsheet, but at least next time I'll know to follow those steps before I start copying and editing full sheets. I have pivot tables that when the data is refreshed loses the conditional formatting in the cells.