Conditional formatting

Overview

Excel conditional formatting allows the format of a cell to change based on its value or a formula.

Conditional Formatting in Grids

Conditional formatting can be setup on specific members, by using the "Format this member" right-click option and then adding the conditional format to the data cell for that slice on the format sheet.

Conditional formatting can be applied to the whole grid by formatting the "Data Cell" value on the format sheet.

New in Version 9.1

Version 9.1 adds integrated support for Excel Conditional formatting in Grids and for web publication. In addition to cell font and background colour, this also includes colour scales and icon sets.

Conditional formats can be applied to any grid using the right click menu. Right click on a value in the grid to format the entire grid (XLCubed - Formatting - Conditional Format this Grid), or on a specific membername to format just that member or it's level. This then opens the standard Excel Conditional formatting menu and the user-selected formats are added onto the format sheet. A format sheet with conditional formatting applied to the entire grid looks like the below, with the * wildcard character specified for both Hierarchy and Member.

This also works for tables.

XLCubed Web Edition Restrictions (Version 9.1 and later)

The restrictions listed below for earlier versions have been lifted. Mulitple conditions, overlapping conditions are supported. Icon sets, colour gradients and data bars are also supported.

XLCubed Web Edition Restrictions (Version 9.0 and earlier)

XLCubedWeb supports 3 conditional formats per cell and only supports the conditional formatting available in Excel 2003, that is:

• Format based on cell value
• Format based on Formula (the formula must keep all references on the same sheet as the cell being formatted)
• Formula based formats may not reference cells on other worksheets

The Colour support is limited to the workbook set of colours.

However, you may still find that a colour you have picked still does not display as expected when the workbook is published. If this happens you then need to take the following steps:

• Right-click on a cell with the required colour
• Click More colours – Custom and note the settings for Red, Green , Blue
• Go to File > Options > Save

• Select Choose what colours will be seen in previous versions of Excel (at bottom of window)

• Select a colour closest to the one you want and Modify:

• Click Custom and enter the values for Red, Green, Blue you noted earlier

• Click OK – you should find that when the workbook is published the colour is now OK

In cells, though not automatically in the Grid, XLCubedWeb also supports "Solid data bars" but not the other options such as Icon Sets or gradient fills. In order to use this format in the Grid, you should deselect the XLCubed > Properties > Appearance > Apply formatting Grid property, then apply it to the Grid area.

Overlapping conditional formats can cause an issue when a report is published. You may need to rationalize these by going to "Manage Conditional Formatting" and select "Show Formatting rules for:" This Worksheet