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
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Conditional Formatting on a Pivot Table

Thread ID:





140872 Nov 12,2018 04:57 PM UTC Nov 15,2018 11:52 AM UTC ASP.NET Core - EJ 2 5
Tags: XlsIO
Steve Good
Asked On November 12, 2018 04:57 PM UTC

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!

Karthikeyan Chandrasekar [Syncfusion]
Replied On 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. 

Steve Good
Replied On 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?

Karthikeyan Chandrasekar [Syncfusion]
Replied On 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. 

Steve Good
Replied On 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.

Karthikeyan Chandrasekar [Syncfusion]
Replied On 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. 


This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Please sign in to access our forum

This page will automatically be redirected to the sign-in page in 10 seconds.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

Live Chat Icon For mobile
Live Chat Icon