Integrations → Excel Add-in

About the Excel Add-in

With the Incorta Excel Add-in, you can extract data from Incorta to Excel into a Table or Pivot Table. You can save a query for later use, and refresh the data when you reopen your spreadsheet. You can also use the Excel built-in charts to create meaningful insights into your data.

Note

Your worksheet must be unprotected in order to use the Incorta Excel Add-in.

Install the Incorta Excel Add-in

Use Centralized Deployment or Sideloading to install the Incorta Excel Add-in on your local machine.

Install the Incorta Excel Add-in on your Local Machine

There are two options for installing the Incorta Excel Add-in on your local machine: Centralized Deployment or Sideloading. Both options require an Incorta Excel Add-in manifest XML file:

  • incorta-manifest.xml

You can get the manifest file via the following URL: https://<INCORTA_HOST>/incorta/external-add-ins/excel/#/download-manifest

Centralized Deployment

Use Centralized Deployment to make the Incorta Excel Add-in available to users in their Office applications right away, with no client configuration required. To publish the Incorta Excel Add-in, your system administrator can reference Publish Office Add-ins using Centralized Deployment via the Microsoft 365 admin center. Microsoft recommends Centralized Deployment for add-in deployment in a production environment.

After the system administrator deploys the add-in, you can access it with the following steps, which apply to both Windows and macOS:

  • Open Microsoft Excel.
  • Create a new Excel workbook or open an existing workbook.
  • In the ribbon, select the Insert tab.
  • In the Add-ins group, select My Add-ins.
  • In the Office Add-ins dialog, select Admin Managed.
  • If Incorta is not listed in the dialog, select Refresh in the upper right corner.
  • Select Incorta.
  • Select Add.
  • If a dialog says that the Incorta Add-in loaded successfully, select Got It.
  • In the ribbon, select the Home tab.
  • In the Home ribbon, in the Incorta group, select Show to open the Incorta Excel Add-in Login panel.

Sideloading

If Centralized Deployment is not applicable in your organization, you can use a method called Sideloading to install the Incorta Excel add-in on your Windows or macOS local machine.

Use Sideloading to Install the Incorta Excel Add-in on Windows
  • Open Windows Explorer.
  • Navigate to your user folder.
  • In your user folder, create a folder with any name you choose (e.g. ExcelAddin).
  • Copy the Incorta Excel Add-in manifest file to your folder.
  • Right-click on the folder and choose Properties.
  • In the Properties dialog, select the Sharing tab and then select Share…
  • Within the Network access dialog
    • Add yourself and any other users or groups you want to share the Incorta Excel Add-in with. The minimum permission level is Read/Write .
    • Select Share.
  • When you see confirmation that Your folder is shared, copy the full folder network path that is displayed below the folder name.
  • Select Done.
  • Open a blank workbook in Excel
  • In the Excel Menu bar, select FileOptions.
    • Select Trust Center.
    • Select Trust Center Settings....
    • Select Trusted Add-in Catalogs.
    • In Catalog Url, paste the full folder network path you copied earlier and select Add catalog.
    • Select Show in Menu, and then select OK to close the Trust Center dialog.
    • Select OK to close the Excel Options dialog.
  • Restart Excel.
  • Create a new Excel workbook or open an existing workbook.
  • In the ribbon, select the Insert tab.
  • In the Add-ins group, select My Add-insIncorta.
  • In the Office Add-ins dialog
    • Select Shared Folder. If Incorta is not listed in the dialog, select Refresh in the upper right corner.
    • Select Incorta.
    • Select Add.
  • If a dialog says that the Incorta Add-in loaded successfully, select Got It.
  • In the ribbon, select the Home tab.
  • In the Home ribbon, in the Incorta group, select Show to open the Incorta Excel Add-in Login panel.
Note
  • To use the Incorta Excel Add-in on Windows, you must have Microsoft 365 16.0.13530.20424 on Windows 10 version 1903 or later. You must also have Microsoft Edge with WebView2 (Chromium-based).
  • If you would like to simplify the installation process for multiple users, you can host the manifest file in a shared folder. For more details, refer to Sideload Office Add-ins for testing from a network share.
