We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date
close icon

Conditional Formatting on a Pivot Table

Hello ~

I am trying to put conditional formatting on a pivot table using this:

IPivotCache cacheMyPivot = workbook.PivotCaches.Add(sData["A4:F13281]);

IPivotTable pivotMyPivot = pivotMyPivot.PivotTables.Add("Remaining Locations", pivotMyPivot["A4"], cacheMyPovit);

IConditionalFormats condition = pivotMyPivot.Range["E2:E5000"].ConditionalFormats;
IConditionalFormat condition1 = condition.AddCondition();
condition1.FormatType = ExcelCFType.ColorScale; 

In the spreadsheet that is created, the conditional formatting on the column is there, but only on the rows outside of the pivot table.  See screen print below from created spreadsheet.  How can I get the formatting on the pivot table itself?      

Thank you!

5 Replies

KC Karthikeyan Chandrasekar Syncfusion Team November 13, 2018 12:47 PM UTC

Hi Steve, 
Thank you for contacting Syncfusion support. 
As per Microsoft Excel behavior, when a pivot table is added in a range where already data exist then the conditional format address in that range will be split into range above and below pivot table. This is the reason for the conditional format address change in the worksheet. So, we suggest you to set the pivot table location below the pivot table data range. The following notification pop up will be displayed in Microsoft Excel when this scenario occurs. 


If you click “Yes” then the data will be changed, otherwise the pivot table will not be inserted. 

Please let us know if you have any other queries. 

SG Steve Good November 13, 2018 04:07 PM UTC

Thank you for the suggestion.  I put the pivot table in a new tab, starting in cell A1 and now the formatting is below the pivot table.  Is there a way to get the formatting on the pivot table?  I can do it in Excel after the spreadsheet is created.  Is there a way to do that in code?

KC Karthikeyan Chandrasekar Syncfusion Team November 14, 2018 12:17 PM UTC

Hi Steve, 
We suspect that the conditional formatting is not applied because of Microsoft Excel refresh the layout of the pivot table while opening the output document. So, we suggest you to use IPivotTable.Layout() method before saving the workbook.  
We have shared a simple sample to achieve your requirement which can be downloaded from the following location. 
Kindly try this and let us know whether it helps. 

SG Steve Good November 14, 2018 06:38 PM UTC

I tried that and it did fix the conditional formatting on the pivot able.  However, it removed the Tabular style that I had added earlier in the program with this line:

myPivotTable.Options.RowLayout = PivotTableRowLayout.Tabular;

I did try to put that line back in after the myPivotTable.Layout() call, but it still showed them grouped.

KC Karthikeyan Chandrasekar Syncfusion Team November 15, 2018 11:52 AM UTC

Hi Steve, 
We are not able to reproduce the issue from our end. So, we request you modify the sample provided in our previous update and share us along with issue reproducing input files. It will be helpful for us to analyze further on this and provide you the solution at earliest. 

Live Chat Icon For mobile
Up arrow icon