Share via

Reconcile data with Copilot for Finance (preview)

  • Article

[This article is prerelease documentation and is subject to change.]

This article describes how to reconcile data by using Microsoft 365 Copilot for Finance.

Data reconciliation in Copilot for Finance simplifies the process of reconciling two data sets in Excel workbooks. You can define the data to reconcile, use AI to determine the reconciliation rules, and benefit from a comprehensive report that provides a generative AI summary of the results of the reconciliation process.

Start working with Copilot for Finance in Excel

To start working with Copilot for Finance in Excel, follow these steps.

  1. Open an Excel workbook that contains the data that you want to compare. Copilot lets you reconcile two data sets that are represented as tables, and that reside in one or two worksheets in Excel.

  2. Go to Home > Copilot to open the Copilot for Finance sidecar.

    Note

    If you don't see the Copilot symbol, or if it's unavailable, follow the instructions in Install Copilot for Finance (business user-deployed).

Select data for reconciliation

To start the process and select data for reconciliation, follow these steps.

  1. In the sidecar, select Reconcile data.
  2. Select the worksheets and tables to reconcile.

Note

The worksheets and table references are populated according to their order in the workbook.

Define the reconciliation vectors

After the worksheets and tables are specified, select Next to trigger AI-aided analysis of the data structures and suggest reconciliation rules.

Before the reconciliation can be done, at least two columns must be selected for each table:

  • Mapping key type – This column is used to find matching records.
  • Monetary column – This column is used to compare values.

You can use Copilot-suggested parameters or user-defined parameters.

To define the reconciliation vectors, follow these steps.

  1. Review the Copilot-suggested rules. For each rule, select one of the following options for interacting with AI-generated suggestions:

    • Keep it – Accept the suggestions, and open Edit mode for the rules.
    • Regenerate – Rerun the data structure analysis to have Copilot suggest other possible sets of columns.
    • Remove all suggestions – Clear the suggested parameters, and open Edit mode.

    Note

    If you want to skip reconciliation vector editing and proceed with reconciliation based on AI-suggested criteria, select Next.

  2. In Edit mode, select Add column to add reconciliation parameters, and select a type. A pair of parameters is added to the tables so that you can select the columns.

  3. Use the dropdown list to select columns for each table.

    Screenshot of Reconcile data, where a column is being selected for a table.

  4. Remove unwanted columns as you require.

    Screenshot of Reconcile data with the remove buttons highlighted.

    Note

    If you remove a column parameter from one table, the corresponding column is removed from the other table.

  5. To start the data reconciliation process after all parameters are defined, select Next.

Reconciliation report

The reconciliation report shows the results of the reconciliation process.

Screenshot of a reconciliation report.

All transactions are compared according to the given parameters and classified into three categories:

  • Unmatched transactions

    • Mapping keys match, but there's a discrepancy in monetary values.
    • The transaction doesn't have a corresponding match in the other table.

  • Potentially matched transactions

    • Mapping keys and monetary values are equal, but more than one transaction in one or both data sets sums up to the matched monetary values. All one-to-many (1:n), many-to-one (n:1), and many-to-many (n:m) combinations are shown.

  • Matched transactions (perfectly matched)

    • Values in all selected columns that were given as reconciliation parameters are equal for a single transaction in each table.

Generative AI analyzes the reconciliation results and, based on the transaction types, provides insights and suggestions for addressing inconsistencies in the report summary.

Select Regenerate to rerun the generative AI-driven analyses for the reconciliation report and provide a new summary.

To generate a downloadable PDF version of the summary report, select Save as PDF.

Note

To view a PDF of the reconciliation report on the screen, use the Excel Print to PDF function.

Use suggested actions

After the report summary is generated, you can use a suggested action.

To copy the summary that's available in the sidecar into the body of the report, select Include the summary in the report.

To get transaction details together with a troubleshooting guide for a single row of a reconciliation report, select Troubleshoot a transaction. In this mode, select the rows for any unmatched or potentially matched transactions on the report to get additional information. Select Back to return to the Report summary page.