Skip to main content

Creating Excel charts

You can add a native Excel chart to your reports. The same chart does not need to exist in the original document or app.

What you will do

You will:

  1. Add a QlikView table to the template as a level.
  2. You will add level fields and create an Excel chart.

This tutorial uses QlikView data that can be found on Sample files. You can also use your own Qlik Sense or QlikView data.

Creating a new Excel report

Do the following:

  1. Select Reports in the Qlik NPrinting main menu, and then click Create report.
  2. Enter a Title for the report. .
  3. Select Excel from the Type drop-down list.
  4. Select an app from the App drop-down list.
  5. Click Create to create the report.
  6. Click Edit template to open the Template Editor.

Adding a table as a level

Do the following:

  1. Right-click the Levels node, and select Add levels.
  2. Select a chart. For example: Top 10 Products. Click OK.

    The Add Levels window

  3. Under Levels, click the CH184_Level - Top 10 Products node and drag it onto three empty vertically-aligned cells.

    Excel template with level added.

  4. Click the + next to the CH184_Level - Top 10 Products node to expand it.
  5. Click on Total Sales. In the Properties pane, clear Keep Source Formats.
  6. Hold Shift and click on the ProductName and Total Sales fields. Drag them onto the empty row between the two level tags.
  7. Click on the cell containing <Total Sales>. Use the Excel ribbon to format this cell as currency.
  8. In the row above the opening <CH184_Level> tag, type in heading titles.

    Format these headings using the Excel ribbon.

    Excel template with level fields added.

Creating the Excel chart

Do the following:

  1. On the Excel ribbon, click Insert.
  2. Click the Insert Column Charts icon, and then select 2-D Column chart.

    Excel template showing add chart menu.

  3. Click the Design tab, and click the Select Data icon.
  4. In the Legend Entries (Series) pane, click the Add button.

    Chart window

  5. Click the arrow icon to the right of Series name field.

    Excel template showing how to add chart axis.

  6. Select the cell with the Total Sales heading.
  7. Confirm by clicking on the arrow icon at the right of the Edit Series field.

  8. Click the arrow icon to the right of Series values.
  9. Select the Total Sales range by including the cells in the rows containing:
    • the level opening and closing the tags
    • the cell containing the <Total Sales> tag.
  10. Confirm the selection by clicking on the arrow icon to the right of the Edit Series field.

    Excel template showing how to add chart axis.

  11. Click OK.
  12. In the Horizontal (Category) Axis Labels pane, click Edit.
  13. Select the Product Name range by including the cells in the rows containing:
    • the level opening and closing the tags
    • the cell containing the <ProductName> tag.
  14. Confirm the selection by clicking on the icon at the right end of the Axis label range field, and then click OK.

    Excel template showing how to add chart axis.

  15. Click OK. You will see a table, and an empty chart.

    Excel template table and blank chart.

Previewing the report

Do the following:

  1. Click Preview.

    Excel launches and displays your report.

  2. You will have a report with a table and a chart.

    Excel template preview showing table and populated chart.

  3. Click Save and Close to save the template, and close the Template Editor.