How to use Excel 365 in a metric
Add Excel 365 spreadsheet data to journey-map metric cards by picking a workbook, sheet, and the columns to plot.
Turn a column of Excel 365 data into a live chart on any journey map.
Prerequisites
The Excel 365 integration is set up at the account level. See How to set up the Excel 365 integration.
Editor role at the workspace level.
Add an Excel 365 metric
You can create an Excel 365 metric from two places:
From the workspace Metrics tab - Click Metrics in the sidebar, then + Create metric. The full-page Add metric form opens.
From a journey map - Click + Add card on a card slot and pick Metric under Advanced cards. In the Add metric picker, pick an existing metric or type a name and click Create. The metric opens in a compact modal; use Full screen view to switch to the full-page form.
The steps below walk the workspace-tab flow because the full-page form shows every field on screen. The journey map modal has the same fields in a more compact layout.
Name the metric and pick Excel 365 as the source
In the workspace sidebar, click Metrics, then + Create metric. Type a name that describes what the metric shows (for example, Annual salary by employee), then open the Source dropdown and pick Excel (Office 365).
The source is labelled Excel (Office 365) in this dropdown, even though the integration is listed as Office 365 Excel on the account settings page. They are the same connection.
Connect Excel 365 first if you see the setup gate
If a pink Office365 setup required block appears below the Source field, the integration isn't connected for this account yet. The metric fields stay hidden until it is.
Click Setup Office365 in the block and follow How to set up the Excel 365 integration. Once connected, return here and the spreadsheet fields appear.
Set the metric type
Open the Type dropdown and pick the type that fits what you're showing: Series for a column of values plotted as a chart, Number for a single headline figure, or Comparison for two values side by side. The fields below depend on the type you pick; this guide walks Series, the typical choice for charting a column from a sheet.
Pick the workbook and sheet
Next to Spreadsheet, click Change to choose an Excel file from your connected Office 365 account. Then pick the relevant tab from the Sheet dropdown.
If your data has labelled columns in the first row, check Make top row headers. This treats the first row as column names, which is what populates the Data label and Data values pickers in the next step.
Map the label and value columns
Two columns drive the chart:
Data label - The column holding the categories or labels for the metric (for example, a
Full Namecolumn).Data values - The numeric column to plot (for example, an
Annual Salarycolumn).
Set how many rows to pull
Under Number of rows, set how many rows to include. The maximum is 50, so a larger sheet is truncated to the first 50 rows. Check Select bottom rows to take the last rows instead of the first, which is useful when the most recent entries are appended at the bottom of the sheet.
Pick chart options and save
In the Default card preview panel on the right, pick a default chart type (Bar chart, Horizontal bar, Pie chart, Line chart, or Table) and add an optional Chart heading and Chart subheading. The preview updates as you change selections. These are the defaults for new cards built from this metric; each card can override them on a journey map. For the full chart-options reference, see How to create and configure a metric.
Click Save. The metric appears in the workspace Metrics list and is ready to drop onto any journey map as a metric card. For placing and customising the card per-map, see How to use metric cards.
Field mapping
Excel 365 metrics are column-based: you pick the columns to chart, not a cell range like A1:B10. Each field in the Add metric form maps to part of your workbook.
Spreadsheet - The Excel workbook, chosen with the Change button from your connected Office 365 account. This is a file picker, not a typed path.
Sheet - The tab within that workbook, picked from a dropdown of the file's sheets.
Make top row headers - Treats the first row as column names. Check this when your sheet has a header row so the label and value pickers show meaningful column names.
Data label - The column whose values become the categories on the chart (the x-axis on a bar or line chart, the slice labels on a pie chart). Example: a
Full Namecolumn.Data values - The numeric column whose values are plotted. Must be numeric to render as a chart; a text column can only display as a table. Example: an
Annual Salarycolumn.Number of rows - Caps how many rows the metric pulls. Maximum 50, taken from the top of the sheet, or from the bottom if Select bottom rows is checked.
There is no aggregation step: the metric plots the rows as they are, up to the row cap. To show a summarised figure, pre-summarise the data in Excel and point the metric at the summary column.
Refresh behaviour
Excel 365 metrics refresh periodically, on the same rough hourly cycle as other metrics integrations rather than streaming live. When a journey map containing the metric loads, Smaply pulls fresh values if the last update was more than about an hour old. There's no manual refresh button.
For how integrations behave across tools, including disconnecting and switching auth, see How to manage integrations.
Troubleshooting
If the connection itself isn't working, see How to set up the Excel 365 integration for connection troubleshooting. The issues below cover data-side problems once the connection is in place.
"Office365 setup required" block in the metric form - Connection not configured
Picking Source = Excel (Office 365) shows a pink block reading "You need to set up the Office365 connection in this workspace before you can use it in metrics."
The connection hasn't been configured for this account, or it was disconnected. Click Setup Office365 in the block to jump to the integrations page, then follow How to set up the Excel 365 integration.
The chart is empty or shows a table instead of bars - Data values column isn't numeric
A chart needs a numeric column under Data values. If you mapped a text column there, the metric can only render as a table.
Edit the metric and confirm Data values points at a numeric column. If the column looks numeric in Excel but doesn't chart, check it isn't stored as text in the source sheet (a common cause is numbers formatted as text or with stray characters).
Some rows are missing from the chart - Row cap or row direction
A metric pulls at most 50 rows. If your sheet has more, only the first 50 are included, or the last 50 if Select bottom rows is checked.
For larger sheets, narrow the data in Excel to the rows you care about, or pre-summarise so the meaningful rows fit within the 50-row cap. Use Select bottom rows when the most recent entries sit at the bottom of the sheet.
A metric that worked before stopped pulling data - Workbook or sheet changed
If the workbook was renamed, moved, or deleted in Office 365, or the sheet or header columns were renamed, the metric's references break.
Edit the metric, click Change to re-select the workbook, re-pick the Sheet, and re-map Data label and Data values, then save. If the file is no longer reachable from your connected account, confirm Office 365 access in How to set up the Excel 365 integration.
Still not working? Contact support at help@smaply.com
Related topics
Last updated