Formatting Grids/Version 9.0

Formatting in XLCubed is handled in 3 key areas:

Format sheet: This controls the basic formatting for a grid, in terms of the overall colour scheme and fonts. It is also used to store user applied formatting for measures or slices, as detailed below.

Measures formatting: Users can select a measure in the XLCubed member selector, and apply a numeric format to the selected measure using the standard Excel dialog.

Formatting via XLCubed’s right-click menu: Users can right click on any member in the row or column area of a grid, choose XLCubed - Formatting and apply a format to that cell through the standard Excel dialog.

Format sheet

This shows the default format settings for grids. These can be changed as required eg there may be corporate defaults for reports with particular preferred fonts and colours.

Rows 5 to 16 define the basic formatting for a grid. By directly formatting the relevant cells here using native Excel, you can control the font style and colour and cell background of the labels, the grid header and row and column headers and members.

Row 47 onwards is for formatting applied to individual slices of a grid, normally when a user wants to format a particular member. This area will be updated automatically when formatting is applied using XLCubed’s right-click menu.

If you have more than one grid in your workbook and you need to apply different formatting to each grid, you need to name the grid (through Grid Properties) and then refer to each specific grid in this area in column B. Please note: There is an empty row between each slice highlight, so that the borders do not conflict with each other.

Any updates you make directly on the format sheet will be applied the next time the grid is refreshed

The two screenshots below are of the standard format sheet, and a grid inserted using that format sheet.

The second shows a format sheet which has been amended for custom formatting, and the grid that produces. The top area of the format sheet shown here covers the base layout of a grid. The lower area (rows 47+ are slice or measure specific).

To Edit the format sheet you directly apply the required formatting changes to the relevant cells in the FormatSheet using normal Excel formatting.

This is the grid after all the formatting above has been applied:

Cells Q2 to V3 in the format sheet hold XLCubed slicer settings. Here you can change the display format for the slicer title, slicer buttons and slicer items.

Upgrading pre-v9 workbooks

If you start a new workbook, you will automatically get the new format worksheet, workbooks built in older versions can be upgrading to the new formatting, by going to Workbook Options -> Reset Format sheet.

You will be given an option to keep any customisation as part of the upgrade - once the upgrade has completed the old format sheet is renamed and can be safely deleted once you are happy that all the formatting has been saved correctly.

Advanced Options

You can click in cell A16 to toggle the visibility of the advanced options, from here you can alternate row formatting and other writeback/grid active cell formatting.

For example, to set the alternate row format you need to toggle the selector to "Alternate Rows - On". The formats for the members and cells will be applied for every other row.

Row 20 is for the formatting of the Active cell

Rows 22 to 28 are specific to writeback-enabled grids where you can write new values to the cube.

Measure formatting

Formatting can also be achieved through the Member Selector by clicking the highlighted button below:

The standard Excel format window is displayed and allows the user to format the number as required.

Formatting via XLCubed’s right-click menu

If you right-click on a grid row or column header and select Formatting from XLCubed’s menu you will be given the option to Format This Level or Format This Member.

If you do the same on a grid cell you will be able to Format This Cell.

All of these formatting options will write to the bottom area of the formats sheet.

Here is an example grid:

Although there is indentation of grid members when you drill down it is a little difficult to differentiate between the different levels.

Let’s format the country level by right-clicking on a country and selecting Format This Level and selecting a fill colour of blue. As you can see each country now stands out.

You can see that the format sheet has been updated automatically:

If you wish to change this formatting in any way you can just right-click the entry in the format sheet and edit using standard Excel formatting.

Format This Member

In v7 formatting is available on XLCubed right-click menu as option, Format This Member.

So if we want to format Accessories row, we right-click on Accessories, XLCubed, Format This Member. We are presented with the standard Excel Format Cells window from which we can choose any of the usual settings to change.

We will choose Fill, select a colour and OK. The refreshed grid now looks like this:

The lower half of the XLCubedFormats sheet shows that both Member Accessories and associated row data will be filled in pink.

If you do not want the data to be filled go to the XLCubedFormats sheet, right-click the Data Format cell for Accessories and remove the fill colour by setting the backgound to 'No Colour'.

Once refreshed, the grid now looks like:

Formatting Rows and Columns

In v7.2 we now have the ability to set formatting on rows and/or columns for grid members.

Here we have a simple report where we have set formatting at different levels:

The format sheet looks like this:

Change the format sheet to ROWS as below:

This format will now be applied for whichever hierarchy is on rows. Let's put Geography on rows instead:

As you can see this is a very simple way to create standardised reporting within an organisation. This could easily be included in the master format sheet so that all grids created would have the same row format. The same applies to columns.

Format this level

New to v7.2, Format this level allows the user to easily format all members at a particular hierarchy level and is available on XLCubed's right-click menu, Formatting, Format this level. You will be presented with the standard Excel Format Cells window from which you can choose any of the usual settings to change.

Format by Member Property

New to v8.1, Format by property allows you to select the affected slice by a member property value. You need the full name of the property, as shown below, and the property must be selected in the grid (although you can set the property display style in Grid Properties to member only so it is not displayed).

See Also

• Format Sheet for more formatting options