Grid Formatting 101

Most formatting in XLCubed can be handled though the formatting options available on the right-click menu, and today’s blog will cover some of the common use cases.

You will be presented with different options depending on whether you click a member on columns or rows:

or a cell within the grid:

Selecting any of these options will bring up the standard Excel formatting dialog as below.

 

Let’s take a closer look at the options available in the first screenshot, where United States is selected. Format this Member will format just the selected row or column. In this case we’ve just used a simple Fill format:

Let’s drill into the grid before we demonstrate the next option.

Although, by default, the lower levels will be indented in the grid it can be useful to us a fill colour to help differentiate between the different levels. Format this Level will format all rows at the same level.

Let’s select Canada (although you can select any of the countries in the grid) and just do another simple fill formatting.

After formatting at the country level, I have also selected and formatted each lower level – ie state, province, city, postal code.  This means that when you drill down into the grid you can see the different levels clearly.

Conditional Format this Member & Conditional Format this Level:

You can also use Excel’s standard conditional formatting options to format a member or level within the grid.

You can also conditionally format the entire grid – this option is available when clicking anywhere on a grid.

I’ve set up a conditional format on this grid using icon sets:

This affects all grid rows that fulfil the condition, and of course the formatting will expand with the grid content if the number of rows and columns changes.

You can also format a single cell in your grid from the Formatting options – simply select the cell and right-click.

We hope you’ve found this blog useful, and feedback is welcome as always!

 

Leave a Reply

Your e-mail address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.