Use Excel's AI to Analyze Compliance Testing Data

What This Does

Excel Copilot lets you analyze compliance testing data — calculating exception rates, identifying trends across testing periods, and generating summary tables — using plain English instead of complex formulas.

Before You Start

• You have Microsoft 365 with Copilot enabled
• Your compliance testing data is in an Excel table (with column headers)
• Data is formatted as a proper Excel Table (Insert → Table)

Steps

1. Format your data as a Table

Select your testing data range. Go to Insert → Table and check "My table has headers." This allows Copilot to reference column names in its analysis.

2. Open Copilot

Click the Copilot button in the Home ribbon (sparkle icon, far right). A Copilot panel opens on the right.

3. Ask for your analysis in plain English

Type your analysis request. Examples:

• "What is the exception rate by exception category?"
• "Show me a trend of exception rates across the last 4 quarters"
• "Which branch has the highest exception rate this quarter?"

4. Review the output

Copilot will either generate a formula in your spreadsheet or display the analysis in the panel. For charts, it may ask if you want to insert one — click Add to sheet.

5. Ask follow-up questions

Continue the conversation: "Now show me just the exceptions where the root cause was 'missing documentation'" or "Create a summary pivot of exceptions by examiner area."

Real Example

Scenario: You have a workpaper with 200 consumer compliance testing results across 5 examination areas.

What you type: "Calculate the exception rate for each examination area and show which area has the most exceptions. Create a bar chart of exception rates by area."

What you get: A calculated exception rate table and an auto-generated bar chart showing that consumer loan disclosure exceptions are your highest-rate finding — information that used to require 30 minutes of manual formula work.

Tips

• Make sure your column headers are descriptive (e.g., "Exception Category" not "Column C") — Copilot uses these to understand the data
• Save your workpaper before making Copilot-generated additions — changes can be difficult to fully undo
• Export the summary Copilot generates as a separate worksheet to use in your board report

Tool interfaces change — if a button has moved, look for similar AI/magic/smart options in the same menu area.