6 Replies Latest reply on Apr 4, 2012 1:40 PM by Ben Neville

    Is there a [Red] custom number format like Excel?

    Andy Kriebel

      I'm looking to use a customer number format like this "$"#,##0;[Red]("$"#,##0).  This is the format Excel uses, and Tableau accepts the format, but it does not display negatives with red text.

       

      Is there a different syntax to use or is it not an option?  I know there are lots of workarounds, but I'm interested in this specific format.

        • 1. Re: Is there a [Red] custom number format like Excel?
          Ben Neville

          Hi Andy,

               Have you tried looking at http://community.tableau.com/thread/109843? It has to do with dragging your number format option onto the color and adjusting it this way. Once you have put your number measure on "Color", you can click the drop-down by the legend, select "Edit Colors..." and select a palette from the drop down. You then adjust the stepped colors to 2 steps, and that should give the desired result. The above forum thread suggests red-green diverging, but as I assume you would like normal text to show up as black, so I would suggest the Red-Black Diverging option. Let me know how this works out.

          • 2. Re: Is there a [Red] custom number format like Excel?
            Andy Kriebel

            Hi Ben.  I am familiar with this option, but that's not what I'm looking for.  I'm wondering if there is a specific custom number format available.

             

            Think about have bars that are colors by product type, but you are labeling them by sales difference to budget.  You might want the text label to be red for negative numbers and black for positive.  I don't have the option drag the difference measure onto the color shelf.

            • 3. Re: Is there a [Red] custom number format like Excel?
              Ben Neville

              There is not a specific format available to my knowledge, but I did mock up a short workaround. The final product utilized the aforementioned 2-stepped color divergence, 2 charts (1 bar, 1 text), and a dual axis to get the text totals to the corresponding bars. They don't line up as neatly as I would like, so the transparency has to be adjusted to more easily see the labels, but this is the only way I know of to accomplish this. Attached is a screen-shot example.

               

              Color_Text_Label_Sample.png

              • 4. Re: Is there a [Red] custom number format like Excel?
                Andy Kriebel

                I've done this technique before too.  It works ok, but still, it's not as simple as a customer number format.  At the Atlanta Tableau User Group meeting today someone mentioned there was a custom formatting code, but they couldn't recall what it was.

                 

                Hopefully one of the Tableau developers will chime in here.

                 

                Thanks for the great responses Ben!

                • 5. Re: Is there a [Red] custom number format like Excel?
                  . Autoanalytx

                  I'd like to pile on here.

                   

                  I am using the color shelf for another visual - the source of the data - internal versus external, so the bars are red or blue.

                   

                  So the color shelf is not a good option.  I'd like to be able to format the actual numerical value associated with each bar as black or red, depending whether it is negative or not - like the excel functionality.

                   

                  Thanks for listening...

                  • 6. Re: Is there a [Red] custom number format like Excel?
                    Ben Neville

                    I'm still not aware of any custom formatting code, not to say that one doesn't exist, but actually .Autoanalytx, what you are proposing sounds to me like exactly what is being done in this case if you look at the viz screenshot. While the bar colors are tied to product category, the text is in fact tied to the value of the number using a dual axis with different chart types (bar and text). I just used a 2-step diverging in the color palate for the second chart type (text). This enables the use of color controls for multiple measure/dimension values.