7 Replies Latest reply on Dec 31, 2015 9:06 AM by Gianni Ceresa

    Conditional formatting to grand total in pivot table

    user12863665

      i am using 'strike through' in column formatting if they are nulls.

       

      but when i do the grand total in pivot view i am again getting nulls in grand total column insted of strike through.

       

      suggest me how can i get 'strike through' for grand totals in pivot view?

        • 1. Re: Conditional formatting to grand total in pivot table
          Gianni Ceresa

          Hi,

          "strike through" ?

          Do you mean you set "strike through" as an effect on a NULL value (so in theory an empty cell) ?

          Or you mean that you want to see the "minus sign" or dash when values are NULL ?

           

          If it's the second you can have the same in grand totals by going in the "data format" tab of your column properties and set a custom numeric format.

          Look at the inline help to see the syntax of this format mask.

          Just for example here is a possible value (from the help): "+"#0.000;"-"#0.000;"null"

          So display "+" in front of positive numbers, display "-" in front for negative numbers and display the text "null" in case of null values. If you put a dash instead of "null" this will apply to your grand total as well.

          • 2. Re: Conditional formatting to grand total in pivot table
            user12863665

            Thanks for the response.

             

            i Want 'strike through' for grand totals if they are null.

             

            on original column i mentioned 'Strike Through' in data format.

             

            but in pivot view there is no option of 'data Format' for grand totals.

             

            Please suggest me how can i get 'strike through' for grand total column in pivot view if the vales are null

            • 3. Re: Conditional formatting to grand total in pivot table
              Gianni Ceresa

              Sorry but again the question is the same: how is your "strike through" supposed to looks like?

               

              This is what "strike through" is : I'm the style strike through

               

              Do we agree?

               

              So if a column is NULL (= no value) what do you want to see? Because nothing with a strike through style is still nothing.

              • 4. Re: Conditional formatting to grand total in pivot table
                user12863665

                IF the value is null for grand total I want to see ' -' in that row.

                • 5. Re: Conditional formatting to grand total in pivot table
                  Gianni Ceresa

                  So go back to my first reply and you have your solution: forget conditional formatting and set a custom format on the column defining the '-' as the 3rd mask (1st = positive value, 2nd = negative value, 3rd = NULL value).

                  • 6. Re: Conditional formatting to grand total in pivot table
                    user12863665

                    I Am creating grand total in pivot view.

                     

                    please find pivot view output structure below At present:

                     

                    DImention column   Fact Column   Grand Total

                    Tevevision                  100               100

                    Smartphones.              -

                     

                    in the above pivot view output, I am applying strikethrough (if the value is null ) on fact column, that's why it's showing strike through or minus on smartphones row in fact column.

                     

                    but when I create grand total in pivot view I am not getting strikethrough or minus sinice there is no way I can add conditional format for grand total.

                     

                    can you suggest is there anyway I can get strike through or minus for grand total column in the pivot view If the value is null?

                     

                    EXpected output:

                    DImention column   Fact Column   Grand Total

                    Tevevision                  100               100

                    Smartphones.              -                    -

                     

                    THanks

                    • 7. Re: Conditional formatting to grand total in pivot table
                      Gianni Ceresa

                      Can you please read what has been posted and if you don't understand it ask something related to that? Your answer is in the first post 6 days ago ...

                      Capture.PNG

                      Capture1.PNG

                      You must not use conditional formatting but edit the "Column Properties" of your fact column to set a custom "Data Format" and there you can define the format for positive values (first mask), negative values (second mask) and null values (third mask). This format will be used in Grand Total as well.

                      For more infos on the available formats / syntax / options of a custom data format mask click the "help" icon on the top-right of the popup and read there about the format and how to use the custom data format.