Use Sideloading to Install the Incorta Excel Add-in on macOS
  • Open Finder.
  • In the Finder Menu bar, select GoGo to Folder....
  • Enter ~/Library/Containers/Microsoft Excel/Data/Documents/ and select Go.
  • If it does not yet exist, create the wef directory in the Documents directory.
  • Copy the Incorta Excel Add-in manifest file to the following directory: /Users/<YOUR USERNAME>/Library/Containers/Microsoft Excel/Data/Documents/wef.
  • Create a new Excel workbook or open an existing workbook.
  • In the ribbon, select the Insert tab.
  • In the Add-ins group, select the down arrow to the right of My Add-ins.
  • Select Incorta under the Developer Add-ins heading.
  • If a dialog says that the Incorta Add-in loaded successfully, select Got It.
  • In the ribbon, select the Home tab.
  • In the Home ribbon, in the Incorta group, select Show to open the Incorta Excel Add-in Login panel.
Note

To use the Incorta Excel Add-in on macOS, you must have Excel for Mac version 16.46 on macOS Catalina or later.

Excel Add-in Anatomy

The Excel Add-in consists of the following:

Login panel

The Login panel is used to connect to Incorta from Excel. By default, you can connect to Incorta Excel Add-in with a Single Sign On (SSO) using Incorta URL and the tenant name. Here are the Login panel properties:

Login

PropertyControlDescription
Incorta URLdrop down listSelect the URL of the Incorta cluster you would like to connect to. After you sign in, you need to create a new workbook if you would like to use a different Incorta URL
Tenanttext boxEnter the name of the Incorta tenant you would like to connect to
ContinuebuttonSelect Continue to connect to the tenant directly
Note

By default, login takes place using the SSO. Yet, you can choose to connect to Incorta Excel Add-in with a direct login using your Incorta credentials. To switch between SSO and direct log in, go back to the task pane and select Continue with direct log in or Continue with SSO.

Build Tables panel

In the Build Tables bar, you can perform several Excel Add-in tasks.

  • With the menu button (three horizontal lines icon), you can
    • Select About to view version information. Select the copy button in the bottom right corner to copy the version information.
    • Sign out
  • With the information button, you can
    • Get support
    • Reload
    • View the source
    • View security information
    • Clear the web cache

Data panel

The Data panel is used to manage the data you would like to analyze within Excel. Use the arrow to the left of DATA to hide (left arrow) or show (down arrow) the Data Panel.

To filter and find items in the Data panel, enter a search term in the Search text box or use the Column Type drop down menu to narrow your results. The Column Types include:

  • String
  • Numerical
  • Date
  • Timestamp
  • Boolean
  • Key

For a given column in the tree, select the information icon to view the column details and preview sample data.

You can also manage the tree hierarchy. The More Options (⋮ vertical ellipsis icon) menu allows you to:

  • Collapse to Schema and Table Level or Expand All
  • Sort by Name or Original Order
Multi-select

To select multiple columns in the Data panel, you must use the following keystrokes:

  • On Mac OS, use command
  • On Windows OS and Linux OS, use Ctrl

You must drag and drop multi-select columns to a tray or target box in another panel.

Manage Data Sets panel

You can use the Manage Data Sets panel to add selected schemas, business schemas, tables, or views to the Data panel.

The Manage Data Sets panel contains the Views and Tables tabs that can be filtered using search.

Insight panel

The Insight panel shows the selected visualization, and provides access to its settings. The default selection is Listing Table. Simply select the downward arrow (V) to change the visualization type.

Visualizations

Here is a list of available visualizations:

  • Listing Table
  • Aggregated Table
  • Pivot Table
Settings

Select settings (gear icon) to control the properties for the selected visualization.

