# Chapter 8 — AI + Excel

# ── PROMPT 1: Copilot-style formula generation ──────────────────
I have a sales table in Excel named tblSales with columns:
date, customer, region, product, category, quantity, unit_price, total, status, sales_rep

Write a structured table formula (using the table name) that:
Calculates the total revenue for Delivered orders in the North region.
Use SUMIFS.

# ── PROMPT 2: Power Query via prompt ────────────────────────────
I have 4 Excel files: sales_q1.xlsx, sales_q2.xlsx, sales_q3.xlsx, sales_q4.xlsx
Each has a sheet called "Data" with the same column structure.

Write step-by-step instructions to combine them using Power Query (Get Data)
without any code. I am using Excel 365.

# ── PROMPT 3: VBA from description ──────────────────────────────
Write a VBA macro that:
1. Opens a FileDialog to let the user select a folder
2. Loops through all .xlsx files in that folder
3. Opens each file, reads the "total" column (column H), sums it
4. Closes each file without saving
5. Writes a summary to the active sheet: filename | row count | total revenue
6. Adds a grand total row at the bottom
7. Formats the total column as €#,##0.00

# ── PROMPT 4: Pivot Table interpretation ────────────────────────
I have a Pivot Table in Excel showing:
Rows: region | Columns: status | Values: Sum of total

The data shows:
              Delivered   Shipped    Pending    Cancelled   Grand Total
East          €234,567    €45,234    €23,456    €12,345     €315,602
North         €312,890    €56,789    €34,567    €8,901      €413,147
South         €198,234    €34,567    €19,012    €15,678     €267,491
West          €147,456    €28,901    €14,234    €11,234     €201,825

Interpret this data. What are the 3 most important insights?
What action should the Sales Director take based on this data?
