Viewports

XLCubed Viewports help with the layout of variable length or width reports. For example they allow you to present a range from worksheet 1 on worksheet 2. This can be useful in many presentation scenarios, you can have separate worksheets for each business area, and bring them together on a formatted summary sheet.

They have a number of advantages over standard Excel camera objects (detailed here - http://blog.xlcubed.com/2012/07/a-snappy-fix-for-layout-problems-in-excel/). Firstly Viewports are scrollable. The Viewport can reference a much larger area on the source sheet, and if the Viewport control itself is smaller it will show vertical and horizontal scrollbars as required. This can be very useful for row dynamic reporting as the display area remains unchanged and additional parts of the report beneath the Viewport can remain in place even if the number of rows in the Viewport changes. They also support freeze panes so that the row and column headers can always be visible as you scroll.

Secondly they retain interactivity. If the Viewport contains a Grid, the grid is still drillable and editable even through the Viewport. If there are active cell links which update other parts of the workbook these are still interactive in the Viewport.

Lastly, they support named ranges being specified as the area (type in the name when picking a range). This works will when combined with specifying Named Ranges on Grids or Tables.

Viewports are added from the Visualise menu on the XLCubed Ribbon. You specify the source range to be displayed, and where to insert the viewport control. You can also define the placement options and border settings as shown below.

After insertion, the viewport control can be scrolled, any grids it contains can be drilled, and xl3link() and grid active cell settings are retained.

Excel Limitations

The viewport will only update when a cell value in the referenced range changes, so if it just contains a chart or image you may need to add a "Helper" cell to trigger the update. This can just be a reference to a cell that will be updated when the chart/image needs to be updated (it can be hidden or formatted so that it doesn't display anything)

Chart Viewports

Version 9

In Version 9, when displaying an Excel chart in a Viewport, it is possible to select the chart object itself rather than the Excel range of cells which contain the chart. The main advantage of this is that when the viewport size differs from the original chart size rather than stretching the image, the chart itself is re-drawn to the new dimensions. In most cases this will give a crisper display and avoid cases where legends and axes can look "fuzzy".

This approach is particularly useful on Dashboard sheets, where the viewport size will change based on the screen size of the device it's being used on.Another advantage is the chart will automatically refresh when the underlying data changes.

Web Limitations

Due to the way charts are resized:

• You can not have two viewports on the same chart on one sheet.
  • But you can have them on different sheets.

• Charts may overlap standard range based viewports.
  • In this case you can move the chart to a different sheet from the range viewport.

Version 9.1

In Version 9.1 you can also add viewports based on Small Multiple Charts and Treemaps. These give the same benefits of viewports on Excel charts, the fuzziness of resizing the chart is improved.