Listing Table Properties
PropertyControlDescription
Include sheet in refresh AlltoggleEnable this property to refresh the data in the sheet when you select Refresh All in the Home ribbon of the Incorta group
Max Rows Limittext boxEnter the maximum number of rows that will be populated in Excel with data from Incorta. The default is 1000.
TotaltoggleEnable this property to include a total for the entire table
HeadertoggleEnable this property to display column names
Aggregated Table Properties
PropertyControlDescription
Include sheet in refresh AlltoggleEnable this property to refresh the data in the sheet when you select Refresh All in the Home ribbon of the Incorta group
Max Rows Limittext boxEnter the maximum number of rows that will be populated in Excel with data from Incorta. The default is 1000.
Merge ColumnstoggleEnable this property to merge table columns, creating a simplified visualization
Merge RowstoggleEnable this property to merge table rows, creating a simplified visualization
Subtotaldrop down listSelect Yes to include a subtotal at the end of each group
TotaltoggleEnable this property to include a total for the entire table
HeadertoggleEnable this property to display column names
TransposetoggleEnable this property to convert the table columns to rows and the table rows to columns
Pivot Table Properties
PropertyControlDescription
Include sheet in refresh AlltoggleEnable this property to refresh the data in the sheet when you select Refresh All in the Home ribbon of the Incorta group
Max Rows Limittext boxEnter the maximum number of rows that will be populated in Excel with data from Incorta. The default is 1000.
Merge RowstoggleEnable this property to merge table rows, creating a simplified visualization
Row Grand TotaltoggleEnable this property to include a row grand total at the top of the insight
Row Subtotaldrop down listSelect Yes to include a row subtotal at the top of each group
Row Total Atdrop down listSelect the position of the row total in the insight. The options are:
  • Top (default)
  • Bottom
Column Grand TotaltoggleEnable this property to include a column grand total to the left of the insight
Column Subtotaldrop down listSelect Yes to include a subtotal at the left of each group
Column Total Atdrop down listSelect the position of the column total in the insight. The options are:
  • Left (default)
  • Right
Trays

The visualization selection determines the available trays within the Insight panel.

From the Data panel, you can add one or more columns to a tray. You can easily reorder columns and formulas in a tray by selecting the column or formula, and dragging it up or down the scrolled list to drop it in the desired location. All trays have a command. A column or formula in a tray is a Pill. Each pill has configurable properties. The parent tray determines the available properties of a pill.

Columns Tray

The Columns tray is available for Listing Table, Aggregated Table, and Pivot Table insights. Use this tray to add a column to your table.

Rows Tray

The Rows tray is available for Listing and Aggregated table insights. Use this tray to add a row to your table.

Values Tray

The Values tray is available for Pivot table insights only. Use this tray to display summary numeric data in your pivot table.

Filters Tray

The Filters tray is available for Listing Table, Aggregated Table, and Pivot Table insights. Use this tray to add a filter to your table.

Query panel

The Query panel enables you to run a query, and view information on when the last query was run.

Connect to Incorta with the Excel Add-in

Here are the steps to connect to Incorta with the Excel Add-in:

  • In the Home ribbon, in the Incorta group, select Show to open the Incorta Excel Add-in Login panel.
  • Enter the Login panel properties.
  • Select Sign In. When you connect to Incorta successfully, the Excel Add-in panel will open.

Steps to Create a Table Visualization in Excel

  • In the Data panel, select Manage Dataset.
  • In the Manage Data Sets panel, select one or more business schema views and/or one or more physical schema tables.
  • To close the Manage Data Sets panel, select X.
  • In the Insight panel, select V. Select Listing Table, Aggregated Table, or Pivot Table.
  • From the Data panel, drag and drop various columns to various trays in the Insight panel:
    • Columns
    • Rows (Aggregated and Pivot Table only)
    • Values (Pivot Table only)
    • Filter
  • Configure the pill properties by selecting the right arrow next to the pill name.
  • Configure various insight properties by selecting settings (gear icon).
  • Select Query.
Important

To retain the query in Excel, you must save the Excel file before you close it.