How to Use AI for Execution, Not Strategy: Excel Macros That Automate Repetitive Work Without Replacing Decisions

Stop letting AI make the call: use it to do the work, not replace the decision

Spreadsheets are the beating heart of UK small businesses — and they’re where repetitive work, error-prone cleanups and one-off formula fixes waste hours every week. In 2026 most teams treat AI as a productivity engine, not a strategist: studies show ~78% see AI as executional help while only a sliver trust it for positioning or big-picture decisions. That split is your permission to build workflows that leverage AI for suggestions and automation, but enforce human sign-off before any decision changes are applied.

Why this matters now (late 2025 → 2026)

By the end of 2025 enterprise tools and Excel add-ins started shipping tighter integrations with large language models and API-powered suggestions. That acceleration makes automation tempting — but also increases the risk of incorrect transformation logic and “silent” errors. ZDNet’s January 2026 coverage and the 2026 State of AI in B2B Marketing report show the trend: teams will adopt AI for execution, but governance and auditability are critical if you want to keep productivity gains without doubled-cleanup work.

Quick take: Use AI to propose formulas and cleaning steps, not to apply them. Build approval flags, an audit sheet and automated logs so humans stay in control.

Design principles: how to combine AI, VBA and Power Query safely

1. AI = Suggestion Engine — treat model output as a recommended change, not authoritative truth.
2. Human Approval Gate — every suggestion must carry a flag, approver name and timestamp before being applied.
3. Immutable Audit Log — log who requested the change, the suggested change, the original value and who approved it.
4. Reproducible Recipes — store the exact prompt, model version and sample input alongside the suggestion.
5. Fail-Safe Defaults — never overwrite original data automatically; write suggestions to a staging area.

Practical pattern: three-layer architecture

Use a reproducible, lightweight structure across workbooks:

• Raw Data sheet — never edited directly.
• AI_Suggestions sheet — holds AI generated formulas, transformations and a PendingApproval flag.
• AuditLog / Approvals sheet — immutable log of actions, approvals and versioning.

VBA snippets: request AI suggestions, stage them, require approvals

Below are practical VBA snippets you can drop into a workbook. They show the lifecycle: ask AI for a formula, write the suggestion to an AI_Suggestions table, and only apply when ApprovedBy is set. Replace the placeholder API call with your chosen service or Excel Copilot hook.

1) RequestSuggestions: send sample to AI and write suggestions

This macro samples a column, sends it to an AI model (API call placeholder), and writes back suggested formula text into the AI_Suggestions sheet with a Pending flag.

Sub RequestAISuggestions()
    ' Assumes sheets: RawData, AI_Suggestions
    Dim src As Worksheet, dst As Worksheet
    Set src = ThisWorkbook.Worksheets("RawData")
    Set dst = ThisWorkbook.Worksheets("AI_Suggestions")

    Dim sampleRange As Range
    Set sampleRange = src.Range("A2:A101") ' sample first 100 rows

    Dim sampleText As String
    sampleText = Join(Application.Transpose(sampleRange.Value), "\n")

    ' BUILD PROMPT
    Dim prompt As String
    prompt = "Given these sample values, suggest an Excel formula or Power Query step to clean them. " & _
             "Return only the formula or single-line instruction. Samples:\n" & sampleText

    ' CALL YOUR AI SERVICE - placeholder
    Dim suggestion As String
    suggestion = CallAiService(prompt) ' Implement API call function securely

    ' Append to AI_Suggestions table
    Dim nextRow As Long
    nextRow = dst.Cells(dst.Rows.Count, 1).End(xlUp).Row + 1

    dst.Cells(nextRow, 1).Value = Now()           ' RequestedAt
    dst.Cells(nextRow, 2).Value = prompt          ' Prompt
    dst.Cells(nextRow, 3).Value = suggestion      ' SuggestionText
    dst.Cells(nextRow, 4).Value = "Pending"     ' Status
    dst.Cells(nextRow, 5).Value = ""            ' ApprovedBy
    dst.Cells(nextRow, 6).Value = ""            ' ApprovedAt

    MsgBox "Suggestion received and staged. Review in AI_Suggestions."
  End Sub

  ' Placeholder: implement secure API call
  Function CallAiService(ByVal prompt As String) As String
    ' Use WinHttpRequest or MSXML2.XMLHTTP to call OpenAI or your provider.
    ' Store keys in workbook query parameters, environment variables or Azure Key Vault.
    CallAiService = "=TRIM(SUBSTITUTE(TEXTJOIN(CHAR(10),TRUE, 'AI_FORMULA()'),""" & """ ,""""))" ' demo
  End Function

2) ApplySuggestionWithApproval: only apply once approved

Run this macro to apply a suggestion row to the target sheet, but it checks the status and records the action in an AuditLog. It prevents accidental overwrites.

