none
conditional formatting in pivot table excel 2010

    Question

  • I have the pivot table which has column A and B in the data fields, and I would like to  conditional format the column B based on the value from column A - column B, is there any simple way to implement this?
    Wednesday, March 23, 2011 12:45 AM

Answers

  • Hi,

    According to your description of the problem, I think your issue is that conditional format the column B based on the value from column A-column B is not possible to achieve, because the all field of your workbook will automatically be generated in the pivot table after you create a pivot table.

    Based on my analysis of the issue, I can give you some other suggestions.

    You can add a new field in your pivot table, and set its value based on the value from column A-column B using the CalculatedFields() method.

    Code sample:

    private void Sheet1_Startup(object sender, System.EventArgs e)

            {

    Excel.PivotCache pc1 = Globals.ThisWorkbook.PivotCaches().

                  Create(Excel.XlPivotTableSourceType.xlDatabase, this.Range["A1", "B4"]);

                Excel.PivotTable pt1 = pc1.CreatePivotTable(this.Range["F4", missing], "test",

                    missing, missing);

                pt1.AddFields("A",missing,missing,missing);

                Excel.PivotField pfnew = pt1.CalculatedFields().Add("z", "=A-B", false);
                       //Add a new field z and set its value.

              }


    SeeSeaIng
    • Marked as answer by Bruce Song Monday, April 04, 2011 1:34 AM
    Wednesday, March 23, 2011 8:17 AM