A.I. Use Cases for Excel, VBA and Power Query Workflows

In many cases, the best architecture is to leave Excel, VBA and Power Query doing what they already do well — calculations, structured data processing, workflow control and reporting — while adding A.I. at specific points where conventional programming struggles with language, interpretation, classification or judgement.

The A.I. capability can sit behind a secure API rather than being embedded directly into the workbook.

For XLS Experts solutions, that API could sit within the Google Cloud ecosystem and manage authentication, prompt construction, data validation, calls to OpenAI or another approved model, logging and response handling.

The following five use cases demonstrate different ways that architecture can be applied.

Many Excel reports contain plenty of numbers but still require somebody to interpret what those numbers mean.

A finance team may produce a monthly variance report containing hundreds of accounts. A sales manager may receive a table showing revenue, margin and year-on-year movements by customer. An operations team may have service performance data by branch, product or employee.

Excel can identify that a number has changed.

A.I. can help explain what deserves attention and how to describe it in plain English.

Example

Consider a management reporting workbook containing:

Traditional Excel formulas can calculate:

• Actual versus budget
• Percentage variance
• Prior-year movement
• Gross margin
• Volume movement
• Threshold breaches

A.I. could then generate commentary such as:

Auckland materially exceeded both budget and prior year, with revenue 6.6% above budget and stronger volume. Margin remains healthy at 31%, suggesting growth has not been achieved through excessive discounting.

For Wellington:

Wellington is 7.9% below budget and 4.6% below the prior year. The combination of weaker revenue and a 24% margin warrants investigation, particularly if the decline is concentrated in specific customers or product groups.

Instead of management manually writing commentary for 30 branches every month, A.I. creates a first draft in seconds.

Potential Technical Architecture

A practical implementation using Power Query could work as follows:

1. 1

   Excel creates the analytical dataset

   Existing formulas, VBA or Power Query produce a clean table containing the metrics the A.I. needs.

   A.I. should generally not be asked to recalculate values that Excel already calculates reliably.

2. 2

   Power Query packages the dataset

   Power Query converts the required rows into JSON. For example:

   {
     "period": "July 2026",
     "business_units": [
       {
         "name": "Auckland",
         "actual": 842000,
         "budget": 790000,
         "prior_year": 765000,
         "margin": 0.31,
         "volume": 4220
       }
     ]
   }

3. 3

   Power Query calls the XLS Experts API

   • The workbook sends an HTTPS request to a secured Google Cloud endpoint.
   • The workbook does not need to contain the OpenAI API key.
   • Authentication can instead be handled between the workbook and our API.
4. 4

   The API prepares the A.I. request

   The cloud service adds controlled instructions such as:

   • Analyse performance only from supplied data
   • Do not invent explanations that cannot be supported by the data
   • Identify material movements
   • Distinguish facts from possible causes
   • Write for a senior management audience
   • Return one summary per business unit
   • Return structured JSON
5. 5

   A.I. returns structured results

   For example:

   {
     "business_units": [
       {
         "name": "Auckland",
         "status": "Positive",
         "priority": "Medium",
         "summary": "...",
         "key_observations": [
           "...",
           "..."
         ]
       }
     ]
   }

6. 6

   Power Query expands the response

   Power Query converts the JSON back into rows and columns. The resulting table can then feed:

   • Management reports
   • Dashboard commentary
   • Board packs
   • Monthly reporting templates
   • Exception reports
   • Automated emails

Important Design Principle

The A.I. should interpret the data rather than become the calculation engine.

For example, Excel should calculate that revenue is 7.9% below budget. A.I. should explain whether that movement appears noteworthy in the context provided.

This separation makes the workflow considerably more auditable.

Additional Possibilities

The same architecture can produce:

• One-line commentary per row
• Detailed commentary only for exceptions
• Executive summaries covering the entire dataset
• Risk classifications
• Suggested questions for management to investigate
• Positive and negative performance highlights
• Commentary written differently for management, customers or operational teams

The workbook remains an Excel reporting solution. A.I. simply adds a new interpretation layer that would traditionally require significant human effort.

A large amount of business information enters organisations through documents rather than databases.

Examples include:

• Supplier quotations
• Purchase orders
• Customer emails
• Insurance claims
• Engineering specifications
• Tender documents
• Statements
• Contracts
• Application forms
• Inspection reports
• PDF schedules
• Delivery documents

Employees then manually read those documents and type selected information into Excel. That is precisely the type of workflow where A.I. can sit between an unstructured document and an existing Excel process.

Example — Supplier Quotation Processing

An engineering business requests quotes from several suppliers. Each supplier responds differently. One PDF may state:

UB 310 x 46.2 — 12 metres — Qty 6 — $1,287.50 each
Hot-dip galvanising additional $145.00 per item
Freight Auckland $385
Quote valid 30 days.

Another supplier may send the same information in an email using completely different terminology. The company's Excel workbook needs columns such as Supplier, Product, Length, Qty, Unit Price, Galv, Freight and Valid Until.

Historically somebody reads every quote and rekeys the information. A.I. can convert the documents into that structure.

Potential Technical Architecture

This workflow is often best controlled through VBA.

1. 1

   User selects the source material

   An Excel interface could allow users to select one or more PDF files, an email attachment directory, paste email text, or select files from a network location.

   VBA sends the documents or extracted text to the cloud API. Alternatively, the process may be triggered outside Excel and Excel simply retrieves the completed dataset later.

2. 2

   Cloud service extracts document content

   Depending on document type, the API layer may use:

   • Native document text
   • PDF parsing
   • OCR where required
   • Email body extraction
   • Spreadsheet parsing

   The extracted content is passed to the A.I. model along with a strict schema.