Sub ApplySuggestionWithApproval(suggestionRow As Long)
    Dim dst As Worksheet, logWS As Worksheet, sugWS As Worksheet
    Set sugWS = ThisWorkbook.Worksheets("AI_Suggestions")
    Set dst = ThisWorkbook.Worksheets("RawData")
    Set logWS = ThisWorkbook.Worksheets("AuditLog")

    Dim status As String
    status = UCase(Trim(sugWS.Cells(suggestionRow, 4).Value))

    If status <> "APPROVED" Then
      MsgBox "Suggestion not approved. Set Status to 'Approved' and enter your name in ApprovedBy first.", vbExclamation
      Exit Sub
    End If

    Dim suggestionText As String
    suggestionText = sugWS.Cells(suggestionRow, 3).Value

    ' Example: if suggestion is a formula for column B row 2, write formula into B2
    ' To keep it safe, write into staging and let user review, or change logic accordingly.
    dst.Range("B2").Formula = suggestionText

    ' Log the change
    Dim nextLog As Long
    nextLog = logWS.Cells(logWS.Rows.Count, 1).End(xlUp).Row + 1
    logWS.Cells(nextLog, 1).Value = Now()
    logWS.Cells(nextLog, 2).Value = Application.UserName
    logWS.Cells(nextLog, 3).Value = suggestionText
    logWS.Cells(nextLog, 4).Value = sugWS.Cells(suggestionRow, 5).Value ' ApprovedBy
    logWS.Cells(nextLog, 5).Value = sugWS.Cells(suggestionRow, 6).Value ' ApprovedAt

    MsgBox "Suggestion applied and logged."
  End Sub

3) ApproveSuggestion: quick UI helper

A small helper to mark a suggestion approved and stamp the approver name.

Sub ApproveSuggestion(suggestionRow As Long)
    Dim sugWS As Worksheet, approver As String
    Set sugWS = ThisWorkbook.Worksheets("AI_Suggestions")

    approver = Application.UserName
    sugWS.Cells(suggestionRow, 4).Value = "Approved"
    sugWS.Cells(suggestionRow, 5).Value = approver
    sugWS.Cells(suggestionRow, 6).Value = Now()

    MsgBox "Suggestion marked Approved by " & approver
  End Sub

Power Query recipes: stage AI suggestions and only merge approved edits

Power Query is great for deterministic transforms, but you still need governance when suggestions come from models. The pattern below uses two tables loaded to sheets: AI_Suggestions (staged) and ApprovedEdits (user-managed approvals). The Power Query output applies only approved edits.

Recipe: Build a suggested-clean view and an approved-clean view

1. Load RawData as a Power Query table called RawData.
2. Load AI_Suggestions (columns: RowID, FieldName, SuggestedValue, Prompt, Status, SuggestedAt).
3. Load ApprovedEdits (same schema but filled by user when approving).
4. In Power Query, left-join RawData to ApprovedEdits and produce FinalData where ApprovedEdits.SuggestedValue replaces RawData.FieldName only when not null.

Power Query M example: merge approved edits

let
    // Load tables
    Raw = Excel.CurrentWorkbook(){[Name="RawData"]}[Content],
    Approved = Excel.CurrentWorkbook(){[Name="ApprovedEdits"]}[Content],

    // Normalize: ensure keys align
    RawWithKey = Table.AddIndexColumn(Raw, "RowID", 1, 1),

    // Pivot Approved edits into a wide table keyed by RowID
    ApprovedPivot = Table.Pivot(Table.TransformColumnTypes(Approved, {"RowID", Int64.Type}), List.Distinct(Approved[FieldName]), "FieldName", "SuggestedValue"),

    // Join
    Joined = Table.NestedJoin(RawWithKey, "RowID", ApprovedPivot, "RowID", "Approved", JoinKind.LeftOuter),

    // Replace values when approved values exist
    ReplaceStep = Table.TransformColumns(Joined, List.Transform(Table.ColumnNames(Raw), each {_, (v) =>
      let approved = Record.FieldOrDefault([Approved]{0}, _, null)
      in if approved <> null and approved <> "" then approved else v
    }))
  in
    ReplaceStep

Note: Power Query can call web APIs. If you choose to call an AI endpoint from Power Query, do it from a controlled environment and securely store keys. Many organisations now use an intermediate microservice (Azure Function / internal API) to control request volume and logging before touching AI endpoints.

Audit log structure (practical schema)

Keep the audit table simple and immutable. Here's a practical set of columns to capture every change lifecycle:

• LogID — GUID or incremental ID
• RequestedAt — timestamp
• RequestedBy — user who requested the AI suggestion
• Prompt — exact prompt used (including model version)
• SuggestionText — AI output
• Status — Pending / Approved / Rejected / Applied
• ApprovedBy and ApprovedAt
• AppliedBy and AppliedAt
• BeforeValue and AfterValue
• Checksum — optional hash of original row for immutability

Reproducible workflow: step-by-step for your team

