Format Sheet

More complex formatting scenarios or grid specific formatting are handled directly on the format sheet. Any of the cells on the format sheet can be formatted directly in Excel, and will then be applied to the appropriate are of the grid when the grid is next refreshed. The main body of the grid is contained in C6:D21, and the data cells, column and row member names, and the filter area can all be set directly here.

Alternate Rows: Alternate row formatting can be used by setting the drop down selector in E13 to ‘True’ and then applying the format required in C and D13.

Writeback Leaf Level: The grid can display the lowest level data and members in a specific format where required, by setting E15:21 to true as required.

Conditional Formatting: Conditional formatting on the grid data can be applied by setting the conditions on the default cell format (D11). This is currently only available at a grid level.

Grid Specific Formatting: Where multiple grids exist in a workbook, there may be a requirement to format a slice of data in a specific way in grid A, but not in grid B. Using the right click menus as in 5.14.2, will apply the selected format to that slice of data in all grids. To make this grid specific, edit the format sheet and supply the grid name for the appropriate slice in column B. The example below in B26 is restricting the Bold formatting on CY 2004 to the ‘Sales’ grid, whereas the formatting on Germany is not restricted and will be applied on all grids.

When using this approach you must first name the grid, which is done in grid properties, on the appearance tab. Note that it’s also possible to use wildcards in the GridName field, so that for example all grids starting with Sales would apply this format (Sales*).