Getting started with XLReportGrids
Download / Installation
Download the setup file to your computer and execute it from there. This will install the add-in file for you and register it with windows.
The next time you open Excel you will see a new group under the 'Insert' Tab for XLReportGrids .
Building your first 'Grid
Building a template
A template is just a range on a worksheet. Anything you put in this range will be copied across to cells in the XLReportGrid. That includes:
- Regular Charts
- Word Art
The data within the template must be driven by a Pivot-table page field, such as the cell B1 in the example here. Page Fields are used as a filter by pivottables and pivotcharts. Select the drop-down button and you can see all the items that you could select for this filter. The add-in cycles through each item in this page field, to select new data. If your template range is not linked to a page-field, all your output cells will look exactly the same.
The easiest thing to put in your template are pivot-tables and pivotcharts because these are already driven by your page-field selection
Formulas that reference cells in your Pivot Table will also update as the Page Field changes and make it easy to create Titles or look-up data in other worksheets (with Match, Index, VLookup etc.)
Use your imagination !
In this example:
- The Template Range is F5:K23.
- The chart is a standard pivot-chart driven directly from the pivot-table
- The title in H2 is just a formula referencing the PivotTable PageField
- (="Combo chart for - " & Templates!B1). This is then referenced as a Title formula for the chart.
- The data section below the chart is using formulas to look-up data for the selected Group in another worksheet.
- The chart and data section have been manually formatted and aligned within the template range so it looks good when copied.
- It took less than 5 minutes start to end.
Running the add-in
With your template ready to go, Go to the Insert Tab, and click on the XLReportGrids button
This opens the Form that let's specify exactly how to create XLReportGrids . For new 'Grids the form will start out with sensible defaults:
- It will output to a new Worksheet. (As the 'Grid deletes everything else on the worksheet it writes to, you can't have a 'Grid on your TemplateSheet)
- [Rows per Page] and [Columns Per Page] will default but you can change this to whatever you want.
- [Max Cells] allows you to set a limit on the total number of cells to output. (the default is unlimited)
Select the [Template Range] you created and any cell within your pivot-table for [Pivot Table Range] and press OK to generate your first 'Grid.
All your chosen form options are saved with the worksheet.
Once you see the output you will probably want to make a few changes. Update your template as necessary then go back to the output sheet and modify the Page Layout (Page Size, Orientation, Margins, Headers and Footer). When you are ready, click on [Refresh] in the XLReport'sGrid group in the Insert Tab of the Ribbon.
More Grid Options
The lower part of the Form has more advanced formatting options.
The Grid Format section lets you
- set [Border Size] - the gap between each cell (typically 0-10 points)
- add [Shadows]
- add an [Outline] to each cell
The Chart Format section applies only to the first chart in your template range and lets you:
- Lock the Primary value axis, so that it stays the same across all charts. This makes it much easier to compare chart to chart knowing that the scale is not changing. The add-in will automatically lock on the largest range necessary for all the charts you will output. If you have already locked the primary value axis manually this will have no effect.
- Lock the Secondary Value Axis (as above)
- Force the legend to appear in the header section of the output and print out on each page.
Template formatting tips
- If you don't want grid-lines to show on your output, turn off visible gridlines in your worksheet.
- Take care to align your charts, pivot-tables, data sections. text boxes etc. neatly within your pivot-range. What you see in the template is what you will get as output. Use the [Align to Grid] command in the ribbon to align any selected object with the grid of cells in the worksheet.
- Try setting chart properties to 'don't move or size with cells'
- Make the template area about the right size for the output you are looking for. Assuming you have selected [Fit To Page] as an option, the add-in will resize the cell to fit in the space available. The more this is done the more distortion you will see in the output.
Working with multiple PivotTable PageFields
If you have just one PivotTable PageFields, as in the previous example, XLReportGrids will fill a row on one page with cells then just start on the next row. Where you have 2 (or more) PageFields like this, it sets up each item in your first (top) PageField as a column header to give you a more meaningful 2D display. In this case I want a separate column for each [Region] and a separate Row for each [Group]
And here is what the output looks like - each column is a 'Region', each row is a 'Group':
Other Menu Options
[Refresh] will refresh the XLReportGrid on the Active Worksheet, from the saved definition.
[Refresh All] refreshes all XLReportGrids in the Active Workbook.
[Align to Grid] will align any selected worksheet object (chart, shape, text box etc.) to the grid of cells underneath. This makes it much easier to build professional looking templates.
[Help (web-page)] Directs you to our web support page
[About] provides you with the version number and contact information
Start by adding a reference to the add-in from the Visual Basic Editor. You can then refer directly to a number of XLReportGrids' Public Subroutines from your own code. These are probably the most useful for automation.
Public Sub XLReportGrids_Refresh(OutputWorksheet As Worksheet)
Refresh the XLReportGrid on a specifc Worksheet
Public Sub XLReportGrids_RefreshActive() Refresh the XLReportGrid in the Active Worksheet
Public Sub XLReportGrids_RefreshAll() Refresh All XLReportGrids in the ActiveWorkbook (in just 1 line of code)