Use Excel's AI to Build OTB Formulas and Analyze Sales Data

What This Does

Excel's AI features can generate the complex formulas that power your OTB tracking and sell-through analysis — and explain formulas you've inherited but don't understand. Instead of spending 20–30 minutes debugging a nested IF/SUMIF formula, you describe what you need and get the formula in seconds.

Before You Start

• You have Microsoft 365 (Excel desktop or Excel online)
• Your workbook is open with the relevant data
• For Copilot: Microsoft 365 Business Standard or higher with Copilot add-on
• For "Analyze Data": available in most Microsoft 365 plans — look under the Home tab

Steps

1. Open your OTB or sales analysis workbook

Open the Excel file where you track open-to-buy, sell-through, or weekly sales data. Make sure headers are in row 1 — AI works best with clearly labeled columns.

2. Use "Analyze Data" for quick insights (no Copilot needed)

Click on any cell in your data range. Go to Home tab → Analyze Data (far right of the ribbon). A sidebar appears showing automatically generated charts and insights. Click Insert PivotTable or any suggested chart to add it instantly.

What you should see: A sidebar with "Insights" showing top-performing items, trends, and suggested visualizations based on your data.

3. Use Copilot to generate a formula

If you have Copilot enabled, click the Copilot button in the Home tab. In the chat panel, type what you need: "Create a formula that calculates sell-through rate as units sold divided by units received, but only for rows where column A equals 'ACCESSORIES'." Copilot generates the exact formula and explains it.

4. Ask for formula explanations

Select a cell with a formula you don't understand. Open Copilot and ask: "Explain what the formula in cell F12 does and how to modify it if I want to include returns in the calculation."

What you should see: A plain-language explanation of the formula logic.

5. Generate pivot tables by description

Open Copilot and type: "Create a pivot table showing total sales by vendor, sorted by descending sales." Copilot builds the pivot table on a new sheet.

Real Example

Scenario: You have weekly sales data with columns for: Week, Dept, Vendor, Units Sold, Units Received, Net Sales. You need a sell-through calculation that only applies to the current season's receipts.

What you type into Copilot: "Create a formula for column G called 'Sell-Through %' that divides Units Sold (column D) by Units Received (column E), formatted as a percentage, but only for rows where the Week in column A is greater than or equal to week 1 of this season."

What you get: A complete formula with SUMIF logic and percentage formatting that you can drag down the column.

Tips

• For Analyze Data (no Copilot), format your data as an Excel Table first (Ctrl+T) — it dramatically improves the quality of AI suggestions
• Save frequently asked formula requests as comments in the cell for future reference — e.g., "Generated by Copilot 2026-03-25"
• If Copilot isn't available, copy your question into ChatGPT with "Write me an Excel formula that..." — paste the result back into Excel

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