1. Analyst clicks "Request AI Suggestions" (VBA) for a column or dataset. This writes a request to AI_Suggestions and the AuditLog with Status=Pending.
2. AI returns suggestions into AI_Suggestions. No changes to RawData are made.
3. Reviewer inspects suggestions in AI_Suggestions, edits the suggested text if needed, and either marks Approved or Rejected (using the ApproveSuggestion macro), adding a short rationale.
4. When Approved, either the user runs ApplySuggestionWithApproval for fine-grained actions, or a separate batch macro applies approved suggestions to a staging sheet for a final review.
5. All applied edits are recorded in AuditLog with before/after snapshots. Keep a read-only archival copy of the AuditLog daily.

Advanced strategies & integrations (2026 trends)

In 2026 we see a few practical patterns emerging across high-performing teams:

• AI Suggestion Fingerprinting — record the model version and prompt hash; this helps reproduce the same suggestion later if audits require it.
• Automated email approvals — integrate macros with Outlook to send a one-click approval link that fills the ApprovedBy cell (use secure tokens). See Inbox automation patterns for ideas.
• Power Automate / Teams notifications — notify stakeholders when high-risk suggestions are staged for approval. These notification flows are a small operational win but require governance hooks.
• Separation of duties — the requester cannot be the approver for critical flows; enforce with VBA checks and user lists.

Common pitfalls and how to avoid them

• Automatic overwrites: Never let an AI-powered macro replace RawData without explicit approval. Always stage.
• Insufficient prompts: Save prompts with the suggestions — you’ll need them if suggestions look odd later.
• Lack of ownership: Assign approvers for each dataset; use VBA to validate approver is on the authorised list.
• Audit gaps: Store before/after snapshots or row checksums so you can detect undisclosed changes. See provenance & logging patterns.

Real-world example: sales CSV cleanup with AI-suggested formulas

Scenario: monthly sales CSV contains inconsistent date formats, stray currency symbols and a few missing postcodes. The team uses AI to propose the cleaning formula for the Date and Currency columns, and Power Query to stage the cleaned view.

1. Analyst runs RequestAISuggestions for Date column — AI suggests `=IFERROR(DATEVALUE(TRIM(A2)), "Check")` and a regex-based cleanup idea.
2. Suggestion saved in AI_Suggestions with Status=Pending.
3. Reviewer opens suggestions, tweaks the formula for UK formats, and approves with comments: "Use dd/mm/yyyy fallback".
4. ApplySuggestionWithApproval writes formula into staging, and AuditLog records original date strings and the applied formula, approved by the Head of Ops.
5. Power Query reads staging and merges approved changes into FinalReport. The report loads to the BI tool only after QA sign-off.

Checklist before you automate with AI

• Do you have an AuditLog sheet capturing prompt, model and user actions?
• Are suggestions staged, not auto-applied?
• Is there a defined approver role for each dataset?
• Do you periodically export an immutable archive of audit logs?
• Have you limited the requester’s ability to self-approve high-risk edits?

Final takeaways: use AI to scale execution, and governance to protect decisions

Through late 2025 and into 2026 the pragmatic approach is clear: teams who win are those that let models handle repetitive execution while people retain decision authority. Build lightweight, reproducible patterns in Excel — staging sheets, approval flags, and an immutable audit log — and glue them with VBA and Power Query. This lets you capture the speed advantages of AI without introducing systemic risk.

Actionable next steps:

1. Clone the workbook template: create RawData, AI_Suggestions and AuditLog sheets.
2. Install the three VBA macros and test against a small sample.
3. Wire a Power Query that merges ApprovedEdits into a FinalData view.
4. Run an approval drill: stage 5 suggestions and practice approval and rollback.

Want the templates and example macros?

We built a compact workbook with the sheets, macros and a sample Power Query you can drop into your process. Click through to our templates library for an immediate download, or book a short coaching session to adapt this governance pattern to your reports.

Call to action: Download the free AI-suggestions + audit workbook from excels.uk/templates, try the macros on a copy of your report, and sign up for a 30-minute setup call to make it production-ready with your team’s approval rules.

References: Move Forward Strategies (2026 State of AI in B2B Marketing), MarTech (Jan 2026), ZDNet (Jan 2026 coverage on cleaning up after AI). Use these sources to justify governance in board-level conversations — they back a practical distinction: AI for execution, humans for strategy.

Related Reading

• Field Report: Spreadsheet-First Edge Datastores for Hybrid Field Teams (2026)
• Top 10 Prompt Templates for Creatives (2026)
• Practical Playbook: Responsible Web Data Bridges in 2026
• Zero-Downtime Release Pipelines & Quantum-Safe TLS: A 2026 Playbook
• Edge-First Model Serving & Local Retraining: Practical Strategies (2026)
• ABLE Accounts Expanded — Can Beneficiaries Safely Hold Crypto Without Losing Benefits?
• Complete List: How To Unlock Every Lego and Splatoon Item in Animal Crossing 3.0
• Quick Guide: Buddy Buying for Bulk VistaPrint Savings — When Group Orders Make Sense
• Designing Offline Printable Transfer Sheets for Multimodal Journeys
• Affordable CRM Tools That Save Time for Busy Grocery Operators