# Do you know these tricks?

During the last couple of months I have discovered some usefull tricks, which in turn are too small for a post on its own, so I have combined them into this one.

Formula for calculating the maximum scale for chart
max scale: =ROUNDUP(Ceiling(MAX(RANGE)),0)*(1&REPT(0,(Len(Ceiling(MAX((RANGE)),0))-1))

wrap legend series
Set the format of the series name cell in Excel to wrap and use the carridge return to set the breakpoint ( alt enter ).

wrap label at specific point
Unfortunately the formula Char(10) is not supported. But with using the html format setting you can use
to set the break where you want in a formula concatenation. Just remember with this setting, you have to add the font formatting and color codings as well, as they are ignored otherwise. And do NOT FORGET to deselect the wrap option in the behaviour tab.

scorecard sorts rows, even when disabled, after reload button
If you have deselected the option enable sort in the scorecard, the user is not able to sort in the scorecard even though he clicks on the column headers. However when the user clicks the reload button, the dashboard is reset but the scorecard “retains” the sorted column and the whole scorecard is messed up. The only way to prevent this behaviour is by placing a transparent push button over the headers.

mouse over displays different decimals for different series
When you format the values in the spreadsheet with different decimals and keep the format text setting in the properties tab of the chart on general, the number is formatted as in the spreadsheet.
Note: unfortunately Excel and thus Xcelsius will shorten any number ending on 0. Hence the value 1.50 will be shown as 1.5 eventhough the formula specifies 2 decimals , like: =round(A1;2) will result into 1.5 if A1 is 1.5000 while 1.4999 will be 1.50.

scale y-axes displays different decimals depending on metrics
Following situation: combo box with selections on different metrics. Metric A has to be displayed with 0 decimals, metric B has 2 decimals. One chart displays the selected metric. The mouseover is like the above trick. The scale will follow formatting of the manual scale setting (link min and max to spreadsheet) and format vertical axes to general on text property tab.
Note: unfortunately Excel and thus Xcelsius will shorten any number ending on 0. Hence the value 1.50 will be shown as 1.5 eventhough the formula specifies 2 decimals , like: =round(A1;2) will result into 1.5 if A1 is 1.5000 while 1.4999 will be 1.50.

Recent Related Posts
Femke's website