Configure the steps as described in the following section, depending on the selected source system.
Connecting to a Spreadsheet
Last updated on 2025-09-25
Overview
in xP&A, you can connect to the following spreadsheet file formats:
- Google Sheets
- Excel Online
- Excel Upload
- CSV Upload
Google Sheets and Excel Online are synchronized, i.e. you can refresh the data source and the latest data will be imported automatically, where as data integrated via CSV Upload and Excel Upload need to be updated manually.
The following three data formats for spreadsheet files are supported:
- Tables
- Transactions
- Time-series
Each connected file needs to be formatted in a certain way so that the data can be imported into xP&A.
We also provide various templates for the different file and data formats, helping you to set up the files much easier.
This article contains the following sections:
Formatting a Spreadsheet for Integration
In order to pull in data from a spreadsheet, the spreadsheet needs to have a certain format. The format requirements depend on the type of data you you want to connect.
The table format is useful where your values do not vary over time, i.e. it is more of a static database of information.
A table must structured as follows:
- The first row represents the dimension name or variable name.
- Every subsequent row (below the top row) represent a single dimension item.
- The first column must contain the primary dimension.
- Additional columns can contain:
- Mapped dimensions:
In case of mapped dimensions, the first row must have the name of the dimension being mapped from and the name of the dimension being mapped to, in the following format: [from dimension] > [to dimension]. Each subsequent row contains the "mapped" dimension
Note: (The arrow (">") is important because it is used by xP&A to distinguishes between a normal dimension column, and a mapped dimension column! This is equivalent to linking dimensions in xP&A, except that you cannot modify the link from within xP&A in this case.) - Unmapped dimensions:
The first row must contain the name of the dimension and all subsequent other row contains a dimension item.
Note: (It is rare that you will use unmapped or nested dimensions in the table format) - Variables:
The first row must contain the name of the variable and all subsequent rows will contain the values, whether it's a date or a number, for the associated dimension item.
Note: You must not include a > in a variable column.
- Mapped dimensions:
Example
The transactions format is useful where your data varies over time, and you track it on a granular basis.
xP&A allows two types of transactions format:
Standard format
The standard format for transaction data allows you to pull in data on a transaction-by-transaction basis into xP&A. This is useful for values that are recorded on a per-day, per-week, or per-event basis, like Sales by Customer.
The standard format rules are:
- The first column must have the name Date.
- Subsequent columns can be values (such as variables or Data Items) or dimenisons, with a freely-definable name.
Note: You can add [dimension] or [variable] to a column name to ensure that the system recognizes the column correctly. - You can also add dimension mappings. In this case, the column name must have the name of the dimension being mapped from and the name of the dimension being mapped to, in the following format: [from dimension] > [to dimension].
- You can also add a Cohort column to integrate cohorts.
Example
Alternative format
If your data source has Data Items coming through in rows rather than columns then you can use the Alternative format for data ingestion.
The alternatove format ruels are:
- The first column must have the name Date.
- The second column must have the name Type and contain the Data Item or variable names.
- The third column must have the name Value and contain the respective values.
Example
The time-series format is useful where your data varies over time (e.g. by month).
xP&A allows three types of time-series format:
Basic time-series
The basic time-series rules are:
- The first row must have dates for each value.
- The first column must have the names of each variable.
- Each subsequent row represents a single variable.
Example
Time-series with dimensions
With this type, you can include several dimensions.
The rules for time-series with dimensions are:
- Each row represents a single variable, for a single dimension item.
- The columns can be split into four sections:
- The first column must have the variables' names.
- The next columns represent the dimensions:
- The first row of this section must have the name of the dimension.
- The rows below may have the name of an item in that dimension.
- The last columns represent the values.
- The first row of this section must have dates.
- The rows below contain the values themselves - whcoh should be numbers (not text)
- If a variable has more than one dimension, you can create a row for each possible iteration. For example, if Website Revenue was tracked by both Product and Geography, you would have 12 rows for Website Revenue (3 Products x 4 Geographies).
Example
Time-series with linked dimensions
With this type, you can include dimensions and mappings (i.e. links between dimensions and dimension items). You can include several dimensions and several mappings at the same time.
If you set up the mappings in your data source, xP&A will automatically know how to slice and dice your variables.
The rules for time-series with linked dimensions are:
- Each row represents a single variable, for a single dimension item.
- The columns can be split into four sections:
- The first column must have the variables' names.
- The next columns represent the dimensions:
- The first row of this section must have the name of the dimension.
- The rows below may have the name of an item in that dimension.
- The next columns hold the dimension mappings:
- The first row must have the name of the dimension being mapped from and the name of the dimension being mapped to, in the following format: [from dimension] > [to dimension].
- The rows below may have the name of an item in the dimension being mapped to
- The last columns represent the values.
- The first row of this section must have dates.
- The rows below contain the values themselves - whcoh should be numbers (not text)
Example
Connecting to a Spreadsheet
To connect to a Spreadsheet:
Choose one of the follwing options:
- Open the Data workspace from the overview on the start page and click + New.
- Open the model into which you would like to integrate the data, click the + sign next to Data in the overview, and choose New data source:
In the Data sources dialog, open the Spreadsheets tab and choose the system from which you want to integrate data.
For Excel Upload and CSV Upload configure the following steps in the New Data Source dialog:
Step
Description
Upload file
Drag & drop or browse for the file you would like to connect.
Headers
Worksheet: Select the worksheet you would like to pull the data from.
Headers: In the file preview on the right, select the row representing the column headers of your dataset.
Format
Select the format of your data set and click Apply to see a preview of the formatted data.
Dimensions
Assign the dimensions of your data sources to the dimensions in xP&A in order to use them in xP&A.
For Excel Online configure the following steps in the New Data Source dialog:
Step
Description
Connection
Choose a connection: Authenticate with you Excel Online account or choose an existing connection with Excel Online.
Select your file: Select the file which you would like to connect.
Select a worksheet from your file: Select the worksheet from which you would like to pull in the data.
Headers
Headers: In the file preview on the right, select the row representing the column headers of your dataset.
You can click Refresh data to synchronize the file and get the latest data of your selected file.
Format
Select the format of your data set and click Apply to see a preview of the formatted data.
Dimensions
Assign the dimensions of your data sources to the dimensions in xP&A in order to use them in xP&A.
For Google Sheets configure the following steps in the New Data Source dialog:
Step
Description
Connection
Choose a connection: Authenticate with you Excel Online account or choose an existing connection with Excel Online.
Select your file: Select the sheet which you would like to connect.
Paste URL: Alternatively, you can paste the URL of the sheet which you would like to connect.
Select a worksheet from your file: Select the worksheet from which you would like to pull in the data.
Headers
Headers: In the file preview on the right, select the row representing the column headers of your dataset.
You can click Refresh data to synchronize the file and get the latest data of your selected file.
Format
Select the format of your data set and click Apply to see a preview of the formatted data.
Dimensions
Assign the dimensions of your data sources to the dimensions in xP&A in order to use them in xP&A.
Spreadsheet Templates
In order to set up your spreadsheets according to the requirements more efficiently, you can use the following templates for Google Sheets and/or MS Excel:
Template/Use case
Link to template and example
Payroll actuals by month (time-series)
Time-series cohorts
MRR is the variable, row 1 is the month, column B is the cohort
Transaction format
Column B is the variable name, column E is a dimension
Basic time-series
Column A are the variable names
Time-series with two dimensions
Column A are the variable names, column B and C the dimensions