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.
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.
Great Blog, Mate! Between my better half and I we are constantly on the prowl for new and exciting lingerie which led me here. I certainly plan on visiting again! Cheers
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
PS: Look at your own example and add a line chart to it. The volume line will be displayed, the other two will not be displayed.
Ryan you shd have first shown how tro download the basic xcelsius. u shd think abt starters side also.
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..
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
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?
We still cannot quite assume that I should have come to be the type staring at the important points available your webblog. Our family and so i are sincerely thankful for a generosity for giving me possibility pursue our chosen profession path. Just information I was from a web-site.
I do trust all of the concepts you’ve introduced for your post. They’re very convincing and will definitely work. Nonetheless, the posts are too quick for starters. May just you please lengthen them a bit from subsequent time? Thanks for the post. See http://platnosci-online.pl site.
Some great article, it helped me very much!
Dynamic Formatting in Xcelsius Tables Excellent goods from you, man. I’ve understand your stuff previous to and you’re just too magnificent. I really like what you have acquired here, really like what you are stating and the way in which you say it. You make it enjoyable and you still take care of to keep it wise. I can not wait to read much more Dynamic Formatting in Xcelsius Tables again from you. Thanks For Share .
Thanks a lot for providing the great services in this blog that to utilize the different services in this blog. I hope you very much enjoyed for the great services in this blog and using the amazing services in this blog.