Formatting properties for tabular data

This help topic provides an overview of the available formatting properties for tabular data in an Excel report template.

Formatting properties for entire tables

Every table added as a single tag has three table settings enabled by default:

• Keep source formats: The Excel add-in tries to keep the original object formats. By turning this setting off, you can apply customized Excel formats to the table. For example, you can set a background color in the cell with the table tag and it will be propagated on all cells in the generated report.

• Expand range: The Excel add-in will add an adequate number of rows under the table to avoid overlap with table tags placed underneath. If the Expand range option is cleared, the add-in will not add rows under the table. You will have to manually manage the position of other table tags to stop related cells from being overwritten. Cell references will be kept.

• Wrap text: All text in table cells will be wrapped.

If you change the Display headers or Keep source formats settings at the table level, you are prompted to automatically change any individual columns that have different values for these settings.

You can configure table column headers with the Display headers setting.

Each of these settings is explained in more detail below.

Keep source formats setting at the table level

By default, your generated reports are set to use the same formatting as the source Qlik Sense objects. If you manually apply custom formats to the template, they will be ignored. To add custom styling, you must turn off Keep source formats.

The image below is an example of the output generated from a single table tag highlighted in yellow, with Keep source formats turned off.

Expand range

If the number of rows and columns in a table is fixed and known during the development of the template, you can add Excel formulas to the table. However, if you keep the Expand range box checked, the generated report will be wrong. It will contain incorrect cell references and the formulas will not apply to the correct cells.

In the example below, we have added some calculated cells outside the cells to be populated with the Qlik Sense table columns. Additionally, calculated cells are placed at the bottom of three columns in the table. We leave the Expand range setting turned on.

However, in the report generated from this template, the cell references are not respected, and the calculated cells are instead added below the table. See the image below.

If we turn the Expand range setting off and generate the report, we can see that the cell references are now kept and function as intended. This is shown in the image below.

Wrap text

When Wrap text is turned on at the table level, every column in the table will have text wrapping automatically applied to it. Turn off this setting if this is not desired.

Header settings at the table level

Use the Display headers setting to configure column headers for the entire table. You have three options:

• Show in tag cell (default): Table headers will appear in the same row as the table tag, and table data will start on the row directly below it.

• Hide: Headers are removed entirely. Table data will start on the same row as the table tag. If you use this option and keep header manually entered above the tags, you can customize both the header text and the formatting of the header cells.

• Show above tag cell: Headers are moved to the row above the table tag. Table data starts on the same row as the table tag. You can apply custom Excel formatting to the cell above the table tag, so that the header cells are styled to your preference. The text in the header cells themselves, however, is generated with the dimension and measure titles from the Qlik Sense app.

Formatting properties for individual columns

If you add a table as columns, or if you do not add all the columns of the source table, the table properties function the same as if you added an entire table tag. The only exception is that the Display header column setting also inserts a customizable fixed string.

The Expand range and Wrap text settings affect the whole table. Keep source formats and Display header can be defined separately for each column.

If you change the Display headers or Keep source formats settings at the table level, you are prompted to automatically change any individual columns that have different values for these settings.

Keep source formats at the column level

Keep source formats can be set at the column level to customize the formats of every single column. Click on a column tag and uncheck the related Keep source formats checkbox. Alternatively, open the table in the Tables objects within the add-in, and expand the column. Then, uncheck the checkbox.

Changing the Keep source formats value at the table level will change the value of this setting for all individual columns in the table. For information about setting this at the table level, see Keep source formats setting at the table level.

Column header settings

You can set the header style at the column level. Use the Display header setting to configure headers for each column.

Removing content

Removing an object

You can remove an object from the report template, and from the list of that object type in the add-in pane.

Do the following:

1. Select the cell where the object is added.

2. Click Delete in the bottom of the add-in window.

This removes the tags or the preview image and the object from the corresponding object list.

If you instead use Excel features to delete an object, it will not be inserted in the generated report. However, the object will still appear in the list of added objects. This means you can easily re-add the object later.

Removing table columns

For chart data added by column as a table, you can delete columns from the template. Navigate to the cells of the column you want to remove, highlight all its components, and delete or clear the cell contents.

Removing columns from the template does not remove the columns from the table object, so you can use the add-in to add back any columns as needed.