Dynamic Formatting in Xcelsius Tables

8

Xcelsius’ Excel paradigm for binding data inherits Excel’s absence of dynamic cell formatting. In Excel, you define a cell format as text, percentage, value, date, etc… When you bind a component to a cell or range, it will automatically inherit the formatting. If your hoal is to filter data by measures, you may have found that there is no way to dynamically change number formats on the fly. For example, if you have a table that is filtered by sales, volume, and growth %, dynamic formatting becomes a problem. While there is a good workaround for displaying values in a table, there is no good option for graphs.

The solution for displaying dynamically formatted tabular data is converting values to text using the TEXT() formula. The beauty of the text formula is the option to define number formatting. TEXT(value,text_format)

TEXT(A1,$#,##)
TEXT(A1,##%)
TEXT(A1,#,###)

The following example illustrates how this text formatting can be utilized to present tabular data in the correct format without using multiple components. Hopefully at some point we will see this dynamic formatting built into Xcelsius as standard functionality for charting. One of the few drawbacks of Xcelsius’ Excel paradigm is the borrowing of Excel’s lack of dynamic cell formats. In Excel, you define a cell format as text, percentage, value, date, etc… When you bind a component to a cell or range, it will automatically inherit the formatting. In most cases this is a good feature. In others where you are filtering data by measures, it creates a problem. For example, if you have a table that is filtered by sales, volume, and growth %, dynamic formatting becomes a problem. While there is a good workaround for displaying values in a table, there is no good option for graphs.

The solution for displaying dynamically formatted tabular data is converting values to text using the TEXT() formula. The beauty of the text formula is the option to define number formatting. TEXT(value,text_format)

TEXT(A1,$#,##)
TEXT(A1,##%)
TEXT(A1,#,###)

The following example illustrates how this text formatting can be utilized to present tabular data in the correct format without using multiple components. Hopefully at some point we will see this dynamic formatting built into Xcelsius as standard functionality for charting.

Download Source Files

Share.

8 Comments

  1. Andries Schuttinga on

    Hi Ryan,

    I really like your inventive formatting trick. I have been using it to distinguish between data like hours flown (eg 450), payloadkm (eg 234,500) and a % (eg 28%) in a line chart.
    The funny thing is that it does show the hours flown, but not the payloadkm and the percentage. If it would be a problem of not recognising text as a figure, you would expect all to be ignored, but to have the hours shown and the others not, is weird.
    Any idea why?

    Andries Schuttinga

  2. Hi i am unable to see % and $ on the chart.I am looking to present this all dynamically but i can see only the number format but not the % and $.
    Apperciate if any one could of you could give your suggestion on this..

  3. Hi Ryan,

    I try the formatting trick for my example, in the workspace view the table shows the data in the format expected, but in the preview mode, the format of the data changes, and I don’t know why
    I want that my tables shows the data in a special currency formatting,
    example 700.00 DZD in the preview mode it shows 0.70.

    Can you please help me to solve this matter.

    Tahnks

  4. Unfortunately this will only work in table components. The Xcelsius charting components will only recognize values and not text. When you use the TEXT formula, it causes Xcelsius to not recognize the numbers. This is a limitation of Excel that gets inherited by Xcelsius. The only option I have found for this scenario is still to use multiple charts for each number format.

  5. Srinivas Dandamudi on

    I have applied this trick, but this is working fine in the table or List view Components. I have tried to apply this chart it shows the Number format only, it doesn’t shows the percentage(%) format in chart..
    Please let me know any idea?

Leave A Reply