3. 3

   A.I. maps the document into the required fields

   Instead of returning prose, the model is instructed to return something such as:

   {
     "supplier": "ABC Steel",
     "quote_number": "Q-44892",
     "quote_date": "2026-08-04",
     "valid_until": "2026-09-03",
     "currency": "NZD",
     "items": [
       {
         "description": "UB 310 x 46.2",
         "length_mm": 12000,
         "quantity": 6,
         "unit_price": 1287.50
       }
     ],
     "galvanising": 870,
     "freight": 385
   }

4. 4

   API validates the response

   Before Excel receives anything, conventional program logic can validate:

   • Required fields exist
   • Dates are valid
   • Numbers are numeric
   • Quantity × unit price calculations reconcile where appropriate
   • Currency is recognised
   • Duplicate quote numbers have not already been processed
5. 5

   VBA writes the records into the existing workbook

   The data may populate:

   • Quote comparison worksheets
   • Procurement models
   • Estimating systems
   • Accounts payable templates
   • Material schedules
   • Project costing worksheets

   The user's downstream process can remain unchanged.

A.I. Can Also Preserve Source Evidence

A particularly useful implementation is to return not only the extracted value but its evidence. For example:

{
  "field": "freight",
  "value": 385,
  "source_text": "Freight Auckland $385",
  "confidence": 0.98
}

The Excel interface can then allow the user to review questionable records.

Resolving Ambiguous Information

Suppose a quote says: “Delivery approximately 3 weeks from order.” There is no fixed delivery date. Rather than inventing one, the A.I. can return:

{
  "delivery_date": null,
  "delivery_terms": "Approximately 3 weeks from order",
  "requires_review": true
}

This is an important difference between a controlled business application and simply asking ChatGPT to read a document.

Broader Applications

The same pattern could be used to extract:

A.I. becomes a translation layer between the documents the business receives and the structured Excel system it already uses.

Some Excel applications become substantial business systems. They can contain numerous worksheets, thousands of records, PivotTables, reports, VBA interfaces, configuration tables, pricing calculations, project information and historical transactions.

Experienced users may understand them well. Occasional users often do not. A.I. can provide a conversational layer over the application without replacing the underlying workbook.

Example

Consider an Excel-based project costing application. Instead of navigating several worksheets, a project manager could type:

Which projects are currently forecast to exceed their approved labour budget?

The application gathers the relevant structured data and sends it to the A.I. service. The response might be:

Three active projects are currently forecast above approved labour budget:

• Project 4482 — forecast 18% over budget
• Project 4511 — forecast 11% over budget
• Project 4467 — forecast 7% over budget

Project 4482 represents the largest exposure, with approximately $38,400 of forecast labour overspend.

The user could then ask “Why is 4482 running over?” and receive:

The principal driver is installation labour. Actual hours are already at 82% of budget while the project is only 61% complete. Drafting and procurement remain close to budget.

The Important Architectural Constraint

The A.I. should not receive the entire workbook and be expected to understand everything. Instead, VBA acts as the application controller.

1. 1

   User asks a question

   A worksheet or VBA UserForm contains an “Ask the workbook” interface.

2. 2

   VBA sends the question to the cloud API

   {
     "workbook": "Project Costing",
     "user_question": "Which projects are forecast over labour budget?"
   }

3. 3

   A.I. determines what information is required

   The service may classify the request as:

   {
     "intent": "project_labour_variance",
     "required_dataset": "active_project_cost_summary"
   }

4. 4

   VBA retrieves the approved dataset

   Rather than giving A.I. unrestricted access, Excel sends a predefined table containing:

   • Project number
   • Project name
   • Labour budget
   • Labour actual
   • Forecast labour
   • Project completion percentage
5. 5

   A.I. analyses only that data

   The model generates the answer from the controlled dataset.

6. 6

   Excel displays the response

   The response can appear:

   • In a worksheet panel
   • In a VBA UserForm
   • In a task-oriented report
   • As generated commentary
   • As a new worksheet containing supporting records

A.I. Can Trigger Existing VBA Functions

The assistant could eventually become more than a reporting interface. A user might ask: “Prepare the monthly project variance report for Auckland.” The A.I. interprets the instruction:

{
  "action": "generate_variance_report",
  "region": "Auckland",
  "period": "current_month"
}

VBA then executes the existing approved procedure. The model does not write arbitrary VBA and execute it. Instead, it selects from controlled actions such as generate report, refresh data, filter project, create PDF, draft commentary, find customer or display exceptions. This is substantially safer.

Natural Language Search

The same mechanism is extremely useful for locating information. A user could ask: “Find the order where the customer complained about damaged packaging sometime around March.” Traditional Excel search requires knowing precisely what to search for. A.I. can interpret approximate language and identify likely records.

Explain Existing Results

Users might also ask “Why is this price $18,420?” The VBA application can collect the calculation components:

Materials      $8,420
Labour         $4,800
Subcontractors $1,600
Freight          $540
Markup          $3,060

A.I. then translates the calculation into an explanation. The calculation still comes from the Excel pricing engine — A.I. simply explains it.

Potential Applications

• Estimating workbooks
• Financial models
• Operational applications
• Inventory systems
• Project management workbooks
• Engineering calculations
• Property development models
• Sales reporting
• Management reporting
• Resource planning tools

Security and Governance

Enterprise implementations can control:

• Which workbook functions A.I. may invoke
• Which datasets each user may access
• How much information is sent externally
• Which A.I. provider is used
• Prompt versions
• API request history
• User identity
• Response logging
• Retention rules

Sensitive identifiers can also be replaced with internal IDs before data leaves the organisation.

The Result

Users continue working inside Excel. Existing formulas, VBA, Power Query and business rules remain intact. A.I. provides a new interface for interpreting information and interacting with the application.

Instead of replacing a mature Excel system, it makes that system considerably easier to use.