Dynamic Formatting in Xcelsius Tables

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

Read More