Exporting to Pivot Tables

When performing a table export to an XLSX file, you can create a pivot table using the Excel Advanced Features.

Pivot Tables can only be created for XLSX files; they cannot be created for XLS files. If you are using a version of Excel earlier than 2007, you can download the Microsoft Office Compatibility Pack from Microsoft, which will allow Excel 2000 and higher to open XLSX files. The option to create a Pivot Table is not available for a Summary export, since pivot Tables use tabular “raw” data as a source, not summarized data. Monarch Classic will export the table data as a worksheet and the Pivot Table as a separate worksheet, which refers to the exported data.

Exporting to pivot tables

  1. Select Create Export on the Export View Ribbon. The Create Export dialog displays.

  2. Define the name of the export.

  3. Select a Table on the Source View selector.

  4. Select Microsoft XSLX or Microsoft XLSM as the Export File Type.

  5. Select Edit Advanced Options (at the top of the dialog). The Export Design window displays.

  6. On the Table View panel:

    • Enter the Export Name.

    • Select the Filter and Sort definitions that will be applied to the export.

  7. On the Excel Output panel, click on the Advanced tab to display the Advanced options.

  8. Check Include Pivot Table sheet.

  9. Add the desired pivot fields and pivot values for the Pivot Table from the Monarch Classic table fields. These are divided into different types: Report Filter (pivot field), Row Labels (pivot field), Column Labels (pivot field), and Values (pivot value).

    Note: Anyone familiar with Pivot Tables will recognize these elements. For those less familiar, here are some analogies in terms of Monarch Classic Summaries:

    Pivot Table

    Summary Equivalent

    Report filter

    Summary filter

    Row labels

    Down key in a summary

    Column labels

    Across key in a summary


    Measure in a summary


    To add a pivot field or value, simply select it from the New.. drop-down list.

    To further summarize any of the items you place in Row Labels or Values, right-click on an item and, from the options that display, select Edit. This action will launch the Pivot Field dialog for Row Labels and Pivot Values Dialog for Values.  

    Use the sorting button that displays beside each pivot field or value to specify a display order (i.e., ascending or descending).  

  10. Once you have selected the desired pivot fields and values, specify the desired settings using the Pivot Options tab. For example, you can define a new pivot table name. Note that pivot table names must follow the table naming guidelines specified in Naming Conventions.

  11. Select Accept.

  12. To run your newly-created export, refer to Running Exports.

Pivot Options tab




By default, the Pivot Table is named “PivotTable”. You can assign a new name to it by entering it here..


This refers to the Pivot Table style weight that will be applied when the Pivot Table is rendered in Excel. There are 3 options: Light, Medium and Dark. The weight is used in conjunction with the style index.


This is the number of the table format style that will be applied when the Pivot Table is rendered in Excel. Excel has 28 styles available in 3 weights. This style can be viewed in Excel by selecting the “Format as Table” menu item when viewing a Pivot Table.

Apply to row headers

Applies the style settings to the row headers of the Pivot Table.

Apply to column headers

Applies the style settings to the column headers of the Pivot Table.

Banded rows

Applies alternate color shading to the rows in the Pivot Table.

Banded columns

Applies alternate color shading to the columns in the Pivot Table.

Show grand totals for rows

Displays grand totals for each row of the Pivot Table.

Show grand totals for columns

Displays grand totals for each column of the Pivot Table.




© 2024 Altair Engineering Inc. All Rights Reserved.

Intellectual Property Rights Notice | Technical Support